Database logical storage structure Management (5)-Storage-table space segment chunks

viewing tablespace information
SELECT * from V$tablespace

View tablespace data File path information
SELECT * from Dba_data_files;


To view property information for a tablespace
SELECT * from Dba_tablespaces

To view information about a table space group and the tablespace it belongs to
SELECT * from Dba_tablespace_groups
View the composition of tables inside a tablespace
SELECT segment_name,segment_type,tablespace_name from dba_segments WHERE tablespace_name= ' USERS '

To view information about an idle interval in a tablespace
SELECT tablespace_name,bytes,blocks from Dba_free_space;
SYS as [email protected]>select d.tablespace_name,trunc (d.bytes/2014) | | ' M ', d.blocks,e.file_name from Dba_free_space d,dba_data_files E WHERE d.tablespace_name=e.tablespace_name;



Create a tablespace (local management table space)
CREATE smallfile tablespace "RSC" DataFile '/U01/APP/ORACLE/ORADATA/ORCL/RSC. DBF ' SIZE 100M reuse autoextend on NEXT 100M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
Autoallocate//Specifies that the table space is managed by the system
UNIFORM//Manually Manage interval sizes

Large File Table space:
Large file tablespace consists of a unique, very large data file, the ordinary small file table space can contain multiple data files, but the large file table space can contain only one data file




Undo Table Space

SYS as [email protected]>create UNDO tablespace UNDOTBS01 datafile '/u01/app/oracle/oradata/orcl/undotbs01. DBF ' SIZE 2M reuse;

To rename a tablespace by using the ALTER TABLESPACE statement
ALTER tablespace UNDOTBS01 RENAME to UNDOTBS001;


View the table space after use
SELECT * from V$tablespace;

Adding data files to the local management table space
ALTER tablespace RSC ADD datafile '/u01/app/oracle/oradata/orcl/rsc011. DBF ' SIZE 2M reuse;


++++++++++++++++++++++++++++++++++++++++++++++++
ALTER tablespace command to rename a data file:
1. Take the table space offline.
2. Use operating system commands to move or copy files.
3. Execute ALTER tablespace RENAME datafile command.
4. Bring the table space online.
5, if necessary, use the operating system command to delete the original data file.

Here is an example:

1, alter tablespace tbs_t02 offline normal;----table space offline
2, Linux execution: CP tbs_t02.dbf./test/------Linux Modify the data file or rename it
3, alter tablespace tbs_t02 rename datafile '/opt/oracle/oradata/ora36/tbs_t02.dbf ' to '/opt/oracle/oradata/ora36/test /TBS_T02.DBF ';------Execute the Modify command, the first path is the original path, the second is the data that will be changed
4, Alter tablespace tbs_t02 online;--------table Space Online
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Modify the properties of a large file table space
ALTER tablespace Bigtbs RESIZE 4G;

To add a temporary file to a temporary table space
ALTER tablespace TEMP ADD datafile '/u01/app/oracle/oradata/orcl/temp01. DBF ' SIZE 20M reuse;

To view the status of a table space
Offline and online

Set tablespace to offline status in these cases
1. Set a part of the database to be inaccessible, while the other parts can access
2. Performing an offline table space backup
3. Temporarily set the application and the tables it uses to be inaccessible when upgrading or maintaining the application
4. Re-command or reassign table spaces
system table space (systems), Undo tablespace (undo), Temporary tablespace (temporary) cannot be set to offline state
ALTER tablespace RSC OFFLINE; Offline
ALTER tablespace RSC ONLINE; Online

View Table Space Status
SELECT tablespace_name,contents,status from Dba_tablespaces;

To set a read-only table space:
ALTER tablespace RSC READ only;
To view table space status:
SELECT Tablespace_name, STATUS from Dba_tablespaces;
Set table space Read and write:
ALTER tablespace RSC READ WRITE;


To delete a table space:
DROP tablespace RSC;
Delete the included segments and data files while deleting the tablespace
DROP tablespace RSC including CONTENTS and datafiles;
To view the name of a tablespace:
SELECT * from V$tablespace;


