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

前端之家收集整理的这篇文章主要介绍了创建、修改、删除Oracle表空间前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
一 创建表空间
CREATE [TEMPORARY] TABLESPACE
tablesypace_name
TEMPFILE|DATAFILE 'xx.dba' SIZE xx
实例:
  1. sql> create tablespace test1_tablespace
  2. @H_403_29@2 datafile 'test1file.dbf' size 10m;
  3. @H_403_29@
  4. @H_403_29@表空间已创建。
  5. @H_403_29@
  6. @H_403_29@sql> create temporary tablespace temptest1_tablespace
  7. @H_403_29@2 tempfile 'tempfile1_dbf' size 10m;
  8. @H_403_29@
  9. @H_403_29@表空间已创建。
  10. @H_403_29@
  11. @H_403_29@sql> desc dba_data_files
  12. @H_403_29@名称是否为空?类型
  13. @H_403_29@-----------------------------------------------------------------------------
  14. @H_403_29@ FILE_NAME VARCHAR2(513)
  15. @H_403_29@ FILE_ID NUMBER
  16. @H_403_29@ TABLESPACE_NAME VARCHAR2(30)
  17. @H_403_29@ BYTES NUMBER
  18. @H_403_29@ BLOCKS NUMBER
  19. @H_403_29@ STATUS VARCHAR2(9)
  20. @H_403_29@ RELATIVE_FNO NUMBER
  21. @H_403_29@ AUTOEXTENSIBLE VARCHAR2(3)
  22. @H_403_29@ MAXBYTES NUMBER
  23. @H_403_29@ MAXBLOCKS NUMBER
  24. @H_403_29@ INCREMENT_BY NUMBER
  25. @H_403_29@ USER_BYTES NUMBER
  26. @H_403_29@ USER_BLOCKS NUMBER
  27. @H_403_29@ ONLINE_STATUS VARCHAR2(7)
  28. @H_403_29@sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
  29. @H_403_29@FILE_NAME
  30. @H_403_29@--------------------------------------------------------------------------------
  31. @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
  32. @H_403_29@
  33. @H_403_29@sql>select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';
  34. @H_403_29@
  35. @H_403_29@FILE_NAME
  36. @H_403_29@--------------------------------------------------------------------------------
  37. @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEMPFILE1_DBF
修改表空间
1、修改表空间的状态
设置连接或脱机状态
ARTER TABLESPACE tablespace_name
LNLINE|OFFLINE;
  1. @H_403_29@sql> alter tablespace test1_tablespace
  2. @H_403_29@2 offline;
  3. @H_403_29@表空间已更改。
  4. @H_403_29@sql> desc dba_tablespaces
  5. @H_403_29@名称是否为空?类型
  6. @H_403_29@-----------------------------------------------------------------------------
  7. @H_403_29@ TABLESPACE_NAME NOT NULL VARCHAR2(30)
  8. @H_403_29@ BLOCK_SIZE NOT NULL NUMBER
  9. @H_403_29@ INITIAL_EXTENT NUMBER
  10. @H_403_29@ NEXT_EXTENT NUMBER
  11. @H_403_29@ MIN_EXTENTS NOT NULL NUMBER
  12. @H_403_29@ MAX_EXTENTS NUMBER
  13. @H_403_29@ MAX_SIZE NUMBER
  14. @H_403_29@ PCT_INCREASE NUMBER
  15. @H_403_29@ MIN_EXTLEN NUMBER
  16. @H_403_29@ STATUS VARCHAR2(9)
  17. @H_403_29@ CONTENTS VARCHAR2(9)
  18. @H_403_29@ LOGGING VARCHAR2(9)
  19. @H_403_29@ FORCE_LOGGING VARCHAR2(3)
  20. @H_403_29@ EXTENT_MANAGEMENT VARCHAR2(10)
  21. @H_403_29@ ALLOCATION_TYPE VARCHAR2(9)
  22. @H_403_29@ PLUGGED_IN VARCHAR2(3)
  23. @H_403_29@ SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
  24. @H_403_29@ DEF_TAB_COMPRESSION VARCHAR2(8)
  25. @H_403_29@ RETENTION VARCHAR2(11)
  26. @H_403_29@ BIGFILE VARCHAR2(3)
  27. @H_403_29@ PREDICATE_EVALUATION VARCHAR2(7)
  28. @H_403_29@ ENCRYPTED VARCHAR2(3)
  29. @H_403_29@ COMPRESS_FOR VARCHAR2(12)
  30. @H_403_29@sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
  31. @H_403_29@STATUS
  32. @H_403_29@---------
  33. @H_403_29@OFFLINE
  34. @H_403_29@
  35. @H_403_29@sql> alter tablespace test1_tablespace
  36. @H_403_29@2 online
  37. @H_403_29@3;
  38. @H_403_29@表空间已更改。
  39. @H_403_29@sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
  40. @H_403_29@STATUS
  41. @H_403_29@---------
  42. @H_403_29@ONLINE
设置只读或可读写状态
ALTER TABLESPACE tablespace_name
READ ONLY|READ WRITE
实例:
  1. @H_403_29@2 read only;
  2. @H_403_29@sql>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
  3. @H_403_29@
  4. @H_403_29@STATUS
  5. @H_403_29@---------
  6. @H_403_29@READ ONLY
  7. @H_403_29@
  8. @H_403_29@sql> alter tablespace test1_tablespace
  9. @H_403_29@2 read write;
  10. @H_403_29@表空间已更改。
  11. @H_403_29@ONLINE
2、修改数据文件
增加数据文件
ALTER TABLESPACE tablespace_name
ADD DATAFILE ‘xx.dbf’ SIZE xx;
实例:
  1. @H_403_29@2 add datafile 'test2_file.dbf' size 10m;
  2. @H_403_29@sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
  3. @H_403_29@FILE_NAME
  4. @H_403_29@--------------------------------------------------------------------------------
  5. @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
  6. @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF
删除数据文件
ALTER TABLESPACE tablespace_name
DROP DATAFILE 'filename.dbf'
  1. @H_403_29@2 drop datafile 'test2_file.dbf';
  2. @H_403_29@sql>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
  3. @H_403_29@FILE_NAME
  4. @H_403_29@--------------------------------------------------------------------------------
  5. @H_403_29@D:\APP\LENOVO\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
删除表空间
DROP TABLESPACE
tablespace_name [INCLUDING CONTENTS]
  1. @H_403_29@sql> drop tablespace test1_tablespace including contents;
  2. @H_403_29@表空间已删除

猜你在找的Oracle相关文章