Oracle Application Experience-tablespace

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_name;

///////////////////////////////////////////////////////////////////////////

1. Go to Oracle # Su-oracleas4101> LSNRCTL startas4101> connect internal; svrmgrl> shutdown immediate; svrmgrl> startup 2. Check the table spaces svrmgrl> select * From dba_tablespaces; system RBS temp tools users 3. Set the users tablespace dropsvrmgrl> alter tablespace users offline; svrmgrl> drop tablespace users; 4. view the tablespace's free size svrmgrl> select tablespace_name, sum (bytes) /1024/1024 mbfrom dba_free_space group by tablespace_na Me; tablespace_name MB partition --------- dd_data 1136.3672dd _ idx 787.18164jx _ Data Partition _ idx 503.16016rbs 371.9668 system 457.81445 temp 1499.9961 tools 36.462891 5. view the data file path svrmgrl> select tablespace_name, bytes/1024/1024 MB, file_namefrom dba_data_files; tablespace_name MB file_name ------------------------------ --------- ------------- System 500/dev/rdrd/drd4rbs 500/dev/R DRD/drd14rbs 1000/dev/rdrd/drd15rbs 500/dev/rdrd/drd32tools 50/dev/rdrd/drd5temp 1000/dev/rdrd/drd22temp 500/dev/rdrd/release 500/ dev/rdrd/drd33. The system tablespace has a rollback segment, it is system, and some other rollback segments belong to RBS. First, the rollback segments under RBS are offline and drop, then the RBS tablespace is drop and re-created, and finally the rollback segments are created. Four rollback segments, each of which is RBS/4. This value can be considered as an optimal value, that is, equal to initial + next * maxextentssvrmgrl> alter rollback segment R01 offline; svrmgrl> drop rollback segment R01; svrmgrl> alter tablespace RBS offline; svrmgrl> drop tablespace RBS; svrmgrl> Create tablespace "RBS" datafile '/dev/rdrd/rbs01.ora' size 500 m, '/dev/rdrd/rbs02.ora' size 500 m; svrmgrl> Create rollback segment "R01" tablespace "RBS" Storage (initial 200 m next 2 m optim Al 250 mminextents 2 maxextents 25); 7. Check the status of the rollback segments and tablespaces. If it is online, it ends. If it is offline, onlinesvrmgrl> select segment_name, tablespace_name, status from dba_rollback_segs; svrmgrl> alter rollback segment R01 online; 8. Temporary tablespace temp, drop first, and then recreate. Svrmgrl> alter tablespace temp offline; svrmgrl> drop tablespace temp; svrmgrl> Create tablespace temp datafile '/dev/rdrd/drd22' size 1000 m storage (initial 300 m next 20 mminextens 2 maxextents 35 pctincrease 0 ); 9. The tool tablespace tools are 50 MB in size and the system tablespace is MB in size. 10. Create a data table space: dd_data, dd_idx, jx_data, jx_idx, sf_data, sf_idx, jf_data, jf_idxsvrmgrl> Create tablespace dd_data datafile '/dev/rdrd/drd9' size 1000, '/dev/rdrd/drd10' size 1000 m,'/dev/rdrd/drd26' size 1000 m, '/dev/rdrd/drd35' size 1000 m, '/dev/rdrd/drd42' size 500 m; 11. create user svrmgrl> create user ddbh identified by ddbhdefault tablespace quota tablespace tempquota unlimited on quota unlimited on rbsquota unlimited on temp; 12. user permission svrmgrl> grant connect, resources, imp_full_database, exp_full_database, create public synonym, drop public synonym to ddbh; To view v $ session, kill session, drop user, create user, etc, then svrmgrl> grant select on V _ $ session to public; svrmgrl> grant alter system, drop user, create user "*******";