Usage of the statistical table space
Dba_data_files: Information for querying all data files
Dba_free_space: Idle interval information for querying table spaces
Dba_tablespaces: Information for querying all table spaces
To count the total space size of all table spaces
SELECT tablespace_name,sum (BYTES)/1024/1024 bytes_m from Dba_data_files GROUP by Tablespace_name;
To count the size of all table space free space
SELECT A.tablespace_name, SUM (b.bytes)/1024/1024 bytes_m
From Dba_data_files A,dba_free_space b
WHERE A.tablespace_name=b.tablespace_name (+) and a.file_id=b.file_id (+)
GROUP by A.tablespace_name;
Usage of the statistical table space:
Select C.tablespace_name tablespace, ROUND (a.bytes/1024/1024,2) tablespace size, ROUND ((a.bytes-b.bytes)/1048576,2) "Table space used", ROUND (b.bytes/1048576,2) "Remaining space", ROUND
(b.bytes/a.bytes*100,2) | | ' % ' "percent remaining"
From
(SELECT tablespace_name,sum (bytes) bytes from Dba_data_files GROUP by Tablespace_name) A,
(SELECT A.tablespace_name,nvl (SUM (b.bytes), 0) bytes from dba_data_files A, dba_free_space b
WHERE A.tablespace_name=b.tablespace_name (+) and a.file_id=b.file_id (+) GROUP by A.tablespace_name) b,dba_tablespaces C
WHERE A.tablespace_name=b.tablespace_name (+) and A.tablespace_name=c.tablespace_name ORDER by ROUND (b.bytes/1024/ 1024,2);

Create a staging tablespace group in the Create TABLESPACE statement
CREATE Temporary tablespace temp_spc
Tempfile '/U01/APP/ORACLE/ORADATA/ORCL/TEMP03. DBF '
SIZE 20M
Tablespace GROUP temp_grp;

To view the space usage of all temporary tablespace:
SELECT * from Dba_temp_free_space;
View property information for data files for all temporary tablespace:
SELECT * from Dba_temp_files;
SELECT * from V$tempfile;
To scale out a temporary tablespace, use the following statement
ALTER tablespace &tablespace_name ADD tempfile '/oradata/temp01.dbf ' SIZE 2G;

To view the staging tablespace group:
SELECT * from Dba_tablespace_groups;
Add the temporary tablespace that you have created to the temporary table space Group:
ALTER tablespace temp_spc01 tablespace GROUP temp_grp;

To create a temporary tablespace group name by using the ALTER TABLESPACE statement:
ALTER tablespace temp_spc01 tablespace GROUP new_temp_group;
To remove a tablespace from a temporary table space group
ALTER tablespace temp_spc01 tablespace GROUP ';

Assigning a temporary tablespace to a specified user
Allocate a temporary tablespace when creating a user
CREATE USER LEE identified by 123
DEFAULT tablespace USERS
Temporary tablespace new_temp_group;
Assigning temporary table spaces to a specified user
ALTER USER RSC temporary tablespace temp_grp;
Viewing user temporary tablespace information
SELECT username,default_tablespace,temporary_tablespace from Dba_users WHERE username= ' RSC ';
Set the default temporary tablespace group for a database
ALTER DATABASE ORCL DEFAULT temporary tablespace new_temp_group;

Shrinking temporary table spaces
SYS as [email protected]>select tablespace_name,bytes/1024/1024 from Dba_temp_files;

Tablespace_name bytes/1024/1024
------------------------------ ---------------
TEMP 29

ALTER tablespace TEMP SHRINK SPACE KEEP 25M;

SYS as [email protected]>select tablespace_name,bytes/1024/1024 from Dba_temp_files;

Tablespace_name bytes/1024/1024
------------------------------ ---------------
TEMP 26


Monitoring temporary table space
To view the temporary tablespace usage:
SELECT TU. Tablespace_name as "Tablespace_name",
Tt. Total-tu. Used as "free (G)",
Tt. Total as "total (G)",
ROUND (NVL (TU. Used, 0)/TT. Total *, 3) as "Used (%)",
ROUND (NVL (TT. Total-tu. Used, 0) * 100/tt. Total, 3) as "Free (%)"
From (SELECT Tablespace_name,
SUM (bytes_used)/1024/1024/1024 used
From Gv_$temp_space_header
GROUP by Tablespace_name) TU,
(SELECT Tablespace_name,
SUM (BYTES)/1024/1024/1024 as Total
From Dba_temp_files
GROUP by Tablespace_name) TT
WHERE TU. Tablespace_name = TT. Tablespace_name;


COL temp_file for A60;
Select ROUND ((F.bytes_free + f.bytes_used)/1024/1024/1024, 2) as "Total (GB)",
ROUND (((F.bytes_free + f.bytes_used)-NVL (p.bytes_used, 0))/1024/1024/1024,2) as "Free (GB)",
D.file_name as "Temp_file",
ROUND (NVL (p.bytes_used, 0)/1024/1024/1024, 2) as "Used (GB)",
ROUND ((f.bytes_used + f.bytes_free)/1024/1024/1024, 2) as "Total (GB)",
ROUND (((f.bytes_used + f.bytes_free)-NVL (p.bytes_used, 0))/1024/1024/1024, 2) as "Free (GB)",
ROUND (NVL (p.bytes_used, 0)/1024/1024/1024, 2) as "Used (GB)"
From SYS. V_$temp_space_header F, Dba_temp_files D, SYS. V_$temp_extent_pool P
WHERE F.tablespace_name (+) = D.tablespace_name
and f.file_id (+) = d.file_id
and p.file_id (+) = d.file_id;


