Oracle 工作常用SQL

Database►Oracle Comments
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 查看oracle数据库的字符集
select userenv('language') from dual;

# 查看oracle数据库的编码
select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

# 查询当前oracle的并发连接数
select count(*) from v$session where status='ACTIVE';

# 查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;

# 查询用户会话
select username,serial#,sid,program,machine,status from vsession where username='USERNAME' AND STATUS='ACTIVE';; 

select saddr,sid,serial#,paddr,username,status from vsession where username is not null;

# 删除相关用户会话
alter system kill session 'sid, serial#';

Oracle 打补丁

1） 如果服务器是此数据库独占的，可以设置SGA到30%到40%的操作系统物理内存。
2） 如果是多个数据库共享的服务器，可以设置SGA为12GB到16GB，具体根据应用压力判断。一般小库可以设置8GB
PGA设置为SGA的50%；
原则上，主机上所有数据库内存加起来不能超过主机的50%内存。
inux主机由于内存通常较多，unix相对较少，所以Linux内存分配为UNIX的1.5倍。

temp tablespace

1
2
3
4
5
6
7
alter tablespace temp add tempfile '+DATA' size 10M autoextend off;
alter tablespace temp add tempfile size 10M autoextend off;
alter tablespace temp drop tempfile '+DATA/susu/tempfile/temp.382.1059144303';
alter tablespace undotbs1 add datafile size 10M autoextend off;
alter tablespace undotbs1 add datafile '+DATA' size 20M autoextend off;
select name,sum(bytes)/1024/1024 from v$datafile group by name;
alter tablespace undotbs1 drop datafile '+DATA/susu/datafile/undotbs1.382.1059144851';

redo log file

1
2
3
4
5
6
7
8
select group#,status,member from v$logfile;

alter database add logfile thread 1 group 5 '+data' size 30M;
# 日志组成员不需要指定大小，因为所有成员大小都是一样的。
alter database add logfile member '+data' to group 5;
alter database add logfile member '+data/susu/onlinelog/group_5.2.log' to group 5;
ALTER DATABASE ADD LOGFILE GROUP6('/usr/oracle/app/oradata/orcl/redo06a.log','/usr/oracle/app/oradata/orcl/redo06b.log') SIZE 2048M;
alter database drop logfile group 5;

开启归档日志

1
2
3
4
5
6
7
sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter system set log_archive_dest_1='/arch' scope=both;
archive log list;
alter database open;

创建profile

创建用户

1
2
create user patrol default tablespace users identified by 'abcd1234' profile default;
grant connect,create session,alter session,select any dictionary to patrol;

时间

1
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

logfile

1
2
3
select group#,type,status,member from v$logfile;
select group#,sequence#,status from v$log;
alter system switch logfile;

用户相关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 查看当前用户的缺省表空间
select username,default_tablespace from user_users;

# 查看当前用户的角色
select * from user_role_privs;

# 查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

# 查看用户下所有的表
select * from user_tables;

# 显示当前会话所具有的权限
select * from session_privs;

# 显示指定用户所具有的系统权限
select * from dba_sys_privs where grantee=’GAME’;

# 查看名称包含log字符的表
select object_name,object_id from user_objects where instr(object_name,’LOG’)>0;

# 查看序列号，last_number是当前值
select * from user_sequences;

表相关

1
2
3
4
5
6
7
8
9
10
11
# 查看某表的大小
select sum(bytes)/(1024*1024) as “size(M)” from user_segments where segment_name=upper(‘&table_name’);

# 查看某表的创建时间
select object_name,created from user_objects where object_name=upper(‘&table_name’);

# 查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,’Y')>0;

# 同义词，查看同义词的名称
select * from user_synonyms;

视图

1
2
3
4
5
6
7
# 查看视图的名称
select view_name from user_views;

# 查看创建视图的select语句
select view_name,text_length from user_views;
set long 2000; 说明：可以根据视图的text_length值设定set long 的大小
select text from user_views where view_name=upper(‘&view_name’);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

select * from dba_users; --数据库用户信息
select * from dba_roles; --角色信息
select * from dba_segments; --表段信息
select * from dba_extents; --数据区信息
select * from dba_objects; --数据库对象信息
select * from dba_lobs; --lob数据信息
select * from dba_tablespaces; --数据库表空间信息
select * from dba_data_files; --数据文件设置信息
select * from dba_temp_files; --临时数据文件信息
select * from dba_rollback_segs; --回滚段信息
select * from dba_ts_quotas; --用户表空间配额信息
select * from dba_free_space; --数据库空闲空间信息
select * from dba_profiles; --数据库用户资源限制信息
select * from dba_sys_privs; --用户的系统权限信息
select * from dba_tab_privs; --用户具有的对象权限信息
select * from dba_col_privs; --用户具有的列对象权限信息
select * from dba_role_privs; --用户具有的角色信息
select * from dba_audit_trail; --审计跟踪记录信息
select * from dba_stmt_audit_opts; --审计设置信息
select * from dba_audit_object; --对象审计结果信息
select * from dba_audit_session; --会话审计结果信息
select * from dba_indexes; --用户模式的索引信息

select * from all_users; --数据库所有用户的信息
select * from all_objects; --数据库所有的对象的信息
select * from all_def_audit_opts; --所有默认的审计设置信息
select * from all_tables; --所有的表对象信息
select * from all_indexes; --所有的数据库对象索引的信息
select * from all_tab_comments; --查询所有用户的表,视图等
select * from all_col_comments; --查询所有用户的表的列名和注释.
select * from all_tab_columns; --查询所有用户的表的列名等信息(详细但是没有备注)

