SQL> desc dba_data_files Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(257) 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> select file_name,tablespace_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +DATA/orcl/datafile/users.259.738776263 USERS +DATA/orcl/datafile/sysaux.257.738776261 SYSAUX +DATA/orcl/datafile/undotbs1.258.738776263 UNDOTBS1 FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +DATA/orcl/datafile/system.256.738776261 SYSTEM +DATA/orcl/datafile/undotbs2.265.738776351 UNDOTBS2 +DATA/orcl/datafile/sp1data.dbf SP1DATA FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +DATA/orcl/datafile/hapi.dbf HAPI +DATA/orcl/datafile/hapbi.dbf HAPBI +DATA/orcl/datafile/szsq.dbf SZSQ FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +DATA/orcl/datafile/bireport.dbf BIREPORT +DATA/orcl/datafile/g3bi.dbf G3BI +DATA/orcl/datafile/ehrtbs.dbf EHRTBS FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +DATA/orcl/datafile/ehrdoctbs.dbf EHRDOCTBS +DATA/orcl/datafile/ehrindtbs.dbf EHRINDTBS +DATA/orcl/datafile/g2_data.ora G2_DATA FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ +DATA/orcl/datafile/g2_index_data.ora G2_INDEX_DATA /opt/tablespace/nnc_data01.dbf NNC_DATA01 /opt/tablespace/nnc_data02.dbf NNC_DATA02 FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ /opt/tablespace/nnc_data03.dbf NNC_DATA03 /opt/tablespace/nnc_index01.dbf NNC_INDEX01 /opt/tablespace/nnc_index02.dbf NNC_INDEX02 FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ /opt/tablespace/nnc_index03.dbf NNC_INDEX03 22 rows selected.
删除表空间：
SQL> drop tablespace NNC_INDEX03 including contents and datafiles; Tablespace dropped. SQL> drop tablespace NNC_DATA01 including contents and datafiles; Tablespace dropped.
