创建、修改、删除Oracle表空间

一创建表空间

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

2、修改数据文件

增加数据文件

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]

猜你在找的Oracle相关文章