Oracle operation-tablespace

Delete a user

Drop user user_name cascade;

Create a tablespace

Create tablespace data01
Datafile '/Oracle/oradata/DB/data01.dbf' size 500 m
Uniform. Size 128 K; # specify the partition size as 128 K. If not specified, the default partition size is 64 K.

Delete a tablespace

Drop tablespace data01 including contents and datafiles;

1. Create a tablespace

Create tablespace data01
Datafile '/Oracle/oradata/DB/data01.dbf' size 500 m
Uniform. Size 128 K; # specify the partition size as 128 K. If not specified, the default partition 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. If you want to use a new tablespace, you must switch

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