Oracle Table Space Query maintenance one of the most complete in the history of the Command encyclopedia (data table space)

A tablespace is a logical division of a database, and a table space can only belong to one database.

All database objects are stored in the specified table space. But the main storage is the table, so called table space. There is at least one table space in the Oracle database. That is, the table space of the system. A table space can include multiple segments. and district. and the smallest block, which can also include multiple data files (physical structure) at the same time.
Oracle is able to significantly protect the relevant user quotas and disk space based on the tablespace, and can also be stored flexibly. At the same time can also be different data files stored separately, to ensure that different data files of the security line.

So after the general data is created, the relevant tablespace is created and the associated user is created.

Oracle database pioneered the design concept of tablespaces, which made an indelible contribution to the high performance of Oracle databases. To be able to say this, much of the optimizations in Oracle are based on the design philosophy of the tablespace.

The following summarizes some related commands for the data table space

1, Table space Summary

--Create a table space script
CREATE tablespace Test_file
LOGGING
DataFile '/u02/app/oracle/oradata/itsdb/test_file. DBF '
SIZE 10M
Autoextend on
NEXT 10M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL;

--View the user's default tablespace
SELECT T.username,t.default_tablespace from Dba_users T

--Change the user's default tablespace tablespace_b
ALTER USER user_a DEFAULT tablespace tablespace_b

--User release restrictions on tablespace
ALTER USER user_a QUOTA UNLIMITED on Tablespace_b;

--Reclaim permissions
REVOKE UNLIMITED tablespace on tablespace_a from user_a

--so that the User_a account cannot create any objects on tablespace_a.
ALTER USER user_a QUOTA 0 on Tablespace_a


--table space for moving tables
ALTER TABLE CQRM. Cq_flights_seats_sequence MOVE tablespace cqrm; COMMIT;

--a statement that moves the table space in bulk
SELECT ' ALTER TABLE ' | | table_name| | ' MOVE tablespace cqrm; COMMIT; ' From User_tables WHERE tablespace_name= ' TEST ';

--Rebuilding an index on a table space
SELECT ' ALTER INDEX ' | | owner| | '. ' | | index_name| | ' REBUILD tablespace cqrm; COMMIT; ' From Dba_indexes WHERE table_name in (")
and owner= ' TEST '


SELECT ' ALTER TABLE ' | | owner| | '. ' | | table_name| | ' MOVE tablespace TEST; COMMIT; ' From Dba_tables WHERE tablespace_name= ' TEST1 ' and owner= ' TEST '

2, change table space status

1. Take the table space offline

ALTER tablespace GAME OFFLINE;

Assuming that the data file was accidentally deleted, you must have the recover option

ALTER tablespace GAME OFFLINE for RECOVER;

2. Bring the table space online

ALTER tablespace GAME ONLINE;

3. Take the data file offline

ALTER DATABASE datafile 3 OFFLINE;

4. Bring the data file online

ALTER DATABASE datafile 3 ONLINE;

5. Make table spaces Read Only

ALTER tablespace GAME READ only;

6. Make table space readable and writable

ALTER tablespace GAME READ WRITE;

Delete Table space

DROP tablespace DATA01 including CONTENTS and datafiles;

Extending table Space

3, first look at the table space name and the file that belongs to

SELECT Tablespace_name, file_id, file_name,
ROUND (bytes/(1024*1024), 0) Total_space
From Dba_data_files
ORDER by Tablespace_name;

1. Adding Data files
ALTER tablespace GAME
ADD datafile '/oracle/oradata/db/game02. DBF ' SIZE 1000M;

2. Manually add data file size
ALTER DATABASE datafile '/oracle/oradata/db/game. DBF '
RESIZE 4000M;

3. Set the data file to expand itself
ALTER DATABASE datafile '/oracle/oradata/db/game. DBF '
Autoextend on NEXT 100M
MAXSIZE 10000M;
4. Change the location of the data file
ALTER tablespace App_Data RENAME datafile
"To"


--Consolidate the fragmentation of the table space
ALTER tablespace tablespacename Coalesce
This statement is a continuation of the table space that consolidates the fragmentation of the tablespace, but he does not shrink the size of a file.

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;

--View Table space usage

Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (G)",
D.tot_grootte_mb-f.total_bytes "used Space (G)",
Case
When D.TOT_GROOTTE_MB = 0 Then
0
ELSE
To_number (To_char (ROUND (d.tot_grootte_mb-f.total_bytes)/
D.TOT_GROOTTE_MB * 100,
2),
' 990.99 '))
END "Use ratio",
F.total_bytes "Spare space (G)",
F.max_bytes "Max Block (G)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024x768 * 1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER by 4 DESC
--also be able to query from the view dba_tablespace_usage_metrics the assumption that the table space is not the active expansion of the two query results also, assuming that the self-expansion of the two are biased, whichever is the above query


--View the table spaces that the user has used
SELECT OWNER, Object_type, Tablespace_name
From (SELECT DISTINCT ' TABLE ' object_type, OWNER, Tablespace_name
From Dba_tables
UNION
SELECT DISTINCT ' INDEX ' object_type, OWNER, Tablespace_name
From Dba_indexes)
WHERE Tablespace_name is not NULL
and OWNER = ' Ezoffice '
ORDER by 1, 2, 3;


SELECT T.segment_name, T.tablespace_name, bytes/1024/1024
From Dba_segments T
WHERE T.segment_name in
(‘‘)
and OWNER = ' USER '

--Ability to view the recovered data file size
SELECT ' ALTER DATABASE datafile ' | | A.file_name | | "' RESIZE ' | |
ROUND (A.filesize-(a.filesize-c.hwmsize-100) * 0.8) | | ' M; ',
A.filesize | | ' M ' as "total size of the data file",
C.hwmsize | | Useful size of ' M ' as "Data file"
From (SELECT file_id, file_name, ROUND (bytes/1024/1024) as FILESIZE
From Dba_data_files) A,
(SELECT file_id, ROUND (MAX (block_id) * 8/1024) as Hwmsize
From Dba_extents
GROUP by file_id) C
WHERE a.file_id = c.file_id

and a.filesize-c.hwmsize > 100;

The next article will continue to tell the Uodo table space

Oracle Table Space Query maintenance one of the most complete in the history of the Command encyclopedia (data table space)