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

一创建表空间

CREATE [TEMPORARY] TABLESPACE

tablesypace_name

TEMPFILE|DATAFILE 'xx.dba' SIZE xx

实例：

sql> create tablespace test1_tablespace
2 datafile 'test1file.dbf' size 10m;
表空间已创建。
sql> create temporary tablespace temptest1_tablespace
2 tempfile 'tempfile1_dbf' size 10m;
表空间已创建。
sql> desc dba_data_files
名称是否为空?类型
-----------------------------------------------------------------------------
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>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
sql>select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEMPFILE1_DBF

二修改表空间

1、修改表空间的状态

设置连接或脱机状态

ARTER TABLESPACE tablespace_name

LNLINE|OFFLINE;

sql> alter tablespace test1_tablespace
2 offline;
表空间已更改。
sql> desc dba_tablespaces
名称是否为空?类型
-----------------------------------------------------------------------------
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 status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
STATUS
---------
OFFLINE
sql> alter tablespace test1_tablespace
2 online
3;
表空间已更改。
sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
STATUS
---------
ONLINE

设置只读或可读写状态

ALTER TABLESPACE tablespace_name

READ ONLY|READ WRITE

实例：

2 read only;
sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
STATUS
---------
READ ONLY
sql> alter tablespace test1_tablespace
2 read write;
表空间已更改。
ONLINE

2、修改数据文件

增加数据文件

ALTER TABLESPACE tablespace_name

ADD DATAFILE ‘xx.dbf’ SIZE xx;

实例：

2 add datafile 'test2_file.dbf' size 10m;
sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF

删除数据文件

ALTER TABLESPACE tablespace_name

DROP DATAFILE 'filename.dbf'

2 drop datafile 'test2_file.dbf';
sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF

三删除表空间

DROP TABLESPACE

tablespace_name [INCLUDING CONTENTS]

猜你在找的Oracle相关文章