关于oracle日常命令
#以下命令不要改格式，否则很有可能会报错！！！！！！！！
注：以下命令在oracle11g R2上操作无问题
#rman全备查询
set linesize 300
col input_size for a12
col output_size for a12
col input_speed for a12
col output_speed for a12
col time_taken_display for a12
col status for a10
select * from (select session_stamp,start_time,end_time,input_type,status,input_bytes_display input_size,output_bytes_display output_size,
input_bytes_per_sec_display input_speed,output_bytes_per_sec_display output_speed,time_taken_display
from v$rman_backup_job_details order by start_time desc) where rownum < 50;
#查询上一次归档信息
select to_char(completion_time,'yyyy-mm-dd') archived_date,thread# instance,count(*) arch_qty,sum(blocks*block_size)/1024/1024 arch_GB
from v$archived_log
where archived='YES' and to_char(completion_time,'yyyy-mm-dd')>=to_char(sysdate-1,'yyyy-mm-dd')
group by rollup(to_char(completion_time,'yyyy-mm-dd'),thread#);
#在磁盘上查找最大读写的SQL
SELECT * FROM (SELECT sql_text,disk_reads "total disk",executions "total exec",disk_reads/executions "disk/exec" FRO
M v$sql where executions>0 and is_obsolete='N' and disk_reads !=0 order by 4 desc) where rownum<11;
#数据库cache、buffer命中率（通常应在.90%以上，否则需要加大DB_CACHE_SIZE）
select name,physical_reads,db_block_gets,consistent_gets,1-(physical_reads/(db_block_gets+consistent_gets)) "hit rat
io" from v$buffer_pool_statistics where name='DEFAULT';
#检查内存排序性能
select a.name, to_char(value)
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic# and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');
#排序命中率
select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';
#数据字典命中率
select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"
from v$rowcache;
#共享池大小命中率
select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
#数据缓冲区命中率
select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio from v$sysstat phy,v$sysstat cur,v$sysstat con where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';
#命中率查询
select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
查看数据库中所有的表
select * from dba_tables;
1.1查看当前用户下的所有表
select * from user_tables;
模糊查找表
select * from user_tables where table_name like'%XX_XX_IMPAWN';
精确查找表
select * from user_tables where table_name='XXX_XX_ZDFKXX';
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色：
select * from dba_roles;
6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
比如我要查看用户 wzsb的拥有的权限：
SQL> select * from dba_sys_privs where grantee='WZSB';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
WZSB CREATE TRIGGER NO
WZSB UNLIMITED TABLESPACE NO
比如我要查看用户 wzsb的拥有的角色：
SQL> select * from dba_role_privs where grantee='WZSB';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
WZSB DBA NO YES
查看一个用户所有的权限及角色
select privilege from dba_sys_privs where grantee='WZSB'
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee='WZSB' );
创建用户以及设置密码：
create user <username> identified by <password>;
示例：create user zhangsan identified by 123456;
修改用户：alter user zhangsan identified by 234556;
撤销zhangsan用户：drop user zhangsan;
修改密码
方法1：SQL>alter user sys identified by 123456
方法2：SQL>grant connect to sys identified by 123456
方法3：SQL> password system
为用户授权角色\撤销授权
oracle提供三种标准角色（role）:connect/resource和dba.
1、connect role(连接角色)
connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。
拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link)。
2、resource role(资源角色)
resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
3、dba role(数据库管理员角色)
包括无限制的空间限额和给其他用户授予各种权限的能力。
为用户zhangsan授权：grant connect, resource to zhangsan;
撤销对zhangsan赋予的权限：revoke connect, resource from zhangsan;
查看用户所拥有的角色：select * from user_role_privs;
查看用户表空间
查看所有表空间：select * from user_tablespaces;
查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select <zhangsan> from dba_users;
查看你能管理的所有用户：select * from all_users;
查看当前用户信息: select * from user_users;
查看当前用户表空间名称: select default_tablespace from dba_users where username='ZHANGSAN';
查看当前用户所在表空间：select username,default_tablespace from user_users;
创建用户的时候指定表空间：create user zhangsan identified by 123456 default tablespace userspace; ！！！！！！！！！！！
给已存在的用户指定一个表空间: alter user username default tablespace userspace;
查询所有表空间
方法1：select * from dba_tablespaces;
方法2：select * from v$tablespace;
查询用户下所有表
方法1：select * from user_tables;
方法2: select * from dba_tables where owner='ZHANGSAN';
/*查看表空间下有多少用户，tablespace_name表空间 的名字一定要大写 */
select distinct s.owner from dba_segments s where s.tablespace_name ='ZHANGSAN';
查看表空间路径: select * from dba_data_files;
表空间查看
SELECT a.tablespace_name,ROUND (a.total_size) "total_size(MB)",ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",ROUND (b.free_size, 3) "free_size(MB)",ROUND (b.free_size / total_size * 100, 2) || '%' free_rate FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size FROM dba_data_files GROUP BY tablespace_name) a, ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name(+);
数据文件剩余空间
select b.file_name "数据文件", b.tablespace_name "表空间", b.bytes / 1024 / 1024 "大小M", (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 "已使用M", substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) "使用率%" 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.bytes order by b.tablespace_name;
每个表占用空间查询
查看当前用户每个表占用空间的大小：
Select Segment_Name,Sum(bytes)/1024/1024 from User_Extents Group By Segment_Name
查看每个表空间占用空间的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 from Dba_Segments Group By Tablespace_Name
查看某个表占用空间大小：
select Sum(bytes)/1024/1024||'MB' CAP from DBA_Extents where Segment_Name = 'T_M_CHARGE_PROFILE_MID'
select Segment_Name, Sum(bytes)/1024/1024||'MB' CAP from DBA_Extents s where s.owner='CPSTXN' group by Segment_Name order by Sum(bytes) desc
select partition_name, sum(bytes)/1024/1024 "MB" from dba_segments where segment_name='CPS_ACCOUNT_ENTRY' group by partition_name;
#查看变空间文件及位置
set linesize 300;
col FILE_NAME for a12
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_space from dba_data_files order by tablespace_name;
数据文件查看
共有三种数据库文件：控制文件、数据文件、日志文件
查看控制文件：select * from v$controlfile;
查看数据文件：select status,bytes,name from v$datafile;
查看日志文件：
1、select name from v$logfile;
2、select sum(bytes)/1024/1024 used_mb from v$log; 或者 select * from v$log;
#扩展表空间
查看命令：
select
b.file_name "物理文件名",
b.tablespace_name "表空间",
b.bytes/1024/1024 "大小(M)",
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用(M)",
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率(%)"
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.bytes
order by b.tablespace_name;
#查询结果
序号 物理文件名 表空间 大小（M） 已使用（M） 利用率（%）
1 D:\ORACLE\ORADATA\ORCL\MAS110_01.DBF MAS_110_TABLESPACE 30720 26960 87.76
2 D:\ORACLE\ORADATA\ORCL\MAS110_02.DBF MAS_110_TABLESPACE 30720 28896 94.06
#扩展表空间
ALTER TABLESPACE MAS_110_TABLESPACE ADD DATAFILE 'D:\ORACLE\ORADATA\ORCL\MAS110_01.DBF' size 30720M; （30720M是表空间大小，或30G，默认为bytes，可自定义大小，记得加单位）
也就是说增加一个数据文件，这个数据文件由size来定义大小且xx.DBF不能和之前的重复
注：linux也是一样的方式
查看表空间是否为自动扩展
select file_name,autoextensible,increment_by from dba_data_files;
注：如果autoextensible为yes则为自动扩展,为no则不能自动扩展
开启和关闭oracle数据库中的审计功能
第1步：查看审计功能是否开启
SQL> show parameter audit;
NAME TYPE VALUE
---------------------- ----------- ---------------------------
audit_file_dest string /u01/app/oracle/admin/ORCL/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
audit_trail 的value值为NONE表示不开启；
audit_trail 的value值为FALSE表示不开启；
audit_trail 的value值为DB表示开启；
audit_trail 的value值为TURE表示开启；
audit_trail 的value值为OS表示审计记录写入一个操作系统文件（不是特别理解）；
第2步：开启审计功能
SQL> alter system set audit_sys_operations=TRUE scope=spfile; //审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db,extended scope=spfile;
重启实例
第3步：关闭审计功能
[oracle@xxxx]$ sqlplus / as sysdba //进入数据库
SQL> show parameter audit
SQL> alter system set audit_trail = none scope=spfile;
重启实例
oracle查看库中所有为空的表
select * from all_all_tables where num_rows='0' and owner='用户名';
或者
select * from user_tables where num_rows=0；
oracle查看库中所有不为空的表
select * from all_all_tables where num_rows!='0' and owner='用户名';
或者
select * from user_tables where num_rows!=0
查看各英文表对应的中文表
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name=f.table_name;
查看所有表的创建时间、最后更新时间
select OBJECT_NAME as 表名,CREATED as 创建时间,LAST_DDL_TIME as 最后更新时间 from user_objects where object_type='TABLE'
SGA、PGA使用情况
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
exp导出所有数据库中的表
exp sys/密码@127.0.0.1/orcl file=d:\daochu.dmp log=d:\daochu.log full=y
用户名: sys as sysdba
口令: sys用户的密码
以下所有命令在PL/SQL12(oracle11g)版本中执行
查询本库的所有视图：select * from all_views （owner代表不同的用户）
查看当前用户的所有视图：select * from user_views
查看本库每个用户分别有多少视图：select owner,count(*) from all_views group by owner
查看当前用户下所有视图的所有字段（详情）：select * from user_col_comments
查看所有用户下所有视图的所有字段（详情）：select * from all_col_comments
oracle数据库日常维护
检查数据库基本状况
select instance_name,host_name,startup_time,status,database_status from v$instance;
注：STATUS列表示Oracle当前的实例状态，必须为"open"状态，DATABASE_STATUS表示Oracle当前数据库的状态，必须为"ACTIVE"
select name,log_mode,open_mode from v$database;
注："LOG_MODE"表示Oracle当前的归档方式。"ARCHIVELOG"表示数据库运行在归档模式下，"NOARCHIVELOG"表示数据库运行在非归档模式下
检查Oracle服务进程
ps -ef|grep ora_|grep -v grep && echo "总共: "`ps -ef|grep ora_|grep -v grep|wc –l`
注：
在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：
Oracle写数据文件的进程，输出显示为：ora_dbw0_<实例名>
Oracle写日志文件的进程，输出显示为：ora_lgwr_<实例名>
Oracle监听实例状态的进程，输出显示为：ora_smon_<实例名>
Oracle监听客户端连接进程状态的进程，输出显示为：ora_pmon_<实例名>
Oracle进行归档的进程，输出显示为：ora_arc0_<实例名>
Oracle进行检查点的进程，输出显示为：ora_ckpt_<实例名>
Oracle进行恢复的进程，输出显示为：ora_reco_<实例名>
查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode
from
v$locked_object a,
dba_objects b
where
b.object_id = a.object_id;
查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time
from
v$locked_object a,
v$session b
where a.session_id = b.sid
order by b.logon_time;
查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
查某session 正在执行的sql语句，从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.
SELECT sql_text FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid = '321') /*此处为SID*/
ORDER BY piece ASC;
杀掉进程 sid,serial#
alter system kill session'111,12305';
delete误删除的解决方法
原理：
利用oracle提供的闪回方法，如果在删除数据后还没做大量的操作（只要保证被删除数据的块没被覆写），就可以利用闪回方式直接找回删除的数据
具体步骤为：
例如:
误删除了100条数据
删除语句为：
delete from 表名 where kid = '5';
*确定删除数据的时间（在删除数据之前的时间就行，不过最好是删除数据的时间点）
*可以用以下语句找出执行删除语句的时间
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
*用以下语句找出删除的数据：
语法：select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss') where kid = '5'
示例：select * from 表名 as of timestamp sysdate - 3/1440 where kid = '5' ; --3分钟之前的数据
*把删除的数据重新插入原表：
注意要保证主键不重复。
语法：insert into 表名 (select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss') where kid = '5');
示例：insert into 表名 (select * from 表名 as of timestamp sysdate - 3/1440 where kid = '5');
如果表结构没有发生改变，还可以直接使用闪回整个表的方式来恢复数据。
具体步骤为：表闪回要求用户必须要有flash any table权限
--开启行移动功能
·alter table 表名 enable row movement
--恢复表数据
·flashback table 表名 to timestamp to_timestamp(删除时间点','yyyy-mm-dd hh24:mi:ss')
--关闭行移动功能 ( 千万别忘记 )
·alter table 表名 disable row movement
drop、trancate误删除的解决方法
原理：由于oracle在删除表时，没有直接清空表所占的块,oracle把这些已删除的表的信息放到了一个虚拟容器“回收站”中，而只是对该表的数据块做了可以被覆写的标志，所以在块未被重新使用前还可以恢复。
具体步骤：
*查询这个“回收站”或者查询user_table视图来查找已被删除的表:
· select table_name,dropped from user_tables
· select object_name,original_name,type,droptime from user_recyclebin
在以上信息中，表名都是被重命名过的，字段table_name或者object_name就是删除后在回收站中的存放表名
*如果还能记住表名，则可以用下面语句直接恢复：
flashback table 原表名 to before drop
如果记不住了，也可以直接使用回收站的表名进行恢复，然后再重命名，参照以下语句：
flashback table "回收站中的表名(如：Bin$DSbdfd4rdfdfdfegdfsf==$0)" to before drop rename to 新表名
oracle的闪回功能除了以上基本功能外，还可以闪回整个数据库：
使用数据库闪回功能，可以使数据库回到过去某一状态, 语法如下：
SQL>alter database flashback on
SQL>flashback database to scn SCNNO;
SQL>flashback database to timestamp to_timestamp('2007-2-12 12:00:00','yyyy-mm-dd hh24:mi:ss');
误更新恢复
*可以用一下语句找出执行删除语句的时间
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc;
*创建一个表保存取出的备份数据
create table 表名_bak as select * from 表名 as of timestamp sysdate -5/1440;
操作成功后你看看新表里面是不是你之前的数据.如果是的话再把新表数据弄到原表就好了
数据库字符集
select * from nls_database_parameters WHERE PARAMETER = 'NLS_CHARACTERSET'；
select userenv('language') from dual;
查询索引
select * from user_indexes
select * from dba_indexes
生成报表: @?/rdbms/admin/awrrpt;
注：报表是html格式的
查看当前实例
select instance_name from v$instance;
或者在oracle用户下输入 echo $ORACLE_SID
查看索引失效
select OWNER,STATUS,INDEX_NAME from dba_indexes where OWNER='CPSMGT_1' and STATUS !='VALID';
valid:当前索引有效
N/A :分区索引 有效
unusable:索引失效
查看session状态：（查看哪些主机在连接着）
select MACHINE,username,count(*) from v$session group by MACHINE,username;
开启和关闭归档（在mount模式下操作）
SQL> archive log list; #database log mode项为Archive Mode为归档模式
开启归档流程
shutdown immediate;
startup mount; #启动到mount状态
alter database open;
alter database archivelog #开启归档
alter database noarchivelog #关闭归档
修改归档路径：
mkdir -p /data/arch ---要修改的归档路径
archive log list; ---查看oracle归档
shutdowm immediate;
startup mount; ---oracle需要 启动到mount状态
alter database archivelog;
alter system set log_archive_dest_1='location=/data/arch'; ---归档路径修改为 指定路径
alter database open;
1、set line 180 等同于set linesize 180
注：参数bailinesize用于设置行的总长度，即每行不超过100个字符。如果超过则换行显示。默认值是80。
2、col machine for a20
注：显示machine列的长度，a20 代表字符，长度20，a只用来处理字符，数字时则需额外的格式化处理
3、set pagesize 10000
注：设置页高度为10000
Oracle密码过期
1. 查看用户密码的有效期设置(一般默认的配置文件是DEFAULT)
SQL > SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'
PASSWORD_VERIFY_FUNCTION
2. 将密码有效期由默认的180天修改成“无限制”,修改之后不需要重启动数据库，会立即生效
SQL > Alter PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
3. 帐户再改一次密码
SQL > alter user ink identified by <原来的密码> ----不用换新密码
4. 使用修改后的用户登录,如果报“ORA-28000:用户已被锁”，解锁
SQL > alter user db_user account unlock;
SQL > commit;
查询全局数据库名
select name from v$database;
select * from global_name;
show parameter db;
多表连查之 from 表1，表2
用户中的两张表分别三表1和表2，举例
格式：
select 表别名1.<字段1>, 表别名1.<字段2>, 表别名2.<字段1>
from [用户名.<表名1> <表别名1>]，[用户名.<表名2> <表别名2>]
where 表别名1.<字段x> = 表别名2.<字段x>;
示例：
select a.empno, a.ename, b.dname
from scott.emp a, scott.dept b
where a.deptno = b.deptno;
注：as上图中的as只是起个别名的意思，和python中的 as 类似的用法
多表连查之 inner join
语法：inner join...on (inner可省略)
select a.empno, a.ename, b.dname
from scott.emp a join scott.dept b
on a.deptno = b.deptno;
返回结果同上图
欢迎一起探讨开源、技术
