Oracle tablespace operations

Oracle tablespace operations
 

Author: http://avill.cnblogs.com/archive/2006/04/16/376261.html Source: updated on:






 

Create a tablespace
 



Create tablespace data01

Datafile '/Oracle/oradata/DB/data01.dbf' size 500 m

Uniform size 128 K; # specify the area size as 128 K. If not specified, the default area size is 64 K.
 


 

Delete a tablespace
 



Drop tablespace data01 including contents and datafiles;


Modify the datafile size in a tablespace
Alter database datafile '/path/naddate05.dbf' resize 100 m

Move a table to another tablespace
Alter table move tablespace room1;
 

1. Create a tablespace
 


 
Create tablespace data01
 
Datafile '/Oracle/oradata/DB/data01.dbf' size 500 m

Uniform size 128 K; # specify the area size as 128 K. If not specified, the default area size is 64 K.
 


 

2. Create an undo tablespace
 



Create undo tablespace undotbs02
 

Datafile '/Oracle/oradata/DB/undotbs02.dbf' size 50 m

 


 

# Note: In the open state, only one undo tablespace can be used at some time. To use a new tablespace, you must switch to the tablespace:
 



Alter system set undo_tablespace = undotbs02;
 


 

3. Create a temporary tablespace
 



Create temporary tablespace temp_data

Tempfile '/Oracle/oradata/DB/temp_data.dbf' size 50 m
 


 

Iv. Change the tablespace status
 



1. Take the tablespace offline
 



Alter tablespace game offline;

If the data file is accidentally deleted, the recover option must be included.
 


 

Alter tablespace game offline for recover;
 



2. Bring the tablespace online
 



Alter tablespace game online;
 


 



3. offline data files
 



Alter database datafile 3 offline;
 



4. Bring data files online
 



Alter database datafile 3 online;

 



5. Read-Only tablespace
 



Alter tablespace game read only;
 



6. Make the tablespace readable and writable
 



Alter tablespace game read write;
 


 

5. Delete tablespaces
 



Drop tablespace data01 including contents and datafiles;
 



Vi. Extended tablespace
 



First, check the name and file of the tablespace.
 



Select tablespace_name, file_id, file_name,

Round (Bytes/(1024*1024), 0) total_space

From dba_data_files

Order by tablespace_name;
 



1. Add data files

Alter tablespace game

Add datafile '/Oracle/oradata/DB/game02.dbf' size 1000 m;
 



2. manually add data file size

Alter database datafile '/Oracle/oradata/DB/game. dbf'

Resize 4000 m;
 


 

3. Set automatic expansion of data files
 

Alter database datafile '/Oracle/oradata/DB/game. DBF
 

Autoextend on next 100 m

 
Maxsize 10000 m;
 



 

View tablespace information after setting



 


Select a. tablespace_name, A. bytes total, B. bytes used, C. bytes free,

(B. bytes * 100)/A. bytes "% used", (C. bytes * 100)/A. bytes "% free"

 
From SYS. sm $ ts_avail A, SYS. sm $ ts_used B, SYS. sm $ ts_free C
 

Where a. tablespace_name = B. tablespace_name and A. tablespace_name = C. tablespace_name;

 

Within 10 Gb of ora

Alter Database
Datafile '/u07/Oracle/oradata/train/media01.dbf'
Resize 1g;

Alter Database
Datafile '/u07/Oracle/oradata/train/media01.dbf'
Autoextend on next 100 m maxsize unlimited;

Alter tablespace Media
Resize 1g;

Alter tablespace Media
Autoextend on next 100 m maxsize unlimited;