select * from user_objects; --用户对象信息
select * from user_source; --数据库用户的所有资源对象信息
select * from user_segments; --用户的表段信息
select * from user_tables; --用户的表对象信息
select * from user_tab_columns; --用户的表列信息
select * from user_constraints; --用户的对象约束信息
select * from user_sys_privs; --当前用户的系统权限信息
select * from user_tab_privs; --当前用户的对象权限信息
select * from user_col_privs; --当前用户的表列权限信息
select * from user_col_comments; -- 查询本用户的表的列名和注释
select * from user_role_privs; --当前用户的角色权限信息
select * from user_indexes; --用户的索引信息
select * from user_ind_columns; --用户的索引对应的表列信息
select * from user_cons_columns; --用户的约束对应的表列信息
select * from user_clusters; --用户的所有簇信息
select * from user_clu_columns; --用户的簇所包含的内容信息
select * from user_cluster_hash_expressions; --散列簇的信息

select * from v$database; --数据库信息
select * from v$datafile; --数据文件信息
select * from v$controlfile; --控制文件信息
select * from v$logfile; --重做日志信息
select * from v$instance; --数据库实例信息
select * from v$log; --日志组信息
select * from v$loghist; --日志历史信息
select * from v$sga; --数据库SGA信息
select * from v$parameter; --初始化参数信息
select * from v$process; --数据库服务器进程信息
select * from v$bgprocess; --数据库后台进程信息
select * from v$controlfile_record_section; --控制文件记载的各部分信息
select * from v$thread; --线程信息
select * from v$datafile_header; --数据文件头所记载的信息
select * from v$archived_log; --归档日志信息
select * from v$archive_dest; --归档日志的设置信息
select * from v$logmnr_contents; --归档日志分析的DML DDL结果信息
select * from v$logmnr_dictionary; --日志分析的字典文件信息
select * from v$logmnr_logs; --日志分析的日志列表信息
select * from v$tablespace; --表空间信息
select * from v$tempfile; --临时文件信息
select * from v$filestat; --数据文件的I/O统计信息
select * from v$undostat; --Undo数据信息
select * from v$rollname; --在线回滚段信息
select * from v$session; --会话信息
select * from v$transaction; --事务信息
select * from v$rollstat; --回滚段统计信息
select * from v$pwfile_users; --特权用户信息
select * from v$sqlarea; --当前查询过的sql语句访问过的资源及相关的信息
select * from v$sql; --与v$sqlarea基本相同的相关信息
select * from v$sysstat; --数据库系统状态信息


select * from session_roles; --会话的角色信息
select * from session_privs; --会话的权限信息


select * from dual; --系统伪列表信息
select sysdate from dual; --可将Sysdate视为一个其结果为当前日期和时间的函数，在任何可以使用Oracle函数的地方都可以使用Sysdate。也可以将它视为每个表的一个隐藏的列或伪列。
select current_date from dual; --报告会话的时区中的系统日期。注：可以设置自己的时区，以区别于数据库的时区。
select SYSTIMESTAMP from dual; --报告TIMESTAMP数据类型格式的系统日期。

锁

1
2
3
4
5
# 锁表SQL查询
select object_name,machine,s.sid,s.serial# from gv$locked_object l, dba_objects d, gv$session s where l.object_id = d.object_id and l.session_id = s.sid

# 解除锁表
alter system kill session 'sid, serial#';

查看预设和当前shared_pool_size 和 db_cache_size值

1
2
3
4
5
6
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
 from sys.x$ksppi x,sys.x$ksppcv y 
 where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance') and x.indx=y.indx and x.ksppinm like '%db_cache_size%';


select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from sys.x$ksppi x,sys.x$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance') and x.indx=y.indx and x.ksppinm like '%pool_size%';
1
2
3
select sql_text,parse_calls,loads
from v$sql
where sql_text like 'sql_you_need_specify';

建议当表做大批量数据变更后，马上手工收集统计信息

1
exec dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&tablename',estimate_percent=>30,block_sample=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>8,granularity=>'ALL',cascade=>true);

查看阻塞信息

1
2
3
4
5
6
7
8
9
select sid,type,lmode,request,ctime,block from v$lock where type='TX';

select blocking_session,sid,wait_class,seconds_in_wait from v$session;

select s.session_id,s.object_id,d.object_name from v$locked_object s,dba_objects d where s.object_id=d.object_id;

alter system kill session 'sid,serial#';

select sid,serial#,user#,username,status,process,machine,port,program,type,service_name,lockwait from v$session where username is not null and status='ACTIVE' order by logon_time,sid;

获取当前会话的SID

1
2
3
4
5
SELECT * FROM V$SESSION  WHERE AUDSID = USERENV('SESSIONID');
 
SELECT * FROM V$MYSTAT A WHERE ROWNUM=1;
 
SELECT USERENV('SID') FROM DUAL;

查看当前会话执行的sql语句及进程信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT P.SPID,
       S.SID,
       S.OSUSER,
       S.MACHINE,
       S.PROGRAM,
       S.LOGON_TIME,
       S.SERIAL#,
       Q.MODULE,
       Q.SQL_ID,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT
  FROM V$SESSION S, V$PROCESS P, V$SQLAREA Q
 WHERE S.PADDR = P.ADDR(+)
   AND S.SQL_ID = Q.SQL_ID(+)
   AND S.AUDSID = USERENV('SESSIONID');

让实例重新校验所有online文件的状态，恢复文件的可用小型

1
alter system check datafiles;

素丶素丶丶wodetian

忍一时越想越气，退一步越想越亏。