00027_1通过表空间移动
SQL> col tablespace_name for a30
SQL> set line 120
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
SQL> select tablespace_name,status,contents from dba_tablespaces where tablespace_name like '%TEST_T%';
TABLESPACE_NAME STATUS CONTENTS
------------------------------ ------------------ ------------------
TEST_TEMP ONLINE TEMPORARY
TEST_TAB ONLINE PERMANENT
SQL> alter tablespace test_tab offline;
Tablespace altered.
SQL> select tablespace_name,status,contents from dba_tablespaces where tablespace_name like '%TEST_T%';
TABLESPACE_NAME STATUS CONTENTS
------------------------------ ------------------ ------------------
TEST_TEMP ONLINE TEMPORARY
TEST_TAB OFFLINE PERMANENT
SQL> host cp /home/Oracle/oracle/oradata/mdsp/test_tab.dbf /home/oracle/oracle/oradata/mdsp/test_tab_r.dbf;
SQL> alter tablespace test_tab rename datafile '/home/oracle/oracle/oradata/mdsp/test_tab.dbf' to '/home/oracle/oracle/oradata/mdsp/test_tab_r.dbf';
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> col file_name for a40
SQL> col tablespace_name for a15
SQL> select file_name,tablespace_name,status from dba_data_files where tablespace_name like '%TEST_T%';
FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- --------------- ------------------
/home/oracle/oracle/oradata/mdsp/test_ta TEST_TAB AVAILABLE
b_r.dbf
/home/oracle/oracle/oradata/mdsp/test_ta TEST_TAB AVAILABLE
b1.dbf
SQL> alter tablespace test_tab online;
Tablespace altered.
SQL> select file_name,tablespace_name,status from dba_data_files where tablespace_name like '%TEST_T%';
FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- --------------- ------------------
/home/oracle/oracle/oradata/mdsp/test_ta TEST_TAB AVAILABLE
b_r.dbf
/home/oracle/oracle/oradata/mdsp/test_ta TEST_TAB AVAILABLE
b1.dbf
SQL> select tablespace_name,status,contents from dba_tablespaces where tablespace_name like '%TEST_T%';
TABLESPACE_NAME STATUS CONTENTS
--------------- ------------------ ------------------
TEST_TEMP ONLINE TEMPORARY
TEST_TAB ONLINE PERMANENT
00027_2通过数据库表文件移动
适用于移动系统表空间；
SQL> shutdown immediate
SQL> startup mount
SQL> host cp /home/oracle/oracle/oradata/mdsp/system01.dbf /home/oracle/oracle/oradata/mdsp/system02.dbf
SQL> alter database rename file '/home/oracle/oracle/oradata/mdsp/system01.dbf' to '/home/oracle/oracle/oradata/mdsp/system02.dbf'