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

1. SQL> create tablespace test1_tablespace
2. 2 datafile 'test1file.dbf' size 10m;
4. 表空间已创建。
6. SQL> create temporary tablespace temptest1_tablespace
7. 2 tempfile 'tempfile1_dbf' size 10m;
9. 表空间已创建。
11. SQL> desc dba_data_files
12. 名称是否为空?类型
13. -----------------------------------------------------------------------------
14. FILE_NAME VARCHAR2(513)
15. FILE_ID NUMBER
16. TABLESPACE_NAME VARCHAR2(30)
17. BYTES NUMBER
18. BLOCKS NUMBER
19. STATUS VARCHAR2(9)
20. RELATIVE_FNO NUMBER
21. AUTOEXTENSIBLE VARCHAR2(3)
22. MAXBYTES NUMBER
23. MAXBLOCKS NUMBER
24. INCREMENT_BY NUMBER
25. USER_BYTES NUMBER
26. USER_BLOCKS NUMBER
27. ONLINE_STATUS VARCHAR2(7)
28. SQL>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
29. FILE_NAME
30. --------------------------------------------------------------------------------
31. D:APPLENOVOPRODUCT11.2.0DBHOME_1DATABASETEST1FILE.DBF
33. SQL>select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';
35. FILE_NAME
36. --------------------------------------------------------------------------------
37. D:APPLENOVOPRODUCT11.2.0DBHOME_1DATABASETEMPFILE1_DBF

1. SQL> alter tablespace test1_tablespace
2. 2 offline;
3. 表空间已更改。
4. SQL> desc dba_tablespaces
5. 名称是否为空?类型
6. -----------------------------------------------------------------------------
7. TABLESPACE_NAME NOT NULL VARCHAR2(30)
8. BLOCK_SIZE NOT NULL NUMBER
9. INITIAL_EXTENT NUMBER
10. NEXT_EXTENT NUMBER
11. MIN_EXTENTS NOT NULL NUMBER
12. MAX_EXTENTS NUMBER
13. MAX_SIZE NUMBER
14. PCT_INCREASE NUMBER
15. MIN_EXTLEN NUMBER
16. STATUS VARCHAR2(9)
17. CONTENTS VARCHAR2(9)
18. LOGGING VARCHAR2(9)
19. FORCE_LOGGING VARCHAR2(3)
20. EXTENT_MANAGEMENT VARCHAR2(10)
21. ALLOCATION_TYPE VARCHAR2(9)
22. PLUGGED_IN VARCHAR2(3)
23. SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
24. DEF_TAB_COMPRESSION VARCHAR2(8)
25. RETENTION VARCHAR2(11)
26. BIGFILE VARCHAR2(3)
27. PREDICATE_EVALUATION VARCHAR2(7)
28. ENCRYPTED VARCHAR2(3)
29. COMPRESS_FOR VARCHAR2(12)
30. SQL>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
31. STATUS
32. ---------
33. OFFLINE
35. SQL> alter tablespace test1_tablespace
36. 2 online
37. 3;
38. 表空间已更改。
39. SQL>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
40. STATUS
41. ---------
42. ONLINE

1. 2 read only;
2. SQL>select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
4. STATUS
5. ---------
6. READ ONLY
8. SQL> alter tablespace test1_tablespace
9. 2 read write;
10. 表空间已更改。
11. ONLINE

1. 2 add datafile 'test2_file.dbf' size 10m;
2. SQL>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
3. FILE_NAME
4. --------------------------------------------------------------------------------
5. D:APPLENOVOPRODUCT11.2.0DBHOME_1DATABASETEST1FILE.DBF
6. D:APPLENOVOPRODUCT11.2.0DBHOME_1DATABASETEST2_FILE.DBF

1. 2 drop datafile 'test2_file.dbf';
2. SQL>select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
3. FILE_NAME
4. --------------------------------------------------------------------------------
5. D:APPLENOVOPRODUCT11.2.0DBHOME_1DATABASETEST1FILE.DBF

1. SQL> drop tablespace test1_tablespace including contents;
2. 表空间已删除。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!