Six minutes to learn how to create an Oracle Tablespace

After a long time to learn to create an Oracle tablespace, so share with you, after reading this article you certainly have a lot of gains, hope this article can teach you more things.

1. Query the free space first

1. Select Tablespace_name,file_id,block_id,bytes,blocks from Dba_free_space;

2. Increase the Oracle table space

First, query the data file name, size and path information, the following statement:

1. Select Tablespace_name,file_id,bytes,file_name from Dba_data_files;

3. Modify the file size statement as follows

1. ALTER DATABASE DataFile
2. ' Need to add a path to the data file, which is the path queried above
3. ' Resize 800M;

4. Create Oracle Tablespace

2. Create Tablespace Test
4. DataFile '/home/app/oracle/oradata/oracle8i/test01.dbf ' size 8M
6. Autoextend on
8. Next 5M
10. MaxSize 10M;
14. Create Tablespace Sales
16. DataFile '/home/app/oracle/oradata/oracle8i/sales01.dbf ' size 800M
18. Autoextend on
20. Next 50M
22. MaxSize Unlimited
24. MaxSize Unlimited is not limited in size
28. Create Tablespace Sales
30. DataFile '/home/app/oracle/oradata/oracle8i/sales01.dbf ' size 800M
32. Autoextend on
34. Next 50M
36. MaxSize 1000M
38. Extent management Local uniform;
40. Unform indicates that the size of the zone is the same, default is 1M
44. Create Tablespace Sales
46. DataFile '/home/app/oracle/oradata/oracle8i/sales01.dbf ' size 800M
48. Autoextend on
50. Next 50M
52. MaxSize 1000M
54. Extent management local uniform size 500K;
56. Unform size 500K indicates that the area is the same sized as 500K
60. Create Tablespace Sales
62. DataFile '/home/app/oracle/oradata/oracle8i/sales01.dbf ' size 800M
64. Autoextend on
66. Next 50M
68. MaxSize 1000M
70. Extent management local autoallocate;
72. Autoallocate indicates that the size of the area is changed automatically with the size of the table, large table uses the large Area small table to use the cell
76. Create Tablespace Sales
78. DataFile '/home/app/oracle/oradata/oracle8i/sales01.dbf ' size 800M
80. Autoextend on
82. Next 50M
84. MaxSize 1000M
86. temporary;
88. Temporary creating a dictionary management Temp table space
92. Create temporary tablespace sales
94. Tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf ' size 800M
96. Autoextend on
98. Next 50M
100. MaxSize 1000M
102. Creates a local administrative temporary tablespace, and if it is a temporary tablespace, datafile in all statements is changed to Tempfile
106. 8i system default Create dictionary manage temporary tablespace to create local manage temporary tablespace add temporary tablespace keyword
108. You must not use the Atuoallocate parameter when creating a local administrative temporary tablespace, and the system creates uniform management by default
112. To add a data file to the tablespace:
114. Alter TABLESPACE sales Add
116. DataFile '/home/app/oracle/oradata/oracle8i/sales02.dbf ' size 800M
118. Autoextend on Next 50M
120. MaxSize 1000M;

Create a locally managed temporary Oracle Tablespace, if it is a temporary tablespace, all statements in DataFile are replaced by the tempfile8i system by default to create a dictionary management temporary tablespace, to create a local administrative temporary tablespace to add temporary tablespace keyword When creating a local administrative temporary tablespace, you must not use the Atuoallocate parameter, the system creates uniform management by default

To add a data file to the tablespace:

1. Alter TABLESPACE sales Add
2. DataFile '/home/app/oracle/oradata/oracle8i/sales02.dbf ' size 800M
3. Autoextend on Next 50M
4. MaxSize 1000M;

5. Change Auto-extended properties:

1. ALTER DATABASE DataFile
2. '/home/app/oracle/oradata/oracle8i/sales01.dbf ',
3. '/HOME/APP/ORACLE/ORADATA/ORACLE8I/SALES02.DBF '
4. '/HOME/APP/ORACLE/ORADATA/ORACLE8I/SALES01.DBF
5. Autoextend off;

The above introduction to create an Oracle tablespace, here to share with you, hope to be useful to everyone.