Oracle常用操作

Oracle常用操作

Oracle常用操作

• 1.日常运维类
• • 1.1 exp导出
  • 1.2 imp导入
  • 1.3 表空间
  • 1.4 锁 lock
  • 1.5 归档日志 Archive Log
  • 1.6 PLSQL类
  • 1.7 闪回查询
• 2. 性能优化类
• 3. Oracle创建表空间用户

1.日常运维类

1.1 exp导出

[windows客户端]
将数据库中的用户user1,user2的表导出：exp 用户名/密码@数据库 导出地址 owner=用户
按用户导出：exp test/test@test file=d:daochu.dmp log=d:daochu.log owner=user1,user2
将数据库test完全导出：exp 用户名/密码@数据库 导出地址 full=y
完全导出：exp text/test@test file=d:daochu.dmp full=y
将数据库中的表t1,t2导出：exp 用户名/密码@数据库 导出地址 tables=表名
导出表：exp text/test@test file=d:daochu.dmp tables=(t1,t2)
将数据库中表t1的字段xh以19开头的数据导出：exp 用户名/密码@数据库 导出地址 tables=表名 query=查询条件
导出表中特定数据：exp text/test@test file=d:daochu.dmp tables=t1 query=”where xh like ‘19%’”
[Linux服务器]
按用户导出：exp test/test file=/home/oracle/daochu.dmp log=/home/oracle/daochu.log owner=user1,use2

1.2 imp导入

[windows客户端]
导入单张表：imp test/test@test file=d:daoru.dmp log=d:daoru.log tables=(test)
A用户的导出文件，需要导入用户B：imp userB/userB file=d:daoru.dmp log=d:daoru.log fromuser=userA touser=userB

[Linux服务器]
1、dmp文件上传到/test目录下(例如test.dmp)
2、用root用户ssh登录主机
cd /test
chown oracle:oinstall test.dmp
chmod 775 test.dmp
3、环境变量设置
su - oracle
export LANG=ZH_CN.GB18030 (可以避免导入后，字段注释和表注释乱码)
4、导入
imp test/test file=/test/test.dmp log=/home/oracle/test.log full=Y
5、查看日志
下载/home/oracle/test.log，观察导入是否成功。

1.3 表空间

1.3.1 表空间使用率查询SQL
表空间分类：系统表空间（SYSTEM，SYSAUX，UNDO，TEMP，USER）+业务表空间

1.SELECT DF.TABLESPACE_NAME,  
2.       COUNT(*) DATAFILE_COUNT,  
3.       ROUND(SUM(DF.BYTES) / 1048576 / 1024, 2) SIZE_GB,  
4.       ROUND(SUM(FREE.BYTES) / 1048576 / 1024, 2) FREE_GB,  
5.       ROUND(SUM(DF.BYTES) / 1048576 / 1024 -  
6.             SUM(FREE.BYTES) / 1048576 / 1024,  
7.             2) USED_GB,   
8.       ROUND(MAX(FREE.MAXBYTES) / 1048576 / 1024, 2) MAXFREE,  
9.       100 - ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_USED,  
10.       ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_FREE  
11.  FROM DBA_DATA_FILES DF,  
12.       (SELECT TABLESPACE_NAME,  
13.               FILE_ID,  
14.               SUM(BYTES) BYTES,  
15.               MAX(BYTES) MAXBYTES  
16.          FROM DBA_FREE_SPACE  
17.         WHERE BYTES > 1024 * 1024  
18.         GROUP BY TABLESPACE_NAME, FILE_ID) FREE  
19.WHERE DF.TABLESPACE_NAME = FREE.TABLESPACE_NAME(+)  
20.   AND DF.FILE_ID = FREE.FILE_ID(+)  
21.GROUP BY DF.TABLESPACE_NAME  
22.ORDER BY 8; 

