One of the most comprehensive oracle tablespace query and maintenance commands (data table space) in history
A tablespace is the logical division of a database. A tablespace can belong to only one database. All database objects are stored in the specified tablespace. But it mainly stores tables, which are called tablespaces. There must be at least one tablespace in the oracle database, that is, the tablespace of the SYSTEM. A tablespace can contain multiple segments, partitions, and the smallest blocks. It can also contain multiple data files (physical structures ).
Oracle can greatly protect user quotas and disk space based on tablespaces. It also supports flexible storage and separate storage of different data files, ensure the security of different data files.
Therefore, after the general data is created, the relevant tablespace is created, and then the relevant users are created. Oracle Database pioneered the design concept of tablespace, which makes an indelible contribution to the high performance of Oracle Database. In this case, many Oracle optimizations are implemented based on the tablespace design concept.
The following are some related commands for data table space.
1. Table space summary
-- Create a tablespace script
Create tablespace TEST_FILE
LOGGING
DATAFILE '/U02/APP/ORACLE/ORADATA/ITSDB/test_file.dbf'
SIZE 10 M
AUTOEXTEND ON
NEXT 10 m maxsize 10240 M
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
-- Users can release restrictions on tablespaces.
Alter user USER_A quota unlimited on TABLESPACE_ B;
-- Revoke 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
-- Move the table tablespace
Alter table cqrm. CQ_FLIGHTS_SEATS_SEQUENCE move tablespace cqrm; COMMIT;
-- Statement for batch moving tablespaces
SELECT 'alter table' | TABLE_NAME | 'move tablespace cqrm; COMMIT; 'FROM USER_TABLES WHERE TABLESPACE_NAME = 'test ';
-- Re-index the tablespace
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 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;
Delete a tablespace
Drop tablespace DATA01 including contents and datafiles;
Extended tablespace
3. 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;
4. Change the location of the data file
Alter tablespace APP_DATA RENAME DATAFILE
''To''
-- Integrate tablespace fragments
ALTER TABLESPACE TABLESPACENAME COALESCE
This statement increases the continuity of the tablespace by integrating the tablespace fragments, but 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) "tablespace name ",
D. TOT_GROOTTE_MB "tablespace 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_mbit * 100,
2 ),
'1970. 99 '))
END "usage ratio ",
F. TOTAL_BYTES "Idle space (G )",
F. MAX_BYTES "maximum block (G )"
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024x1024*1024), 2) TOTAL_BYTES,
ROUND (MAX (BYTES)/(1024*1024*1024), 2) MAX_BYTES
From sys. DBA_FREE_SPACE
Group by TABLESPACE_NAME) F,
(Select dd. TABLESPACE_NAME,
ROUND (SUM (DD. BYTES)/(1024*1024*1024), 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 the table space in the DBA_TABLESPACE_USAGE_METRICS view. If the table space is not automatically extended, the query results are the same. If the table space is automatically extended, the query results are different. The preceding query prevails.
-- View the tablespaces used by the user.
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'
-- View the size of recycled data files
SELECT 'alter DATABASE datafile' | A. FILE_NAME | ''' resize' |
ROUND (A. FILESIZE-(A. FILESIZE-C. HWMSIZE-100) * 0.8) |'m ;',
A. FILESIZE | 'M' AS "total data file size ",
C. HWMSIZE | 'M' AS "practical size of data files"
FROM (SELECT FILE_ID, FILE_NAME, ROUND (BYTES/1024/1024) AS FILESIZE
FROM DBA_DATA_FILES),
(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 describe the uodo tablespace.
The relationship between oracle tablespace and data files is one-to-many. How can I query a tablespace, including the data files?
Select s. name, d. name from v $ datafile d, v $ tablespace s where d. ts # = s. ts #;
This is the ing between tablespaces and data files. If you want to see which data files are available for a tablespace, you can add conditions after where.
Oracle command for querying data files owned by a tablespace
Query by a user with dba Permissions
Select
B. file_name physical file name,
B. tablespace_name tablespace,
B. bytes/1024/1024 size M,
(B. bytes-sum (nvl (a. bytes, 0)/1024/1024 M used,
Substr (B. bytes-sum (nvl (a. bytes, 0)/(B. bytes) *, 1, 5) Utilization
From dba_free_space a, dba_data_files B
Where a. file_id = B. file_id
Group by B. tablespace_name, B. file_name, B. bytes
Order by B. tablespace_name
