一 创建表空间
CREATE [TEMPORARY] TABLESPACE
tablesypace_name
TEMPFILE|DATAFILE 'xx.dba' SIZE xx
实例:
sql> create tablespace test1_tablespace
- @H_403_29@2 datafile 'test1file.dbf' size 10m;
- @H_403_29@
- @H_403_29@表空间已创建。
- @H_403_29@
- @H_403_29@sql> create temporary tablespace temptest1_tablespace
- @H_403_29@2 tempfile 'tempfile1_dbf' size 10m;
- @H_403_29@
- @H_403_29@表空间已创建。
- @H_403_29@
- @H_403_29@sql> desc dba_data_files
- @H_403_29@名称是否为空?类型
- @H_403_29@-----------------------------------------------------------------------------
- @H_403_29@ FILE_NAME VARCHAR2(513)
- @H_403_29@ FILE_ID NUMBER
- @H_403_29@ TABLESPACE_NAME VARCHAR2(30)
- @H_403_29@ BYTES NUMBER
- @H_403_29@ BLOCKS NUMBER
- @H_403_29@ STATUS VARCHAR2(9)
- @H_403_29@ RELATIVE_FNO NUMBER
- @H_403_29@ AUTOEXTENSIBLE VARCHAR2(3)
- @H_403_29@ MAXBYTES NUMBER
- @H_403_29@ MAXBLOCKS NUMBER
- @H_403_29@ INCREMENT_BY NUMBER
- @H_403_29@ USER_BYTES NUMBER
- @H_403_29@ USER_BLOCKS NUMBER
- @H_403_29@ ONLINE_STATUS VARCHAR2(7)
- @H_403_29@sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
- @H_403_29@FILE_NAME
- @H_403_29@--------------------------------------------------------------------------------
- @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
- @H_403_29@
- @H_403_29@sql>select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';
- @H_403_29@
- @H_403_29@FILE_NAME
- @H_403_29@--------------------------------------------------------------------------------
- @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEMPFILE1_DBF
二 修改表空间
1、修改表空间的状态
设置连接或脱机状态
ARTER TABLESPACE tablespace_name
LNLINE|OFFLINE;
- @H_403_29@sql> alter tablespace test1_tablespace
- @H_403_29@2 offline;
- @H_403_29@表空间已更改。
- @H_403_29@sql> desc dba_tablespaces
- @H_403_29@名称是否为空?类型
- @H_403_29@-----------------------------------------------------------------------------
- @H_403_29@ TABLESPACE_NAME NOT NULL VARCHAR2(30)
- @H_403_29@ BLOCK_SIZE NOT NULL NUMBER
- @H_403_29@ INITIAL_EXTENT NUMBER
- @H_403_29@ NEXT_EXTENT NUMBER
- @H_403_29@ MIN_EXTENTS NOT NULL NUMBER
- @H_403_29@ MAX_EXTENTS NUMBER
- @H_403_29@ MAX_SIZE NUMBER
- @H_403_29@ PCT_INCREASE NUMBER
- @H_403_29@ MIN_EXTLEN NUMBER
- @H_403_29@ STATUS VARCHAR2(9)
- @H_403_29@ CONTENTS VARCHAR2(9)
- @H_403_29@ LOGGING VARCHAR2(9)
- @H_403_29@ FORCE_LOGGING VARCHAR2(3)
- @H_403_29@ EXTENT_MANAGEMENT VARCHAR2(10)
- @H_403_29@ ALLOCATION_TYPE VARCHAR2(9)
- @H_403_29@ PLUGGED_IN VARCHAR2(3)
- @H_403_29@ SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
- @H_403_29@ DEF_TAB_COMPRESSION VARCHAR2(8)
- @H_403_29@ RETENTION VARCHAR2(11)
- @H_403_29@ BIGFILE VARCHAR2(3)
- @H_403_29@ PREDICATE_EVALUATION VARCHAR2(7)
- @H_403_29@ ENCRYPTED VARCHAR2(3)
- @H_403_29@ COMPRESS_FOR VARCHAR2(12)
- @H_403_29@sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
- @H_403_29@STATUS
- @H_403_29@---------
- @H_403_29@OFFLINE
- @H_403_29@
- @H_403_29@sql> alter tablespace test1_tablespace
- @H_403_29@2 online
- @H_403_29@3;
- @H_403_29@表空间已更改。
- @H_403_29@sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
- @H_403_29@STATUS
- @H_403_29@---------
- @H_403_29@ONLINE
设置只读或可读写状态
ALTER TABLESPACE tablespace_name
READ ONLY|READ WRITE
实例:
- @H_403_29@2 read only;
- @H_403_29@sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
- @H_403_29@
- @H_403_29@STATUS
- @H_403_29@---------
- @H_403_29@READ ONLY
- @H_403_29@
- @H_403_29@sql> alter tablespace test1_tablespace
- @H_403_29@2 read write;
- @H_403_29@表空间已更改。
- @H_403_29@ONLINE
ALTER TABLESPACE tablespace_name
ADD DATAFILE ‘xx.dbf’ SIZE xx;
实例:
- @H_403_29@2 add datafile 'test2_file.dbf' size 10m;
- @H_403_29@sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
- @H_403_29@FILE_NAME
- @H_403_29@--------------------------------------------------------------------------------
- @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
- @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF
ALTER TABLESPACE tablespace_name
DROP DATAFILE 'filename.dbf'
- @H_403_29@2 drop datafile 'test2_file.dbf';
- @H_403_29@sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
- @H_403_29@FILE_NAME
- @H_403_29@--------------------------------------------------------------------------------
- @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
三 删除表空间
DROP TABLESPACE
tablespace_name [INCLUDING CONTENTS]