oracle 常用命令

1. 登陆数据库有两种方式：
操作系统认证： 在本机服务器登陆，不需要用户名密码
sqlplus "/as sysdba"
用户密码认证：远程连接数据库，需要用户名密码
sqlplus scott/tiger

如果有多个实例，可以在登陆后设置实例名称：
>sqlplus "/as sysdba"
>set oracle_sid = orcl
>sqlplus "/as sysdba"

2. 查看数据库实例的状态
select status from v$instance;

3. 环境变量设置
如果从控制台不能进入sqlplus，那么添加：系统变量--》Path--》 C:oracleproduct10.2.0db_1in；
系统变量--》ORACLE_HOME--》C:oracleproduct10.2.0db_1
系统变量--》ORACLE_SID--》orcl

4. 数据库关闭
shutdown immediate

5. 重新启动
startup

6. 查看数据库的名称
show parameter db_name

7. 查询数据库用户状态
select username, account_status from dba_users where username = 'SCOTT';

8. 对用户进行解锁
alter user scott account unlock;

9. 如果用户处理到期状态expired, 需要用原来的用户名/密码登陆后，重新设置新的密码

10. 查看当前用户
show user

11. 切换用户登陆
conn scott/tiger

12. 切换sys登陆
conn /as sysdba
或者 conn sys/password as sysdba

13. 查询用户有哪些表
select table_name from user_tables;

14. 修改数据库中默认的时间格式
alter session set nls_date_format = 'YYYY-MM-DD';

15. 查询平均工资大于2500的岗位
select job , avg(sal) from emp having avg(sal) > 2500 group by job;

16. 表约束
主键 外键 非空 唯一 条件
create table stu(
id number(3) constraint pk_stu primary key,
classid number(3) constraint fk_stu foreign key(classid) references CLASS (id) ,
name varchar2(10) not null,
address varchar2(50) unique,
age number(3) constraint ch_age check(age>0 and age<100)
);

17 快速备份一个表的数据
create table tb_temp as select * from tt (只是复制数据， 不复制约束)

18 快速导入一个表的数据
insert into tb_temp select * from tt;

19 增加表的字段
alter table tb add (mycolumn varchar(10));

20 修改字段长度
alter table tb modify (mycolumn varchar2(100));

21 删除某一列
alter table tb drop column mycolumn;

22 创建新用户并授权
create user lisi identified by lisi;
grant connect to lisi;
grant select on emp to lisi; (在scott用户下把表查询的权限授权给用户)
在lisi 用户下查询scott用户下的表：
select * from scott.emp

23. 回收权限
revoke select on emp from lisi;

24 把表的增删改查权限授权给用户
grant select ,insert, delete, update on emp to lisi;

revoke select ,insert, delete, update on emp from lisi;

25. 修改用户的密码
alter user lisi identified by 1234;

26 删除用户
drop user lisi;

27 查看用户权限
在scott用户下： select * from session_privs;

28 查看用户角色(DBA, RESOURCE, CONNECT)
在scott用户下： select * from user_role_privs;

29 把角色授予用户
grant connect, resource to lisi;

30 创建视图
求每个部门的平均工资
create view avgsal
as
select d.dname as "部门",
round(avg(e.sal),2) as "平均工资",
from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

31. 创建序列
create sequence bookID
start with 1
Increment by 1
maxvalue 9999999999999
nocache
nocycle;

32 创建函数
根据ID获取薪水
create or replace function get_empsal(emp_no in number)
return number
is emp_sal number(7,2);
begin
select sal
into emp_sal
from emp
where empno=emp_no;
return(emp_sal);
end;

33 创建索引
create index id_index on test_tb (id) ;

34 查看用户有没有创建同义词的权限， 同义词是对象的一个别名
select * from session_privs where privilege like '%synonym%';
授权： grant create any synonym to scott;
公共同义词： grant create public synonym to scott;

