一、查看数据文件位置
SQL> show user;
User is "SYS"
SQL> desc dba_data_files;
Name Type Nullable Default Comments
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME VARCHAR2(513) Y Name of the database data file
FILE_ID NUMBER Y ID of the database data file
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace to which the file belongs
BYTES NUMBER Y Size of the file in bytes
BLOCKS NUMBER Y Size of the file in ORACLE blocks
STATUS VARCHAR2(9) Y File status: "INVALID" or "AVAILABLE"
RELATIVE_FNO NUMBER Y Tablespace-relative file number
AUTOEXTENSIBLE VARCHAR2(3) Y Autoextensible indicator: "YES" or "NO"
MAXBYTES NUMBER Y Maximum size of the file in bytes
MAXBLOCKS NUMBER Y Maximum size of the file in ORACLE blocks
INCREMENT_BY NUMBER Y Default increment for autoextension
USER_BYTES NUMBER Y Size of the useful portion of file in bytes
USER_BLOCKS NUMBER Y Size of the useful portion of file in ORACLE blocks
ONLINE_STATUS VARCHAR2(7) Y Online status of the file
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/jerry.db
/u01/app/oracle/oradata/orcl/bftbs01.dbf
/u01/app/oracle/oradata/orcl/rman01.dbf
/u01/app/oracle/oradata/orcl/eygle01.dbf
/u01/app/oracle/oradata/orcl/fbra01.dbf
/u01/app/oracle/oradata/orcl/demo01.dbf
/u01/app/oracle/oradata/orcl/undotbs02.dbf
12 rows selected
二、创建测试表空间
SQL> create tablespace testreuse datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' size 100m;
Tablespace created
三、创建测试表
SQL> create table testreuse tablespace testreuse as select * from dba_objects;
Table created
SQL> select count(*) from testreuse;
COUNT(*)
----------
72890
四、查看表所在空间
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TESTREUSE';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS TESTREUSE TESTREUSE
五、指定REUSE，需要指定文件大小，否则报错。
SQL> alter tablespace testreuse add datafile '/u01/app/oracle/oradata/orcl/testreuse02.dbf' reuse;
alter tablespace testreuse add datafile '/u01/app/oracle/oradata/orcl/testreuse02.dbf' reuse
ORA-01119: error in creating database file '/u01/app/oracle/oradata/orcl/testreuse02.dbf'
ORA-17610: file '/u01/app/oracle/oradata/orcl/testreuse02.dbf' does not exist and no size specified
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter tablespace testreuse add datafile '/u01/app/oracle/oradata/orcl/testreuse02.dbf' size 200m reuse;
Tablespace altered
六、文件不能已经存在于此表空间，否则报错。
SQL> alter tablespace testreuse add datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' size 200m reuse;
alter tablespace testreuse add datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' size 200m reuse
ORA-01537: cannot add file '/u01/app/oracle/oradata/orcl/testreuse01.dbf' - file already part of database
七、使用ALTER DATABASE DATAFILE OFFLINE DROP（不会实际删除，相关信息还会记录在数据字典中，仅标记为offline，需要RECOVER）
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse01.dbf 12 TESTREUSE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse02.dbf 13 TESTREUSE AVAILABLE ONLINE
14 rows selected
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' offline drop;
Database altered
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse01.dbf 12 TESTREUSE AVAILABLE RECOVER
/u01/app/oracle/oradata/orcl/testreuse02.dbf 13 TESTREUSE AVAILABLE ONLINE
14 rows selected
SQL> alter tablespace testreuse add datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' size 200m reuse;
alter tablespace testreuse add datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' size 200m reuse
ORA-01537: cannot add file '/u01/app/oracle/oradata/orcl/testreuse01.dbf' - file already part of database
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' online;
alter database datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' online
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/u01/app/oracle/oradata/orcl/testreuse01.dbf'
SQL> recover datafile 12;
Media recovery complete.
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse01.dbf 12 TESTREUSE AVAILABLE OFFLINE
/u01/app/oracle/oradata/orcl/testreuse02.dbf 13 TESTREUSE AVAILABLE ONLINE
14 rows selected
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' online;
Database altered
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse01.dbf 12 TESTREUSE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse02.dbf 13 TESTREUSE AVAILABLE ONLINE
14 rows selected
八、使用alter tablespace xxx drop datafile（文件被删除，同时不会记录在数据字典里面）
SQL> alter tablespace testreuse drop datafile '/u01/app/oracle/oradata/orcl/testreuse02.dbf' ;
Tablespace altered
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse01.dbf 12 TESTREUSE AVAILABLE ONLINE
九、删除表空间，重用testreuse01.dbf，也可以使用drop tablespace xxx including contents and datafiles，但是会实际删除物理文件，此实验不能继续。因此只删除控制文件。
SQL> drop tablespace testreuse including contents;
Tablespace dropped
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
12 rows selected
在操作系统层面
[root@pc-centos ~]# cd /u01/app/oracle/oradata/orcl/
[root@pc-centos orcl]# ls testreuse*
testreuse01.dbf
SQL> select * from v$tablespace;
TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP
---------- ------------------------------ --------------------------- ------- ------------ -----------------
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
16 TEMP01 NO NO YES
6 EXAMPLE YES NO YES
10 JERRY YES NO YES
11 BFTBS YES YES YES
13 RMAN YES NO YES
12 EYGLE YES NO YES
14 FBRA YES NO YES
17 DEMO YES NO YES
5 UNDOTBS2 YES NO YES
表空间TESTREUSE被删除，文件testreuse01.dbf没有被实际删除
SQL> create tablespace testreuse2 datafile '/u01/app/oracle/oradata/orcl/testreuse01.dbf' size 100m reuse;
Tablespace created
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse01.dbf 12 TESTREUSE2 AVAILABLE ONLINE
数据文件被复用了。
十、在操作系统建立数据文件并利用
[root@pc-centos orcl]# su - oracle
[oracle@pc-centos ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@pc-centos orcl]$ vi testreuse03.dbf
保存退出
SQL> drop tablespace testreuse2 including contents and datafiles;
Tablespace dropped
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
12 rows selected
SQL> create tablespace testreuse3 datafile '/u01/app/oracle/oradata/orcl/testreuse03.dbf' size 100m reuse;
Tablespace created
SQL> select t.file_name,t.file_id,t.tablespace_name,t.status,t.online_status from DBA_DATA_FILES t;
FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ --------- -------------
/u01/app/oracle/oradata/orcl/users01.dbf 4 USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf 5 EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/jerry.db 6 JERRY AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/bftbs01.dbf 7 BFTBS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/rman01.dbf 8 RMAN AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/eygle01.dbf 9 EYGLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/fbra01.dbf 10 FBRA AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/demo01.dbf 11 DEMO AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/undotbs02.dbf 14 UNDOTBS2 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/testreuse03.dbf 12 TESTREUSE3 AVAILABLE ONLINE
13 rows selected
文件同样复用。
综上，如果文件存在，并不属于任何表空间，则可以利用，如果属于一个表空间，则不能再复用。如果文件不存在，指定reuse则需要指定文件大小。
阅读(1440) | 评论(0) | 转发(0) |
