个人笔记存档

ArchonSbkd

已于 2023-02-16 11:34:56 修改

阅读量386

点赞数

文章标签： dba

于 2022-06-14 14:46:48 首次发布

本文链接：https://blog.csdn.net/ArchonSbkd/article/details/125277911

版权

'   '
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
oracle:
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
清除所有oracle进程（session\连接数）
ps -ef|grep oracle|grep LOCAL=NO|grep -v grep |awk {'print $2'} |xargs kill -9
#———————————————————————————
按oracleSID来查询连接数据库的客户端IP （linux）
netstat -anpT | grep aepdb | awk '{print $5}' | grep -o -E '1.*:' | awk -F ':' '{print $1}' | sort
#———————————————————————————
按监听端口1521来查询连接数据库的客户端IP （linux）
netstat -anpT | grep 1521 | awk '{print $5}' | grep -o -E '1.*:' | awk -F ':' '{print $1}' | sort
#———————————————————————————
aix按oracleSID来查询连接数据库的客户端IP
netstat -anpT | grep ngechdb1 | awk '{print $5}' | grep -o -E '1.*:' | awk -F ':' '{print $1}' | sort
#———————————————————————————
aix版本按1521端口查询连接数据库的客户端IP
netstat -n|grep EST|grep 1521|awk '{print $5}'|awk -F "." '{print $1 "." $2"." $3 "."$4}'|sort|uniq -c|sort -rn
#———————————————————————————
aix批量杀进程
ps -ef | grep xxxx | grep -v grep | awk '{print $2}' | xargs kill -9
（grep -v grep,去除本身grep的条目）
#———————————————————————————
查看链接数量
ps -ef | grep xxxx | wc -l
#———————————————————————————
设置检查点
alter system checkpoint global;
#———————————————————————————
强制登录oracle
sqlplus -prelim / as sysdba
#———————————————————————————
重启AIX系统:
shutdown -Fr
#———————————————————————————
--字符集
select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
#———————————————————————————
sqlplus / as sysdba @j0412.sql > j0412.log &
nohup sqlplus / as sysdba @0411zcj.sql > j0412.log &
#———————————————————————————
并行dml
alter session enable parallel dml;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
集群状态:
crsctl stat res -t
crsctl check crs
#———————————————————————————
检查crs相关服务状态
crsctl check crs
(event manage is online就正常了)
(启动时候可以看cssd日志)
#———————————————————————————
启动/停止crs服务:(失败的话用root用户)
crsctl start crs
crsctl stop crs
show parameters dum
tail -2000f /app/oracle/diag/rdbms/resdb/resdb1/trace/a*.log|more
#———————————————————————————
crs数据库停止/启动
srvctl stop database -d DATABAS
srvctl stop instance -d DATABASE -i INSTANCE
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
监听
lsnrctl stat
#———————————————————————————
集群状态
crsctl stat res -t
lsnrctl
3)、关闭监听,命令：stop
4)、启动监听,命令：start
5)、监听状态,命令：status
6)、退出监听器控制台,命令：exit
注册监听:
alter system register;
清理监听文件
(listener.log)
一定要使用grid用户,到指定路径下。
lsnrctl set log_status off
cat /dev/null > listener.log
lsnrctl set log_status on
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
rac日志路径
oracle 下的alert日志$ORACLE_BASE/diag/rdbms/orcl/orcl1/trace/alertorcl1.log
grid 下的 alert 日志 $ORACLE_HOME/log/rac1/alertrac1.log
css的日志$ORACLE_HOME/log/rac1/cssd/cssd.log ocssd.log
crs的日志$ORACLE_HOME/log/rac1/crsd/crsd.log
ohas的日志$ORACLE_HOME/log/rac1/ohasd/ohasd.log
evm的日志$ORACLE_HOME/log/rac1/evmd/evmd.log
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
oracle环境变量（试下$ORACLE_HOME）
cd /home/oracle
cat .profile
查看历史补丁
$ORACLE_HOME/OPatch/opatch lsinventory
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看所有隐含参数
col name for a30;
col value for a10;
select x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf, 7),
1,
'MODIFIED',
4,
'SYSTEM_MOD',
'FALSE') ismod,
decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
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
order by translate(x.ksppinm, ' _', ' ');
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
#———————————————————————————
从oracle里面查看服务器文件:
SQL> host ls -lrt /db/11.2.0.4/dbs/
#———————————————————————————
查看实例有哪些
ps -ef|grep smon
#———————————————————————————
切换实例
export ORACLE_ISD=xx
#———————————————————————————
查看scanip在哪个节点上(grid用户)
srvctl status scan
#———————————————————————————
查看连接数
select inst_id,count(*) from gV$session group by inst_id;
#———————————————————————————
查看不活动的连接数
col "username" for a13
col "program" for a35
col "machine" for a15
col "status" for a13
col "LOGON_TIME" for a25
select sid,serial#,username,machine,status,program,LOGON_TIME, 'alter system kill session '''||sid||','||serial#||''' immediate; 复制'
from v$session
where status ='INACTIVE';
#———————————————————————————
查看自己的sid
select sid from v$mystat where rownum=1;
#———————————————————————————
查询profile
select distinct profile from dba_profiles;
#———————————————————————————
按节点查看数据库状态
select inst_id,name,created,log_mode,open_mode,flashback_on from gv$database order by inst_id;
#———————————————————————————
恢复进度
select distinct ktuxesiz from x$ktuxe where KTUXESTA='ACTIVE';
#———————————————————————————
死事务
select distinct ktuxecfl,count(*) from x$ktuxe group by ktuxecfl;
#———————————————————————————
查看重复数据
select a.rowid,b.rowid,a.* from xx a,xx b where a.rowid=b.rowid;
#———————————————————————————
查看数据库名、实例
select name from v$database;
#———————————————————————————
查看告警信息
tail -10000 alert*.log|grep -E 'ORA-|TNS-'|more
#———————————————————————————
查询oracle最大连接数
select value from v$parameter where name = 'processes'
#———————————————————————————
修改进程连接数：
alter system set processes=1000 scope=spfile;
#———————————————————————————
修改会话数：
alter system set sessions=1000 scope=spfile;
#———————————————————————————
show parameters               --查看参数
session = process x1.5       (大小)
#———————————————————————————
重启数据库,启用参数：
shutdown immediate;
startup;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看cdb下的pdb
show pdbs
#———————————————————————————
从cdb切换到pdb
alter session set container=ORCLPDB;
#———————————————————————————
从pdb切换到cdb
alter session set container=CDB$ROOT;
#———————————————————————————
用户已有权限
select * from DBA_SYS_PRIVS where grantee='XXX'
#———————————————————————————
用户所有权限
SELECT *
FROM (SELECT a.username,
'ROLE' AS TYPE,
b.granted_role ||
DECODE(admin_option, 'YES', ' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_role_privs b
WHERE a.username = b.grantee
UNION
SELECT a.username,
'SysPrivs' AS TYPE,
b.privilege ||
DECODE(admin_option, 'YES', ' (With Admin Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_sys_privs b
WHERE a.username = b.grantee
UNION
SELECT a.username,
'ObjPrivs' AS TYPE,
b.owner || '.' || b.table_name || ' - ' || b.privilege ||
DECODE(grantable, 'YES', ' (With Grant Option)', NULL) what_granted
FROM sys.dba_users a, sys.dba_tab_privs b
WHERE a.username = b.grantee
ORDER BY 1)
WHERE username = upper('');
#———————————————————————————
存过授权
Grant execute on pro_finish_order_ob to LLW
#———————————————————————————
revoke撤回相应权限
查看某个角色包含啥权限
select * from role_sys_privs;
#———————————————————————————
回收ddl权限
select 'revoke '||privilege||' on '||OWNER||'.'||TABLE_NAME||' FROM '||GRANTEE||';' from dba_tab_privs where grantee='XIZANG_TEST' and privilege in ('CREATE','DROP','ALTER');
#———————————————————————————
查看所有角色
select * from dba_roles;
#———————————————————————————
dba_sys_privs 用户的系统权限信息
dba_tab_privs 用户具有的对象权限信息--表
dba_col_privs 用户具有的列对象权限信息
dba_role_privs 用户具有的角色信息
#———————————————————————————
查最后ddl时间
SELECT LAST_DDL_TIM,OWNER FROM DBA_OBJECTS;
#———————————————————————————
表被操作的记录
select * from USER_TAB_MODIFICATIONS;
select * from ALL_TAB_MODIFICATIONS;
#———————————————————————————
删除序列
drop sequence ZSC_GUANGXI_189.SEQ_FN_HEB;
#———————————————————————————
将序列高速缓存cache设置为1(每次往内存里丢20)
alter sequence sequencename cache 20 ;
不设置cache,或者过小的话,会产生过多redo log
#———————————————————————————
查看依赖关系(某个存过用过某个xx)
dba_dependencies
#———————————————————————————
删除object
begin
dbms_job.remove(4233483);
commit;
end;
#———————————————————————————
查看dml lock
set linesize 300
select * from dba_dml_locks order by owner, name, session_id;
#———————————————————————————
锁定/解锁账号
alter user xx account lock;(unlock)
#———————————————————————————
查看用户锁定状态
select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where username in ;
#———————————————————————————
查用户大小(可用观察数据泵导入)
SELECT SUM(s.BYTES)/1024/1024 "sizes(MB)" from dba_segments s where s.OWNER= 'XXXX'
#———————————————————————————
查表大小
select * from dba_segments where segment_name='TF_B_TRADE_WIDENET';
#———————————————————————————
查表ddl
SELECT DBMS_METADATA.GET_DDL('TABLE','TABLEXXXX','USERXXX') FROM DUAL;
#———————————————————————————
给用户使用某directory的权限
grant read,write on directory DATA_PUMP_DIR to 用户;
#———————————————————————————
查看分区信息:
select * from user_tab_partitions a where a.table_name='SALES';
#———————————————————————————
查看某表中某个分区的数据
select * from tablexx partition(pxx);
#———————————————————————————
分区操作:
添加分区:
alter table xx add partition px(这个必须是已有分区之后) value less than (xxx);
删除分区:
alter table xx drop partition px;
截断分区:
alter table xx truncate partition px;
合并分区:
alter table xx merge partitions S1,S2 into partition S2;
拆分分区:
alter table xx split partition p2 at (1500)
into (partition p21,partition p22);
#———————————————————————————
查看存储过程:
select NAME,type,line,text from dba_source where name='IP_DIZHI_TONGJI' order by line;
--可用
select text from dba_source where name='IP_DIZHI_TONGJI' order by line;
授权用户执行存储过程
Grant execute on procedure_name to user;
如果要求被授权用户也能够授权给其它用户
grant execute on procedure_name to user1 with grant option;
#———————————————————————————
带字段解释的describe:
set linesize 600 pagesize 600
col COLUMN_NAME for a20
select COLUMN_NAME,COMMENTS from dict_columns where table_name=upper('v$sqlarea');
#———————————————————————————
--按sql_id查执行计划explain
select * from table(dbms_xplan.DISPLAY_CURSOR('0aufwj4ut1zx7', null, 'ALLSTATS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bm5df9uz0jvbw',null));
（显示秒）
#———————————————————————————
开启最小化日志
（文档:表级日志）
ALTER TABLE USERXX.TABLEXX ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
#———————————————————————————
完整行约束,键值
not null
unique                       --可以存在1个值为空,然后不能重复
check                       --按用户要求
primary key --不可以重复不可为空
foreign key
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
sql输出设置（脚本）
1.term命令：
当和SPOOL命令联合使用时,可以取消SQLPLUS输出,查询结果仅仅存在于假脱机文件中
set term on：               查询结果既显示于假脱机文件中,又在SQLPLUS中显示；
set term off：               查询结果仅仅显示于假脱机文件中。
#———————————————————————————
2.其他命令：
SQL>set colsep'|';　　　   //-域输出分隔符
SQL>set echo off;　　　       //显示start启动的脚本中的每个sql命令,缺省为on
SQL>set echo on    //设置运行命令是是否显示语句
SQL>set feedback on;     //设置显示“已选择XX行”
SQL>set feedback off;　     //回显本次sql命令处理的记录条数,缺省为on
SQL>set heading off;　　   //输出域标题,缺省为on
SQL>set pagesize 0;　　 //输出每页行数,缺省为24,为了避免分页,可设定为0。
SQL>set linesize 80;　　    //输出一行字符个数,缺省为80
SQL>set numwidth 12;　     //输出number类型域长度,缺省为10
SQL>set termout off;　　    //显示脚本中的命令的执行结果,缺省为on
SQL>set trimout on;　　    //去除标准输出每行的拖尾空格,缺省为off
SQL>set trimspool on;　    //去除重定向（spool）输出每行的拖尾空格,缺省为off
SQL>set serveroutput on;    //设置允许显示输出类似dbms_output
SQL>set timing on; //设置显示“已用时间：XXXX”
SQL>set autotrace on; //设置允许对执行的sql进行分析
SQL>set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.
#———————————————————————————
3.用sqlplus运行查询时设置
set echo off;                #控制start命令不列出命令文件中的每一命令
set feedback off;            #显示由查询返回的记录数 off等价为0
set heading off;            #报表中列标题不打印
set pagesize off;            #置从顶部标题至页结束之间的行数
set linesize off;            #置一行中显示的字符总数
set termout off;            #由文件执行命令时不显示输出
set trimout on;            #每一显示行的末端去掉空格
set trimspool on;       #去掉spool输出时每行末端空格
#———————————————————————————
4.修改时间格式:
alter session set nls_date_format='yyyy-mm-dd';
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
#———————————————————————————
5.spool - spool off 将xxxx内容写入到xx.sql里
spool xx.sql
xxxx
xxxx
spool off;
#———————————————————————————
<<EOF
后续的输入作为子命令或子Shell的输入,直到遇到EOF为止,再返回到主Shell。
而'EOF'可以换成任何其他字符都可以
#———————————————————————————
脚本格式(待优化)
. ~/.profile
sqlplus -s "/as sysdba" << EOF_A
set pages 9999;
set heading off;
set feedback off;
spool bfx_7.sql
xxxx
xxxx
spool off;
@bfx_7
EXIT
#——————————
(可以不带第一行. ~/.profile)
. ~/.profile
sqlplus -s "/as sysdba" << EOF_A
set pagesize 9999 linesize 900
set heading off
set feedback off
xxx
xxx
xxx
EXIT
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
定时任务
crontab -l
crontab -e修改
(报错:You (oracle) are not allowed to access to (crontab) because of pam configuration.
解决:将密码设置为不过期:
chage -M 99999 oracle)
#———————————————————————————
时间
sysdate-A表示往前推A天
select sysdate - 1/6 from dual 得出的时间是当前时间往前推4小时
select sysdate - 1/24/60 from dual 得出的时间是当前时间往前推1分钟
#———————————————————————————
修改下次执行时间
exec dbms_ijob.next_date(1565,to_date('2022-07-21 12:00:00','yyyy-mm-dd hh24:mi:ss'));
commit;
#———————————————————————————
crontab文件的使用示例：
30 21 * * *                             表示每晚的21:30
45 4 1,10,22 * *                        表示每月1、10、22日的4 : 45
10 1 * * 6,0                            表示每周六、周日的1 : 10
0,30 18-23 * * *                        表示在每天18 : 00至23 : 00之间每隔30分钟
0 23 * * 6                                表示每星期六的11 : 00 pm
* */1 * * *                            每一小时
* 23-7/1 * * *                            晚上11点到早上7点之间,每隔一小时
* 8,13 * * 1-5                            从周一到周五的上午8点和下午1点
0 11 4 * mon-wed                        每月的4号与每周一到周三的11点
0 4 1 jan *                            一月一号的4点
#———————————————————————————
库里查定时任务
select job job_id,
log_user,
priv_user,
schema_user,
last_date,
this_date ,--上次没成功就是空
next_date,
total_time,
interval ,--时间间隔
what --执行的是哪个存储过程
from user_jobs;
#———————————————————————————
白名单crontab
. ~/.profile
sqlplus -s "/as sysdba" << EOF_A
set pages 999;
set heading off;
set feedback off;
spool qingli.sql
select 'alter system kill session''' || sid || ',' || serial# || ''' immediate;'
from v\$session
where username not in ('GGADM','RES_CHECK_JIANGSU','RES_CHECK_HENAN','RES_CHECK_FUJIAN','RES_CHECK_LTJT')
and CLIENT_INFO not in ('10.245.6.107','134.95.3.107','134.95.3.108','10.245.7.39','10.190.2.66','10.190.2.67','10.243.1.119','10.243.1.123','10.243.0.15','10.243.0.16')
and status='ACTIVE'
and SQL_EXEC_START<sysdate-1/6
order by logon_time, sid;
spool off;
@qingli
EXIT
———————————————————————————————————————————————————————————————————————————————————————————————————————————————
USER
create user resltjt_skxr identified by resltjt_skxr_205 ;
#———————————————————————————
alter user resltjt_skxr default tablespace GENERAL;
#———————————————————————————
select 'grant select on '||owner||'.'||object_name||' to resltjt_skxr;' from dba_objects where owner =upper('resltjt') and object_type='TABLE';
#———————————————————————————
select 'create or replace synonym resltjt_skxr .'||object_name||' for '||owner||'.'||object_name||';' from dba_objects where owner = upper('resltjt') and object_type='TABLE';
#———————————————————————————
create or replace synonym xx for RES_HUBEI.xx;
#———————————————————————————
查用户大小(可用观察数据泵导入)
SELECT SUM(s.BYTES)/1024/1024 "sizes(MB)" from dba_segments s where s.OWNER= 'XXXX'
#———————————————————————————
查看用户密码默认管理方式
select profile from dba_users where username=users;
#———————————————————————————
查看数据库默认的密码管理方式有效期
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
#———————————————————————————
设置不限日期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
DBLINK
查看dblink
select * from dba_objects where OBJECT_NAME like '%D_LINK%';
#———————————————————————————
创建dblink
create public database link sc_gis
connect to sde_sichuan identified by sde_sichuan
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.162.64.242)(PORT = 9999))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gisdb)
)
)
';
select db_link,'select count(*) from dba_objects@'||db_link||';' from dba_db_links where owner='RESFLOW_TWO';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
审计
#———————————————————————————
添加精细审计
exec DBMS_FGA.ADD_POLICY(object_schema => 'ORDERS_CENTER',object_name => 'BJ_CODE',policy_name => 'BJ_CODE_POL',enable => TRUE,statement_types => 'INSERT, UPDATE, DELETE',audit_column_opts => DBMS_FGA.ANY_COLUMNS);
查看已设置的精细审计：
SELECT * FROM DBA_AUDIT_POLICIES;
删除精细审计：
EXEC dbms_fga.drop_policy(object_schema=>'schema_name',object_name=>'table_name',policy_name=>'CHK_table_name');
禁用精细审计：
EXEC dbms_fga.disable_policy(object_schema=>'schema_name',object_name=>'table_name',policy_name=>'CHK_table_name');
启用精细审计：
EXEC dbms_fga.disable_policy(object_schema=>'schema_name',object_name=>'table_name',policy_name=>'CHK_table_name');
查看审计结果：
SELECT * FROM dba_fga_audit_trail;
删除审计结果：
DELETE FROM sys.fga_log$;
#———————————————————————————
--查看statements相关审计
SELECT * FROM DBA_STMT_AUDIT_OPTS;
#———————————————————————————
--查看privileges相关审计
SELECT * FROM DBA_PRIV_AUDIT_OPTS;
#———————————————————————————
--查看object相关审计
SELECT * FROM DBA_OBJ_AUDIT_OPTS;
#———————————————————————————
--审计表
select * from dba_audit_trail;
TIMESTAMP(时间点)
#———————————————————————————
审计表delete相关查询
select username, obj_name, owner, action, action_name
from dba_audit_trail
where action_name = 'DELETE'
and timestamp > sysdate - 4;
#———————————————————————————
某个时间点某个object的审计内容
select OBJ_NAME, SQL_TEXT, timestamp
from dba_audit_trail
where obj_name = 'BS_BUSSINESS_UPDATE'
and timestamp > to_date('2023-01-17', 'yyyy-mm-dd');
#———————————————————————————
show parameter audit
audit_file_dest             /u01/app/oracle/admin/ORCL/adump
audit_sys_operations        FALSE
audit_syslog_level
audit_trail                    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步：关闭审计功能
SQL> alter system set audit_trail = none scope=spfile;
重启实例
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
INDEX
创建index
create index xx on table(zd);
#———————————————————————————
create index 索引名 on 用户.表名(字段)
tablespace 表空间名
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
#———————————————————————————
查看索引实效状态
select table_owner,table_name,index_name,status,index_type from dba_indexes where status='UNUSABLE';
select * from dba_indexes where index_name='xxxx';
select index_owner,PARTITION_NAME,index_name,status from dba_ind_partitions where status='UNUSABLE';
status:三种状态
valid:当前索引有效
N/A :分区索引有效
unusable:索引失效
#———————————————————————————
查看索引
select * from dba_ind_columns where table_name='TF_B_TRADE_WIDENET';
#———————————————————————————
查看索引并行度
select s.degree from dba_indexes s where s.index_name = upper('IDX_TF_B_ACTION_DISNTLOG');
#———————————————————————————
查看索引
select * from dba_ind_columns where table_name='TF_B_TRADE_WIDENET';
#———————————————————————————
查看索引创建时间
select object_name, created
from dba_objects
where object_type = 'INDEX'
AND object_name in
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
ogg:
查看ogg位置
ps -ef|grep rpt
#———————————————————————————
su - ogg
ggsci
info all
或者
(echo info all;echo exit)|./ggsci|grep -E "EXTRACT|REPLICAT" |awk 'BEGIN {FS=" +"} {print $1,$3,$2,$4,$5}' >> $tmpfile
#———————————————————————————
OGG日志:
/ogg/install/
tail -f ggserr.log
tail -3000 ggserr.log |grep error
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
闪回查询
在 from xx表后面加 -7 或者别的
as of timestamp sysdate -7;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
TABLESPACE
#———————————————————————————
查看数据库默认表空间
SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE' ;
查看默认临时表空间
select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
#———————————————————————————
查看默认临时表空间
select TABLESPACE_NAME,
to_char(TABLESPACE_SIZE) / 1024 / 1024 TABLESPACE_SIZE(M),
to_char(ALLOCATED_SPACE) / 1024 / 1024 ALLOCATED_SPACE(M),
to_char(FREE_SPACE) / 1024 / 1024 FREE_SPACE(M)
from dba_temp_free_space;
#———————————————————————————
表空间大小
select tablespace_name,sum(bytes)/1024/1024,sum(maxbytes)/1024/1024 from dba_data_files group by tablespace_name;
临时表空间大小
select tablespace_name,sum(bytes)/1024/1024,sum(maxbytes)/1024/1024 from dba_temp_files group by tablespace_name;
#———————————————————————————
表空间剩闲
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
#———————————————————————————
总
select a.tablespace_name,sum(a.bytes)/1024/1024,sum(b.bytes)/1024/1024 from dba_data_files a,dba_free_space b where a.tablespace_name = b.tablespace_name group by a.tablespace_name;
#———————————————————————————
查表空间+表空间
select * from DBA_DATA_FILES where tablespace_name='SUB_AREA2';
alter tablespace xx add datafile'' size 1024m AUTOEXTEND on next 500m maxsize unlimited;
#———————————————————————————
临时表空间
select * from DBA_TEMP_FILES where tablespace_name='SUB_AREA2';
ALTER TABLESPACE xx ADD TEMPFILE '' SIZE 1G AUTOEXTEND ON next 500m maxsize unlimited;
#———————————————————————————
组
select * from dba_tablespace_groups;
#———————————————————————————
将某临时表空间xx1放置到表空间组yy1中
alter tablespace xx1 tablespace group yy1;
#———————————————————————————
创建临时表空间xx1放置到表空间组yy1中
create temporary tablespace xx1 tempfile '/../../xx1.dbf' size 10M tablespace group yy1;
#———————————————————————————
将默认临时表空间设置为表空间组yy1
alter database default temporary tablespace yy1;
#———————————————————————————
将临时表空间从表空间组中删除
alter tablespace mytemp2 tablespace group '';
#———————————————————————————
asm表空间情况:
有时候会看到表空间位置+data/xxx
这种情况下去grid用户
export ORACLE_SID=+ASM1
然后ls查看各个路径
#———————————————————————————
查看表空间使用率
col "TABLESPACE_NAME" for a25
col "USED_RATE_%" for a13
SELECT UPPER(T.TABLESPACE_NAME) TABLESPACE_NAME,
T.TOTAL_BYTES_MB,
D.BYTES_MB-NVL(F.FREE_BYTES_MB,0) USED_MB,
TO_CHAR(ROUND((D.BYTES_MB-NVL(F.FREE_BYTES_MB,0))/T.TOTAL_BYTES_MB*100,2),'990.99') "USED_RATE_%",
(T.TOTAL_BYTES_MB-(D.BYTES_MB-NVL(F.FREE_BYTES_MB,0))) FREE_BYTES_MB,
F.MAX_FREE_BYTES_MB
FROM ( SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) FREE_BYTES_MB,
ROUND(MAX(BYTES)/(1024*1024),2) MAX_FREE_BYTES_MB
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F,
( select tablespace_name, ROUND(sum(decode
(AUTOEXTENSIBLE,'YES',MAXBYTES,'NO',BYTES))/1024/1024) TOTAL_BYTES_MB
FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) T,
( SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) BYTES_MB
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) D
WHERE T.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME=D.TABLESPACE_NAME
ORDER BY 4 DESC;
#———————————————————————————
单节点临时表空间使用率
comp sum of "TBS_SIZE(G)" "TBS_USED(G)" "TBS_FREE(G)" on report\r
break on report\r
col "TBS_NAME" for a15\r
SELECT d.status "tbs_status",d.tablespace_name "TBS_NAME",d.contents "TBS_TYPE",d.extent_management "TBS_MAG_TYPE",\r
round(NVL(a.bytes/1024/1024/1024,0),2) "TBS_SIZE(G)",\r
round(NVL(t.bytes/1024/1024/1024,0),2) "TBS_USED(G)", \r
round(NVL((a.bytes-t.bytes)/1024/1024/1024,0),2) "TBS_FREE(G)",\r
round(NVL(t.bytes/a.bytes*100,0),2) "TBS_PERT(%)"\r
FROM sys.dba_tablespaces d,\r
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) a,\r
(select tablespace_name,sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t\r
WHERE d.tablespace_name=a.tablespace_name(+)\r
AND d.tablespace_name=t.tablespace_name(+)\r
AND d.extent_management like 'LOCAL'\r
AND d.contents like 'TEMPORARY';\r
#———————————————————————————
全部表空间使用率
comp sum of "TBS_SIZE(G)" "TBS_USED(G)" "TBS_FREE(G)" on report\r
break on report\r
col "TBS_NAME" for a15\r
SELECT d.status "tbs_status",d.tablespace_name "TBS_NAME",d.contents "TBS_TYPE",d.extent_management "TBS_MAG_TYPE",\r
round(NVL(a.bytes/1024/1024/1024,0),2) "TBS_SIZE(G)",\r
round(NVL(t.bytes/1024/1024/1024,0),2) "TBS_USED(G)", \r
round(NVL((a.bytes-t.bytes)/1024/1024/1024,0),2) "TBS_FREE(G)",\r
round(NVL(t.bytes/a.bytes*100,0),2) "TBS_PERT(%)"\r
FROM sys.dba_tablespaces d,\r
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) a,\r
(select tablespace_name,sum(bytes_cached) bytes from gv$temp_extent_pool group by tablespace_name) t\r
WHERE d.tablespace_name=a.tablespace_name(+)\r
AND d.tablespace_name=t.tablespace_name(+)\r
AND d.extent_management like 'LOCAL'\r
AND d.contents like 'TEMPORARY';\r
#———————————————————————————
清理system表空间
①
select segment_name,sum(bytes)/1024/1024 FROM user_segments where tablespace_name='SYSTEM' group by segment_name order by 2;
②
SELECT OWNER,SEGMENT_NAME,BYTES FROM DBA_SEGMENTS WHERE tablespace_name='SYSTEM' ORDER BY BYTES desc;
如果审计日志并不是很大可以直接清理
truncate table SYS.AUD$;
truncate table SYS.FGA_LOG$;
注意的是,如果AUD$表过大,那么直接TRUNCATE AUD$表,系统要立即释放大量的EXTENTS,会严重影响系统性能。
以下2个步骤逐步释放EXTENTS：
1、清空数据并且保留原来的EXTENTS：
TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;
#———————————————————————————
查看某个表空间的具体表、字段占用
col owner for a10
col segment_name for a25
col table_name for a10
col column_name for a10
col index_name for a10
select a.owner,
a.segment_name,
b.table_name,
b.column_name,
b.index_name,
sum(a.bytes) / 1024 / 1024 / 1024 g
from dba_segments a, dba_lobs b
where a.tablespace_name = 'IFM_ATTACH_FILE'
and a.segment_name = b.segment_name
group by a.owner,
a.segment_name,
b.table_name,
b.column_name,
b.index_name
having sum(bytes) / 1024 / 1024 / 1024 > 1
order by sum(bytes) / 1024 / 1024 / 1024 desc;
———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
UNDO
show parameter undo
数据库可能有多个undo表空间,但是当前只有一个在使用
#———————————————————————————
alter system set undo_retention=xxxxx scope=both;(3600的倍数/1h)
或者扩展undo表空间
#———————————————————————————
v$transaction
#———————————————————————————
查看undo段
select owner,tablespace_name,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name in ('UNDOTBS1','UNDOTBS2');
#———————————————————————————
查看undo的alive状态
SELECT DISTINCT tablespace_name,STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS,tablespace_name;
#———————————————————————————
查看undo表空间--自动扩展/保留
select b.tablespace_name, AUTOEXTENSIBLE, RETENTION
from dba_tablespaces a, dba_data_files b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME
and b.TABLESPACE_NAME in ('UNDOTBS1','UNDOTBS2');
#———————————————————————————
查看当前undo占用高的sql_id
select *
from (select maxqueryid, round(sum(undoblks) * 8 / 1024) consumed_size_MB
from v$undostat
group by maxqueryid
order by consumed_size_MB desc)
where rownum < 10;
#———————————————————————————
undo 表空间使用率
select round(((select round(sum(bytes) / 1024 / 1024 / 1024, 2)
from dba_undo_extents
where tablespace_name = 'UNDOTBS1'
and status = 'EXPIRED'
group by tablespace_name) +
nvl((select round(sum(bytes) / 1024 / 1024 / 1024, 2)
from dba_free_space
where tablespace_name = 'UNDOTBS1'
group by tablespace_name),
0)) * 100 /
(select round(sum(bytes) / 1024 / 1024 / 1024, 2)
from dba_data_files
where tablespace_name = 'UNDOTBS1'
group by tablespace_name),
2) as "UNDOTBS AVAILABLE %"
from dual;
#———————————————————————————
undo 各表空间使用率
comp sum of "TBS_NAME" "TOTAL(G)" "USED(G)" "FREE(M)" on report\r
break on report \r
SELECT upper(d.tablespace_name) "TBS_NAME",\r
d.Tot_grootte_Gb "TOTAL(G)",\r
d.Tot_grootte_Gb - f.total_bytes "USED(G)",\r
to_char((d.Tot_grootte_Gb -f.total_bytes)/decode(d.Tot_grootte_Gb,0,1,d.Tot_grootte_Gb)*100,'990.99') "PERT(%)",\r
f.total_bytes "FREE(G)",\r
f.max_bytes "MAX_EXTEND(G)" \r
FROM (SELECT tablespace_name,\r
round(SUM(bytes)/(1024*1024*1024),2) total_bytes,\r
round(MAX(bytes)/(1024*1024*1024),2) max_bytes\r
FROM sys.dba_free_space \r
GROUP BY tablespace_name) f,\r
(SELECT dd.tablespace_name,\r
round(nvl(SUM(dd.bytes)/(1024*1024*1024),0),2) Tot_grootte_Gb \r
FROM sys.dba_data_files dd \r
GROUP BY dd.tablespace_name) d \r
WHERE d.tablespace_name = f.tablespace_name\r
and d.tablespace_name like 'UNDO%' \r
ORDER BY 4 desc,1;\r

