频道导航

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

2020-07-08 Oracle 前端之家

前端之家收集整理的这篇文章主要介绍了创建、修改、删除Oracle表空间，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

@H_403_1@ 一创建表空间

@H_403_1@ CREATE [TEMPORARY] TABLESPACE

@H_403_1@ tablesypace_name

@H_403_1@ TEMPFILE|DATAFILE 'xx.dba' SIZE xx

@H_403_1@ 实例：

@H_403_1@ @H_403_1@

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

@H_403_1@ 二修改表空间

@H_403_1@ 1、修改表空间的状态 @H_403_1@ 设置连接或脱机状态 @H_403_1@ ARTER TABLESPACE tablespace_name @H_403_1@ LNLINE|OFFLINE; @H_403_1@ @H_403_1@

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

@H_403_1@ 设置只读或可读写状态 @H_403_1@ ALTER TABLESPACE tablespace_name @H_403_1@ READ ONLY|READ WRITE @H_403_1@ 实例： @H_403_1@ @H_403_1@

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

@H_403_1@ 2、修改数据文件 @H_403_1@ 增加数据文件 @H_403_1@ ALTER TABLESPACE tablespace_name @H_403_1@ ADD DATAFILE ‘xx.dbf’ SIZE xx; @H_403_1@ 实例： @H_403_1@ @H_403_1@

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

@H_403_1@ 删除数据文件 @H_403_1@ ALTER TABLESPACE tablespace_name @H_403_1@ DROP DATAFILE 'filename.dbf' @H_403_1@ @H_403_1@

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