创建一个表的同义词
create synonym sg for salgrade;
创建scott用户下emp表的公共同义词：
create public synonym emp for scott.emp;
在其他用户下，可以直接查询emp， 不用写scott.emp;

35 创建表空间
查看数据文件：
select file_name, tablespace_name from dba_data_files;
创建表空间:
CREATE SMALLFILE TABLESPACE "test_space"
DATAFILE 'c:oracleproduct10.2.0oradataorcl est.dbf'
SIZE 20M
AUTOEXTEND ON NEXT 1000K
MAXSIZE UNLIMITED ;

创建用户：
drop user web_excel cascade;
create user web_excel identified by "web_excel"
default tablespace WEB_EXCEL_DB;

授权：
grant connect,resource to web_excel ;

36 导出 exp
exp lisi/lisi@orcl file=c:lisi.dmp log=c:lisi.log
exp scott/tiger@orcl file=c:scott.dmp log=c:scott.log
37 导入Imp
imp lisi/lisi@orcl fromuser=scott touser=lisi file=c:scott.dmp log=c:scott.log

38. 查看用户是否有闪回权限
SQL>conn lisi/lisi
SQL>select * from session_privs where privilege like 'flashback%';
授权
SQL>sqlplus "/as sysdba"
SQL>grant flashback any table to lisi;

39. 恢复删除的一条数据
在lisi用户下 ： delete from emp where ename = 'SMITH';
在sys用户下恢复：
select * from flashback_transaction_query t where table_name = 'EMP';
恢复语句是undo_sql.
拷贝并执行undo_sql

40 恢复到某一时刻的数据
当前时间：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
读取表某一时刻的数据：
select * from emp as of timestamp to_timestamp('2015-05-05 00:44:00','yyyy-mm-dd hh24:mi:ss');
闪回某一时刻的数据：
alter table emp enable row movement;
flashback table emp to timestamp to_timestamp('2015-05-05 00:44:00','yyyy-mm-dd hh24:mi:ss');

41 恢复删除的表：
flashback table emp to before drop;

42 . 查看回收站, 判断表是否可以闪回
select * from user_recyclebin order by droptime desc;

查看闪回的最大时间： 默认15分钟，表空间足够大的话不仅仅是15分钟
show parameter undo_retention;

43 通过windows计划任务自动备份
创建bat文件，内容是：
exp scott/tiger@127.0.0.1/orcl file=c:ackupscott.dmp log=c:ackupscott.log

创建windows 计划任务：
程序》系统工具》计划任务
create basic task

44. 恢复某个表三天前的数据，时间太长不能使用闪回
创建新的测试用户
sql>conn /as sysdba;
sql>create user test identified by test ;
sql>grant connect, resource to test;

然后向这个测试用户导入三天前的数据：
imp test/test@orcl fromuser=scott touser=test file=c:ackupscott.dmp log=c:ackuplisiImp.log

在test用户下授权：
sql>conn test/test;
sql>grant select ,update, insert,delete on emp to lisi;

向lisi用户插入test用户下的表：
sql>conn lisi/lisi;
sql>insert into emp select * from test.emp;

45. RMAN备份
rman 备份必须在归档模式下，查看数据库是否在归档状态：
sql>conn /as sysdba;
sql> archive log list

更改数据库的归档模式，需要重新启动数据库，在mount模式下修改
sql>shutdown immediate
sql>startup mount
sql>alter database archivelog;
sql>alter database open;
sql>archive log list #查看当前日志序列

RMAN备份步骤
1. 连接数据库
>RMAN target= sys/sys@orcl

2. 查看Rman环境参数
RMAN> show all;
数据库控制文件configure controlfile autobackup off 默认不自动备份控制文件
启动备份控制文件
RMAN>configure controlfile autobackup on;

3. 备份控制文件和数据文件
RMAN>backup database;
或者 备份全库
RMAN>backup as compressed backupset full database format 'c: man_backupfull_%d_%s_%p_%u.bak';
备份控制文件
RMAN>backup current controlfile format 'c: man_backupctl_%d_%s_%p_%u.bak';
备份spfile
RMAN>backup spfile format 'c: man_backupspfile_%d_%s_%p_%u.bak';

