Oracle tablespace statements

Oracle tablespace is an important part of Oracle databases. The following describes some statements related to Oracle tablespace for your reference.

Create an Oracle 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 Oracle tablespace
Drop tablespace data01 including contents and datafiles;

Modify the tablespace size
Alter database datafile '/path/naddate05.dbf' resize 100 M

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

1. Create an Oracle 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

4. Change the Oracle 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;

4. view the 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;
 

Default ORACLE user and password

Implementation of oracle multi-column subquery

Oracle multi-Table query instance

ORACLE Database Encoding

Oracle memory structure-SGA