Oracle tablespace Management (Management)

1. First query free space

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

 

2. Add Oracle tablespace

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

select tablespace_name,file_id,bytes,file_name from dba_data_files; 

 

3. The statement for modifying the file size is as follows:

Alter database datafile the path of the data file to be added, that is, the path 'resize 800 m;

 

4. Create an oracle tablespace

View code

Create tablespace test datafile '/home/APP/Oracle/oradata/Oracle8i/test01.dbf' size 8 m autoextend on next 5 m maxsize 10 m; create tablespace sales datafile '/home/APP/Oracle/oradata/Oracle8i/sales01.dbf' size 800 m autoextend on next 50 m maxsize unlimited is not limited in size create tablespace sales datafile '/ home/APP/Oracle/oradata/Oracle8i/sales01.dbf 'size 800 m autoextend on next 50 m maxsize 1000 m extent management local uniform; unform indicates that the partition size is the same, the default value is 1 m create tablespace sales datafile '/home/APP/Oracle/oradata/Oracle8i/sales01.dbf' size 800 m autoextend on next 50 m maxsize 1000 m extent management local uniform size 500 K.; unform size k indicates the same partition size, for 500 k create tablespace sales datafile '/home/APP/Oracle/oradata/Oracle8i/sales01.dbf' size 800 m autoextend on next 50 m maxsize 1000 m extent management local autoallocate; autoallocate indicates that the partition size automatically and dynamically changes with the table size, big Tables use big tables use cells create tablespace sales datafile '/home/APP/Oracle/oradata/Oracle8i/sales01.dbf' size 800 m autoextend on next 50 m maxsize 1000 m temporary; temporary create dictionary management temporary tablespace create temporary tablespace sales tempfile '/home/APP/Oracle/oradata/Oracle8i/sales01.dbf' size 800 m autoextend on next 50 m maxsize 1000 m

Create a temporary tablespace for local management. If it is a temporary tablespace, replace datafile in all statements with tempfile.

By default, the 8i system creates a temporary tablespace for dictionary management. To create a temporary tablespace for local management, the temporary tablespace keyword must be added.

Do not use the atuoallocate parameter when creating local temporary tablespace management. The system creates the uniform management mode by default.

 

5. Delete Oracle tablespace

DROP TABLESPACE FESCO INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; 

 

6. 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 a local temporary Oracle tablespace. If it is a temporary tablespace, replace the datafile in all statements with the tempfile8i system, and create a dictionary to manage the temporary tablespace by default, to create a local management temporary tablespace, you must add the temporary tablespace keyword to create a local management temporary tablespace. Do not use the atuoallocate parameter. By default, the system creates an uniform management mode.

 

7. Modify auto scaling attributes:

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; 

 

8. Create a user and specify the tablespace:

create user username identified by passworddefault tablespace test_datatemporary tablespace test_temp;

 

9. Change the tablespace status

View code

1. offline alter tablespace game offline for tablespace; if a data file is accidentally deleted, it must have the recover option alter tablespace game offline for recover; 2. online alter tablespace game online; 3. offline alter database datafile 3 offline; 4. online alter database datafile 3 online; 5. make the tablespace read-only alter tablespace game read only; 6. enable the tablespace to read and write alter tablespace game read write;

10. Table space classification:

View code

1.Permanent tablespacecreate tablespace ts_something  logging  datafile '/dbf1/ts_sth.dbf'   size 32m   autoextend on   next 32m maxsize 2048m  extent management local;create tablespace data datafile '/home/oracle/databases/ora10/data.dbf'size                                  10Mautoextend on maxsize                200Mextent management local uniform size  64K;2.Temporary tablespacecreate temporary tablespace temp_mtr tempfile '/dbf1/mtr_temp01.dbf' size 32m autoextend on next 32m maxsize 2048mextent management local;Note, a temporary tablespace has tempfiles, not datafiles.  3.Undo tablespacecreate undo tablespace ts_undodatafile '/dbf/undo.dbf' size 100M;4.Misc    More than one datafile can be created with a single create tablespace command: create tablespace ts_sth datafile 'c:\xx\sth_01.dbf' size 4M autoextend off,         'c:\xx\sth_02.dbf' size 4M autoextend off,         'c:\xx\sth_03.dbf' size 4M autoextend offloggingextent management local;

 

 

Reference address:

Http://database.51cto.com/art/200910/158936.htm

Http://www.adp-gmbh.ch/ora/ SQL /create_tablespace.html

Http://space.itpub.net/13873293/viewspace-605134