数据库常用SQL语句

数据库：

select * from v$database


检查数据库是否启动正常

select * from gv$instance;


检查数据库当前实例是否启动正常
select * from v$instance

查看gv视图，确认连接是否为负载均衡
select inst_id,count(*) from gv$session group by (inst_id);

当前实例
select instance_name from v$instance

检查监听
$lsnrctl
lsnrctl>status

用虚拟表检查数据库是否正常
select * from dual
select * from dual@dblink
如果显示两行记录，说明数据库都正常启动，如果只有一行，说明有一台停止服务了
看HOST_NAME字段下面的值，就可以知道是哪一台停止服务了

查询版本号
SQL>select * from v$version;
查询小版本号
[oracle@rac1 ~]$./$RACLE_HOME/OPatch/opatch lsinv -bugs_fixed|grep PSU
9952230    12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10248542   12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
11724962   12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
12419392   12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
上面的显示表示PSU版本已经是10.2.0.5.4了！
数据库里也可以查询到具体信息，（v$version里面不会显示这个小版本号的）具体如下：
select * from registry$history;

日期
select to_char(sysdate,'YYYY-MM-DD') checkdate from dual;

时间
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') indextime from dual;

版本
select * from v$version;

实例
select instance_name instname from v$instance;

1.查看所有用户：
select * from dba_users;   
select * from all_users;   
select * from user_users;
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
8.SqlPlus中查看一个用户所拥有权限
SQL>select * from dba_sys_privs where grantee='username';
其中的username即用户名要大写才行。
比如：
SQL>select * from dba_sys_privs where grantee='TOM';

9、Oracle删除指定用户所有表的方法
select 'Drop table '||table_name||';' from all_tables
where owner='要删除的用户名(注意要大写)';
10、删除用户
drop user user_name cascade;
如：drop user SMCHANNEL CASCADE
11、获取当前用户下所有的表：select table_name from user_tables;
12、删除某用户下所有的表数据: select 'truncate table  ' || table_name from user_tables;
13、禁止外键
ORACLE数据库中的外键约束名都在表user_constraints中可以查到。其中constraint_type='R'表示是外键约束。
启用外键约束的命令为：alter table table_name enable constraint constraint_name
禁用外键约束的命令为：alter table table_name disable constraint constraint_name
然后再用SQL查出数据库中所以外键的约束名：
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
14、ORACLE禁用/启用外键和触发器
--启用脚本
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;
exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
end;
/
commit;
--禁用脚本
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;
exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
end;
/
commit;
 

查看当前实例
env | grep SID

查看数据库保留字
SELECT * FROM V$RESERVED_WORDS

查看数据库、实例、许可状态、数据库连接
SELECT * FROM V$DATABASE
SELECT * FROM V$INSTANCE
SELECT * FROM V$LICENSE
SELECT * FROM V$DBLINK

查看密码文件用户
SELECT * FROM V$PWFILE_USERS

查看资源限制
SELECT * FROM V$RESOURCE_LIMIT

查看数据库选项、对象参数
SELECT * FROM V$OPTION
SELECT * FROM SYS.ARGUMENT$

查看兼容性
SELECT * FROM V$COMPATIBILITY

查看数据库数据字典(表、视图、索引)
SELECT * FROM V$FIXED_TABLE
SELECT * FROM V$FIXED_VIEW_DEFINITION
SELECT * FROM V$INDEXED_FIXED_COLUMN

查看数据库NLS状态
SELECT * FROM SYS.PROPS$

查看表空间、控制、日志、数据文件及备份、读写状态
SELECT * FROM V$TABLESPACE
SELECT * FROM V$CONTORLFILE
SELECT * FROM V$LOGFILE
SELECT * FROM V$DATAFILE 数据文件的存储划分位置
SELECT * FROM V$BACKUP
SELECT * FROM V$FILESTAT
查看归档日志数、路径、进程

SELECT * FROM V$ARCHIVE_LOG
SELECT * FROM V$ARCHIVED_DEST
SELECT * FROM V$ARCHIVE_PROCESSES

查看回滚段名、状态
SELECT * FROM V$ROLLNAME
SELECT * FROM V$POLLSTAT

数据字典及某些字段意义
SELECT sum(decode(n.statistic#, 15, s.value,0)) UGA,
sum(decode(n.statistic#, 20, s.value,0))/1024||'K' PGA,
sum(decode(n.statistic#, 180, s.value,0)) Sore_In_Member,
sum(decode(n.statistic#, 181, s.value,0)) Sore_In_Disk,
sum(decode(n.statistic#, 182, s.value,0)) Sore_Of_Row,
sum(decode(n.statistic#, 6, s.value,0)) User_Call,
sum(decode(n.statistic#, 5, s.value,0)) User_Rollback,
sum(decode(n.statistic#, 4, s.value,0)) User_Commit,
sum(decode(n.statistic#, 3, s.value,0)) Current_Opened_Cursor,
sum(decode(n.statistic#, 1, s.value,0)) Current_Logon,
sum(decode(n.statistic#, 9, s.value,0)) Session_Logical_Read ,
sum(decode(n.statistic#, 150, s.value,0)) Short_Table_Scans ,
sum(decode(n.statistic#, 151, s.value,0)) Long_Table_Scans
FROM V$SESSTAT s,V$STATNAME n
WHERE s.STATISTIC# = n.STATISTIC#;
select * from v$rollstat;
select * from v$sgastat;
select * from v$sysstat;
select * from V$PROCESS;
select * from V$THREAD;
select * from V$TIMER;
select * from V$OBJECT_DEPENDENCY;
select * from V$ROWCACHE;
SID：会话唯一标识
TS#：表空间唯一标识
FILE#：数据文件唯一标识
RFILE#：？
LATCT#：LATCH
PADDR；进程地址
SADDR：会话地址
SQL_ADDRESS ：SQL语句地址
PREV_SQL_ADDR；前一个SQL语句地址
KADDR：锁地址
ADDR：对象地址
LADDR：LATCH





修改密码
1、有原密码
SQL>password
2、无原密码
SQL>alter user sys identified by  新密码；

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = webserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

二、Oracle RAC常用的日志文件:

1、    db的alertlog文件：
$ORACLE_BASE/admin/orcl/bdump/
$ORACLE_BASE/admin/ams/bdump/

2、    oracle crs日志文件：
$ORACLE_CRS_HOME/crs/log
$ORACLE_CRS_HOME/css/log
$ORACLE_CRS_HOME/evm/log
$ORACLE_CRS_HOME/srvm/log

3、查看日志
SQL>show paramter dump
value中bdump目录地址
alert_实例名.log
tail -100 alert_ora92.log |more
view alert_ora92.log

清空日志 清空文件
cat /dev/null >alert_ora92.log
当前用户
select * from V$PWFILE_USERS;

删除用户及所有对象
drop user user_name cascade;

查询权限
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' );

查询用户数据占用磁盘空间
select round(sum(bytes)/1024/1024/1024)||'G' from dba_segments where owner not in('SCOTT','SYSTEM','SYS','EXFSYS','