Learn the implementation steps of creating Oracle tablespace in six minutes

After a long time of learning to create Oracle table space, so share with everyone 1, read this article you certainly have a lot of harvest, I hope this article can teach you more things.

1. Query the free space first

select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space; 

2. Add Oracle tablespace

First, query the information of the data file name, size and path, and the statement is as follows:

select tablespace_name,file_id,bytes,file_name from dba_data_files; 

3. Modify the file size statement as follows

alter database datafile   
' The data file path that needs to be added, that is, the path queried above   
'resize 800M; 

4. Create an Oracle tablespace

create tablespace test   
datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M   
autoextend on   
next 5M   
maxsize 10M;   

create tablespace sales   
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M   
autoextend on   
next 50M   
maxsize unlimited   
maxsize unlimited  Is unlimited in size    

create tablespace sales   
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M   
autoextend on   
next 50M   
maxsize 1000M   
extent management local uniform;   
unform Indicates that the size of the area is the same, and the default is 1M   

create tablespace sales   
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M   
autoextend on   
next 50M   
maxsize 1000M   
extent management local uniform size 500K;   
unform size 500K Indicates that the size of the area is the same, which is 500K   

create tablespace sales   
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M   
autoextend on   
next 50M   
maxsize 1000M   
extent management local autoallocate;   
autoallocate It indicates that the size of the area changes automatically and dynamically with the size of the table, and the large table uses the large area and the small table uses the cell    

create tablespace sales   
datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M   
autoextend on   
next 50M   
maxsize 1000M   
temporary;   
temporary Create a dictionary to manage temporary tablespace    

create temporary tablespace sales   
tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M   
autoextend on   
next 50M   
maxsize 1000M   
 Create a locally managed temporary tablespace. If it is a temporary tablespace, the datafile They are all replaced by tempfile   

8i By default, the system creates a dictionary management temporary tablespace. To create a local management temporary tablespace, add temporary tablespace Keyword    
 When creating a locally managed temporary tablespace, you must not use the atuoallocate Parameter, which is created by default uniform Management mode    

 Add data files to the tablespace:    
alter tablespace sales add   
datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M   
autoextend on next 50M   
maxsize 1000M; 

Create local managed temporary Oracle tablespace. If it is a temporary tablespace, datafile in all statements will be replaced by tempfile8i. The system will create dictionary managed temporary tablespace by default. To create local managed temporary tablespace, add temporary tablespace keyword to create local managed temporary tablespace, you must not use atuoallocate parameter. The system will create uniform management mode by default

Add data files to the tablespace:

alter tablespace sales add   
datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800M   
autoextend on next 50M   
maxsize 1000M; 

5. Change auto-extension properties:

alter database datafile   
'/home/app/oracle/oradata/oracle8i/sales01.dbf',   
'/home/app/oracle/oradata/oracle8i/sales02.dbf'   
'/home/app/oracle/oradata/oracle8i/sales01.dbf   
autoextend off; 

The above introduction creates the Oracle table space, here takes out and shares 1 below, hoped to be useful to everybody.