4. 备份日志文件
RMAN> backup archivelog all delete input
或者
sql 'alter system archive log current'; //切换归档日志
RMAN>backup archivelog all format 'C: man_backuplog_%d_%s_%p_%u.bak' delete all input; //备份所有归档日志

5. 查看备份信息
RMAN>list backup ;

46 编写备份脚本
查看要备份的数据库的名称
sql>show parameter db_name

在RMAN中设置冗余数，即保留几次备份：
>RMAN target/
RMAN> configure retention policy to redundancy 1;

全备份的脚本
run{
#CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
configure retention policy to redundancy 1;#冗余数
allocate channel d1 type disk; #开启多个通道
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup spfile format 'c: man_backupspfile_%d_%s_%p_%u.bak'; #备份初始化文件
backup current controlfile format 'c: man_backupctl_%d_%s_%p_%u.bak'; #备份当前控制文件，并指定备份文件路径
backup as compressed backupset full database format 'c: man_backupfull_%d_%s_%p_%u.bak'; #全库备份，并指定备份文件存放路径
sql 'alter system archive log current'; #切换归档日志
backup archivelog all format 'c: man_backuplog_%d_%s_%p_%u.bak' delete all input; #备份所有归档日志
release channel d1; #释放通道
release channel d2;
release channel d3;
release channel d4;
report obsolete; #报告过期或无效的备份
crosscheck backup; #交叉检查备份
delete noprompt expired backup; #删除过期的备份，不需交互确认
delete noprompt obsolete; #删除无效的备份，不需交互确认
}

全备份的可执行文件rman_full_backup.bat
rman target sys/sys@orcl cmdfile='c: man_backupscript man_full_backup.sql' log='c: man_backuplogfull_backup.log' append

查看数据库是否备份
RMAN>list backup summary

47 RMAN 恢复步骤， 恢复数据文件
1. 连接数据库到Mount模式
sql> shutdown immediate;
sql>startup mount;

2.
>rman target sys/sys@orcl
rman>restore database;

3.
rman>recover database;

4.
rman> sql 'alter database open';

48.恢复日志文件，
sql>shutdown immediate
sql>startup
sql>recover database until cancel;
sql>alter database open resetlogs;

49. 恢复控制文件, 及其他文件
sql>shutdown immediate
sql>startup nomount
>rman target /
rman> restore controlfile from autobackup; #恢复控制文件，控制文件在默认位置
或者, 控制文件不在默认位置
run
{
set controlfile autobackup format for device type disk to 'c: man_backup\%F';
allocate channel c1 device type disk;
restore controlfile from autobackup;
}

rman>alter database mount;
rman>restore database; #恢复数据文件
rman>recover database ;恢复数据库
rman>exit
>sqlplus "/as sysdba"
sql > alter database open resetlogs ; #恢复日志文件

50. 恢复 初始化文件spfile 及其他文件
>sqlplus "/as sysdba"
sql> show parameter spfile; #查看spfile文件的路径
sql> startup pfile = 'C:oracleproduct10.2.0adminorclpfileinit.ora.03201391340' # 通过pfile 文件启动数据库
>rman target /
rman>restore spfile from autobackup; #恢复spfile 文件
rman>exit
>sqlplus "/as sysdba"
sql>shutdown immediate
sql>startup nomount
sql> show parameter spfile; #查看是否已经恢复
sql>exit
>rman target /
rman> restore controlfile from autobackup; #恢复控制文件，控制文件在默认位置
或者, 控制文件不在默认位置
run
{
set controlfile autobackup format for device type disk to 'c: man_backup\%F';
allocate channel c1 device type disk;
restore controlfile from autobackup;
}

rman>alter database mount;
rman>restore database; #恢复数据文件
rman>recover database ;恢复数据库
rman>exit
>sqlplus "/as sysdba"
sql > alter database open resetlogs ; #恢复日志文件

