Oracle table space query and operation methods

one. Query
1. Query the usage of oracle table space
select b.file_id　File ID,
　b.tablespace_name　Table space,
　b.file_name　　Physical file name,
　b.bytes　　　Total Number of bytes,
　(b.bytes-sum(nvl(a.bytes,0)))　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. Query the default tablespace and temporary tablespace of Oracle system users
select default_tablespace,temporary_tablespace from dba_users
3. Query a single table Usage
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 thirty usage sizes of all user tables
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 current user's default table space
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(a.bytes,0))) 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. Query the tables of user tablespace
select * from user_tables
============== ================================================== ===================
1. Create table space
CREATE TABLESPACE test
DATAFILE 'c:/oracle/oradata/db/test01.dbf ' SIZE 50M
UNIFORM SIZE 1M; #The specified area size is 128k. If not specified, the area 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 100 PCTINCREASE 0);
You can view the information of the newly created table space from dba_tablespaces
二, Create UNDO table space
CREATE UNDO TABLESPACE test_undo
DATAFILE 'c:/oracle/oradata/db/test_undo.dbf' SIZE 50M
The EXTENT of UNDO table space is managed locally, and when created Only DATAFILE and EXTENT MANAGEMENT clauses can be used in SQL statements.
ORACLE stipulates that only one restore table space can be assigned to the database at any time, that is, multiple restore table spaces can exist in an instance, but only one can be active. You can use the ALTER SYSTEM command to switch to a restored table space.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = test_undo;
3. Create a temporary table space
CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE '/oracle/oradata/db/test_temp.dbf' SIZE 50M
View The current default temporary tablespace of the system
select * from dba_properties where property_name like 'DEFAULT%'
Change the system default temporary tablespace
alter database default temporary tablespace test_temp;
4. Change the tablespace status
1. Take the table space offline
ALTER TABLESPACE test OFFLINE;
If the data file is accidentally deleted, the RECOVER option must be used
ALTER TABLESPACE game test FOR RECOVER;
2.Use Table space online
ALTER TABLESPACE test ONLINE;
3. Take the data file offline
ALTER DATABASE DATAFILE 3 OFFLINE;
4. Make the data file online
ALTER DATABASE DATAFILE 3 ONLINE;
5. Make the table space read-only
ALTER TABLESPACE test READ ONLY;
6. Make the table space readable and writable
ALTER TABLESPACE test READ WRITE;
5. Delete the table space
DROP TABLESPACE test INCL ING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE　Table space name [INCL ING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]
1. INCL ING CONTENTS clause is used to delete segments
2. AND DATAFILES clause is used to delete data files
3. CASCADE CONSTRAINTS clause is used to delete all referential integrity constraints

6. Extended table space
First check the name of the table space 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 file
ALTER TABLESPACE test
ADD DATAFILE '/oracle/oradata/db/test02.dbf' SIZE 1000M;
2. Manually increase the data file size
ALTER DATABASE DATAFILE 'c: /oracle/oradata/db/test01.dbf'
RESIZE 100M;
3. Set the data file to automatically expand
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 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 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 user logs in as DBA under CMD:
Enter sqlplus /nolog in CMD
Then
conn / as sysdba
//Create 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 data tablespace
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 user and specify 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') || '%' "usage ratio",
F.TOTAL_BYTES "free 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 table Free space of 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 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
#The specified area size is 128k, and the block size is the default 8K
#Large file table space create bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf ' size 100G
2. Create a non-standard table show parameter db alter system set db_2k_cache_size=10M create tablespace test datafile '/u01/test.dbf' size 10M blocksize 2K uniform size 128k
#Common errors
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 be modified because specified value is invalid ORA-00384: Insufficient memory to grow cache
#Solution
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(10) select bytes/1024 from user_segments where segment_name=upper('t');
#Block sizeShow parameter block (default 64K)
#blocksize of non-standard table space 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. Drop tablespace drop tablespace test including contents and datafiles
5. Check table space:#Check data files select * from v$dbfile; #All table spaces select * from v$tablespace;
#Data files of table space select file_name,tablespace_name from dba_data_files;
6. Create undo tablespace create undo tablespace undotbs01 datafile '/u01/undotbs01.dbf' size 5M;
#Switch to the new undo tablespace 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. Change table space status
(0.) View status
#Table space status select tablespace_name,block_size,status from dba_tablespaces;
#Data file status select name,block_size,status from v$datafile;
(1.)Table space offline alter tablespace test offline
#If the data file is accidentally deleted 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 the table space read-only alter tablespace test read only
(6.)Make the table space available Read and write alter tablespace test read write;
9.Extended table space#First check the name of the table space and the file and space 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; #Three extension methods
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;
#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_name;
10. Move the data file of the table space
#First determine the data file data In the table space
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. Commonly used data dictionaries and dynamic performance views for table spaces 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) "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') || '%' "usage ratio",
F.TOTAL_BYTES "free 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 
--查询表空间的free 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; 
--查询表空间的总容量 
select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_data_files 
group by tablespace_name; 
--查询表空间使用率 
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 connect,resource to username; 
//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间， 
这就不用在每创建一个对象给其指定表空间了 
撤权： 
revoke 权限... from 用户名; 
删除用户命令 
drop user user_name cascade; 

建立表空间 
CREATE TABLESPACE data01 
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M 
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 

删除表空间 
DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES; 
一、建立表空间 
CREATE TABLESPACE data01 
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M 
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 
二、建立UNDO表空间 
CREATE UNDO TABLESPACE UNDOTBS02 
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M 
#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间: 
ALTER SYSTEM SET undo_tablespace=UNDOTBS02; 
三、建立临时表空间 
CREATE TEMPORARY TABLESPACE temp_data 
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M 
四、改变表空间状态 
1.使表空间脱机 
ALTER TABLESPACE game OFFLINE; 
如果是意外删除了数据文件，则必须带有RECOVER选项 
ALTER TABLESPACE game OFFLINE FOR RECOVER; 
2.使表空间联机 
ALTER TABLESPACE game ONLINE; 
3.使数据文件脱机 
ALTER DATABASE DATAFILE 3 OFFLINE; 
4.使数据文件联机 
ALTER DATABASE DATAFILE 3 ONLINE; 
5.使表空间只读 
ALTER TABLESPACE game READ ONLY; 
6.使表空间可读写 
ALTER TABLESPACE game READ WRITE; 
五、删除表空间 
DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES; 

六、扩展表空间 
首先查看表空间的名字和所属文件 
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 game 
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M; 
2.手动增加数据文件尺寸 
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' 
RESIZE 4000M; 
3.设定数据文件自动扩展 
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf 
AUTOEXTEND ON NEXT 100M 
MAXSIZE 10000M; 

设定后查看表空间信息 
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

更多Oracle 表空间查询与操作方法相关文章请关注PHP中文网！