Oracle tablespace operations

1. View tablespace Information Java Code

2. Select F. tablespace_name, A. Total, U. Used, F. Free, round (U. Used/a. Total )*100)"% Used",
4. Round (f. Free/a. Total )*100)"% Free"
6. From
8. (Select tablespace_name, sum (Bytes /(1024*1024) Total
10. From dba_data_files group by tablespace_name),
12. (Select tablespace_name, round (sum (Bytes /(1024*1024) Used
14. From dba_extents group by tablespace_name) U,
16. (Select tablespace_name, round (sum (Bytes /(1024*1024) Free
18. From dba_free_space group by tablespace_name) f
20. Where a. tablespace_name = f. tablespace_name
22. And a. tablespace_name = U. tablespace_name;

Select F. tablespace_name,. total, U. used, F. free, round (U. used/. total) * 100) "% used", round (f. free/. total) * 100) "% free" from (select tablespace_name, sum (Bytes/(1024*1024) Total from dba_data_files group by tablespace_name) A, (select tablespace_name, round (sum (Bytes/(1024*1024) used from dba_extents group by tablespace_name) U, (select tablespace_name, round (sum (Bytes/(1024*1024 ))) free from dba_free_space group by tablespace_name) fwhere. tablespace_name = f. tablespace_nameand. tablespace_name = u. tablespace_name;
 

2. Create a tablespaceJava code

1. SQL> Create tablespace testspace
2. Datafile'D:/oracletest/test001.dbf'Size 10 m autoextend on next 5 m maxsize Unlimited
3. Extent management local;

 
SQL> Create tablespace testspace datafile 'd:/oracletest/test001.dbf 'size 10 m autoextend on next 5 m maxsize unlimited extent management local;
 

2. Add data files to the tablespace

Java code

1. SQL> alter tablespace testspace add datafile 'd: /oracletest/test002.dbf' size 5 m
2. autoextend on next 3 m maxsize 50 m;
4. SQL> alter tablespace testspace add datafile 'd: /oracletest/test002.dbf' size 5 m
5. autoextend on next 3 m maxsize 50 m;

SQL> alter tablespace testspace add datafile 'd:/oracletest/test002.dbf 'size 5 m autoextend on next 3 m maxsize 50 m; SQL> alter tablespace testspace add datafile 'd: /oracletest/test002.dbf 'size 5 m autoextend on next 3 m maxsize 50 m;
 

3. Delete the data files in the tablespace.Java code

1. SQL> alter tablespace testspace drop datafile'D:/oracletest/test002.dbf';
3. SQL> alter tablespace testspace drop datafile'D:/oracletest/test002.dbf';

SQL> alter tablespace testspace drop datafile 'd:/oracletest/test002.dbf '; SQL> alter tablespace testspace drop datafile 'd:/oracletest/test002.dbf ';
 

4. Modify the data file size of the tablespace File

Java code

1. SQL> alter database datafile'D:/oracletest/test001.dbf'Resize 10 m;
3. SQL> alter database datafile'D:/oracletest/test001.dbf'Resize 10 m;

 
SQL> alter database datafile 'd:/oracletest/test001.dbf 'resize 10 m; SQL> alter database datafile 'd:/oracletest/test001.dbf' resize 10 m;
 

5. Modify the automatic growth attribute of the tablespace Data File

Java code

1. SQL> alter database datafile'D:/oracletest/test001.dbf'Autoextend off;
3. SQL> alter database datafile'D:/oracletest/test001.dbf'Autoextend off;

 
SQL> alter database datafile 'd:/oracletest/test001.dbf 'autoextend off; SQL> alter database datafile 'd:/oracletest/test001.dbf' autoextend off;
 

6. Modify the read/write attributes of a tablespace.

Java code

1. SQL> alter tablespace testspace read only; (read-only)
3. SQL> alter tablespace testspace read write; (read/write)
5. SQL> alter tablespace testspace read only; (read-only)
7. SQL> alter tablespace testspace read write; (read/write)

 
SQL> alter tablespace testspace read only; (read-only) SQL> alter tablespace testspace read write; (read and write) SQL> alter tablespace testspace read only; (read-only) SQL> alter tablespace testspace read write; (read/write)
 

7. Set tablespace disconnection/online

Java code

1. SQL> alter tablespace testspace offline;
3. SQL> alter tablespace testspace online;
5. SQL> alter tablespace testspace offline;
7. SQL> alter tablespace testspace online;

 
SQL> alter tablespace testspace offline; SQL> alter tablespace testspace online;
 

8. Transfer physical file paths

Java code

2. (1) Set the offline alter tablespac testspace offline for the tablespace;
6. (2) Physically transfers the tablespace file, that is, transfers the physical file of your tablespace to the path you want to move.
10. (3) Logical transfer: Alter tablespace testspace rename datafile'D:/oracletest/test001.dbf'To'E:/test001.dbf';
14. (4) Set online alter tablespace testspace online for the tablespace;
18. (1) Set the offline alter tablespac testspace offline for the tablespace;
22. (2) Physically transfers the tablespace file, that is, transfers the physical file of your tablespace to the path you want to move.
26. (3) Logical transfer: Alter tablespace testspace rename datafile'D:/oracletest/test001.dbf'To'E:/test001.dbf';
30. (4) Set online alter tablespace testspace online for the tablespace;

 
(1) set the tablespace offline alter tablespac testspace offline; (2) physical transfer tablespace file; that is, transfer your tablespace physical file to the path you want to move (3) logical transfer: alter tablespace testspace rename datafile 'd:/oracletest/test001.dbf 'to 'e:/test001.dbf'; (4) set online alter tablespace testspace online; (1) set the tablespace offline alter tablespac testspace offline; (2) physical transfer tablespace file; that is, transfer your tablespace physical file to the path you want to move (3) logical transfer: alter tablespace testspace rename datafile 'd:/oracletest/test001.dbf 'to 'e:/test001.dbf'; (4) set online alter tablespace testspace online;
 

9. delete a tablespace

Java code

1. ( 1 ) do not delete the file drop tablespace testspace;
3. ( 2 ) Drop tablespace testspace including contents and datafiles;
5. ( 1 ) do not delete the file drop tablespace testspace;
7. ( 2 ) Drop tablespace testspace including contents and datafiles;

(1) do not delete the file drop tablespace testspace; (2) delete the file drop tablespace testspace including contents and datafiles; (1) do not delete the file drop tablespace testspace; (2) delete the file drop tablespace testspace including contents and datafiles;
 

10. How do I start a database when a physical file is deleted illegally?

Java code

1. (1) Shut down the database service.
3. (2) Alter database datafile'D:/test001.dbf'Offline drop;
5. (3) Alter database open;
7. (4) Enable the Database Service Startup;

 
(1) shutdown the Database Service (2) alter database datafile 'd:/test001.dbf' offline drop; (3) alter database open; (4) Enable the Database Service Startup;
 

11. view the tablespace name

SQL code

1. Select DistinctTablespace_nameFromTabs;
2. SelectTablespace_nameFromUser_tablespaces;

 
Select distinct tablespace_name from tabs; select tablespace_name from user_tablespaces;
 

Example: Join table space Java code when creating a table

2. -- Create tablespace
4. Create tablespace tab_news datafile'F: \ oradata \ NPMs \ tab_news.dbf'Size 5 m autoextend on next 2 m maxsize unlimited extent management local;
8. Autoextend on next 2 m represents auto-increment in 2 m size
12. -- Create Table
14. Create Table news_bbs
16. (
18. Bbsid number (10) NotNull,
20. Project varchar2 (128) NotNull,
22. Userid number (10),
24. Content clob,
26. Answerid number (10),
28. Answercount number (10),
30. Publishtime date
32. )
34. Tablespace news_tab
36. Pctfree10
38. Initrans1
40. Maxtrans255
42. Storage
44. (
46. Initial64
48. Minextents1
50. Maxextents Unlimited
52. );
56. -- Create/recreate primary, unique and foreign key constraints
58. Alter table news_bbs
60. Add constraint pk_news_bbs primary key (bbsid)
62. Using Index
64. Tablespace et_tab
66. Pctfree10
68. Initrans2
70. Maxtrans255
72. Storage
74. (
76. Initial 64 K
78. Minextents1
80. Maxextents Unlimited
82. );