51 恢复数据库到某个时间点的数据
>sqlplus "/as sysdba"
sql>shutdown immediate
sql>exit
>rman target /
rman> startup mount
rman>restore database;
rman> sql ' alter session set nls_date_format = "yyyy-mm-dd hh24:mi:ss" '; # 修改时间格式
rman>recover database until time '2015-05-06 18:48:00' ; #恢复到指定时刻
rman> sql 'alter database open resetlogs' ; #重新生成日志文件
rman>exit

52 审计 （监视用户执行的数据库操作）
>sqlplus "/as sysdba"
sql> show parameter audit_sys_operations; #查看审计参数
sql>show parameter audit_trail;
sql> alter system set audit_sys_operations=TRUE scope = spfile; #审计参数设置为打开
sql>alter system set audit_trail=db scope = spfile; #audit_trail=db
sql> startup force # 重启数据库， 使审计参数有效
sql> show parameter audit_sys_operations; #查看审计参数
sql>show parameter audit_trail;

#定义审计的用户和表，以及哪些操作
>sqlplus "/as sysdba"
sql>execute dbms_fga.add_policy(object_schema=>'lisi',object_name=>'emp', policy_name=>'chk_emp',statement_types=>'insert,update,delete' ) ; #审计lisi用户下的emp表

#授权普通用户查看审计表的权限
>sqlplus "/as sysdba"
sql> grant select on dba_fga_audit_trail to lisi;

#lisi用户下的emp表进行了操作

#在lisi用户下查看审计操作
sql>select t.timestamp, t. sql_text from sys.dba_fga_audit_trail t ;

#取消审计
>sqlplus "/as sysdba"
sql> execute dbms_fga.drop_policy(object_schema=>'lisi',object_name=>'emp', policy_name=>'chk_emp') ;

53 分析数据库日志变化
>conn lisi/lisi
#进行了CRUD操作
#下面要分析归档日志
>sqlplus "/as sysdba"
sql>alter system switch logfile ; #生成归档日志
sql>select sequence#,first_time from v$log_history order by first_time desc ; # 查询归档日志序号和时间

#oralce默认归档日志是在/flash_recovery_area/sid/archivelog/ 下面
sql> select to_char(t.first_time,'yyyy-mm-dd hh24:mi:ss') , t.name from v$archived_log t order by t.first_time desc; # 查看归档日志

#使用logminer分析数据
>sqlplus "/as sysdba"
sql>execute dbms_logmnr.add_logfile(logfilename=>'C:oracleproduct10.2.0flash_recovery_areaORCLARCHIVELOG2015_05_06O1_MF_1_2_BNOOHRWX_.ARC', options=>dbms_logmnr.new); #启动分析归档日志

#启动logminer
sql>execute dbms_logmnr.start_logmnr ( options=>dbms_logmnr.dict_from_online_catalog);

#把分析数据放入表中并授权给普通用户
sql>create table logminer_20150506 as select * from v$logmnr_contents;
sql> grant select on logminer_20150506 to lisi;

#结束logminer分析
sql> execute dbms_logmnr.end_logmnr;

#查看logminer分析结果
sql> select t.timestamp, t.sql_redo, t.sql_undo from sys.logminer_20150506 t where t.seg_owner= 'lisi' and t.sql_undo is not null;
sql> select t.timestamp, t.sql_redo, t.sql_undo from sys.logminer_20150506 t where t.sql_undo is not null;

54 . 查看数据库警告日志
警告日志文件在admin/sid/dbump/
sql>show parameter background_dump_dest

55. 数据库自动负载报告
>sqlplus "/as sysdba"
sql>execute dbms_workload_repository.create_snapshot(); # 手动创建一个数据库快照
sql> @?/rdbms/admin/awrrpt.sql #执行自动负载的sql脚本
#报告类型是txt 和 html， 直接enter是html
#num_days：1 是当天的快照， 2是最近2天的快照
#begin_sap :输入开始的snap id
#end_sap :输入结束的snap id
#report_name:输入报告的名称
#报告地址在c:usersadministrator

