SELECT
'ALTER '||
SUBSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),
INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'DEFAULT')-1) ,
INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'USER')) USER_PASS , USERNAME, PROFILE
FROM DBA_USERS
WHERE USERNAME IN
(SELECT USERNAME
FROM DBA_USERS
WHERE (USERNAME in
(select username from dba_users where (account_status like '%EXPIRED%' or account_status like '%LOCK%'))));
import re
pattern = re.compile("ORA-")
for line in open("x2.txt"):
for match in re.finditer(pattern, line):
print(line)
# count number of files generated per day
find . -type f -printf '%TY-%Tm-%Td\n' | sort | uniq -c
# count number of files generated per hour in last 600 minutes
find . -cmin -600 -type f -printf '%TY-%Tm-%Td-%TH\n' | sort | uniq -c
find . -maxdepth 1 -type f -printf '%TY-%Tm-%Td\n' | awk '{array[$0]+=1}END{ for(val in array) print val" "array[val] }'|sort
check profile
col PROFILE format a10
col LIMIT format a15
select * from dba_profiles where resource_type='PASSWORD' order by 1,2;
option 1
turn on trace : alter system set events '1017 trace name errorstack level 10';
check listener , alert.log
turn off trace : alter system set events '1017 trace name errorstack off';
option 2
col dbusername form a15
col userhost form a15
col event_timestamp form a30
select dbusername,userhost,event_timestamp
from unified_audit_trail
where return_code=1017 and
event_timestamp>sysdate-1/12 order by 3
option 3
AUDIT connect whenever NOT SUCCESSFUL;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
-- oracle doc : 352389.1
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
select userid, userhost, terminal, clientid, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE from aud$ where returncode=1017
and NTIMESTAMP# > sysdate - 1/12 ;
option 4
-- oracle doc : 221944.1
SQL>AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;
SQL>AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;
col USERID format a10
col USERHOST format a50
col TERMINAL format a10
col userhost form a20
select returncode, action#, userid, userhost, terminal, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF') from aud$ where returncode='1017' and action#=100
and NTIMESTAMP# > sysdate - 1/12 ;
SELECT TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE from aud$ where returncode=1017
and NTIMESTAMP# > sysdate - 1/12 ;
Oracle 19c - CDB / PDB - useful sqls
sqlplus / as sysdba
set lines 200
COL name FORMAT A30
col CON_ID form 999999
col OPEN_TIME FORMAT a40
col pdb form a30
COLUMN pdb_name FORMAT A20
SELECT con_id, name, open_mode, restricted, open_time FROM gv$containers;
SELECT name, cdb, open_mode FROM v$database;
SELECT name, pdb FROM v$services ORDER BY name;
select PDB_ID,PDB_NAME,CON_ID,STATUS,LOGGING,FORCE_LOGGING from dba_pdbs ORDER BY pdb_name;
SELECT con_id, name, open_mode, restricted FROM v$pdbs ORDER BY name;
select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs
# the end
oracle 19c - CDB/ PDB _ Save the state of PDB
-- save the state, so that pdb will open when cdb is restarted
sqlplus / as sysdba
alter pluggable database all open;
ALTER PLUGGABLE DATABASE demo SAVE STATE instances=all;
-- check
COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
# The end