1.3.2 表空间扩容
随着业务系统的使用，表空间的使用率也会逐步增加，为了不影响业务系统的使用，有时候需要对表空间进行扩容。
问：什么时候需要扩容表空间 答：表空间使用率超过92%时
问：一般通过什么方式扩容表空间 答：添加数据文件的方式
问：是否需要开启自动扩展 答：一般不建议
问：数据文件一次性可以加多大 答：视具体的业务而定，最大不允许超过20G
一、查询目前的表空间和数据文件命名规则和路径(必须做)
Select file_name,tablespace_name from dba_data_files where tablespace_name='XX' order by 1 ;
二、扩容表空间（标红路径必须按照第一步的结果来）【要区分单机和rac，谨慎操作，先问后做】
1.Alter tablespace TEST add datafile '/u01/oracle/oradata/test02.dbf'（样例1,实际路径根据步骤一的file_name填写） size 8g autoextend off;
2.`Alter tablespace TEST add datafile ‘+DATA/xxxxx/datafile/test02.dbf’（样例2,实际路径根据步骤一的file_name填写） size 8g autoextend off;

1.4 锁 lock

1.4.1数据库锁表SQL（单机）

1.SELECT l.session_id sid,  
2.      s.serial#,  
3.      l.locked_mode,  
4.      l.oracle_username,  
5.      l.os_user_name,  
6.      s.machine,  
7.      s.terminal,  
8.      o.object_name,  
9.      s.logon_time  
10. FROM v$locked_object l, all_objects o, v$session s  
11.WHERE l.object_id = o.object_id  
12.  AND l.session_id = s.sid  
13.ORDER BY sid, s.serial#;  
14. -–杀锁 
15.alter system kill session '&sid,&serial#';  

1.4.2数据库锁表SQL（集群rac）

1.--Oracle RAC 锁表处理  
2.SELECT l.session_id sid,  
3.       s.serial#,  
4.       s.inst_id,  
5.       l.locked_mode,  
6.       l.oracle_username,  
7.       l.os_user_name,  
8.       s.machine,  
9.       s.terminal,  
10.       o.object_name,  
11.       s.logon_time  
12.  FROM gv$locked_object l, all_objects o, gv$session s  
13. WHERE l.object_id = o.object_id  
14.   AND l.session_id = s.sid  
15. ORDER BY sid, s.serial#;  
16.   
17. --杀锁 语法  
18. alter system kill session 'sid, serial#,@inst_id';  
19. --杀锁 样例  
20. alter system kill session '871, 21,@1';  

1.5 归档日志 Archive Log

1.[root@orasit ~]# su - oracle  
2.  
3.[oracle@orasit ~]$ rman target /  
4.  
5.--删除7天前的归档日志  
6.RMAN> delete archivelog until time 'sysdate-7';  

1.6 PLSQL类

1.6.1 DBLINK管理

1.create public database link dblink_link(dblink名称)  
2.connect to  link(对方) identified by link(对方密码)    
3.using '(DESCRIPTION =  
4.    (ADDRESS_LIST =  
5.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.11 )(PORT = 1521))  
6.    )  
7.    (CONNECT_DATA =  
8.      (SID =orcl )  
9.      (SERVER = DEDICATED)  
10.    )  
11.  )'  

1.6.2 Job管理

1.--所有JOB 的查询   
2.select * from dba_jobs;  
3.  
4.--当前正在运行的JOB查询   
5.select * from dba_jobs_running;  
6.  
7.--创建JOB  
8.  
9.declare  
10.  v_jobnum number;  
11.begin  
12.  dbms_job.submit(v_jobnum, 'my_procedure_name;', sysdate, 'sysdate+1');  
13.  COMMIT;  
14.end;  

1.7 闪回查询

短暂时间（一般是15分钟）内的 数据库丢失，可以通过闪回查询的方式找回。

select *  from test.table_name as of timestamp to_timestamp('20-03-14 00:00:00', 'yy-mm-dd hh24:mi:ss')  

可以通过创建新表create table test_table as 的方式，对闪回查询的结果 进行记录。
后续再做相应的数据处理

2. 性能优化类

Select table_name,num_rows,last_analyzed From dba_tables where table_name ='表名';  

select count(*) from 用户名.表名;  

如果以上两者的查询结果差别很大，可以采用下面的语句对表进行分析

1.begin   
2.dbms_stats.gather_table_stats (ownname          => '用户名',  
3.                                tabname          => '表名',  
4.                                estimate_percent => 100,  
5.                                method_opt       => 'for all indexed columns',  
6.                                cascade          => true);  
7.end;  

3. Oracle创建表空间用户

-oracle创建表空间、用户 - 副本
--查询表空间路径
select * from dba_data_files;--删除表空间（慎用！！！！）
drop tablespace test including contents and datafiles;--创建表空间
CREATE TABLESPACE test 
DATAFILE '/test/oracle/oradata/test01.dbf' 
SIZE 32m 
AUTOEXTEND ON 
NEXT 32m MAXSIZE 2048m
创建表空间test
数据文件'/test/oracle/oradata/test01.dbf'
尺寸32m
打开自动扩展
下一个32m最大尺寸2048mCREATE TABLESPACE lxxt 
DATAFILE '/test/oracle/oradata/lxxt01.dbf' 
SIZE 32m 
AUTOEXTEND ON 
NEXT 32m MAXSIZE 2048m--扩充、加大表空间alter tablespace test add datafi le '/test/oracle/oradata/test02.dbf' size 2048M autoextend off;
alter tablespace test add datafile '/test/oracle/oradata/test03.dbf' size 2048M autoextend off;
alter tablespace test add datafile '/test/oracle/oradata/test04.dbf' size 2048M autoextend off;
alter tablespace test add datafile '/test/oracle/oradata/test05.dbf' size 2048M autoextend off;alter tablespace lxxt add datafile '/test/oracle/oradata/lxxt02.dbf' size 2048M autoextend off;--创建用户
create user bxxt 
identified by bxxt
default tablespace test
temporary tablespace temp
profile DEFAULT
quota unlimited on test;
-- 授予DBA权限 
grant dba to bxxt;
-- 无限表空间
grant unlimited tablespace to bxxt;--创建用户
create user testsoft_jw 
identified by testsoft_jw
default tablespace test
temporary tablespace temp
profile DEFAULT
quota unlimited on test;
-- 授予DBA权限 
grant dba to testsoft_jw;
-- 无限表空间
grant unlimited tablespace to testsoft_jw;--创建用户
create user testsoft_xgxt 
identified by testsoft_xgxt
default tablespace test
temporary tablespace temp
profile DEFAULT
quota unlimited on test;
-- 授予DBA权限 
grant dba to testsoft_xgxt;
-- 无限表空间
grant unlimited tablespace to testsoft_xgxt;--创建用户
create user testsoft_yxxt 
identified by testsoft_yxxt
default tablespace test
temporary tablespace temp
profile DEFAULT
quota unlimited on test;
-- 授予DBA权限 
grant dba to testsoft_yxxt;
-- 无限表空间
grant unlimited tablespace to testsoft_yxxt;--删除用户（慎用！！！）
drop user yxxt cascade;--创建用户
create user testsoft_lxxt 
identified by testsoft_lxxt
default tablespace lxxt
temporary tablespace temp
profile DEFAULT
quota unlimited on lxxt;
-- 授予DBA权限 
grant dba to testsoft_lxxt;
-- 无限表空间
grant unlimited tablespace to testsoft_lxxt;--授权连接
grant connect to testsoft_lxxt;--CMD 导入数据imp testsoft_yxxt/testsoft_yxxt@orcl63 file=D:testsoft_szhxytestso
ft-yxgl-ty-3.6.11αyxxt_v3.6.11-α.dmp full=y