Oracle table space Query and operation method _oracle

One. Query article
1. Query Oracle Table space usage
Select b.file_id file ID,
B.tablespace_name table Space,
B.file_name Physical file name,
The 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 percent 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. Querying the default table space and temporary tablespace for Oracle system users
Select Default_tablespace,temporary_tablespace from Dba_users
3. Query the use 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 you want to query
4. Query all user tables using the top 30 size
SELECT * FROM (select Segment_name,bytes to dba_segments where owner = USER order BY bytes desc) where RowNum <= 30
5. Query the current User default table space usage
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. Table to query user table space
SELECT * FROM User_tables
==================================================================================
first, 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);
You can view information about the tablespace you just created from the Dba_tablespaces
Second, create the undo table space
CREATE UNDO tablespace Test_undo
DataFile ' c:/oracle/oradata/db/test_undo.dbf ' SIZE 50M
The extent of the undo table space is managed locally, and only the datafile and extent management clauses can be used in SQL statements at the time of creation.
Oracle stipulates that only one restore table space can be given to the database at any time, that is, multiple restore table spaces exist in one instance, but only one is active. You can use the ALTER system command to restore a table space switch.
sql> ALTER SYSTEM SET undo_tablespace = Test_undo;
iii. establishment of temporary table spaces
CREATE Temporary tablespace test_temp
Tempfile '/oracle/oradata/db/test_temp.dbf ' SIZE 50M
View the system's current default temp table space
SELECT * from dba_properties where property_name like ' default% '
Change system default Temp table space
ALTER DATABASE default temporary tablespace test_temp;
Four, change the table space State
1. Make Tablespace offline
ALTER tablespace test OFFLINE;
If you accidentally delete a data file, you must have the recover option
ALTER tablespace game test for RECOVER;
2. Bring the table space online
ALTER tablespace test ONLINE;
3. Make data files offline
ALTER DATABASE datafile 3 OFFLINE;
4. Bring the data file online
ALTER DATABASE datafile 3 ONLINE;
5. Make table space Read-only
ALTER tablespace test READ only;
6. Make the table space readable and writable
ALTER tablespace test READ WRITE;
Five, 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 used to delete segments
2. and datafiles clause used to delete data files
3. The CASCADE CONSTRAINTS clause is used to remove all referential integrity constraints

vi. expansion of the table space
First look at the name of the tablespace and the file that belongs to it
Select Tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;
1. Additional Data files
ALTER tablespace Test
ADD datafile '/oracle/oradata/db/test02.dbf ' SIZE 1000M;
2. Manually increase the size of the data file
ALTER DATABASE datafile ' c:/oracle/oradata/db/test01.dbf '
RESIZE 100M;
3. Set up data file automatic expansion
ALTER DATABASE datafile ' c:/oracle/oradata/db/test01.dbf '
Autoextend on NEXT 100M
MAXSIZE 200M;
After setting, you can view the table space 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 Create tablespace sys users log on as DBA under CMD:
Playing 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 datasheet 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 table space
Create user username identified by password
Default Tablespace user_data
Temporary tablespace user_temp;
Query 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)/(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
Free spaces 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;
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;
--------------------------------------------------------------------------------------------------------------- --------------
1. Create TABLE space: Create tablespace test datafile '/u01/test.dbf ' size 10M uniform size 128k
#指定区尺寸为128k, block size is the default 8K
#大文件表空间 Create Bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf ' size 100G
2. 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 to 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 and block Size # area size conn y/123 CREATE table T (I number) tablespace test; Insert into T values (a) 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 tablespace drop tablespace test including contents and Datafiles
5. look-up table Space: #查数据文件 select * from V$dbfile; #所有表空间 select * from V$tablespace;
#表空间的数据文件 select File_name,tablespace_name from Dba_data_files;
6. Set up undo table space Create undo tablespace undotbs01 datafile '/u01/undotbs01.dbf ' size 5M;
#切换到新建的undo表空间 alter system set UNDO_TABLESPACE=UNDOTBS01;
7. Create temporary table space Create temporary tablespace temp_data tempfile '/u01/temp.db ' size 5M; Create Bigfile temporary tablespace bigtem tempfile '/u01/bigtemp.db ' size 5M;
8. Change the state of the table space
(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 files 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 with ALTER TABLESPACE test Read only
(6) Make the table space readable and writable alter tablespace test read write;
9. Extended Tablespace # First Look at the name and file of the tablespace and the space 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 (automatically add a 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 the table space data file
#先确定数据文件据在表空间
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 space and data file commonly used data dictionary and dynamic performance view 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) "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)/(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
--Free spaces 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;
--Query table space utilization rate
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 that you create will be in the user_temp and User_data tablespace after you log on to that user.
This does not have to specify the table space for each object created
Withdrawal Right:
Revoke permissions ... from user name;
Delete User command
Drop user user_name cascade;

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

Delete a table space
DROP tablespace data01 incl ING CONTENTS and datafiles;
First, 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
Second, create the undo table space
CREATE UNDO tablespace UNDOTBS02
DataFile '/oracle/oradata/db/undotbs02.dbf ' SIZE 50M
#注意: You can only use one undo table space at some point in the open state, and if you want to use a new tablespace, you must switch to that table space:
ALTER SYSTEM SET Undo_tablespace=undotbs02;
Iii. establishment of temporary table spaces
CREATE Temporary tablespace temp_data
Tempfile '/oracle/oradata/db/temp_data.dbf ' SIZE 50M
Four, change the table space state
1. Make Tablespace offline
ALTER tablespace game OFFLINE;
If you accidentally delete a data file, you must have the recover option
ALTER tablespace game OFFLINE for RECOVER;
2. Bring the table space online
ALTER tablespace game ONLINE;
3. Make data files offline
ALTER DATABASE datafile 3 OFFLINE;
4. Bring the data file online
ALTER DATABASE datafile 3 ONLINE;
5. Make table space Read-only
ALTER tablespace game READ only;
6. Make the table space readable and writable
ALTER tablespace game READ WRITE;
Five, delete table space
DROP tablespace data01 incl ING CONTENTS and datafiles;

Vi. expansion of the table space
First look at the name of the tablespace and the file that belongs to it
Select Tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;
1. Additional Data files
ALTER tablespace Game
ADD datafile '/oracle/oradata/db/game02.dbf ' SIZE 1000M;
2. Manually increase the size of the data file
ALTER DATABASE datafile '/oracle/oradata/db/game.dbf '
RESIZE 4000M;
3. Set up data file automatic expansion
ALTER DATABASE datafile '/oracle/oradata/db/game.dbf
Autoextend on NEXT 100M
MAXSIZE 10000M;

View table space 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