This article describes how to query and operate Oracle tablespaces. For more information, see
This article describes how to query and operate Oracle tablespaces. For more information, see
I. Query
1. query the usage of oracle tablespace
Select B. file_id File ID,
B. tablespace_name tablespace,
B. file_name physical file name,
B. Total bytes,
(B. bytes-sum (nvl (a. bytes, 0) already in use,
Sum (nvl (a. bytes, 0) remaining,
Sum (nvl (a. bytes, 0)/(B. bytes) * 100 percentage remaining
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. file_id, B. bytes
Order by B. tablespace_name
2. query the default and temporary tablespaces of oracle users
Select default_tablespace, temporary_tablespace from dba_users
3. query the usage of a single table
Select segment_name, bytes from dba_segments where segment_name ='re _ STDEVT_FACT_DAY 'and owner = USER
RE_STDEVT_FACT_DAY is the name of the table to be queried.
4. query the first thirty tables of all users.
Select * from (select segment_name, bytes from dba_segments where owner = USER order by bytes desc) where rownum <= 30
5. query the usage of the default tablespace of the current user.
Select tablespacename, sum (totalContent), sum (usecontent), sum (sparecontent), avg (sparepercent)
From
(
SELECT B. file_id as id, B. tablespace_name as tablespacename, B. bytes as totalContent, (B. bytes-sum (nvl (. bytes, 0) as usecontent, sum (nvl (. bytes, 0) as sparecontent, sum (nvl (. bytes, 0)/(B. bytes) * 100 as sparepercent
FROM dba_free_space a, dba_data_files B
WHERE a. file_id = B. file_id and B. tablespace_name = (select default_tablespace from dba_users where username = user)
Group by B. tablespace_name, B. file_name, B. file_id, B. bytes
)
Group by tablespacename
6. query the table in the user tablespace
Select * from user_tables
========================================================== ========================================================== =
1. Create a tablespace
Create tablespace test
DATAFILE 'C:/oracle/oradata/db/test01.dbf' SIZE 50 M
Uniform size 1 M; # specify the partition SIZE as 128 k. If not specified, the default partition SIZE is 64 k.
Or
Create tablespace test
DATAFILE 'C:/oracle/oradata/db/test01.dbf' SIZE 50 M
Minimum extent 50 K EXTENT MANAGEMENT LOCAL
Default storage (INITIAL 50 k next 50 k maxextents 100 PCTINCREASE 0 );
You can view the information of the created tablespace from dba_tablespaces.
2. Create an UNDO tablespace
Create undo tablespace test_undo
DATAFILE 'C:/oracle/oradata/db/test_undo.dbf' SIZE 50 M
The EXTENT of the UNDO tablespace is managed locally, and only the DATAFILE and extent management clauses can be used in the SQL statement at the time of creation.
ORACLE requires that only one restored tablespace can be assigned to the database at any time, that is, multiple restored tablespaces can exist in one instance, but only one of them can be active. You can use the alter system command to restore the tablespace.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = test_undo;
3. Create a temporary tablespace
Create temporary tablespace test_temp
TEMPFILE '/oracle/oradata/db/test_temp.dbf' SIZE 50 M
View the current default temporary tablespace OF THE SYSTEM
Select * from dba_properties where property_name like 'default %'
Change System Default temporary tablespace
Alter database default temporary tablespace test_temp;
Iv. Change the tablespace status
1. Take the tablespace offline
Alter tablespace test OFFLINE;
If the data file is accidentally deleted, the RECOVER option must be included.
Alter tablespace game test for recover;
2. Bring the tablespace online
Alter tablespace test 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 test read only;
6. Make the tablespace readable and writable
Alter tablespace test read write;
5. Delete tablespaces
Drop tablespace test incl ing contents and datafiles cascade constraints;
Drop tablespace name [incl ing contents [and datafiles] [cascade constraints]
1. The incl ing contents clause is used to delete segments.
2. The and datafiles clause is used to delete data files.
3. the cascade constraints clause is used to delete all reference integrity CONSTRAINTS.
Vi. Extended tablespace
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 test
Add datafile '/oracle/oradata/db/test02.dbf' SIZE 1000 M;
2. manually add data file size
Alter database datafile 'C:/oracle/oradata/db/test01.dbf'
RESIZE 100 M;
3. Set automatic expansion of data files
Alter database datafile 'C:/oracle/oradata/db/test01.dbf'
Autoextend on next 100 M
MAXSIZE 200 M;
After setting, you can view the tablespace information from dba_tablespace and the corresponding data file information from v $ datafile.
========================================================== ========================================================== =
Create tablespace scgl
Datafile 'e: \ ORACLE \ prod t \ 10.1.0 \ ORADATA \ ORCL \ scgl2.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
Create tablespace test_data
Logging
Datafile 'e: \ ORACLE \ prod t \ 10.1.0 \ ORADATA \ ORCL \ user_data.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
Create user scgl identified by qwer1234
Default tablespace scgl
Temporary tablespace scgl_temp;
Tempfile 'e: \ ORACLE \ prod t \ 10.1.0 \ ORADATA \ ORCL \ user_temp.dbf'
Create temporary tablespace scgl_temp
Tempfile 'e: \ ORACLE \ prod t \ 10.1.0 \ ORADATA \ ORCL \ scgl_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
Grant connect, resource, dba to scgl;
Oracle creates a tablespace. The SYS user logs in as a DBA under CMD:
Run sqlplus/nolog in CMD.
Then
Conn/as sysdba
// Create a temporary tablespace
Create temporary tablespace user_temp
Tempfile 'd: \ oracle \ oradata \ Oracle9i \ user_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
// Create a data table space
Create tablespace test_data
Logging
Datafile 'd: \ oracle \ oradata \ Oracle9i \ user_data.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;
// Create a user and specify the tablespace
Create user username identified by password
Default tablespace user_data
Temporary tablespace user_temp;
Query table space usage
Select upper (F. TABLESPACE_NAME) "tablespace name ",
D. TOT_GROOTTE_MB "tablespace size (M )",
D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M )",
TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 990), '2014. 99') | '%' "usage ratio ",
F. TOTAL_BYTES "Idle space (M )",
F. MAX_BYTES "maximum block (M )"
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) TOTAL_BYTES,
ROUND (MAX (BYTES)/(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), 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 1
Queries the free space of a table space.
Select tablespace_name,
Count (*) as extends,
Round (sum (bytes)/1024/1024, 2) as MB,
Sum (blocks) as blocks
From dba_free_space
Group by tablespace_name;
-- Query the total table space capacity
Select tablespace_name, sum (bytes)/1024/1024 as MB
From dba_data_files
Group by tablespace_name;
Query table space usage
Select total. tablespace_name,
Round (total. MB, 2) as Total_MB,
Round (total. MB-free. MB, 2) as Used_MB,
Round (1-free. MB/total. MB) * 100, 2) | '%' as Used_Pct
From (select tablespace_name, sum (bytes)/1024/1024 as MB
From dba_free_space
Group by tablespace_name) free,
(Select tablespace_name, sum (bytes)/1024/1024 as MB
From dba_data_files
Group by tablespace_name) total
Where free. tablespace_name = total. tablespace_name;
Bytes -----------------------------------------------------------------------------------------------------------------------------
1. create a tablespace: create tablespace test datafile '/u01/test. dbf' size 10 M uniform size 128 k
# The size of the specified area is 128 kb, and the block size is 8 KB by default.
# Create bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf' size 100 GB
2. create a non-standard table show parameter db alter system set db_2k_cache_size = 10 M create tablespace test datafile '/u01/test. dbf' size 10 M blocksize 2 K uniform size 128 k
# Common Errors
SQL> alter system set db_2k_cache_size = 2 M; alter system set db_2k_cache_size = 2 M ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00384: Insufficient memory to grow cache
# Solution
SQL> alter system set sga_max_size = 400 M scope = spfile; SQL> shutdown immediate; SQL> startup SQL> alter system set db_2k_cache_size = 10 M; System altered.
3. view partition size and block size # partition size conn y/123 create table t (I number) tablespace test; Insert into t values (10) select bytes/1024 from user_segments where segment_name = upper ('T ');
# Show parameter block size (64 kB by default)
# Blocksize SQL> select * from v $ dbfile; SQL> select name, block_size, status from v $ datafile; SQL> select block_size from v $ datafile where file # = 14;
4. Delete the tablespace drop tablespace test including contents and datafiles
5. Table space: # query the data file select * from v $ dbfile; # select * from v $ tablespace for all tablespaces;
# Select file_name, tablespace_name from dba_data_files;
6. create undo tablespace undotbs01 datafile '/u01/undotbs01.dbf' size 5 M;
# Switch to the newly created undo tablespace alter system set undo_tablespace = undotbs01;
7. create temporary tablespace temp_data tempfile '/u01/temp. db 'size 5 M; create bigfile temporary tablespace bigtem tempfile'/u01/bigtemp. db 'size 5 M;
8. Change the tablespace status
(0.) view the status
# Select tablespace_name, block_size, status from dba_tablespaces;
# Select name, block_size, status from v $ datafile;
(1) Table space offline alter tablespace test offline
# If the data file alter tablespace test offline for recover is accidentally deleted
(2) online alter tablespace test online
(3) offline select * from v $ dbfile; alter database datafile 3 offline
(4) online Data File recover datafile 3; alter database datafile 3 online;
(5) Make the tablespace read-only alter tablespace test read only
(6) Enable the tablespace to read and write alter tablespace test read write;
9. extended tablespace # first view the tablespace name and file and space select tablespace_name, file_id, file_name, round (bytes/(1024*1024), 0) total_space from dba_data_files order by tablespace_name; # Three extension methods
1. alter tablespace test add datafile '/u01/test02.dbf' size 10 M (automatically add a datafile)
2. alter database datafile '/u01/test. dbf' resize 20 M;
3. alter database datafile '/u01/test. dbf' autoextend on next 10 M maxsize 1G;
# View the tablespace information after setting
Select. tablespace_name,. bytes total, B. bytes used, c. bytes free, (B. bytes * 100)/. bytes "% used", (c. bytes * 100)/. bytes "% free" from sys. sm $ ts_avail a, sys. sm $ ts_used B, sys. sm $ ts_free c where. tablespace_name = B. tablespace_name and. tablespace_name = c. tablespace_name;
10. Move the tablespace Data File
# Determine whether the data file is in the tablespace
SQL> select tablespace_name, file_name from dba_data_files where file_name = '/u01/test. dbf ';
# Open Status
SQL> alter tablespace test offline; SQL> host move/u01/test. dbf/u01/oracle/test. dbf; SQL> alter tablespace test rename datafile '/u01/test. dbf 'to'/u01/oracle/test. dbf'; SQL> alter tablespace test offline;
# Mount status SQL> shutdown immediate; SQL> startup mount SQL> host move/u01/test. dbf/u01/oracle/test. dbf; SQL> alter database rename file '/u01/test. dbf 'to'/u01/oracle/test. dbf ';
11. Common data dictionaries and dynamic performance views for tablespace and data files v $ dbfile v $ datafile dba_segments user_segments dba_data_files v $ tablespace dba_tablespaces user_tablespaces
-- Query table space usage
Select upper (F. TABLESPACE_NAME) "tablespace name ",
D. TOT_GROOTTE_MB "tablespace size (M )",
D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M )",
TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 990), '2014. 99') | '%' "usage ratio ",
F. TOTAL_BYTES "Idle space (M )",
F. MAX_BYTES "maximum block (M )"
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) TOTAL_BYTES,
ROUND (MAX (BYTES)/(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), 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 1
-- Query the free space of a table space
Select tablespace_name,
Count (*) as extends,
Round (sum (bytes)/1024/1024, 2) as MB,
Sum (blocks) as blocks
From dba_free_space
Group by tablespace_name;
-- Query the total table space capacity
Select tablespace_name, sum (bytes)/1024/1024 as MB
From dba_data_files
Group by tablespace_name;
-- Query table space usage
Select total. tablespace_name,
Round (total. MB, 2) as Total_MB,
Round (total. MB-free. MB, 2) as Used_MB,
Round (1-free. MB/total. MB) * 100, 2) | '%' as Used_Pct
From (select tablespace_name, sum (bytes)/1024/1024 as MB
From dba_free_space
Group by tablespace_name) free,
(Select tablespace_name, sum (bytes)/1024/1024 as MB
From dba_data_files
Group by tablespace_name) total
Where free. tablespace_name = total. tablespace_name;
// Grant permissions to users
Grant connect, resource to username;
// Log on to the user later. Any database objects created belong to the user_temp and user_data tablespaces,
This eliminates the need to specify a tablespace for each created object.
Withdrawal:
Revoke permission... from user name;
Delete USER commands
Drop user user_name cascade;
Create a tablespace
Create tablespace data01
DATAFILE '/oracle/oradata/db/data01.dbf' SIZE 500 M
Uniform size 128 k; # specify the area SIZE as 128 k. If not specified, the default area SIZE is 64 k.
Delete a tablespace
Drop tablespace data01 incl ing contents and datafiles;
1. Create a tablespace
Create tablespace data01
DATAFILE '/oracle/oradata/db/data01.dbf' SIZE 500 M
Uniform size 128 k; # specify the area SIZE as 128 k. If not specified, the default area SIZE is 64 k.
2. Create an UNDO tablespace
Create undo tablespace UNDOTBS02
DATAFILE '/oracle/oradata/db/undotbs02.dbf' SIZE 50 M
# Note: In the OPEN state, only one UNDO tablespace can be used at some time. To use a new tablespace, you must switch to the tablespace:
Alter system set undo_tablespace = UNDOTBS02;
3. Create a temporary tablespace
Create temporary tablespace temp_data
TEMPFILE '/oracle/oradata/db/temp_data.dbf' SIZE 50 M
Iv. 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;
5. Delete tablespaces
Drop tablespace data01 incl ing contents and datafiles;
Vi. Extended tablespace
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;
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