————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
RMAN/归档
#———————————————————————————
查看数据库模式:
select name,log_mode from v$database;
#———————————————————————————
查看归档:(sysdba)
archive log list;
#———————————————————————————
查看用了多少归档百分比,如果超过90%随时有宕机的危险
select * from v$flash_recovery_area_usage;
#———————————————————————————
5、查看归档相关信息:
show parameter db_recovery
db_recovery_file_dest查看归档到的路径
db_recovery_file_dest_size大小
#———————————————————————————
切换日志,（老的日志会归档）
alter system switch logfile;
#———————————————————————————
指定的归档位置1_10
show parameter log_archive_dest
#———————————————————————————
查oracle查有哪些盘:（grid用户 sqlplus / as sysasm）
set linesize 200 pagesize 200
col path for a30
col name for a20
col failgroup for a20
select header_status,group_number,disk_number,total_mb,FREE_MB,name,failgroup,path from v$asm_disk order by 2,3;
#———————————————————————————
查看磁盘空间占用1
col name for a15
col rate for a15
select group_number,
name,
state,
type,
total_mb,
total_mb - free_mb used_mb,
free_mb,
USABLE_FILE_MB,
round((total_mb - free_mb) / total_mb * 100, 2) || '%' rate
from v$asm_diskgroup
order by total_mb desc;
查看磁盘空间占用2
col usedpct for a15
select name,
state,
total_mb,
free_mb,
round((1 - USABLE_FILE_MB / total_mb) * 100, 0) usedpct
from v$asm_diskgroup;
#———————————————————————————
清归档
rman target /
进入备份环境
#———————————————————————————
list archivelog all;                                        --查看（可以看路径）
crosscheck archivelog all;                                   --校验
list expired archivelog all;                               --查看过期的
delete expired archivelog all;                               --删除所有过期的
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';           --删除1天前的
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1/6';       --删除1/6天前的
#———————————————————————————
写在crontab 里
delete noprompt force archivelog until time 'sysdate-1';
#———————————————————————————
切换日志,（老的日志会归档）
alter system switch logfile;
#———————————————————————————
集群rac开归档:
1、单节点关闭集群状态
alter system set cluster_database=false scope=spfile sid='gkl1';
2、双节点关闭数据库
shutdown immediate;
3、单节点到mount状态(控制文件已经打开但是用户信息不能查看)
startup mount
4、单节点将指定归档日志放到某个路径下(需要切换日志后生效)
alter system set log_archive_dest_1='location=/xxxxxx'
5、单节点打开归档
alter database archivelog;（noarchivelog）
6、单节点切回集群状态
alter system set cluster_database=true scope=spfile sid='gkl1';
7、双节点打开数据库
shutdown immediate;
alter database open;
#———————————————————————————
asmcmd
去grid用户开asmcmd查看asm卷组情况
查看磁盘组空间使用情况
lsdg
↓等待补全:
查看详细信息
ls -l
查看文件大小信息
ls -s
查看文件夹的详细信息
ls -ld
按时间,详情信息排序
ls -lt
查看权限
ls --permission
#———————————————————————————
redolog相关:
--查看redo
select * from v$log;
--查看单个大小
select l.GROUP#,l.THREAD#,l.BYTES/1024/1024 M,l.MEMBERS,l.STATUS,f.MEMBER from v$log l,v$logfile f where l.GROUP#=f.GROUP# order by 1;
#———————————————————————————
单日双节点redo,可以将THREAD#去掉按总共算
--查看日期时间对应大小
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",THREAD#,
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23",
round(count(1) / 24, 2) "Avg"
FROM V$log_history
group by trunc(first_time), to_char(first_time, 'Dy'),THREAD#
Order by 1;
--精简
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
round(count(1) / 24, 2) "Avg"
FROM V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;
#———————————————————————————
每日归档产生量
select LPAD(to_char(first_time, 'yyyymmdd hh:mi:ss'), 18) "日期",
trunc(sum(blocks * block_size) / 1024 / 1024) "Size(M)",
count(*)
from v$archived_log
where first_time > sysdate - 7
group by first_time
order by 1 desc;
#———————————————————————————
查看某个时间段是哪个object产生redo多
col object_name for a40
SELECT to_char(begin_interval_time, 'YY-MM-DD HH24') snap_time,
dhso.object_name,
dhso.owner,
sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN
to_date('23-01-18 04:30', 'YY-MM-DD HH24:MI') AND
to_date('23-01-18 05:30', 'YY-MM-DD HH24:MI')
GROUP BY to_char(begin_interval_time, 'YY-MM-DD HH24'),
dhso.object_name,
dhso.owner
HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc;
具体sql--我并没有执行出来
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24') WHEN,
dbms_lob.substr(sql_text, 4000, 1) SQL,
dhss.instance_number INST_ID,
dhss.sql_id,
executions_delta exec_delta,
rows_processed_delta rows_proc_delta
FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%HISTGRM$%' --名称
AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'
AND dhss.snap_id = dhs.snap_id
AND dhss.instance_number = dhs.instance_number
AND dhss.sql_id = dhst.sql_id
AND begin_interval_time BETWEEN
to_date('23-01-18 04:30', 'YY-MM-DD HH24:MI') --日期
AND to_date('23-01-18 05:30', 'YY-MM-DD HH24:MI');
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
数据泵
expdp parfile=expdp.par
#———————————————————————————
expdp \'/ as sysdba\'
directory=
schemas=
dumpfile=xx_2022xxxx_%U.dmp
logfile=
parallel=4
compression=ALL
(
EXCLUDE=对象类型.对象名称(排除这些)
include=table/table,table/table_data,xxxxx
tables=xx.xx
cluster=n --参数是排除集群备份（只在本地导出）
compression=all --压缩参数
flashback_scn=xx --加上scn导出
)
#———————————————————————————
查scn号:
set num 50
select current_scn from v$database ;
#———————————————————————————
nohup impdp \'/ as sysdba\'
DUMPFILE=
DIRECTORY=
logfile=
remap_schema=
remap_tablespace=
parallel=4
transform=segment_attributes:n   --指定想要的表空间
table_exists_action=replace &
(
include=table/table,table/table_data,xxxxx
tables=xx.xx
cluster=n --参数是排除集群备份
(成对使用
remap_table=
remap_schema=)
exclude=PROCACT_INSTANCE (报错↓,加这个参数ORA-39126、ORA-31604)
)
#———————————————————————————
数据泵杀
select * from dba_datapump_jobs;
#———————————————————————————
--观察state、jobname、OPERATION
①select job_name,state from dba_datapump_jobs;
②SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50
SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;
可以看到ctrl+C终止的任务运行状态仍为EXECUTING
#———————————————————————————
expdp enmo_jy/ocm_1990 attach=SYS_EXPORT_TABLE_02
Export> stop_job=immediate
yes
#———————————————————————————
drop table SYS_EXPORT_TABLE_01 purge;
#———————————————————————————
最下下策
ps -ef |grep ora_dm
#———————————————————————————
(
EXCLUDE=SEQUENCE,VIEW --过滤所有的SEQUENCE,VIEW
EXCLUDE=TABLE:"IN ('EMP','DEPT')" --过滤表对象EMP,DEPT
EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT
EXCLUDE=INDEX:"= 'INDX_NAME'" --过滤指定的索引对象INDX_NAME
INCLUDE=PROCEDURE:"LIKE 'PROC_U%'" --包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符)
INCLUDE=TABLE:"> 'E' " --包含大于字符E的所有表对象
)
#———————————————————————————
未正常删除的dump外部表
select owner, table_name, default_directory_name, access_type
from dba_external_tables
order by 1, 2;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
表压缩/高水位
#———————————————————————————
select owner,
table_name,
round((blocks * 8), 2) / 1024 "GSW m",
round((num_rows * avg_row_len / 1024), 2) / 1024 "ZHENSHI m",
round((blocks * 10 / 100) * 8, 2) / 1024 "YULIU m",
round((blocks * 8 - (num_rows * avg_row_len / 1024) -
blocks * 8 * 10 / 100),
2) / 1024 "LANGFEI m",
to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
from dba_tables
where TABLE_NAME in
and owner = 'ONEDRYV8';
--AND temporary = 'N'
#———————————————————————————
--大小
select table_name,num_rows,blocks,empty_blocks from dba_tables where table_name='ADDR_SEGM' and owner ='ZSC_HAINAN';
blocks
231767
--开启行移动
alter table ZSC_HAINAN.ADDR_SEGM enable row movement;
--收缩
alter table ZSC_HAINAN.ADDR_SEGM shrink space
--关闭行移动
alter table ZSC_HAINAN.ADDR_SEGM disable row movement;
#———————————————————————————
move方式必须停业务跑
alter table PUB_LOG_CIRCUIT enable row movement;
alter table RES_JIANGSU.xx move partition SYS_P101;
alter table RES_JIANGSU.xx move partition SYS_P102;
alter table RES_JIANGSU.xx move partition SYS_P103;
alter table RES_JIANGSU.xx move partition SYS_P104;
alter table PUB_LOG_CIRCUIT disable row movement;
然后重建索引
#———————————————————————————
无分区
select owner,index_name,status from dba_indexes where status='UNUSABLE' ;
#———————————————————————————
拼写索引脚本
select 'alter index '||owner||'.'||index_name||' rebuild parallel 20;' from dba_indexes where status='UNUSABLE' ;
#———————————————————————————
有分区
select index_owner,index_name,partition_name,status from dba_ind_partitions where status='UNUSABLE' ;
#———————————————————————————
拼写索引脚本
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' parallel 20;' from dba_ind_partitions where status='UNUSABLE' ;
#———————————————————————————
有子分区
拼写脚本
select 'alter index '||index_owner||'.'||index_name||' rebuild SUBpartition '||SUBpartition_name||' ;' from DBA_IND_SUBPARTITIONS where status='UNUSABLE' ;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
表分析
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',method_opt=>'for all indexed columns size 254',granularity=>'ALL',cascade=>TRUE);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P101',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P102',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P103',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P104',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
select owner,table_name,partition_name from dba_tab_statistics where owner in () and table_name in () and partition_name is not null;
#———————————————————————————
索引:
begin dbms_stats.gather_index_stats(ownname => 'TEST',indname => 'IDX_STUDENT_BIRTH',degree => 4);end;
#———————————————————————————
(有时候中文需要改掉)
非分区表
select a.owner,
a.table_name, --表名
round(a.num_rows * a.avg_row_len / 1024 / 1024 / 1024, 2) 数据大小_G,
round(a.blocks * 8 / 1024 / 1024, 2) 占用块大小_G,
round(s.bytes / 1024 / 1024 / 1024, 2) 占用段大小_G,
to_char(a.num_rows * a.avg_row_len /(decode(a.blocks, 0, 1, a.blocks) * 8 * 1024) * 100,'90.99') || '%' 空间利用率,
a.partitioned,
a.last_analyzed
from dba_tables a, dba_segments s
where a.table_name = s.segment_name
and a.owner = s.owner
and a.blocks > 6400 --大于50m的表
and partitioned = 'NO'
and a.owner not in ('SYS', 'SYSTEM', 'SYSMAN')
and a.num_rows * a.avg_row_len /
(decode(a.blocks, 0, 1, a.blocks) * 8 * 1024) * 100 <= 80 --小于80%空间占比表
order by 空间利用率;
#———————————————————————————
分区表
select a.owner,
a.table_name,
a.partition_name,
round(a.num_rows * a.avg_row_len / 1024 / 1024 / 1024, 2) 数据大小_G,
round(a.blocks * 8 / 1024 / 1024, 2) 占用块大小_G,
round(s.bytes / 1024 / 1024 / 1024, 2) 占用段大小_G,
to_char(a.num_rows * a.avg_row_len /(decode(a.blocks, 0, 1, a.blocks) * 8 * 1024) * 100,'90.99') || '%' 空间利用率,
a.last_analyzed
from dba_tab_statistics a, dba_segments s
where a.table_name = s.segment_name
and a.owner = s.owner
and a.partition_name = s.partition_name
and a.partition_name is not null
and a.blocks > 6400 --大于50m的分区表
and a.owner not in ('SYS', 'SYSTEM', 'SYSMAN')
and a.num_rows * a.avg_row_len /
(decode(a.blocks, 0, 1, a.blocks) * 8 * 1024) * 100 <= 80
order by 空间利用率;
#———————————————————————————
--统计信息是否过时的,大于xx G的表
select 'exec dbms_stats.gather_table_stats(''' || s.owner || ''',''' ||
s.segment_name ||
''',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>''ALL'',cascade=>true);',
s.owner,
s.segment_name,
round(sum(bytes) / 1024 / 1024 / 1024, 1) bytes_G
from dba_segments s,
(select distinct owner, table_name
from dba_tab_statistics a
where a.stale_stats = 'YES'                                               --统计信息是否过时
or a.last_analyzed is null) t
where s.owner = t.owner
and s.segment_name = t.table_name
and s.owneR IN ('RES_JIANGSU','RES_GUANGDONG')
AND T.OWNER IN ('RES_JIANGSU','RES_GUANGDONG')
group by s.owner, s.segment_name
having sum(bytes) / 1024 / 1024 / 1024 > 3
order by 4;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
手动执行一个快照:
exec dbms_workload_repository.create_snapshot;
#———————————————————————————
Sql>select * from dba_hist_wr_control;
SNAP_INTERVAL=+00000 01:00:00.0 表示采样间隔是1小时
RETENTION=+00008 00:00:00.0 表示采样数据保留期限是8天
#———————————————————————————
AWR实用
select INSTANCE_NUMBER,
SNAP_ID,
to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi') end_time,
round(REDO_SIZE / 3600 / 1024 / 1024) REDO_SIZE_M,
round(LOGICAL_READS / 3600 * 8 / 1024) LOGICAL_READS_M,
round(PHYSICAL_READS / 3600 * 8 / 1024) PHYSICAL_READS_M,
round(physical_writes / 3600 * 8 / 1024) physical_writes_M,
round(read_IO_request / 3600) read_IO_request
from (select s.instance_number,
s.snap_id,
s.stat_name,
st.begin_interval_time,
st.end_interval_time,
value - lag(value) over(partition by s.stat_name order by s.snap_id) value
from dba_hist_sysstat s, dba_hist_snapshot st
where stat_name in
('redo size', 'session logical reads', 'physical reads',
'physical writes', 'physical read IO requests')
and s.instance_number = st.instance_number
and s.instance_number = 1
and s.snap_id = st.snap_id) pivot(sum(value) for stat_name in ('redo size' as redo_size, 'session logical reads' as logical_reads, 'physical reads' as physical_reads, 'physical writes' as physical_writes, 'physical read IO requests' as read_IO_request))
order by snap_id;
#———————————————————————————
2、ASH关键
在执行SQL>
@?/rdbms/admin/ashrpt.sql
命令后,会让有如下参数需要手动填写：
（1）日志报告类型
Enter value for report_type: text
— 选择生成的ASH 报告类型,是text 还是html
（2）日志报告起始时间
Enter value for begin_time: 06/18/22 09:00:00
— 输入ASH 开始的时间,时间格式上面的示例有说明,比如我这里是xxxx年6月18日 9:00:00
（3）日志报告结束时间
Enter value for duration:120
(2h)
— 输入ASH 结束时间,默认是SYSDATE - begin_time,一般输入的分析统计的总时间,一般默认是秒,比如这里7200就是2个小时,拿出2个小时的ash分析日志来
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
游标
创建:
cursor cursor_namexxx is xxxxsql;
打开:
open cursor_namexxx;
关闭:
close cursor_namexxx;
例如:
Declare
   xx1 number(10);
   xx2 varchar2(20);
   cursor cursor_namexxx is xxxxsql;
Begin
   open cursor_namexxx;
       loop
           FETCH cursor_namexxx into yy1,yy2(sql中的字段);
           exit when cursor_namexxx%notfound;
           dbms_output.put_line(xx1||' '||xx2);
           --每一个yy1,yy2就往下走一次,直到没有yy1,就exit
       end loop;
   close cursor_namexxx;
end;
#———————————————————————————
修改oracle实例的session游标数
SQL> alter system set open_cursors=500 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
Oracle清理数据库以各种日志文件
1. （optional）可以创建备份目录，提前备份需要清理日志
mkdir /log_bak_date
2. 数据库各项日志文件路径
sqlplus / as sysdba
SYS@honor1 > show parameter dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/honor/honor1/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/honor/honor1/cdump
user_dump_dest string /u01/app/oracle/diag/rdbms/honor/honor1/trace
11gR1之后引入全新的诊断信息架构：
SYS@honor1 > show parameter diag;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
SYS@honor1 > show parameter audit_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/honor/adump
audit_file_dest /* 路径下存放的是数据库审计信息文件,如果未设置audit_trail为OS，则默认此目录只存放SYS登陆审计信息。
background_dump_dest /* 存放数据库后台进程调试信息，与记录影响实例、数据库的trace文件和alert日志文件，11gR1之后，缺省忽略此参数设置，根据diagnostic_dest存放
diagnostic_dest /* 11gR1之后，引入，后台进程与alert告警日志会根据ADR base目录存放
user_dump_dest /* 存放用户进程调试trace文件，11gR1之后，引入新的诊断架构这个参数将被忽略，由diagnostic_dest控制trace文件生成目录
core_dump_dest /* 存放Oracle核心转储文件
#———————————————————————————
rac日志路径
oracle 下的alert日志$ORACLE_BASE/diag/rdbms/orcl/orcl1/trace/alertorcl1.log
grid 下的 alert 日志 $ORACLE_HOME/log/rac1/alertrac1.log
css的日志$ORACLE_HOME/log/rac1/cssd/cssd.log ocssd.log
crs的日志$ORACLE_HOME/log/rac1/crsd/crsd.log
ohas的日志$ORACLE_HOME/log/rac1/ohasd/ohasd.log
evm的日志$ORACLE_HOME/log/rac1/evmd/evmd.log
#———————————————————————————
3. 数据库审计信息文件清理
find /u01/app/oracle/admin/honor/adump -mtime +30 -name \*.aud
/* 可选择如下命令备份审计文件：
find /u01/app/oracle/admin/honor/adump -mtime +30 -name \*.aud -exec mv {} /log_bak_date \;
/* 查看审计目录下30天前的审计文件
find audit_file_dest -mtime +30 -name \*.aud
/* 确保审计日志无需保留，可选择删除
cd /log_bak_date
rm -rf *.aud
/* 数据库中审计一般可以选择直接truncate掉aud$
#———————————————————————————
4. 数据库trace文件
/* 清理trace文件
进入background_dump_dest路径下，将30天之前的文件移到准备删除的文件夹，执行命令如下：
find /opt/oracle/diag/rdbms/orcl/czh19c/trace -mtime +30 -name \*.tr*
/* （可选）可选择将文件备份
$ find /opt/oracle/diag/rdbms/orcl/czh19c/trace -mtime +30 -name \*.tr* -exec mv {} /log_bak_date/background_dump_dest \;
/* 确保日志无用处之后，手工执行删除命令
cd /log_bak_date/background_dump_dest
rm -rf *.tr*
#———————————————————————————
5.清理数据库alert日志
(慎重)
/* alert有两种办法可以清理
/* /* cp备份
cd /u01/app/oracle/diag/rdbms/honor/honor1/trace
cp alert_$ORACLE_SID.log /log_bak_date/alert_$ORACLE_SID.log.bak （确保空间足够）
/* 清理办法
方法1.清空
cat /dev/null > alert_sid.log
方法2.截断
tail -n 1000 alert_sid.log > alert_sid.log
方法3.使用echo
echo "" > alert_sid.log
#———————————————————————————
6. 数据库sql trace文件清理
数据库默认user_dump_dest和background_dump_dest路径一致，如不一致，参照4中清理trace文件步骤清理
#———————————————————————————
7. 数据库监听日志清理
/* Oracle 11g的监听日志和告警日志分别在，RAC日志在grid用户的$ORACLE_BASE下：
$ORACLE_BASE/diag/tnslsnr/hostname/listener/trace（文本监听日志）
$ORACLE_BASE/diag/tnslsnr/hostname/listener/alert（xml监听日志）
/* 删除监听日志的方法如下：
/* 切换到oracle用户（如果为RAC，切换到grid用户）
$ su oracle
$ lsnrctl
LSNRCTL> set log_status off
$ mv listener.log listener.log.bak /*（备份一下监听文件或者删除监听日志文件）
$ mv listener.xml listener.xml.bak
LSNRCTL> set log_status on /*（这时候会自动创建一个listener.log文件）
/* 11g可以使用ADRCI工具查看告警日志
/* 要看警告日志可以在oracle用户下执行adrci
$ adrci
adrci> show alert /* 可根据提示查看数据库或者监听日志内容。
$ adrci
adrci> SHOW TRACEFILE /* 可以看到所有的trace以及alert日志文件
/* 使用adrci清理trace文件
$ adrci
adrci> show home
adrci> set homepath diag/rdbms/honor/honor1
adrci> help purge /* 可根据清理多少分钟前的数据，也可以show problem查看日志中错误信息
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
SGA/PGA
oracle SGA PGA 内存大小调整
个人归纳总结：SGA 占用总数据库实例内存的30%左右,PGA占用到70%左右。
alter system set sga_max_size=XXXM from spfile;
alter system set sga_target=XXXM from spfile;
alter system set pga_aggregate_target=XXXM from spfile;
重启数据库。
在11GR2中修改了sga_max_size,从8G到2G没有生效。设置好PGA和SGA target参数后,oracle自动计算了sga_max_size为4G,不能低于4G
#———————————————————————————
修改oracle实例可用内存大小：
SQL> alter system set memory_target=4340763136 scope=spfile;
#Oracle实例可用内存增大为4G，注意：最大不能超过物理内存的80%；
SQL> shutdown immediate;
SQL> startup;
#———————————————————————————
MEMORY_TARGET参数:
(1)该参数指定Oracle实例可用内存大小;
(2)取值范围:152 MB to MEMORY_MAX_TARGET;
(3)Oracle实例在这个值的范围根据自身负载自动调节SGA和PGA的小;
(4)如果在参数文件中忽略了MEMORY_MAX_TARGET,则该参数的大小为MEMORY_TARGET的大小;
(5)如果忽略了MEMORY_TARGET,而设置了MEMORY_MAX_TARGET大小,则MEMORY_TARGET值为0,
为0表示禁用自动内存管理;
(6)实例启动后可动态设置MEMORY_TARGET的大小,但不可超过MEMORY_MAX_TARGET的大小;
(7)SGA_MAX_SIZE 的默认值依靠于MEMORY_TARGET 和MEMORY_MAX_TARGET的大小;
#———————————————————————————
SGA_TARGET:
(1)是用于存储数据库信息的内存区,该信息为数据库进程所共享。它包含Oracle服务器的数据和控制信息,它是在
Oracle服务器所驻留的计算机的实际内存中得以分配,如果实际内存不够再往虚拟内存中写;
(2)SGA指定所有SGA组件的总大小,将该值设为正数则表示启用自动共享内存管理特性,下列内存池会自动调节大小:
Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
Streams pool (STREAMS_POOL_SIZE)
(3)当启用自动内存管理(MEMORY_TARGET设置成正数),SGA_TARGET为最小的SGA大小;
3、PGA_AGGREGATE_TARGET:
(1)包含单个服务器进程或单个后台进程的数据和控制信息,与几个进程共享的SGA正相反,PGA是只被一个
进程使用的区域,PGA在创建进程时分配,在终止进程时回收,将该参数设成非零值启用自动PGA管理;
(2)将该参数设成非零值启用自动PGA管理,同时 WORKAREA_SIZE_POLICY该参数会设为auto,将该参数
设成零时,WORKAREA_SIZE_POLICY参数会自动设成MANUAL
(3)当启用自动内存管理(MEMORY_TARGET )时该值为最小PGA值;
4、Oracle官方推荐的MEMORY_TARGET、PGA、SGA的大小:
(1)ORACLE实例分配的物理内存的大小:
MEMORY_TARGET=物理内存 x 80%
MEMORY_MAX_SIZE=物理内存 x 80%
(2)对于OLTP系统:
SGA_TARGET=(物理内存 x 80%) x 80%
SGA_MAX_SIZE=(物理内存 x 80%) x 80%
PGA_AGGREGATE_TARGET=(物理内存 x 80%) x 20%
(3)对于DSS系统:
SGA_TARGET=(物理内存 x 80%) x 50%
SGA_MAX_SIZE=(物理内存 x 80%) x 50%
PGA_AGGREGATE_TARGET=(物理内存 x 80%) x 50%
(4)pga和sga的总和是oracle使用计算机内存的实际大小;
SQL> show parameter sga;
SQL> show parameter pga;
#———————————————————————————
SGA
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) from v$sga) total,
(select sum(bytes/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 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual)
union
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
(select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
union
select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
(select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
(select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pctused desc;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
密码复杂度
查看当前开启用户及其profile
select username,profile from dba_users where account_status='OPEN';
#———————————————————————————
设置密码复杂度
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlpwdmg.sql
#———————————————————————————
取消密码复杂度
alter profile default limit password_verify_function null;
#———————————————————————————
密码过期
alter profile default limit password_life_time 30;
#———————————————————————————
取消密码过期
alter profile default limit password_life_time unlimited;
#———————————————————————————
创建具有密码复杂度的Profile;
create profile XXXX limit
SESSIONS_PER_USER unlimited            --每个用户拥有的会话数不限。
PASSWORD_REUSE_TIME 60             --密码重用次数。
FAILED_LOGIN_ATTEMPTS 10             --失败可尝试登录次数10次。
PASSWORD_LIFE_TIME 180            --密码180天过期。
PASSWORD_GRACE_TIME 7            --最多可以延迟7天必须修改密码。
PASSWORD_REUSE_MAX 1            --密码只能重用一次。
PASSWORD_LOCK_TIME 1/48             --锁定时间30分钟。
IDLE_TIME 4320             --空闲时间。
PASSWORD_VERIFY_FUNCTION verify_function_11G;    --指定密码复杂度函数：最少8位,包含大写,小写,数字,特殊符号
(例如:create profile XXXX limit failed_login_attempts 3 password_lock_time 2;)
#———————————————————————————
将某个用户xsq加入到这个密码策略中,此用户将使用该策略。
alter user xx profile XXXX;
create user xx identified by Oracle#123 default tablespace xx temporary tablespace temp profile XXXX;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
ORA常用sql:
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
--杀3分钟的语句
select t.MACHINE,
t.USERNAME,
T.SQL_EXEC_START,
--to_char(substr(B.SQL_FULLTEXT, 1, 3000)) as sqlline_1_3000,
--to_char(substr(B.SQL_FULLTEXT, 3001, 6000)) as sqlline_3001_6000,
--to_char(substr(B.SQL_FULLTEXT, 6001, 9000)) as sqlline_6001_9000,
'alter system kill session ''' || t.sid || ',' || t.serial# ||
''' immediate;' as kill_session_cmd,
'1' as deal_state,
sysdate as check_time
from v$session t
LEFT JOIN V$SQLAREA B ON B.SQL_ID = T.SQL_ID
where t.status = 'ACTIVE'
and t.USERNAME = 'RESFLOW_TWO'
AND t.SQL_ID in (select sql_id
from v$session a
where sysdate - a.SQL_EXEC_START > 3 / 1440
and a.STATUS = 'ACTIVE')
AND (UPPER(B.SQL_TEXT) LIKE '%SELECT%') or (UPPER(B.SQL_TEXT) LIKE '%WITH%');
————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看数据库负荷,暂不确定用途
SELECT *
FROM (SELECT A.INSTANCE_NUMBER,
A.SNAP_ID,
B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
B.END_INTERVAL_TIME + 0 END_TIME,
ROUND(VALUE - LAG(VALUE, 1, '0')
OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
FROM (SELECT B.SNAP_ID,
INSTANCE_NUMBER,
SUM(VALUE) / 1000000 / 60 VALUE
FROM DBA_HIST_SYS_TIME_MODEL B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND UPPER(B.STAT_NAME) IN UPPER(('DB TIME'))
GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
ORDER BY BEGIN_TIME;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
某条sqlid
select a.sql_text ,
b.etime , --执行耗时
c.user_id ,
c.SAMPLE_TIME , --执行耗时
c.INSTANCE_NUMBER , --实例数勒
u.username ,
a.sql_id
from dba_hist_sqltext a,
(select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime
from dba_hist_sqlstat
where ELAPSED_TIME_DELTA / 1000000 >= 1) b,
dba_hist_active_sess_history c,
dba_users u
where a.sql_id = b.sql_id
and a.sql_id='5u5bk5azah7sg'
and c.user_id = u.user_id
and b.sql_id = c.sql_id
--and a.sql_text like '%IN%'
order by SAMPLE_TIME desc, b.etime desc;
#———————————————————————————
改1:
select a.sql_id, a.user_id, b.username,a.machine, max(a.sql_exec_start)
from dba_hist_active_sess_history a, dba_users b
where a.sql_id = '5u5bk5azah7sg'
and a.user_id = b.user_id
group by a.sql_id, a.user_id, a.machine,b.username;
#———————————————————————————
改2:
select b.sql_id, b.user_id, c.username,a.LAST_ACTIVE_TIME,a.ELAPSED_TIME, b.machine, max(b.sql_exec_start)
from V$SQLSTATS a, dba_hist_active_sess_history b, dba_users c
where a.SQL_ID = b.sql_id
and a.sql_id='18ck8bj5tp3rm'
and b.USER_ID = c.user_id
group by b.sql_id, b.user_id, c.username,a.LAST_ACTIVE_TIME,a.ELAPSED_TIME,b.machine
————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看回滚
select usn,
state,
undoblocksdone,
undoblockstotal,
cputime,
pid,
xid,
rcvservers
from v$fast_start_transactions;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
阻塞
set linesize 1200
COL USERNAME FOR A15
COL EVENT FOR A15
COL SID FOR 999999
COL INST_ID FOR 99
select inst_id,sid, username, event, blocking_session,
seconds_in_wait, wait_time
from gv$session where state in ('WAITING')
and wait_class != 'Idle';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
等待事件
set lines 200 pages 9999
col wait_class for a30
col event for a45
select inst_id,event,wait_class,count(*) from gv$session_wait where wait_class <> 'Idle' group by inst_id,event,wait_class order by 4 desc;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
当前正在执行的sql
SELECT b.sid,
b.username,
b.serial#,
b.sql_id,
b.EVENT,
b.LOGON_TIME,
spid,
b.SQL_EXEC_START,
lockwait,
paddr,
sql_text,
b.machine
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value
and c.sql_id in ('5kjp7xjmspjyx', '5xn0h4h0zmmmn');
--and c.sql_id = upper('&locked_object');
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
检查消耗 CPU 最高的进程
SELECT P.PID PID,
S.SID SID,
P.SPID SPID,
S.USERNAME USERNAME,
S.OSUSER OSNAME,
P.SERIAL# ,
P.TERMINAL,
P.PROGRAM PROGRAM,
P.BACKGROUND,
S.STATUS,
RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM
FROM V$PROCESS P, V$SESSION S, V$SQLAREA A
WHERE P.ADDR = S.PADDR
AND S.SQL_ADDRESS = A.ADDRESS(+)
AND P.SERIAL# <> '1';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
资源占用情况--待确认
select a.username,
b.block_gets,
b.consistent_gets,
b.physical_reads,
b.block_changes,
b.consistent_changes
from v$session a, v$sess_io b
where a.sid = b.sid
and username is not null
order by a.username;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
锁表1
set linesize 300 pagesize 5000
col spid for a8
col object_name for a23
col username for a15
col program for a30
col sql_id for a18
select s.inst_id,
o.object_name,
l.locked_mode LMODE,
p.spid,
s.sid,
s.serial#,
s.blocking_session b_sid,
s.username,
s.program,
s.status,
nvl(s.sql_id, s.prev_sql_id) sql_id
from gv$locked_object l, dba_objects o, gv$session s, gv$process p
where l.inst_id = s.inst_id
and s.inst_id = p.inst_id
and l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and o.object_name = upper('&locked_object')
order by s.SQL_ID, s.inst_id;
#———————————————————————————
锁表2
col object_name for a23 \r
col username for a15 \r
col owner for a15 \r
col sql_id for a18 \r
select c.username, \r
c.sid, \r
c.serial#, \r
logon_time, \r
b.owner, \r
b.object_name, \r
a.session_id, \r
a.locked_mode, \r
c.SQL_ID, \r
c.MACHINE, \r
c.CLIENT_INFO \r
from v$locked_object a, dba_objects b, v$session c \r
where b.object_id = a.object_id \r
and a.session_id = c.sid \r
order by c.logon_time; \r
#———————————————————————————
某表的查询记录--可以like内容改为别的
select *
from v$sqlarea t
where t.SQL_TEXT like '%SELECT%FROM BS_BUSSINESS_UPDATE%'
order by t.LAST_ACTIVE_TIME desc;
#———————————————————————————
某用户某表的操作记录--未查出
select *
from v$sqlarea t
where t.PARSING_SCHEMA_NAME in ('TFH_SFU')
AND SQL_TEXT like '%BS_BUSSINESS_UPDATE%'
AND LAST_ACTIVE_TIME >= to_date('2023-01-17', 'yyyy-mm-dd')
order by t.LAST_ACTIVE_TIME desc;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看回收站大小
select sum(mb) from (
select * from (
select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,b.bytes/1024/1024 MB
from dba_recyclebin a,dba_segments b where a.object_name=b.segment_name
order by MB desc) where rownum < 11);
#———————————————————————————
启动回收站
ALTER SYSTEM SET recyclebin = ON;
#———————————————————————————
清理回收站
purge dba_recyclebin;
#———————————————————————————
查看回收站
SELECT * FROM DBA_RECYCLEBIN;
#———————————————————————————
恢复某信息
FLASHBACK TABLE <<Dropped_Table_Name>> TO BEFORE DROP RENAME TO <<New_Table_Name>>;
#———————————————————————————
清空某表
PURGE TABLE <<Table_NAME>>;
#———————————————————————————
清空某索引
PURGE INDEX <<Index_NAME>>;
#———————————————————————————
清空某表空间
PURGE TABLESPACE<<Table_NAME>>;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看实例信息
set pages 9999
COL PRODUCT FORMAT A40
COL VERSION FORMAT A15
COL STATUS FORMAT A20
SELECT * FROM PRODUCT_COMPONENT_VERSION;
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
column host_name format a20 tru spool / tmp / inst_status.log append
select sysdate from dual;
select inst_id, instance_name, host_name, version, status, startup_time
from gv$instance
order by inst_id;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
批量干掉sql_id
set linesize 600 pagesize 200
col username for a15
col machine for a15
col CLIENT_INFO for a15
col EVENT for a28
SELECT b.sid,
b.username,
b.serial#,
b.CLIENT_INFO,
b.sql_id,
b.machine,
b.EVENT,
b.SQL_EXEC_START,
   'alter system kill session '''||b.sid||','||b.serial#||''' immediate; 复制'
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value
and c.sql_id = '&locked_object';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
--建议使用绑定变量
select to_char(FORCE_MATCHING_SIGNATURE), count(1)
from gv$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 1000
order by 2 desc;
--通过FORCE_MATCHING_SIGNATURE查看sql
select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
FROM V$SQL
WHERE to_char(FORCE_MATCHING_SIGNATURE)='11167613790136244795';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
--缓冲区命中率
select round((1 - sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0))) ),4) *100 ||'%' chitrati
from v$sysstat;
--当前db_cache_size是否很大
select value/1024/1024 cache_size from v$parameter where name='db_cache_size';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
--表上次ddl时间
select owner, object_name, max(last_ddl_time)
from dba_objects
where object_name in
group by owner,object_name;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
--查看某个表空间的具体表、字段占用
col owner for a10
col segment_name for a25
col table_name for a10
col column_name for a10
col index_name for a10
select a.owner,
a.segment_name,
b.table_name,
b.column_name,
b.index_name,
sum(a.bytes) / 1024 / 1024 / 1024 g
from dba_segments a, dba_lobs b
where a.tablespace_name = 'IFM_ATTACH_FILE'
and a.segment_name = b.segment_name
group by a.owner,
a.segment_name,
b.table_name,
b.column_name,
b.index_name
having sum(bytes) / 1024 / 1024 / 1024 > 1
order by sum(bytes) / 1024 / 1024 / 1024 desc;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
--查看超过1G的表、索引等类型segment大小
col owner for a15
col segment_name for a30
col tablespace_name for a25
col SEGMENT_TYPE for a25
select owner, segment_name, tablespace_name, SEGMENT_TYPE, sum(bytes) / 1024 / 1024 m
from dba_segments
where owner in ('RESFLOW_TWO', 'ORDERS_CENTER', 'ONEDRYV8')
group by owner, segment_name, tablespace_name, SEGMENT_TYPE
having sum(bytes) / 1024 / 1024 / 1024 > 1
order by sum(bytes) / 1024 / 1024 / 1024 desc;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看各命中率历史!!!!!!!!!(可以只保留要查的词条)
select snap_id,max(end_time) end_time,
trunc(sum(case metric_name when 'Host CPU Utilization (%)' then average end),2) Host_CPU_util,
trunc(sum(case metric_name when 'Current OS Load' then average end),2) OS_LOAD,
trunc(sum(case metric_name when 'Session Count' then average end),2) sessions,
trunc(sum(case metric_name when 'Database Time Per Sec' then average end),2) Database_Time_Per_Sec,
trunc(sum(case metric_name when 'User Transaction Per Sec' then average end),2) User_Txn_Per_Sec,
trunc(sum(case metric_name when 'Executions Per Sec' then average end),2) Executions_ptx,
trunc(sum(case metric_name when 'Response Time Per Txn' then average end),2) Response_Time_ptx,
trunc(sum(case metric_name when 'Redo Generated Per Sec' then average end)/1024/1024,2) Redo_MBps,
trunc(sum(case metric_name when 'Redo Writes Per Sec' then average end),2) PhyWts_redo_IOPS,
trunc(sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end),2) PhyRead_IOPS,
trunc(sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end),2) Phywrite_IOPS,
trunc(sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end)/1024/1024,2) Phywrite_Tot_MBps,
trunc(sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end)/1024/1024,2) PhyRead_Tot_MBps,
trunc(sum(case metric_name when 'Logons Per Sec' then average end),2) Logons_PS,
trunc(sum(case metric_name when 'User Commits Per Sec' then average end),2) User_Commit_Per_Sec,
trunc(sum(case metric_name when 'User Rollbacks Per Sec' then average end),2) User_Rollback_Per_Sec,
trunc(sum(case metric_name when 'Logical Reads Per Txn' then average end),2) Logical_Rds_PTxn,
trunc(sum(case metric_name when 'Hard Parse Count Per Sec' then average end),2) HardParse_Per_Sec,
trunc(sum(case metric_name when 'Network Traffic Volume Per Sec' then average end/1024/1024),2) Network_Mbps,
trunc(sum(case metric_name when 'Enqueue Waits Per Sec' then average end),2) Enq_wait_Per_Sec,
trunc(sum(case metric_name when 'DB Block Changes Per Sec' then average end),2) BLOCK_CHG_Per_Sec,
trunc(sum(case metric_name when 'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits_ps,
trunc(sum(case metric_name when 'Process Limit %' then average end),2) Process_Limit,
trunc(sum(case metric_name when 'Session Limit %' then average end),2) Session_Limit,
trunc(sum(case metric_name when 'Shared Pool Free %' then average end),2) Shared_Pool_Free_PCT,
trunc(sum(case metric_name when 'Buffer Cache Hit Ratio' then average end),2) Buffer_CACHE_HIT_RAT,
trunc(sum(case metric_name when 'Memory Sorts Ratio' then average end),2) Memory_Sorts_Ratio,
trunc(sum(case metric_name when 'Redo Allocation Hit Ratio' then average end),2) Redo_Allocation_Hit_Rat,
trunc(sum(case metric_name when 'Library Cache Hit Ratio' then average end),2) Library_Cache_Hit_Rat,
trunc(sum(case metric_name when 'Soft Parse Ratio' then average end),2) Soft_Parse_Rat,
trunc(sum(case metric_name when 'Cursor Cache Hit Ratio' then average end),2) Cursor_Cache_Hit_Rat,
trunc(sum(case metric_name when 'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits
from dba_hist_sysmetric_summary
where INSTANCE_NUMBER=2
group by snap_id
order by snap_id;
精简2:
select snap_id,max(end_time) end_time,
trunc(sum(case metric_name when 'Buffer Cache Hit Ratio' then average end),2) Buffer_CACHE_HIT_RAT,
trunc(sum(case metric_name when 'Memory Sorts Ratio' then average end),2) Memory_Sorts_Ratio,
trunc(sum(case metric_name when 'Redo Allocation Hit Ratio' then average end),2) Redo_Allocation_Hit_Rat,
trunc(sum(case metric_name when 'Library Cache Hit Ratio' then average end),2) Library_Cache_Hit_Rat,
trunc(sum(case metric_name when 'Soft Parse Ratio' then average end),2) Soft_Parse_Rat
from dba_hist_sysmetric_summary
where INSTANCE_NUMBER=2
group by snap_id
order by snap_id;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
Sfile、pfile
注意:!!!!
因为:
在rac环境中,尽量不要用:create pfile from spfile
在RAC环境,SPFILE默认情况下会存放在共享存储上(裸设备或ASM磁盘组上)
RAC的每个节点都是使用PFILE指向SPFILE的方法来完成SPFILE的定位的。
即pfile文件中存放的是spfile的路径。
pfile:init实例名1.ora
cat init实例名1.ora内容:
spfile=+DATA/xxx/spfile实例名.ora
所以:
使用create pfile from spfile后,
新的pfile文件生成,替换掉旧的pfile
然后新的pfile文件内容就变成参数文件,而不是单纯的记录一条路径了。
此后数据库实例启动时读取的将不再是共享存储上的SPFILE,转而读取本地的PFILE文件的内容
解决办法:
修改pfile文件,重新指向spfile的路径即可,spfile=+DATA/xxx/spfile实例名.ora
#———————————————————————————
1、如何判断数据库使用的是:pfile还是spfile文件。
show parameter pfile;
#oracle实例使用的是pfile文件则查询不到任何的结果,如果使用的是spfile文件,则显示的都是spfile文件的路径;
#———————————————————————————
2、pfile和spfile可以互相创建
create spfile from pfile
create pfile from spfile               (约等于将spfile文件备份)
#当然在spfile和pfile的后面可以跟具体的路径
create pfile='' from spfile
create pfile='/xxx.ora' from spfile='+DATA/xxx/spfile实例名.ora';
#———————————————————————————
3、简介
Oracle中的参数文件是一个包含一系列参数以及参数对应值的操作系统文件。它们是在数据库实例启动时候加载的,
决定了数据库的物理结构、内存、数据库的限制及系统大量的默认值、数据库的各种物理属性、指定数据库控制文件名
和路径等信息,是进行数据库设计和性能调优的重要文件。可以分为两种类型:
(1)pfile：
初始化参数文件,Oracle9i之前,ORACLE一直采用pfile方式存储初始化参数,
pfile默认的名称为"init+例程名.ora"
文件路径为"/db/11.2.0.4/dbs/",
这是一个文本文件,可以用任何文本编辑工具打开;
(2)spfile：
服务器参数文件,从Oracle9i开始,Oracle引入了Spfile文件,
spfile默认的名称为"spfile+例程名.ora",
文件路径为"/db/11.2.0.4/dbs/",(可以先在库里show,gkl为:"+data/gkl")
!!!!以二进制文本形式存在,不能用vi编辑器对其中参数进行修改,
!!!!只能通过SQL命令在线修改
#———————————————————————————
3、spfile好处:
spfile改正了pfile管理混乱的问题,
在多节点的环境里,pfile会有多个image,启动时候需要跟踪最新的image。这是个烦琐的过程。
用spfile以后,所有参数改变都写到spfile里面(只要定义scope=spfile或both),参数配置有个权威的来源;
9i以前一般都是要备份pfile后再来做参数的修改,而且pfile的修改必须重启实例才能生效。非常的不方便;
在9i以后的spfile就可以同通过命令修改指定的参数了,而且有很多参数都不用重启数据库,能够在线生效,
这个在线生效的参数会随着数据库的版本增高而增加。
如果参数修改有问题数据库起不来了可以在 nomount状态下创建成pfile再修改回来即可
#———————————————————————————
4、启动优先级
startup 启动次序 spfile优先于pfile。
查找文件的顺序是
spfileSID.ora-〉spfile.ora-〉initSID.ora-〉init.ora(spfile优先于pfile)。
如果在数据库的$ORACLE_HOME/dbs/目录下既有spfile又有pfile,
使用spfile启动数据库,不需要指定参数文件路径(因为数据库会优先选择spfile启动),
如果参数文件不在$ORACLE_HOME/dbs/目录下,
无论是通过spfile或pfile启动均需要指定完整路径
#———————————————————————————
5、spfile参数的三种scope：
(1)scope=spfile:
对参数的修改记录在服务器初始化参数文件中,
修改后的参数在下次启动DB时生效,
适用于动态和静态初始化参数;
(2)scope=memory:
对参数的修改记录在內存中,
对于动态初始化参数的修改立即生效,
在重启DB后会丟失,会复原为修改前的参数值;
(3)scope=both:
对参数的修改会同时记录在服务器参数文件和內存中,
对于动态参数立即生效,
对静态参数不能用这个选项,如果使用了服务器参数文件,
则在执行alter system语句时,scope=both是default的选项
(4)如果沒有使用服务器参数文件,
而在执行alter system语句时指定scope=spfile|both都会出错
总结:修改时
参数类型           spfile                   memory                               both
静态参数           可以,重启生效           不可以                               不可以
动态参数           可以,重启生效           可以,立即生效,重启后失效           可以立即生效,重启后失效
(5)
从另一个维度分类参数:(没见过)
有statics和dynamic两种,dynamic设置后立即生效,static重启实例后生效。
dynamic参数scope可选3种,static参数scope只能选spfile。
#———————————————————————————
6、修改参数报错后回退方案,举例:(修改连接数报错后改回)
动态修改参数
alter system set parameter=Value scope=spfile|both|memory
①
SQL> show parameter processes;
#查询当前数据库实例允许的最大连接数;
②
SQL> show parameter spfile;
#判断当前所用的是spfile还是pfile文件;
③
SQL> create pfile from spfile;
#将当前的spfile文件备份;(可以指定路径)
④
SQL> alter system set processes=3000 scope=spfile;
#修改当前数据库实例的(spfile文件)最大连接数;
⑤
SQL> shutdown immediate;
#关闭当前数据库实例;
SQL> startup;
#由于修改数据库允许被连接的最大数太大,导致当前数据库实例无法启动;
⑥
SQL> startup pfile=/application/oracle/product/11.2.0/db_1/dbs/initorcl.ora;
#指定以pfile文件启动;
⑦
SQL> create spfile from pfile;
#将备份的spfile进行还原;
⑧
SQL> shutdown immediate;
#关闭数据库实例
SQL> startup;
#启动数据库实例;
⑨
SQL> alter system set processes=3000 scope=spfile;
#重新指定spfile的processes参数;
SQL> shutdown immediate;
#关闭数据库实例;
SQL> startup
#启动数据库
#———————————————————————————
7、备份:
RMAN不能备份pfile，可备份spfile。
(1)spfile、pfile相互备份:
create spfile from pfile
create pfile from spfile               (约等于将spfile文件备份)
#当然在spfile和pfile的后面可以跟具体的路径
create pfile='' from spfile
(2)内存中读取
create pfile = '/xxxxx/initorcl.ora' from memory;
or
create spfile = '/xxxxx/initorcl.ora' from memory;
查看:
SQL> host ls -l /home/oracle/initorcl.ora
如果spfile有老旧文件,则必须指定新的xxx.ora名字,然后mv xxx.ora spfile实例名.ora
关闭、启动:
SQL> shut immediate
SQL> startup pfile = '/home/oracle/initorcl.ora'
(3)待用

#———————————————————————————
8、报错时判断启动方式:
(1)如果看到数据库启动时报以下错误,则可以知道是spfile启动的
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_FSYYK1'
(2)如果看到数据库启动时报以下错误,则可以知道是pfile启动的,
!!!即多了ORA-01078错误
SQL> startup
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_FSYYK1'
#———————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
并行
parallel
查看并行度:
select table_name ,degree from dba_tables where table_name = '';
#———————————————————————————
关闭并行:
alter table xx.xx noparallel;
alter index xx.xx noparallel;
#———————————————————————————
并行度的选择
一般使用2的幂作为并行度,如2、4、8、16等,正常情况并行度不要设置太高,建议最多不要超过32。
特殊情况特殊对待,强悍的系统（比如exadata）,
如果需要非常高的响应速度,并行度再多个几倍也不是问题。并行高的时候并发就要减少,否则可能会耗光并行资源。
#———————————————————————————#———————————————————————————
使用例:
创建大表
create table test parallel 16 as select .... from t1,t2 where .....;
alter table test noparallel;
#———————————————————————————
创建或重建索引
create index idx_test on table_A(name) parallel 8;
alter index idx_test noparallel;
#———————————————————————————
表分析degree=>6
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',method_opt=>'for all indexed columns size 254',granularity=>'ALL',cascade=>TRUE);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P101',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P102',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P103',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
exec dbms_stats.gather_table_stats('RES_ANHUI','ADDR_SEGM',partname=>'SYS_P104',estimate_percent=>1,no_invalidate=>FALSE,degree=>6,granularity=>'PARTITION',cascade=>true);
#———————————————————————————#———————————————————————————
并行hint的写法
/*+ parallel(n) */
#———————————————————————————
只要在整个sql的任何一个关键字
（select、update、insert、delete、merge）
后面出现一次
parallel(n)
那么整个SQL相关的表,都会使用并行
select /*+ parallel(8) */…from t1,t2…
#———————————————————————————
对指定的表开并行
select /*+ parallel(a 4) parallel(b 4) */ … from t1,t2…
例如对t1表开并行
select /*+ parallel(t1 8) */count(*) from demo t1,test t2 where t1.object_id = t2.object_id;
#———————————————————————————
索引:(括号内可以是空格也可以是逗号)
select /*+ parallel_index(demo idx_id_demo 8) */object_id from demo where object_id > 1000;
#———————————————————————————#———————————————————————————
DML并行:
INSERT、DELETE、UPDATE还有MERGE
#———————————————————————————
这种只会在后面select开并行,dml是默认不开并行的
insert /*+ parallel(4) */ into t1 select .... from ....;
#———————————————————————————
所以:必须在session级别通过下面命令开启
alter session enable parallel dml;
活着用force的语法,可以使下面的dml即使不用parallel的hint,也会使用并行度为n的并行。
alter session force parallel dml parallel n;
开启了DML的并行后,接下来的DML语句将会产生一个表锁,
在commit之前,
当前session 不能对该表做查询和dml操作,其他session也不能对该表做DML操作。
#———————————————————————————
所以:流程应为:
alter session enable parallel dml;
your dml；
commit；
alter session disable parallel dml;
或者
alter session force parallel dml parallel 1;
#———————————————————————————#———————————————————————————
备注:
对于提到的表的情况,并不是指这个表内具体有多少数据,
比如一个表有1 亿行记录,返回一行数据,那么肯定是不能用并行的,效率最高的肯定是索引；
一个表只有100 万行,但是返回了20 万行记录,那么用索引肯定效率低,可以考虑并行；
总的来说,表的分析是指经过where 条件过滤后返回的记录,
如果返回的数据很少,哪怕表的数据量很大,那么都适合使用索引,
如果表的记录数相对不大,返回的记录数多,那么可以考虑使用并行的全表扫描。
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
mysql:
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
mysql开同步日志
ALTER TABLE RES_HEILONGJIANG.SPC_STATION_HEILJ ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
mysql软连接
ln -s /data/mysql/data/5001/mysql.sock /tmp/mysql.sock
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
mysql
-p不能有空格
mysql -u root -pz88632820
service mysql status/stop/start
show variables like '%log_bin%';
按某个用户启动
mysqld_safe --defaults-file=/ossogg/mysql_data/etc/my.cnf --user=hfvast &
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
导出成txt文件命令:
nohup mysqldump -uroot -pMaster8%Pass4! -t -T /ossogg/mysql-files/ tongji --tables rme_port pub_log_userport rme_dslam_port opt_road code_account --default-character-set=gbk &
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
PostGres
连接:
psql -d postgres
或者:
psql -h 127.0.0.1 -p 5432 -d postgres -U postgres -W
-h后面的IP地址（192.168.1.210）是数据库所在的IP地址
-p后面的端口（5432）是数据库的监听端口
-U后面的参数（hr）是创建的用户名
-d后面的参数（hr_db）是创建的数据库名称
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
常用参数:
\c
\d        查看表结构
\db
\dn
\du
\dg
\x
\l        查看数据库有哪些
?
\db+   查看表空间
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
连接数:
#———————————————————————————
PG数据库查看当前连接数：
select count(1) from pg_stat_activity;
#———————————————————————————
PG数据库查看最大连接数：
show max_connections;
#———————————————————————————
postgresql.conf中修改：
max_connections = 500
#———————————————————————————
总:
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,max_conn "max_conn(最大连接数)"
,now_conn "now_conn(当前连接数)"
,max_conn - now_conn "remain_conn(剩余连接数)"
from (
select
setting::int8 as max_conn
,(select count(*) from pg_stat_activity ) as now_conn
from pg_settings
where name = 'max_connections'
) a;
#———————————————————————————
杀掉所有空闲连接
select pg_terminate_backend(pid) from pg_stat_activity WHERE state = 'idle';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
备份恢复:
#———————————————————————————
/usr/pgsql-11/bin/
nohup pg_basebackup -Ft -Pv -Xs -z -Z5 -p 5434 -D /home/postgres_bak/5434/backup/ &> /home/postgres_bak/5434/zcj0925.log &
Ft 打包方式为tar
z 压缩
Z5 压缩等级
Xf 数据和wal串跑,（Xs 并行跑）
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
大小与版本等:
#———————————————————————————
查看客户端版本
psql --version
#———————————————————————————
切换至postgresql数据库用户pguser 或 postgres（根据自己实际情况） psql 进入命令行:
查看版本信息
SELECT version();
#———————————————————————————
查看数据库大小：
SELECT pg_size_pretty(pg_database_size('postgres')) As fulldbsize;
#———————————————————————————
查看所有的数据库的大小：
select pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
from pg_database;
#———————————————————————————
查看各数据库数据创建时间：
select datname,
(pg_stat_file(format('%s/%s/PG_VERSION',
case
when spcname = 'pg_default' then
'base'
else
'pg_tblspc/' || t2.oid || '/PG_11_201804061/'
end,
t1.oid))).*
from pg_database t1, pg_tablespace t2
where t1.dattablespace = t2.oid;
#———————————————————————————
按占空间大小,顺序查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid))
from pg_stat_user_tables
where schemaname = 'public'
order by pg_relation_size(relid) desc;
#———————————————————————————
按占空间大小,顺序查看索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid))
from pg_stat_user_indexes
where schemaname = 'public'
order by pg_relation_size(relid) desc;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看表结构:
\d tablename
#———————————————————————————
SQL方式查看表结构：
SELECT A.attnum,
A.attname AS field,
T.typname AS TYPE,
A.attlen AS LENGTH,
A.atttypmod AS lengthvar,
A.attnotnull AS NOTNULL,
b.description AS COMMENT
FROM pg_class C, pg_attribute A
LEFT OUTER JOIN pg_description b ON A.attrelid = b.objoid
AND A.attnum = b.objsubid,
pg_type T
WHERE C.relname = 'pg_tables'
AND A.attnum > 0
AND A.attrelid = C.oid
AND A.atttypid = T.oid
ORDER BY A.attnum;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
阻塞：
#———————————————————————————
主要是查pg_lock,再结合pg_stat_activity和pg_class：
select relation::regclass, s.pid,mode, granted, client_hostname, application_name, usename, query from pg_locks l, pg_stat_activity s where s.pid = l.pid;
改1:
select relation::regclass, s.pid,mode, granted, client_hostname, application_name, usename, query from pg_locks l, pg_stat_activity s where s.pid = l.pid and l.mode not in ('AccessShareLock');
改2:锁状态时候不影响查询表
select relation::regclass, s.pid,mode, granted, client_hostname, application_name, usename, query from pg_locks l, pg_stat_activity s where s.pid = l.pid and l.mode not in ('AccessShareLock','ExclusiveLock');
#———————————————————————————
可以根据查出来的pid或客户端,找到阻塞的连接和进程,手动杀掉
SELECT pg_cancel_backend(<pid>);
#———————————————————————————
强制结束进程
SELECT pg_terminate_backend(11929);
#———————————————————————————
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看归档的某种方法
show archive_command;
日志状态为开启:
logging_collector = on
日志路径:
log_directory = 'pg_log'
日志名称:
log_filename = 'postgresql-%a.log'
日志是追加还是覆盖:
log_truncate_on_rotation = o
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看用户:
select * from pg_user;
改用户密码:
ALTER USER 用户名 WITH PASSWORD 'xxx';
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看有哪些schema
SELECT * FROM information_schema.schemata;
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
查看端口
netstat -a |grep PG
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
::
相当于强转格式:
expression::type
类似于:
CAST ( expression AS target_type );
(将某个xxx 设为 xxtype)
#———————————————————————————
::regclass
相当于oid字符类型,
oid在pg_class里面代表某项(数据库、表、索引、视图、元祖等等)
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
锁:
#———————————————————————————#———————————————————————————#———————————————————————————#
表锁:
#———————————————————————————
share
ShareLock
(非online建索引)
SHARE是共享锁,也就是读锁,当它加到表上之后,整个表只允许读,不允许改,
如果我们为一个表创建索引（不带CONCURRENTLY）时,会创建这种锁。
它与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE冲突。
#———————————————————————————
exclusive
ExclusiveLock
(任何pg命令都不会自动请求该锁等级)
任何的Postgresql的命令都不会加EXCLUSIVE锁,不过在一些操作时,可能会在某些系统表加上此类锁。
EXCLUSIVE锁和除了ACCESS SHARE外所有锁冲突,而ACCESS SHARE锁在执行select语句会加该锁,
所以一旦表t1被加了EXCLUSIVE锁,对于另一个事务的效果也是只能select,不能修改表。
——————————————————————————————————————————————————————————————————————
意向共享锁ROW SHARE和意向排他锁ROW EXCLUSIVE
意向锁是我们要修改表中某一行的数据时,需要先在表上加的一种锁,
表示即将要在表的部分行上加上共享锁或者排它锁。
也就是说我们在为一个数据表的某些数据行加行锁时,实际上在该表上至少加了两种锁,一种是意向表锁,一种才是行锁。
#———————————————————————————
row share
RowShareLock
(select for update/select for share)
使用select from update 或者使用selet for share会加上此锁,它和EXCLUSIVE以及ACCESS EXCLUSIVE冲突。
#———————————————————————————
row exclusive
RowExclusiveLock
(DML)
该锁会在UPDATE、DELETE、INSERT命令执行时在相关表上自动创建,它和其它三种EXCLUSIVE的锁和SHARE锁冲突。
——————————————————————————————————————————————————————————————————————
Access锁是Postgresql特有的一种锁,主要是针对Postgresql多版本更新数据的方式而创建的锁。
所谓的多版本更新数据,意思是Postgresql在更改某一行数据时,不是在该行直接修改数据,而是另外复制了一个新行,修改都在新行上进行。
Access锁有两种：ACCESS SHARE和ACCESS EXCLUSIVE
#———————————————————————————
access share
AccessShareLock
(select)
select语句会在对应的表上加上ACCESS SHARE类型的锁,
通常情况下,任何只读取表而不修改表的查询都会请求这种锁模式。
加上了该锁之后,表明即使在修改数据的情况下也允许读数据。
ACCESS SHARE锁只和ACCESS EXCLUSIVE锁冲突。
#———————————————————————————
access exclusive
AccessExclusiveLock
(DDL,LOCK TABLE)
ACCESS EXCLUSIVE模式与所有的模式的锁冲突,包括SHARE结尾和EXCLUSIVE结尾的锁,
也就是说当一个表加上ACCESS EXCLUSIVE锁之后,该表会阻塞其它事务的任何操作。
会对表加ACCESS EXCLUSIVE的操作有ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL等等。
——————————————————————————————————————————————————————————————————————
#———————————————————————————
share row exclusive
ShareRowExclusiveLock
(任何pg命令都不会自动请求该锁等级)
SHARE ROW EXCLUSIVE与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE\EXCLUSIVE、ACCESS EXCLUSIVE冲突,
目前任何的Postgresql命令都不会创建该锁,在这里我们就不再赘述。
#———————————————————————————
share update exclusive
ShareUpdateExclusiveLock
(非full模式vacuum/online建索引/analyze)
VACUUM(不带FULL选项)、ANALYZE、CREATE INDEX CONCURRENTLY命令会创建该锁。
它与SHARE UPDATE EXCLUSIVE、SHARE 、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE锁模式冲突。
——————————————————————————————————————————————————————————————————————
语句:
select relation::regclass, s.pid,mode, granted, client_hostname, application_name, usename, query from pg_locks l, pg_stat_activity s where s.pid = l.pid;
改1:
select relation::regclass, s.pid,mode, granted, client_hostname, application_name, usename, query from pg_locks l, pg_stat_activity s where s.pid = l.pid and l.mode not in ('AccessShareLock');
改2:锁状态时候不影响查询表
select relation::regclass, s.pid,mode, granted, client_hostname, application_name, usename, query from pg_locks l, pg_stat_activity s where s.pid = l.pid and l.mode not in ('AccessShareLock','ExclusiveLock');
#———————————————————————————#———————————————————————————#———————————————————————————#
行锁:
相对于表锁来说,行锁就是加到某一行上的锁。
它的模式比较简单,只有共享和排它两种类型。
但是在Postgresql中,实际上由于使用多版本的方式更新数据,实际上当update时创建的排它行锁,也不会影响对该行的读。
#———————————————————————————#———————————————————————————#———————————————————————————#
死锁
当两个以上的事务,比如事务A等待事务B中的资源,而事务B又在等待事务A中的资源,
双方互相等待对方持有的资源,而又不释放自己的资源,就会导致死锁。
#———————————————————————————#———————————————————————————#———————————————————————————#
小结
（1）Postgresql中的锁分为表锁和行锁,其中表锁有8种,行锁有2种。
（2）select                                                   语句会加ACCESS SHARE锁,会和ALTER\DROP\TRUNCATE等操作使用的ACCESS EXCLUSIVE锁冲突
（3）CREATE INDEX（不使用CONCURRENTLY）                       操作会使用SHARE锁,不会和SELECT操作冲突,但是会和UPDATE\DELETE\INSERT操作使用的ROW EXCLUSIVE冲突。
（4）CREATE INDEX CONCURRENTLY                                操作会使用SHARE UPDATE EXCLUSIVE锁,不会和SELECT以及UPDATE\DELETE\INSERT操作冲突。
（5）SELECT FROR UPDATE|SHARE                               会在对应的行加上行排它或者行共享,但是由于Postgresql采用多版本更新的模式,对行加上行排它时,实际上该行照样可以被读取。
（6）形成死锁的主要原因和解决方法是：
事务之间互相等待对方锁定的表。解决方法是申请锁定表时不同事务之间的顺序也要一致。
事务之间对锁定了同一个表,但是某个先获取了锁的事务进行了锁升级,造成了死锁。解决方法是同一个事务种要使用不同级别的锁,要先申请最高级别的锁。
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
常用小查询:
#———————————————————————————
表内索引:
select tablename,indexname from pg_indexes where tablename in
#———————————————————————————