Orcle 11g create tablespace

 

The sys user logs in as a DBA under cmd:

Run sqlplus/nolog in cmd.

Then

Conn/As sysdba

// Four steps

// Step 2: create a temporary tablespace

 

Create temporary tablespace user_temp

Tempfile 'd: \ app \ Administrator \ oradata \ orcl \ user_temp.dbf'

Size 50 m

Autoextend on

Next 50 m maxsize 20480 m

Extent management local;

 

// Step 2: create a data table space

Create tablespace test_daTa

Logging

Datafile 'd: \ app \ Administrator \ oradata \ orcl \ user_data.dbf'

Size 50 m

Autoextend on

Next 50 m maxsize 20480 m

Extent management local;

 

// Step 2: create a user and specify a tablespace

Create User Username identified by password

Default tablespace user_daTa

Temporary tablespace user_temp;

 

// Step 4: grant permissions to the user

 

Grant connect, resource to username;

 

---------------------------------------------------------------------------------

// Log on with this user. Any database objects created belong to user_temp and user_da.Ta tablespace,

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