[Oracle] System 용량 부족 - oracle DB 접속 에러

용량이 꽉차서 , Oracle접속이 안될때..지울게 없을때

DB Full

1. 접속 안됨 ( sql developer도 마찬가지 )

[oracle@devdb ~]$ sqlplus / as sysdba

- 시스템 용량 확인.

[oracle@devdb ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VolGroup-lv_root 50G 47G 0G 100% /

- oracle이 설치된 디렉토리가 용량차지.

[root@devdb /]# du -sh /opt

38G /opt

- 데이타 파일 위치 확인

SQL> select name from v$datafile;

NAME

  --------------------------------------------------------------------------------

  /opt/oracle/oradata/devdb/system01.dbf

  /opt/oracle/oradata/devdb/sysaux01.dbf

  /opt/oracle/oradata/devdb/users01.dbf

  /opt/oracle/oradata/devdb/example01.dbf

  /opt/oracle/product/11gr2/dbs/jms.dbf

  /home/oracle/Data/santa15.dbf

  /opt/oracle/product/11gr2/dbs/santa3_1.dbf

  /opt/oracle/oradata/devdb/undotbs02.dbf

2. dbf 파일 이동.

[root@devdb dbs]# ls rlth /opt/oracle/product/11gr2/dbs

total 12G

-rw-r--r- 1 oracle oinstall 2.8K May 15 2009 init.ora

rw-r---- 1 oracle oinstall 24 Apr 19 2013 lkDEVDB

rw-r---- 1 oracle oinstall 1.5K Nov 10 18:53 orapwdevdb

rw-r---- 1 oracle oinstall 9.6G Dec 11 17:58 santa15.dbf

rw-rw--- 1 oracle oinstall 1.6K Dec 12 09:11 hc_devdb.dat

rw-r---- 1 oracle oinstall 769M Dec 12 09:11 jms.dbf

rw-r---- 1 oracle oinstall 1001M Dec 12 09:11 santa3_1.dbf

rw-r---- 1 oracle oinstall 2.5K Dec 12 09:12 spfiledevdb.ora

- 이중에 santa15.dbf 를 /home/oracle/Data 밑으로 카피.

(1) 디렉토리 생성

[oracle@devdb ~]$ mkdir -p /home/oracle/Data

(2) 파일카피

[oracle@devdb ~]$ cp -rp /opt/oracle/product/11gr2/dbs/santa15.dbf /home/oracle/Data/

(3) 리스너 stop / DB shutdown

[oracle@devdb ~]$ lsnrctl stop

[oracle@devdb ~]$ sqlplus / as sysdba

SQL> shutdown immdiate

(4) startup mount

SQL> startup mount

(5) dbf 위치 변경

SQL> alter database rename file '/opt/oracle/product/11gr2/dbs/santa15.dbf' to '/home/oracle/Data/santa15.dbf';

(6) alter database open

SQL> alter database open ;

(7) 변경 확인

SQL> select name from v$datafile;

NAME

  --------------------------------------------------------------------------------

  /opt/oracle/oradata/devdb/system01.dbf

  /opt/oracle/oradata/devdb/sysaux01.dbf

  /opt/oracle/oradata/devdb/users01.dbf

  /opt/oracle/oradata/devdb/example01.dbf

  /opt/oracle/product/11gr2/dbs/jms.dbf

  /home/oracle/Data/santa15.dbf

  /opt/oracle/product/11gr2/dbs/santa3_1.dbf

  /opt/oracle/oradata/devdb/undotbs02.dbf

- /opt/oracle/product/11gr2/dbs/santa15.dbf 파일 삭제.