Create a tablespace in oracle

The SYS user logs in as a DBA under CMD:
 
Run sqlplus/nolog in CMD.
 
Then
 
Conn/as sysdba
 
// Create a temporary tablespace
 

 
Create temporary tablespace user_temp
 
Tempfile 'd: \ oracle \ oradata \ Oracle9i \ user_temp.dbf'
 
Size 50 m
 
Autoextend on
 
Next 50 m maxsize 20480 m
 
Extent management local;
 

 
// Create a data table space
 
Create tablespace test_data
Logging
 
Datafile 'd: \ oracle \ oradata \ Oracle9i \ user_data.dbf'
Size 50 m
 
Autoextend on
 
Next 50 m maxsize 20480 m
 
Extent management local;
 

 
// Create a user and specify the tablespace
 
Create user username identified by password
 
Default tablespace user_data
Temporary tablespace user_temp;
 

 
// Grant permissions to users
 

 
Grant connect, resource to username;
 

 
// Log on to the user later. Any database objects created belong to the user_temp and user_data tablespaces,
This eliminates the need to specify a tablespace for each created object.
 
Withdrawal:
 
Revoke permission... from user name;
 
Delete USER commands
 
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 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;
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

Author: TinyKing"