Tablespace 관리

SQL> alter tablespace haksa offline;

Tablespace altered.

SQL> !cp /app/oracle/oradata/testdb/haksa01.dbf /app/oracle/disk1/

SQL> !cp /app/oracle/oradata/testdb/haksa02.dbf /app/oracle/disk2/

SQL> select name from v$datafile;

NAME

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

/app/oracle/oradata/testdb/system01.dbf

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

/app/oracle/oradata/testdb/haksa01.dbf

/app/oracle/oradata/testdb/haksa02.dbf

7 rows selected.

SQL> alter tablespace haksa rename

  2  datafile '/app/oracle/oradata/testdb/haksa01.dbf'            

  3  to '/app/oracle/disk1/haksa01.dbf';

Tablespace altered.

SQL> alter tablespace haksa rename

  2  datafile '/app/oracle/oradata/testdb/haksa02.dbf'

  3  to '/app/oracle/disk2/haksa02.dbf';

Tablespace altered.

SQL> select name from v$datafile;

NAME

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

/app/oracle/oradata/testdb/system01.dbf

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

/app/oracle/disk1/haksa01.dbf

/app/oracle/disk2/haksa02.dbf

7 rows selected.

SQL> alter tablespace haksa online;

Tablespace altered.

SQL> 

SQL> @tablespacequery

TABLESPACE_NAME STATUS  CONTENTS  EXTENT_MAN SEGMEN

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

SYSTEM ONLINE  PERMANENT LOCAL      MANUAL

SYSAUX ONLINE  PERMANENT LOCAL      AUTO

UNDOTBS1 ONLINE  UNDO    LOCAL      MANUAL

TEMP ONLINE  TEMPORARY LOCAL      MANUAL

USERS ONLINE  PERMANENT LOCAL      AUTO

EXAMPLE ONLINE  PERMANENT LOCAL      AUTO

HAKSA ONLINE  PERMANENT LOCAL      AUTO

7 rows selected.

SQL> @tablespacesize

TABLESPACE_NAME    MB FILE_NAME

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

USERS    5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 1145 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX  500 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM  680 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE  100 /app/oracle/oradata/testdb/example01.dbf

HAKSA  196 /app/oracle/disk1/haksa01.dbf

HAKSA   20 /app/oracle/disk2/haksa02.dbf

7 rows selected.

SQL> !mkdir /app/oracle/disk3

SQL> 

SQL> 

SQL> 

SQL> 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size    2217952 bytes

Variable Size  490735648 bytes

Database Buffers  335544320 bytes

Redo Buffers    6606848 bytes

Database mounted.

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> @tablespacequery

from dba_tablespaces

     *

ERROR at line 2:

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> !cp /app/oracle/oradata/testdb/system01.dbf /app/oracle/disk3/

SQL> select name from v$datafile;

NAME

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

/app/oracle/oradata/testdb/system01.dbf

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

/app/oracle/disk1/haksa01.dbf

/app/oracle/disk2/haksa02.dbf

7 rows selected.

SQL> alter database rename

  2  file '/app/oracle/oradata/testdb/system01.dbf'

  3  to '/app/oracle/disk3/system01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME

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

/app/oracle/disk3/system01.dbf

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

/app/oracle/disk1/haksa01.dbf

/app/oracle/disk2/haksa02.dbf

7 rows selected.

SQL> alter database open;

Database altered.