To view the usage of temporary files that correspond to temporary table spaces
SELECT Tablespace_name as Tablespace_name,
bytes_used/1024/1024/1024 as tablesapce_used,
bytes_free/1024/1024/1024 as Tablesapce_free
From V$temp_space_header
ORDER by 1 DESC;

Finding SQL statements that consume more temporal tablespace resources
SELECT Se.username,
Se.sid,
Su.extents,
Su.blocks * To_number (RTrim (P.value)) as Space,
Tablespace,
Segtype,
Sql_text
From V$sort_usage Su, v$parameter p, v$session se, v$sql s
WHERE p.name = ' db_block_size '
and su.session_addr = Se.saddr
and S.hash_value = Su.sqlhash
and s.address = Su.sqladdr
ORDER by Se.username, Se.sid;



To create a rollback segment:
CREATE [public] ROLLBACK SEGMENT rollback_segment

[Tablespace tablespace]

[STORAGE ([INITIAL integer[k| M]] [NEXT integer[k| M]]

[Minextents integer]

[Maxtents {integer| UNLIMITED}]

[OPTIMAL {integer[k| m]| NULL}])]

Note:

The rollback segment can indicate private or public at the time of creation and cannot be modified once created.

Minextents must be greater than or equal to 2

Pctincrease must be 0.

Optimal if you want to specify, must be greater than or equal to the initial size of the rollback segment (specified by minextents)

Suggestions:

In general, Initial=next

Set the optimal parameter to save space usage

Do not set maxextents to unlimited

The rollback segment should be created within a specific rollback segment table space

Cases:


CREATE ROLLBACK SEGMENT rbs01

Tablespace RBS

STORAGE (INITIAL 100K NEXT 100K minextents 10

MAXEXTENTS OPTIMAL 1000K);
Make rollback segment Online

When the rollback segment is created, the rollback segment is offline and cannot be used by the database, in order for the rollback segment to be exploited by the transaction, the rollback segment must be online. You can use the following command to bring the rollback segment online:
ALTER ROLLBACK SEGMENT rollback_segment ONLINE;
Cases:
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
In order for the rollback segment to be online automatically when the database starts, you can list the name of the rollback segment in the database's parameter file. For example, add the following line to the parameter file:
Rollback_segment= (RBS01,RBS02)


Types of rollback segments:
System rollback segment:
Used only to hold front images of objects in the system table space

Non-system rollback segments: (Private rollback segment and public rollback segment)
Database must have at least one non-system rollback segment
Private rollback segments: listed in rollback segments in the parameter file
Public rollback segment: appears in OPS (ORACLE parallel server)
defered rollback segment: The rollback segment is automatically created by the system when the table space is offline (OFFLINE) and is automatically deleted by the system when the tablespace is online again, to hold the rollback information generated when the tablespace is offline.


A transaction can request a specified rollback segment with the following statement:
SET transtraction use ROLLBACK SEGMENT rollback_segment



View rollback Information
SELECT segment_name,tablespace_name,bytes,next_extent from dba_segments WHERE segment_type= ' ROLLBACK ';
SELECT * from V$rollname;
SELECT * from V$rollstat;
To view the current operation of the rollback segment:
SELECT s.usn,n.name,s.extents,s.optsize,s.hwmsize,s.status from V$rollstat S, v$rollname N WHERE S.usn=n.usn;
To set the management mode for rollback segments:
To view the current management mode:
SHOW PARAMETER undo_management
To modify the management mode of a rollback segment:
ALTER SYSTEM SET undo_management=manual scope=spfile; The instance will not take effect until you launch it
User-created rollback segments require that the rollback segment be set to manual to modify:
To view how table space segments are managed:
SELECT tablespace_name,segment_space_management from Dba_tablespaces;
To create a rollback segment:
CREATE ROLLBACK SEGMENT ORCLRS01 tablespace RSC STORAGE (inital 5M NEXT 2M MAXEXTENTS UNLIMITED)
To modify the state of a rollback segment:
ALTER ROLLBACK SEGMENT < rollback segment name > < rollback segment status >
ALTER ROLLBACK SEGMENT ORCLRS01 ONLINE;
To view the rollback segment status:
SYS as [email protected]>select name,status from V$rollname,v$rollstat WHERE v$rollstat. Usn=v$rollname. USN;

NAME STATUS
------------------------------ ---------------
SYSTEM ONLINE
To manually shrink the rollback segment:
ALTER ROLLBACK SEGMENT ORCLRS01 SHRINK to 1M;

To delete a rollback segment:
1.ALTER ROLLBACK SEGMENT ORCLRS01 OFFLINE;
2.DROP ROLLBACK SEGMENT ORCLRS01;
3.SELECT * from V$rollname;

Database logical storage structure Management (5)-Storage-table space segment chunks