Oracle运维场景整理

1.迁移表空间

1.1查看表空间ICP的目录

SQL> select file_name,tablespace_name from dba_data_files where file_name like '%ICP%';            
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/data/datafile/ICP01.dbf
ICP

1.2下线表空间

SQL> alter tablespace ICP offline; 

Tablespace altered.

 1.3移动表空间文件

//移动/data/datafile/ICP01.dbf至指定目录/data1/datafile/

SQL> host mv /data/datafile/ICP01.dbf /data1/datafile/

1.4修改表空间配置

SQL> alter tablespace ICP rename datafile '/data/datafile/ICP01.dbf' to '/data1/datafile/ICP01.dbf'; 

1.5表空间上线

SQL> alter tablespace ICP online;

 2.创建表空间、用户并授权

2.1创建临时表空间 

create temporary tablespace TEST_TEMP tempfile 'E:\app\TEST_TEMP.dbf' size 50m autoextend on next 50m maxsize 512m extent management local;
 

2.2创建数据表空间

create tablespace TEST_DATA logging datafile 'E:\app\TEST_DATA.dbf' size 50m autoextend on next 50m maxsize 1024m extent management local; 

2.3创建用户并指定表空间  

create user TEST identified by TEST default tablespace TEST_DATA temporary tablespace TEST_TEMP; 

2.4给用户授予权限  

grant connect,resource,dba to TEST;

3.扩展表空间

3.1扩展数据表空间

//几个size值可根据情况调整，maxsize为30G。

alter tablespace ICP add datafile '/data/datafile/ICP1.dbf' size 1024M autoextend on next 1024M maxsize 30720M unlimited;

3.2扩展临时表空间

alter tablespace ICP_TEMP add  tempfile '/data/datafile/ICP_TEMP.dbf' size 1024M autoextend on next 10240M maxsize  unlimited;

4.表空间文件数量超限解决ORA-00059

 //表空间文件数量超限解决

//ORA-00059: maximum number of DB_FILES exceeded

//说明：该参数大小不影响效率，只是单纯控制数据文件个数

SQL> show parameter db_files;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_files                 integer     300
SQL>  alter system set db_files=1000 scope=spfile;

shutdown immediate;
startup;

5.表空间状态及部分配置调整

//查看各个表空间状态
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files; 
//设置表空间为无限大
alter database DATAFILE '/u02/oradata/rsjdb/users01.dbf'  autoextend on maxsize unlimited;

//设置Oracle的最大sessions数
alter system set sessions=600 scope=spfile;

//查看数据库参数

//查看所有参数
　　show parameters

//查看所有名称带db的参数
　　show parameters db

//查看所有名称带log的参数
　　show parameters log

//设置Oracle的最大游标最大连接数
alter system set open_cursors=1700 scope=both;

6. dump文件简单导入导出

//导出
expdp \'/ as sysdba\' SCHEMAS=ICP directory=mydump dumpfile=ICP.dmp logfile=ICP.log

//导入

impdp res/q1w2e3R$ DIRECTORY=dump_dir dumpfile=ICP.dmp logfile=ICP.log FULL=Y TABLE_EXISTS_ACTION=replace

7.表空间大小查看与使用统计

//查看所有表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;  //未使用的表空间大小 
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;   
//使用空间计算
select a.tablespace_name,total,free,total-free used from   
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files   
group by tablespace_name) a,   
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space   
group by tablespace_name) b  
where a.tablespace_name=b.tablespace_name;   
//查看所有segment的大小。
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name   

//查看oracle临时表空间是当前正在使用的大小
SELECT SE.USERNAME,  
       SE.SID,  
       SU.EXTENTS,  
       SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)) AS SPACE,  
       TABLESPACE,  
       SEGTYPE,  
       SQL_TEXT  
FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE, V$SQL S  
WHERE P.NAME = 'db_block_size'  
AND SU.SESSION_ADDR = SE.SADDR  
AND S.HASH_VALUE = SU.SQLHASH  
AND S.ADDRESS = SU.SQLADDR  
ORDER BY SE.USERNAME, SE.SID;  

//查询所有的表空间
select tablespace_name from dba_tablespaces   
//查看表空间中分布的用户信息
select tablespace_name, owner,sum(bytes) from dba_segments  
group by tablespace_name, owner 

8.命令行情况下将结果放到一个文件里

SQL> spool out.txt   
SQL> select * from v$database;   
SQL> spool off  

9.四种增加表空间的方法

//给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
'/data/orcldata/APP02.DBF' SIZE 50M;
 //新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE
'/data/orcldata/APP03.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 //允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE '/data/orcldata/APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 //手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE '/data/orcldata/APP02.DBF' RESIZE 100M;

10.删除表空间

//删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
//删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
//删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
//删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
//如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
//如果删除表空间报 ORA-00959 错误,那就确认一下表空间是否还存在：
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
//表空间已经不存在了，那就直接删除 rm -rf  *.dbf 文件。 

11.删除用户

//查找用户
select *　from dba_users;
//查找工作空间的路径
select * from dba_data_files; 
//删除用户
drop user 用户名称 cascade;
//删除表空间
drop tablespace 表空间名称 including contents and datafiles cascade constraint;
//例如：删除用户名成为ABC，表空间名称为ABC
//删除用户，及级联关系也删除掉
drop user ABC cascade;
//删除表空间，及对应的表空间文件也删除掉
drop tablespace ABC including contents and datafiles cascade constraint;
lsof | grep delete | cut -c 7-18|xargs kill -s 9 

12.其他情况

删除无任何数据对象的表空间：
首先使用PL/SQL界面化工具，或者使用oracle自带的SQL PLUS工具，连接需要删除的表空间的oracle数据局库。
确认当前用户是否有删除表空间的权限，如果没有 drop tablespace,请先用更高级的用户(如sys)给予授权或者直接用更高级的用户。
用drop tablespace xxx ，删除需要删除的表空间。
删除有任何数据对象的表空间
使用drop tablespace xxx including contents and datafiles;来删除表空间。
注意事项：
如果drop tablespace语句中含有datafiles,那datafiles之前必须有contents关键字,不然会提示ora-01911错误
drop tablespace creditdata_gj including contents and datafiles cascade constraint;

欢迎关注交流：