Oracle table space queries and how-to

Source: Internet
Author: User
Tags readable

One. Inquiry article
1. Querying the usage of Oracle Tablespace
Select b.file_id file ID,
B.tablespace_name table Space,
B.file_name Physical file name,
Total number of bytes b.bytes,
(B.bytes-sum (NVL (a.bytes,0))) has been used,
Sum (NVL (a.bytes,0)) remaining,
Sum (NVL (a.bytes,0))/(b.bytes) *100 remaining percentage
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. Querying the default tablespace and temporary tablespace for Oracle system users
Select Default_tablespace,temporary_tablespace from Dba_users
3. Query the use of single-sheet
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 you want to query
4. Query the top 30 of all user tables using size
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 for 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)) as Usecontent,sum (NVL (a.bytes,0)) as Sparecontent,sum (NVL (a.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. Querying the table for the user table space
SELECT * FROM User_tables
==================================================================================
I. Create a table space
CREATE tablespace Test
DataFile ' c:/oracle/oradata/db/test01.dbf ' SIZE 50M
UNIFORM SIZE 1M; #指定区尺寸为128k, if not specified, the zone size defaults to 64k
Or
CREATE tablespace Test
DataFile ' c:/oracle/oradata/db/test01.dbf ' SIZE 50M
MINIMUM EXTENT 50K EXTENT MANAGEMENT LOCAL
DEFAULT STORAGE (INITIAL 50K NEXT 50K MAXEXTENTS pctincrease 0);
Information about the tablespace you just created can be viewed from the dba_tablespaces
Ii. Creating an undo table space
CREATE UNDO tablespace Test_undo
DataFile ' c:/oracle/oradata/db/test_undo.dbf ' SIZE 50M
The extent of the undo Tablespace is managed locally, and only the datafile and extent management clauses are used in the SQL statement that is created.
Oracle provides that only one restore tablespace can be assigned to the database at any time, that is, multiple restore tablespace exists in one instance, but only one is active. You can use the ALTER SYSTEM command to switch the table space to restore.
sql> ALTER SYSTEM SET undo_tablespace = Test_undo;
Iii. Creating a temporary table space
CREATE Temporary tablespace test_temp
Tempfile '/oracle/oradata/db/test_temp.dbf ' SIZE 50M
View the system's current default temporary tablespace
SELECT * from dba_properties where property_name like ' default% '
Change the system default temp table space
ALTER DATABASE default temporary tablespace test_temp;
Iv. Changing the state of the table space
1. Take the table space offline
ALTER tablespace test OFFLINE;
If the data file was accidentally deleted, you must have the recover option
ALTER tablespace game test for RECOVER;
2. Bring the table space online
ALTER tablespace test 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 test READ only;
6. Make table space readable and writable
ALTER tablespace test READ WRITE;
v. Delete table space
DROP tablespace test incl ING CONTENTS and datafiles CASCADE CONSTRAINTS;
DROP tablespace tablespace name [incl ING CONTENTS [and Datafiles] [CASCADE CONSTRAINTS]
1. Incl ING CONTENTS clause to delete segment
2. And datafiles clauses to delete data files
3. CASCADE CONSTRAINTS clause to remove all referential integrity constraints

vi. Extending table Space
First, look at the name of the tablespace and the file it 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 Test
ADD datafile '/oracle/oradata/db/test02.dbf ' SIZE 1000M;
2. Manually add data file size
ALTER DATABASE datafile ' c:/oracle/oradata/db/test01.dbf '
RESIZE 100M;
3. Set data File Auto-expansion
ALTER DATABASE datafile ' c:/oracle/oradata/db/test01.dbf '
Autoextend on NEXT 100M
MAXSIZE 200M;
After setting, you can view the tablespace information from the Dba_tablespace and view the corresponding data file information from the V$datafile.
==================================================================================
Create Tablespace SCGL
DataFile ' E:\ORACLE\PROD t\10.1.0\oradata\orcl\scgl2.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;
Create Tablespace Test_data
Logging
DataFile ' E:\ORACLE\PROD t\10.1.0\oradata\orcl\user_data.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
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 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;
Grant Connect,resource, dba to SCGL;
Oracle creates table space SYS users log in as DBA under CMD:
Play Sqlplus/nolog in cmd
and then
Conn/as SYSDBA
Create a temporary table space
Create temporary tablespace user_temp
Tempfile ' D:\oracle\oradata\Oracle9i\user_temp.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;
Create a data table space
Create Tablespace Test_data
Logging
DataFile ' D:\oracle\oradata\Oracle9i\user_data.dbf '
Size 50m
Autoextend on
Next 50m maxsize 20480m
Extent management Local;
Create a user and specify a tablespace
Create user username identified by password
Default Tablespace user_data
Temporary tablespace user_temp;
Querying table space usage
Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space 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 * 100,2), ' 990.99 ') | | '% ' "use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(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 1
Free space for query 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 capacity of the table space
Select Tablespace_name, sum (bytes)/1024/1024 as MB
From Dba_data_files
Group BY Tablespace_name;
Querying 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;
--------------------------------------------------------------------------------------------------------------- --------------
1. Create tablespace: Create tablespace test datafile '/u01/test.dbf ' size 10M uniform size 128k
#指定区尺寸为128k, the block size is the default 8K
#大文件表空间 Create Bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf ' size 100G
2. Build non-standard table show parameter db alter system set DB_2K_CACHE_SIZE=10M create Tablespace test datafile '/u01/test.dbf ' size 10M bl Ocksize 2K Uniform size 128k
#常见错误
Sql> alter system set DB_2K_CACHE_SIZE=2M; Alter system set DB_2K_CACHE_SIZE=2M ERROR at line 1:ora-02097:parameter cannot being modified because specified value is I Nvalid ora-00384:insufficient memory to grow cache
#解决
Sql> alter system set sga_max_size=400m Scope=spfile; sql> shutdown immediate; Sql> startup sql> alter system set db_2k_cache_size=10m; System altered.
3. View area size with block size # area size conn y/123 CREATE table T (I number) tablespace test; Insert into T values (Ten) Select bytes/1024 from user_segments where Segment_name=upper (' t ');
#块大小 Show parameter block (default 64K)
#非标准表空间的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 Table space drop tablespace test including contents and Datafiles
5. Check table space: #查数据文件 select * from V$dbfile; #所有表空间 select * from V$tablespace;
#表空间的数据文件 select File_name,tablespace_name from Dba_data_files;
6. Create undo Table Space Create undo tablespace undotbs01 datafile '/u01/undotbs01.dbf ' size 5M;
#切换到新建的undo表空间 alter system set UNDO_TABLESPACE=UNDOTBS01;
7. Create a temporary tablespace create temporary tablespace temp_data tempfile '/u01/temp.db ' size 5M; Create Bigfile temporary tablespace bigtem tempfile '/u01/bigtemp.db ' size 5M;
8. Changing table space Status
(0.) View 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
#如果意外删除了数据文件 alter TABLESPACE test offline for recover
(2.) Table space Online alter tablespace test online
(3.) Data file Offline select * from V$dbfile; ALTER DATABASE datafile 3 offline
(4.) data file online recover datafile 3; ALTER DATABASE datafile 3 online;
(5.) Make Tablespace read-only alter TABLESPACE test READ only
(6) Enable the tablespace to read and write alter tablespace test read write;
9. Extension table Space # First Look at the name and the file and space 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.alter tablespace test Add datafile '/u01/test02.dbf ' size 10M (auto plus one datafile)
2.alter database datafile '/u01/test.dbf ' resize 20M;
3.alter database datafile '/u01/test.dbf ' autoextend on next 10M maxsize 1G;
#设定后查看表空间信息
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;
10. Move Tablespace data files
#先确定数据文件据在表空间
Sql>select tablespace_name,file_name from Dba_data_files where file_name= '/u01/test.dbf ';
#open状态
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状态 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. Table spaces and data files common data dictionaries and dynamic performance views V$dbfile v$datafile dba_segments user_segments dba_data_files v$tablespace dba_tablespaces User_tablespaces
--Querying table space usage
Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space 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 * 100,2), ' 990.99 ') | | '% ' "use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(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 1
--Query The free space of the tablespace
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 capacity of the table space
Select Tablespace_name, sum (bytes)/1024/1024 as MB
From Dba_data_files
Group BY Tablespace_name;
--Querying table space utilization
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;
Granting permissions to Users
Grant Connect,resource to username;
Any database objects created later with that user will belong to the user_temp and user_data table spaces.
This doesn't have to specify a tablespace for each object you create.
Right to withdraw:
Revoke permissions ... from user name;
Delete User command
Drop user user_name cascade;

CREATE TABLE Space
CREATE tablespace DATA01
DataFile '/oracle/oradata/db/data01.dbf ' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k, if not specified, the zone size defaults to 64k

Delete Table Space
DROP tablespace data01 incl ING CONTENTS and datafiles;
I. Create a table space
CREATE tablespace DATA01
DataFile '/oracle/oradata/db/data01.dbf ' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k, if not specified, the zone size defaults to 64k
Ii. Creating an undo table space
CREATE UNDO tablespace UNDOTBS02
DataFile '/oracle/oradata/db/undotbs02.dbf ' SIZE 50M
#注意: In the open state, you can use only one undo tablespace at some point, and if you want to use a new tablespace, you must switch to that tablespace:
ALTER SYSTEM SET Undo_tablespace=undotbs02;
Iii. Creating a temporary table space
CREATE Temporary tablespace temp_data
Tempfile '/oracle/oradata/db/temp_data.dbf ' SIZE 50M
Iv. changing the state of the table space
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;
V. Delete table space
DROP tablespace data01 incl ING CONTENTS and datafiles;

Vi. Extending table Space
First, look at the name of the tablespace and the file it 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;

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

Oracle table space queries and how-to

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.