#分析自动负载报告
Redo size : 每秒产生的redo , per second 每秒产生的redo字节数， per transaciton 每个事务产生的字节数。
如果 per second很大，per transaciton很小，说明commit次数太多。

Logical reads:每秒产生的逻辑读。 Logical reads/Executes, 值不会超过50 ，一般是10左右。
如果指标过大，说明语句不够优化

User call : 每秒用户请求次数， User calls/Executes ，代表每个语句请求次数， 越小越好

Parses: 每秒SQL语句解析次数，超过300次需要关注，可以考虑调整参数session_cursor_cache来改善解析次数过高

Hard parses:硬解析次数，如果每秒执行超过100, 说明SQL语句的重复使用率极低

Executes: 每秒或每事务产生的语句执行次数

#数据库实例效率统计信息
Buffer Nowait %:

56. 生成数据库自动诊断报告
>sqlplus "/as sysdba"
sql> @?/rdbms/admin/addmrpt.sql
#num_days：1 是当天的快照， 2是最近2天的快照
#begin_sap :输入开始的snap id
#end_sap :输入结束的snap id
#report_name:输入报告的名称
#报告地址在c:usersadministrator

57. 如何解决锁表
如果修改数据时不能提交，报如下错误：
Record is locked by another user
该错误可能是其他用户正在修改数据，忘了提交导致。
>sqlplus "/as sysdba"
sql>select t2.username, t2.sid, t2.serial#, t2.logon_time from v$locked_object t1, v$session t2
where t1.session_id = t2.sid order by t2.logon_time; #查询哪些会话被锁
#根据上面的查询结果SID， 来查询具体的sql语句
sql> select sql_text from v$session a, v$sqltext_with_newlines b
where decode(a.sql_hash_value , 0 , prev_hash_value , sql_hash_value) = b.hash_value
and a.sid = &sid order by piece;
#提示输入sid, 输入上面查询出来的sid
#查询到sql， 可以执行并提交， 也可以杀死该会话：
sql> alter system kill session '[sid] , [serial#]' ;

58. 查看并解决最耗CPU的SQL语句
#根据oracle进程的pid查询对应数据库会话 的sid, serial#:
sql> select s.sid, s.serial# from v$session s , v$process p where s.paddr = p.addr and p.spid = '[进程pid]'
#根据会话的sid查询出具体的SQL
sql> select sql_text from v$sqltext a where a.hash_value = (
select sql_hash_value from v$session b where b.sid= '&sid'
) order by piece;
#输入上面查询到的sid, 就查询出了最耗时的SQL语句
#杀死会话：
sql> alter system kill session '[sid] , [serial#]' ;

59. 处理引起等待的SQL语句
#查询哪些会话等待了SQL语句
sql> select sid, username, blocking_session , blocking_session_status , blocking_instance
from v$session where username = 'lisi' ;
#进一步查询
sql> select sid, username, blocking_session , event , wait_time
from v$session where username = 'lisi' ;
#查询出具体的SQL语句
sql> select sql_text from v$session a, v$sqltext_with_newlines b
where decode(a.sql_hash_value , 0 , prev_hash_value , sql_hash_value) = b.hash_value
and a.sid = &sid order by piece;
#先输入第二步查询出的SID， 查看SQL
#再输入第一步查询出的SID， 查看SQL
#可以看到两个会话操作的是同一个记录

# 查看等待的会话
sql>select t2.username, t2.sid, t2.serial#, t2.logon_time from v$locked_object t1, v$session t2
where t1.session_id = t2.sid order by t2.logon_time;
#杀死会话：
sql> alter system kill session '[sid] , [serial#]' ;

60. 解决数据库不能启动ORA-00119 ORA-00130 的问题
#可能是监听器文件出错，修改listener.ora
#修改.net 服务名文件，tnanames.ora
#重新启动监听服务和数据库服务