00069.Oracle移动数据文件

 

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'