oracle 表空间操作命令收集

目录

一、创建表空间及用户并授权：

1、创建临时表空间：

2、创建数据表空间：

3、创建用户：

4、授权给用户：

二、查看表空间情况：

1、查看表空间使用率：

2、查看数据文件的所有值：

3、查看表空间和数据文件对应值：

4、查看数据文件的自动增长相关设置：

5、计算自动增长的大小：

6、修改数据文件的大小：

7、修改数据文件的自动增长及最大值：

8、增加表空间的数据文件：

三、查看用户和表空间情况：

1、查看当前用户的缺省表空间：

2、查看用户、默认表空间、临时表空间的对应值：

3、查看数据文件路径和对应的序号：

4、查看表空间下有多少用户：

---

一、创建表空间及用户并授权：

1、创建临时表空间：

CREATE TEMPORARY TABLESPACE DB_TEMP
TEMPFILE '/u01/app/oracle/oradata/orcl/db_temp.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2G
EXTENT MANAGEMENT LOCAL;

2、创建数据表空间：

CREATE TABLESPACE DB_DATA
LOGGING
DATAFILE '/u01/app/oracle/oradata/orcl/db_data.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL;

3、创建用户：

注意：创建用户之前需要前两步创建临时表空间和数据表空间，若不创建则临时表空间默认为temp，数据表空间默认为system。

CREATE USER NEWUSER IDENTIFIED BY 123456
DEFAULT TABLESPACE DB_DATA
TEMPORARY TABLESPACE DB_TEMP;

4、授权给用户：

GRANT CONNECT,RESOURCE,DBA TO NEWUSER;

二、查看表空间情况：

1、查看表空间使用率：

SELECT tbs 表空间名,                                    
    sum(totalM) 总共大小M,                                    
    sum(usedM) 使用空间M,                                    
    sum(remainedM) 剩余空间M,                                    
    round(sum(usedM)/sum(totalM)*100,2) 使用率,                            
    round(sum(remainedM)/sum(totalM)*100,2) 可用率                            
    FROM(                                            
     SELECT b.file_id ID,                                    
     b.tablespace_name tbs,                                    
     b.file_name name,                                    
     b.bytes/1024/1024 totalM,                                    
     (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,                        
     sum(nvl(a.bytes,0)/1024/1024) remainedM,                            
     sum(nvl(a.bytes,0)/(b.bytes)*100),                                
     (100 - (sum(nvl(a.bytes,0))/(b.bytes)*100))                            
     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                                
    )                                            
    GROUP BY tbs;

SELECT tbs tablespace_name,                                    
    sum(totalM) total_MB,                                    
    sum(usedM) used_MB,                                    
    sum(remainedM) free_MB,                                    
    round(sum(usedM)/sum(totalM)*100,2) used_ratio,                            
    round(sum(remainedM)/sum(totalM)*100,2) free_ratio                           
    FROM(                                            
     SELECT b.file_id ID,                                    
     b.tablespace_name tbs,                                    
     b.file_name name,                                    
     b.bytes/1024/1024 totalM,                                    
     (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,                        
     sum(nvl(a.bytes,0)/1024/1024) remainedM,                            
     sum(nvl(a.bytes,0)/(b.bytes)*100),                                
     (100 - (sum(nvl(a.bytes,0))/(b.bytes)*100))                            
     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                                
    )                                            
    GROUP BY tbs;

 

2、查看数据文件的所有值：

select * from dba_data_files;

3、查看表空间和数据文件对应值：

select tablespace_name,file_name from dba_data_files;

4、查看数据文件的自动增长相关设置：

select
tablespace_name,
file_name,
round(bytes/(1024*1024),0) total_space,
AUTOEXTENSIBLE,
increment_by,
round(maxbytes/(1024*1024),0) maxbytes
from dba_data_files
order by tablespace_name;

• total_space：数据文件大小（MB）
• AUTOEXTENSIBLE：是否为自动增长
• increment_by：块数
• maxbytes：数据文件最大值（MB）

5、计算自动增长的大小：

show parameter db_block;

NAME              TYPE    VALUE   
----------------- ------- ------- 
db_block_buffers  integer 0       
db_block_checking string  FALSE   
db_block_checksum string  TYPICAL 
db_block_size     integer 8192

• db_block_size：每个块的大小
• db_block_size（块大小）* increment_by（块数）=自动增长的大小

6、修改数据文件的大小：

alter database datafile '/u01/app/oracle/oradata/orcl/db_test1.dbf' resize 50m;

7、修改数据文件的自动增长及最大值：

alter database datafile '/u01/app/oracle/oradata/orcl/db_test1.dbf' autoextend on next 50m maxsize 500m;

8、增加表空间的数据文件：

alter tablespace yourtablespacename add datafile '/u01/app/oracle/oradata/orcl/db_test1.dbf' size 5m autoextend on next 50m maxsize 500m;

三、查看用户和表空间情况：

1、查看当前用户的缺省表空间：

select username,default_tablespace from user_users;

2、查看用户、默认表空间、临时表空间的对应值：

select username,temporary_tablespace,default_tablespace from dba_users;

3、查看数据文件路径和对应的序号：

select file#,name from v$datafile;

4、查看表空间下有多少用户：

select distinct  s.owner  from  dba_segments s where s.tablespace_name ='TS_TEST';