A tablespace is a logical division of a database, and a table space can belong to only 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 tablespace in the Oracle database, which is the table space for the system. A table space can contain multiple segments, as well as extents, as well as the smallest blocks, and can also contain multiple data files (physical structure).
Oracle can greatly protect the relevant user quotas and disk space based on the tablespace, and also can store the different data files separately to ensure the security of different data files.
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. As you can say, many 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
--Modify 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;
If 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 Tablespace 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. Add 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 data File Auto-expansion
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 to consolidate the fragmentation of the tablespace to increase the continuity of the table space, 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 "free 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
--You can also query from view dba_tablespace_usage_metrics if the table space is not auto-expanded then the query results are the same, if it is automatically extended then 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 '
--can 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 | | Practical 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
One of Oracle table space Query maintenance commands (data table space) the most complete in history
