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.