Oracle's various table spaces

system table Space: contains data dictionary information

Sysaux tablespace: Stores metadata for various Oracle applications (such as AWR operational data)

You cannot change the size of a table space after you create it

Area Size Management:
Automatic Assignment (autoallocate): Small segment 64K, Middle 1M, large segment 64M
Uniform allocation (UNIFORM): With size, you can choose UNIFORM Area Dimension Management If you think that all segments of the tablespace are roughly the same size and that they will grow in a similar way.

Manual segment space management, using the free list, pctfree,pctused

The Pctfree parameter allows you to retain a certain percentage of space in each block of data.

Automatic segment space management, using bitmaps to track the availability of a segment's available space.

CREATE TABLE Space
Create tablespace datafile creating a permanent table space
Create temporary tablespace tempfile
Create Undo Tablespace

Sql>r
1 Create tablespace Test1
2 datafile '/u02/app/oracle/test1.dbf '
Size 1M
Tablespace created.

Sql>r
1* Select Tablespace_name,allocation_type,contents,extent_management,segment_space_management from Dba_tablespaces

Tablespace_name allocation_type CONTENTS extent_management segment_space_management
----------------------------- --------- --------- -------------------- -------------------------
System System Permanent LOCAL MANUAL
Sysaux SYSTEM PERMANENT LOCAL AUTO
UNDOTBS1 SYSTEM UNDO LOCAL MANUAL
TEMP Uniform temporary LOCAL MANUAL
USERS SYSTEM PERMANENT LOCAL AUTO
TEST1 SYSTEM PERMANENT LOCAL AUTO

Create Tablespace Test3
DataFile '/u02/app/oracle/test3.dbf ' size 8m
Uniform size 4m;

1M Unified Zone is created by default if the zone assignment uses uniform unspecified size

Sql>select Initial_extent,next_extent,extent_management,allocation_type,segment_space_management,tablespace_ Name from Dba_tablespaces;

Initial_extent next_extent extent_management allocatio segment_space_management TABLESPACE_NAME
-------------- ----------- -------------------- --------- ------------------------- ------------------------------
65536 LOCAL System MANUAL System
65536 LOCAL SYSTEM AUTO sysaux
65536 LOCAL SYSTEM MANUAL UNDOTBS1
1048576 1048576 LOCAL UNIFORM MANUAL TEMP
65536 LOCAL SYSTEM AUTO USERS
65536 LOCAL SYSTEM AUTO TEST1
4194304 4194304 LOCAL UNIFORM AUTO TEST3

Increase table space Size:
Alter tablespace test1 add datafile '/u02/app/oracle/test12.dbf ' size 1m;
ALTER DATABASE datafile '/U02/APP/ORACLE/TEST1.DBF ' resize 20m;

Automatically adds 10m,maxsize to table space when requesting tablespace to 100M
Alter tablespace test3 add datafile '/u02/app/oracle/test31.dbf ' size 10m
Autoextend on
Next 10m
MaxSize 100m

Delete Table space
Drop Tablespace test4;
Drop tablespace test4 including CONTENTS and datafiles;
Drop tablespace test3 cascade constraints; Remove referential integrity constraint for table

Sql>desc v$sysaux_occupants
Name Null? Type
----------------------------------------- -------- ----------------------------
Occupant_name VARCHAR2 (64)
Occupant_desc VARCHAR2 (64)
Schema_name VARCHAR2 (64)
Move_procedure VARCHAR2 (64)
Move_procedure_desc VARCHAR2 (64)
Space_usage_kbytes number

Select Occupant_name,schema_name,move_procedure from V$sysaux_occupants;

Occupant_name Schema_nam Move_procedure
-------------------- ---------- ----------------------------------------------------------------

EM Sysman EMD_MAINTENANCE.MOVE_EM_TBLSPC
TEXT Ctxsys Dri_move_ctxsys
Ultrasearch Wksys Move_wk
Ultrasearch_demo_use wk_test Move_wk

If you want to move Sysaux's possessions ultrasearch to a new tablespace ULTRA1, you can use the MOVE_WK process owned by Wksys mode to complete
Exect Wksys. Move_wk (' ULTRA1 ');

Warning limit values
Warning limit value type: percent full, number of bytes remaining
Set Alert limit value: oem--administration--related links--manage metrics--edit Thresholds
oem--RELATED LINKS--metrics and policy settings--Edit

Sql>begin
Dbms_server_alert. Set_threshold (
metrics_id =>dbms_server_alter. Tablespace_byt_free,
Warning_operator =>dbms_server_alter. Operator_le,
Warning_value = ' 10240 ',
Critical_operator =>dbms_server_alter. Operator_le,
Critical_value = ' 2048 ',
Observation_period =>1,
Consecutive_occurrences =>1,
Instance_name =>null,
Object_type =>dbms_server_alter. Object_type_tablespace,
object_name = ' users ');
END;

The above warning limit value Warning_value is 10M, the critical limit value Critical_value is 2M

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

Tablespace_n total used free% used% free
------------ ---------- ---------- ---------- ---------- ----------
Sysaux 629145600 545652736 82444288 86.72 13.28
UNDOTBS1 550502400 44302336 505151488 8.04 91.96
USERS 5242880 327680 3866624 6.25 73.75
SYSTEM 734003200 684457984 48496640 93.25 6.65

Table Space Offline
Alter tablespace test1 offline;

Renaming a table space
Alter tablespace test1 rename to test2;

Select file#, name from V$datafile

file# NAME
---------- --------------------------------------------------
1 +data/ora11g/datafile/system.260.823205117
2 +data/ora11g/datafile/sysaux.261.823205185
3 +data/ora11g/datafile/undotbs1.262.823205249
4 +data/ora11g/datafile/users.264.823205299
5/u02/app/oracle/test1.dbf
6/u02/app/oracle/test3.dbf
8/u02/app/oracle/test12.dbf
9/u02/app/oracle/test31.dbf

Select Tablespace_name, file_name,file_id,autoextensible from Dba_data_files;

Tablespace_n file_name file_id autoextensible
------------ -------------------------------------------------- ---------- --------------------
SYSTEM +data/ora11g/datafile/system.260.823205117 1 YES
Sysaux +data/ora11g/datafile/sysaux.261.823205185 2 YES
UNDOTBS1 +data/ora11g/datafile/undotbs1.262.823205249 3 YES
USERS +data/ora11g/datafile/users.264.823205299 4 YES
TEST1/U02/APP/ORACLE/TEST1.DBF 5 NO
TEST3/U02/APP/ORACLE/TEST3.DBF 6 NO
TEST1/U02/APP/ORACLE/TEST12.DBF 8 NO
TEST3/U02/APP/ORACLE/TEST31.DBF 9 YES

Rename the data file so that the table space is taken offline so that the data file is offline
Alter tablespace test1 offline
Under OS: CP or MV
Under DB: Alter tablespace test1 rename datafile '/u01/app/oracle/test1.dbf ' to '/u02/app/oracle/test1.dbf ';

Read-Only table space
Alter Tablespace Test1 Read only;
Alter tablespace Test1 Read write;

Sort within a temporary table space

By default, all temporary table spaces are created with a uniformly sized, locally managed zone.

When you assign the first temporary tablespace to a table space group, the temporary tablespace group is created automatically.
Alter tablespace TEMP1 tablespace group group1; if Group1 does not exist, the statement will be automatically created group1

Oracle's various table spaces