设置Oracle用户密码永不过期
alter profile default limit PASSWORD_LIFE_TIME unlimited;

查看设置

select * from dba_profiles where resource_name like ‘PASSWORD_LIFE_TIME%’;
设置Oracle用户登陆失败次数限制

设置登陆失败次数限制为100，错误登陆数超过100会导致用户被锁

alter profile default limit FAILED_LOGIN_ATTEMPTS 100;

无限制

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

查看登陆失败限制设置

select * from dba_profiles where resource_name like ‘FAILED_LOGIN_ATTEMPTS%’;
系统进程PID查找对应SQLTEXT
SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM vprocess c WHERE c.spid =&pid)) ORDER BY piece ASC; 查找长事务对应的SQLTEXT with ltr as ( select to_char(sysdate,'YYYYMMDDHH24MISS') TM, s.sid, s.sql_id, s.sql_child_number, s.prev_sql_id, xid, to_char(t.start_date,'YYYYMMDDHH24MISS') start_time, e.TYPE,e.block, e.ctime, decode(e.CTIME, 0, (sysdate - t.start_date) * 3600*24, e.ctime) el_second from vtransaction t, v$session s,v$transaction_enqueue e
where t.start_date <= sysdate - interval ‘200’ second
and t.addr = s.taddr
and t.addr = e.addr(+) )
select ltr.* , (select q1.sql_text from v$sql q1 where ltr.prev_sql_id = q1.sql_id(+) and rownum = 1) prev_sql_text , (select q1.sql_text from v$sql q1 where ltr.sql_id = q1.sql_id(+)
and ltr.sql_child_number = q1.CHILD_NUMBER(+)) sql_text
from ltr ltr;
永久设置sql*plus的环境变量
echo “set pagesize 9999” >> $ORACLE_HOME/sqlplus/admin/glogin.sql
echo “set line 150” >> $ORACLE_HOME/sqlplus/admin/glogin.sql
echo “set long 5000” >> ORACLE_HOME/sqlplus/admin/glogin.sql 查找非系统用户中无主键表 select distinct at.TABLE_NAME, at.OWNER, at.NUM_ROWS from (SELECT owner,table_name FROM all_tables WHERE owner in (select username from dba_users where account_status='OPEN' and username not in ('SYSTEM','SYS','GOLDENGATE','DBSNMP','SYSMAN')) MINUS SELECT owner,table_name FROM all_constraints WHERE owner in (select username from dba_users where account_status='OPEN' and username not in ('SYSTEM','SYS','GOLDENGATE','DBSNMP','SYSMAN')) AND constraint_type = 'P' ) vn, all_tables at where vn.TABLE_NAME=at.TABLE_NAME and vn.OWNER=at.OWNER and at.TABLE_NAME not like '%%’ order by 2,1;
查询有Object的schema
select OWNER,count(OBJECT_NAME) OBJECT_NUM from dba_objects where OWNER in
(select username from dba_users where account_status=‘OPEN’ and username not in (‘SYSTEM’,‘SYS’,‘GOLDENGATE’)) and OBJECT_TYPE=‘TABLE’
group by OWNER;
动态性能视图授权普通用户查看权限
grant select on v_session to ; grant select on v_locked_object to ;
查看并kill掉锁对象
select * from vlocked_object; select object_name,object_type from dba_objects where object_id=&object_id; select sid, serial#, machine, program from vsession where sid=&sid;

set line 150
col OWNER for a20
col OBJECT_NAME for a20
select s.SID,s.SERIAL#,lo.PROCESS,lo.LOCKED_MODE,do.OWNER,do.OBJECT_NAME,do.OBJECT_TYPE
from v$locked_object lo, dba_objects do, v$session s
where lo.OBJECT_ID=do.OBJECT_ID and lo.SESSION_ID=s.SID;

alter system kill session ‘&sid,&serial’;

select sid,type,lmode,request,ctime from v$lock
where type in (‘TM’,‘TX’) order by 1,2;
生成删除所有普通用户及其数据脚本
select ‘drop user ’ || username || ’ cascade;’ from dba_users where account_status=‘OPEN’ and username not in (‘SYSTEM’,‘SYS’,‘GOLDENGATE’);
启动/关闭数据库
startup
startup nomount
startup mount
startup open read only;
startup restrict
查看创建表等数据库对象时的DDL语句
desc dbms_metadata

FUNCTION GET_DDL RETURNS CLOB
Argument Name Type In/Out Default?

OBJECT_TYPE VARCHAR2 IN
NAME VARCHAR2 IN
SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT

set long 9999
set pagesize 9999
select dbms_metadata.get_ddl(’&OBJECT_TYPE’,’&NAME’,’&SCHEMA’) from dual;

Enter value for object_type: TABLE
Enter value for name: YTHYHDZ
Enter value for schema: BFBHDD9
实现将SYS用户的操作信息记录到操作系统日志中
alter system set audit_syslog_level=‘USER.NOTICE’ scope=spfile;
alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=none scope=spfile;

调整后的参数生效需重启数据库

查看系统日志

tail -100f /var/log/messages
查找数据库中无效对象
col OWNER for a15
col OBJECT_NAME for a50
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects
where OWNER not in (‘SYS’,‘SYSTEM’) and STATUS=‘INVALID’;
统计数据库中各类用户对象的数量
select o.owner,o.OBJECT_TYPE,COUNT(*)
from dba_objects o,
(select username from dba_users where account_status=‘OPEN’ and username not in (‘SYSTEM’,‘SYS’,‘GOLDENGATE’,‘SYSMAN’,‘DBSNMP’)) u
where u.username=o.owner group by o.owner,o.OBJECT_TYPE order by 1,2;
列出数据库中指定类型对象列表
col object_name for a50
select owner,object_name,object_type
from dba_objects o,
(select username from dba_users where account_status=‘OPEN’ and username not in (‘SYSTEM’,‘SYS’,‘GOLDENGATE’,‘SYSMAN’,‘DBSNMP’)) u
where o.owner=u.username and object_type in (‘TRIGGER’,‘PROCEDURE’,‘JOB’)
order by 1,3,2;
显示超长的sql语句
select dbms_lob.substr(a.SQL_FULLTEXT) from v$sql a; select dbms_lob.substr(a.SQL_FULLTEXT) from v$sqlarea a where sql_id=’&sql_id’;