Oracle Most Used Commands
top prev next
unlock user
select username, ACCOUNT_STATUS,LOCK_DATE from dba_users where lock_date is not null order by lock_date;
define my_username=xxx
undef my_username
set linesize 250 trimspool on
set pagesize 500
select
'-- PROFILE: <' || PROFILE || '> ACCOUNT_STATUS: <' || ACCOUNT_STATUS ||
'> LOCK_DATE: <' || LOCK_DATE || '> EXPIRY_DATE: <' || EXPIRY_DATE || '>' || chr(10 ) ||
'alter user &&my_username account unlock;' || chr(10) ||
'alter user &&my_username profile default;' || chr(10) ||
-- 'alter user &&my_username identified by tmppswd123;' || chr(10) ||
-- 'connect &&my_username/tmppswd123' || chr(10) ||
'alter user &&my_username identified by values '''||password||''';' || chr(10) ||
'alter user &&my_username profile ' || profile || ';' || chr(10)
from dba_users
where
username = upper('&&my_username');
set linesize 120
set pagesize 500
select username, account_status, lock_date from dba_users
where (account_status like '%LOCK%' or account_status like '%EXPIR%') and lock_date > sysdate - 3 order by lock_date;
select 'alter user ' || username || ' account unlock' || ' -- ' || account_status || ' , ' || lock_date || ';' cmd from dba_users
where (account_status like '%LOCK%' or account_status like '%EXPIR%') and lock_date > sysdate - 3 order by lock_date;
set pagesize 200
define my_username=shrpntdir
select username,account_status from dba_users where username = upper('&&my_username');
alter user &&my_username account unlock;
select username,account_status from dba_users where username = upper('&&my_username');
select '-- user:' || username || ' account status: ' || account_status unlock_user_cmd from dba_users where username = upper('&&my_username')
union all
select decode(account_status, 'OPEN', '-- ', '') || 'ALTER USER ' || username || ' ACCOUNT UNLOCK;' cmd from dba_users where username = upper('&&my_username');
top prev next
Role's Creation Time
-- role, last modified
select r.role, to_char(u.ctime, 'DD-MON-YYYY HH24:MI:SS') ctime
from dba_roles r, sys.user$ u
where r.role = u.name
order by u.ctime;
top prev next
User's Last Password Change
set long 300000
set longchunk 300000
set pagesize 3000
set linesize 380 trimspool on
select dbms_metadata.get_ddl('USER', '&my_username') from dual;
column username format a28
column password_change_time format a32
select u.username, u.PASSWORD_VERSIONS,
s.ctime creation_time,
to_char(s.ptime, 'YYYY-MM-DD HH24:MI:SS') password_change_time
from dba_users u, sys.user$ s
where u.username=s.name and u.username='&my_username'
order by s.ctime, u.username;
column username format a28
column password_change_time format a32
select u.username, u.PASSWORD_VERSIONS,
s.ctime creation_time,
to_char(s.ptime, 'YYYY-MM-DD HH24:MI:SS') password_change_time
from dba_users u, sys.user$ s
where u.username=s.name
order by s.ctime, u.username;
set long 300000
set longchunk 300000
set pagesize 3000
set linesize 380 trimspool on
select dbms_metadata.get_ddl('USER', '&my_username') from dual;
-- by username
select a.name username, a.ctime creation_time, to_char(a.ptime, 'YYYY-MM-DD HH24:MI:SS') password_change_time
from sys.user$ a where a.name ='&my_username';
-- by profile DEFAULT
select a.name username, a.ctime creation_time,
to_char(a.ptime, 'YYYY-MM-DD HH24:MI:SS') password_change_time, b.profile
from sys.user$ a, dba_users b
where a.name = b.username
and b.profile='DEFAULT'
order by a.ptime;
-- By username, list Profile
column profile format a32
column username format a32
set linesize 380 trimspool on
select a.name username, a.ctime creation_time, to_char(a.ptime, 'YYYY-MM-DD HH24:MI:SS') password_change_time, u.PROFILE profile
from sys.user$ a, dba_users u where
u.username = a.name and
a.name in (
'SYS','SYSTEM'
);
top prev next
expired user
-- password is reusable or password change has no issue
set linesize 380 trimspool on pagesize 1000
column username format a32
column profile format a12
column account_status format a32
select username, profile, account_status from dba_users order by account_status;
column profile format a12
column limit format a12
select * from dba_profiles where profile='DEFAULT';
set long 300000
set longchunk 300000
select dbms_metadata.get_ddl('USER', '&&my_username') from dual;
select dbms_metadata.get_ddl('PROFILE', 'DEFAULT') from dual;
alter USER "&&my_username" IDENTIFIED BY VALUES 'hashed_password';
-- password is not reusable and password change has issue
set pagesize 200
define my_username=FPEREZ
CREATE PROFILE "DEFAULT1"
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION null
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;
alter user &&my_username profile default1;
set linesize 280 trimspool on
set long 300000
set longchunk 300000
select dbms_metadata.get_ddl('USER','&&my_username') from dual;
-- ALTER USER &&my_username IDENTIFIED BY VALUES 'S:909080D9F80D5AF69C4EC3E90E53FC517E94CD573CC2BFB4064D5C87DF33;2B8E5A1016199947';
alter user &&my_username profile default;
DROP PROFILE "DEFAULT1";
top prev next
create similar user
define my_username=XXXX
set serveroutput on
set long 30000000
set longchunk 30000000
set pagesize 5000
set linesize 350 trimspool on
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
-- turn off header and exception
SELECT replace(replace(cmd,CHR(13),''),CHR(10),' ') from (
select trim(to_char('SELECT dbms_metadata.get_ddl (''USER'', '''||username||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd
from dba_users where username = '&&my_username'
UNION
select trim(to_char('SELECT dbms_metadata.get_ddl (''ROLE'', '''||role||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd
from dba_roles where role = '&&my_username'
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''TABLESPACE_QUOTA'', '''||username||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd
from dba_ts_quotas where username = '&&my_username' and rownum < 2
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''ROLE_GRANT'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd
from dba_role_privs where grantee = '&&my_username' and rownum < 2
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''DEFAULT_ROLE'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd
from dba_role_privs where grantee = '&&my_username' and rownum < 2
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''SYSTEM_GRANT'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd
from dba_sys_privs where grantee = '&&my_username' and rownum < 2
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''OBJECT_GRANT'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd
from dba_tab_privs where grantee = '&&my_username' and rownum < 2
UNION
select to_char('select to_clob(''-- last statement'') FROM dual;') cmd FROM dual);
set pagesize 1000
set linesize 180 trimspool on
select * from dba_users where username like '%CLAIM%';
select username, account_status from dba_users where username like '%CLAIM%';
alter user claims account unlock;
select username, account_status, lock_date,EXPIRY_DATE from dba_users where account_status like '%LOCK%' order by lock_date;
define my_username=X
select account_status from dba_users where username='&&my_username';
alter user &&my_username account unlock;
select account_status from dba_users where username='&&my_username';
-- ----------------------------------------------------------
select username, account_status, lock_date from dba_users;
alter profile xxx limit PASSWORD_VERIFY_FUNCTION null;
alter profile xxx limit PASSWORD_VERIFY_FUNCTION NCL_VERIFY8_FUNCTION;
define my_username=DVR_USER
select resource_name, limit from dba_profiles where profile = 'DEFAULT' and resource_type='PASSWORD';
ALTER PROFILE DEFAULT limit PASSWORD_REUSE_MAX UNLIMITED;
ALTER PROFILE DEFAULT limit PASSWORD_REUSE_TIME UNLIMITED;
ALTER PROFILE DEFAULT limit PASSWORD_REUSE_MAX 10;
ALTER PROFILE DEFAULT limit PASSWORD_REUSE_TIME 10;
select 'alter profile ' || '&&my_profile' || ' limit PASSWORD_VERIFY_FUNCTION null;'
from dba_users where username = upper('&&my_username')
union all
select 'alter profile ' || '&&my_profile' || ' limit PASSWORD_VERIFY_FUNCTION ' || limit || ';'
from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION'
and profile in ( select profile from dba_users where username = upper('&&my_username') )
;
define my_profile=NCL_PROFILE_DVR_USER_NS
select 'alter profile ' || '&&my_profile' || ' limit PASSWORD_VERIFY_FUNCTION null;' from dual
union all
select 'alter profile ' || '&&my_profile' || ' limit PASSWORD_VERIFY_FUNCTION ' || limit || ';'
from dba_profiles where profile = '&my_profile' and resource_name = 'PASSWORD_VERIFY_FUNCTION';
-- ----------------------------------------------------------
-- batch job
CREATE USER &1
IDENTIFIED BY &1_14
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE MY_PROFILE
ACCOUNT UNLOCK;
GRANT DBA TO &1;
ALTER USER &1 DEFAULT ROLE ALL;
more test.sh
cat sample.sql|sed "s/EMurroni/$1/"
cat sample.sql|sed 's/EMurroni/rhernandez/'
./test.sh rhernandez>a.sql
./test.sh ssingh>>a.sql
./test.sh mweatheritt>>a.sql
@@create_user rhernandez
@@create_user ssingh
-- ----------------------------------------------------------
set pagesize 5000
set linesize 180 trimspool on
set long 300000
set longchunk 300000
select dbms_metadata.get_ddl('PROFILE', 'DEFAULT') from dual;
create user xxx profile default1;
alter user ISSUTRAX profile default;
drop profile "DEFAULT1";
ALTER PROFILE "DEFAULT"
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 8
PASSWORD_VERIFY_FUNCTION "NCL_VERIFY8_FUNCTION"
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;
CREATE PROFILE "DEFAULT1"
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION null
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;
alter profile default1 limit password_reuse_max unlimited;
alter profile default limit PASSWORD_REUSE_MAX unlimited;
alter profile default limit PASSWORD_VERIFY_FUNCTION null;
alter user issutrax identified by its;
alter profile default limit PASSWORD_REUSE_MAX 8;
alter profile default limit PASSWORD_VERIFY_FUNCTION NCL_VERIFY8_FUNCTION;
-- ----------------------------------------------------------
set pagesize 1000
set linesize 138 trimspool on
alter user dbas identified by t1234;
select username, profile from dba_users where username='DBAS';
select p.* from dba_users u, dba_profiles p
where
u.profile=p.profile and
u.username='DBAS' and
p.resource_name='PASSWORD_VERIFY_FUNCTION';
alter profile default limit PASSWORD_VERIFY_FUNCTION null;
alter user dbas identified by t1234;
alter profile default limit PASSWORD_VERIFY_FUNCTION NCL_VERIFY8_XXX;
rem for random password, openssl rand -base64 8
-- *********************************** real one ********************************************
define my_username=XXXX
set serveroutput on
set long 30000000
set longchunk 30000000
set pagesize 5000
set linesize 200 trimspool on
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
-- turn off header and exception
SELECT replace(replace(cmd,CHR(13),''),CHR(10),' ') from (
select trim(to_char('SELECT dbms_metadata.get_ddl (''USER'', '''||username||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd from dba_users where username = '&&my_username'
UNION
select trim(to_char('SELECT dbms_metadata.get_ddl (''ROLE'', '''||role||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd from dba_roles where role = '&&my_username'
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''TABLESPACE_QUOTA'', '''||username||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd from dba_ts_quotas where username = '&&my_username'
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''ROLE_GRANT'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd from dba_role_privs where grantee = '&&my_username' and rownum < 2
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''DEFAULT_ROLE'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd from dba_role_privs where grantee = '&&my_username' and rownum < 2
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''SYSTEM_GRANT'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd from dba_sys_privs where grantee = '&&my_username' and rownum < 2
UNION
select trim(to_char('SELECT dbms_metadata.get_granted_ddl (''OBJECT_GRANT'', '''||grantee||''') FROM dual ' || chr(10) || 'UNION ALL ')) cmd from dba_tab_privs where grantee = '&&my_username' and rownum < 2
UNION
select to_char('select to_clob(''-- last statement'') FROM dual;') cmd FROM dual);
SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','&my_username') FROM DUAL;
ALTER USER "DBAS" DEFAULT ROLE ALL;
select dbms_metadata.get_ddl ( 'USER', '&my_username') from dual;
rem SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA','&my_username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&my_username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&my_username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&my_username') FROM DUAL;
set sqlblanklines on
select
trim(to_char(a.Extracted_DDL)) ||
trim(to_char(b.Extracted_DDL)) ||
trim(to_char(c.Extracted_DDL)) ||
trim(to_char(d.Extracted_DDL)) ||
trim(to_char(e.Extracted_DDL)) ||
trim(to_char(f.Extracted_DDL)) xxx
from
(select (case
when ((select count(*)
from dba_users
where username = '&&my_username') > 0)
then to_char (chr(10) || 'SELECT dbms_metadata.get_ddl (''USER'', ''&&my_username'') from dual ' || chr(10) || 'UNION ALL ')
else to_char ('')
end ) Extracted_DDL from dual) a
,
(select (case
when ((select count(*)
from dba_ts_quotas
where username = '&&my_username') > 0)
then to_char (chr(10) || 'SELECT dbms_metadata.get_granted_ddl( ''TABLESPACE_QUOTA'', ''&&my_username'') from dual ' || chr(10) || 'UNION ALL ')
else to_char ('')
end ) Extracted_DDL from dual) b
,
(select (case
when ((select count(*)
from dba_role_privs
where grantee = '&&my_username') > 0)
then to_char (chr(10) || 'SELECT dbms_metadata.get_granted_ddl (''ROLE_GRANT'', ''&&my_username'') from dual ' || chr(10) || 'UNION ALL ')
else to_char ('')
end ) Extracted_DDL from dual) c
,
(select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&&my_username') > 0)
then to_char (chr(10) || 'SELECT dbms_metadata.get_granted_ddl (''SYSTEM_GRANT'', ''&&my_username'') from dual ' || chr(10) || 'UNION ALL ')
else to_char ('')
end ) Extracted_DDL from dual) d
,
(select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&&my_username') > 0)
then to_char (chr(10) || 'SELECT dbms_metadata.get_granted_ddl (''OBJECT_GRANT'', ''&&my_username'') from dual ' || chr(10) || 'UNION ALL')
else to_char ('')
end ) Extracted_DDL from dual) e,
(select to_char(chr(10) || 'SELECT to_clob(''-- last statement'') from dual;') Extracted_DDL from dual) f ;
-- select dbms_metadata.get_ddl ( 'ROLE', '&my_username') from dual;
-- grant create session to &my_username;
-- grant select any table to &my_username;
-- grant select any dictionary to &my_username;
-- grant select_catalog_role to &my_username;
-- alter system set O7_DICTIONARY_ACCESSIBILITY=true
-- http://arup.blogspot.com/
-- alter user &my_username default temporary tablespace temp;
-- alter user &my_username default tablespace users;
-- alter user &my_username default role all;
-- alter user &my_username quota unlimited on guesthista2008;
alter user &my_username quota unlimited on MY_TABLESPACE_NAME;
alter user &my_username quota unlimited on MY_TABLESPACE_NAME;
set linesize 180 trimspool on
select * from dba_ts_quotas where username='&my_username';
alter usesr &my_username set default role xxx,yyy,zzzz;
alter usesr &my_username set default role all;
alter usesr &my_username set default role all except xxx,yyy;
select * from (
select 'alter user '||name||' identified by values '''||password||''';' from user$ where spare4 is null and password is not null
union
select 'alter user '||name||' identified by values '''||spare4||';'||password||''';' from user$ where spare4 is not null and password is not null
) order by 1;
top prev next
alter user profile, password, expiration date
select username, ACCOUNT_STATUS,LOCK_DATE from dba_users where lock_date is not null order by lock_date;
define my_username=xxx
undef my_username
set linesize 250 trimspool on
set pagesize 500
select
'-- PROFILE: <' || PROFILE || '> ACCOUNT_STATUS: <' || ACCOUNT_STATUS ||
'> LOCK_DATE: <' || LOCK_DATE || '> EXPIRY_DATE: <' || EXPIRY_DATE || '>' || chr(10 ) ||
'alter user &&my_username account unlock;' || chr(10) ||
'alter user &&my_username profile default;' || chr(10) ||
-- 'alter user &&my_username identified by tmppswd123;' || chr(10) ||
-- 'connect &&my_username/tmppswd123' || chr(10) ||
'alter user &&my_username identified by values '''||password||''';' || chr(10) ||
'alter user &&my_username profile ' || profile || ';' || chr(10)
from dba_users
where
username = upper('&&my_username');
top prev next
ts_used.sql
REM
REM Script: ts_used.sql
REM
REM Function: Display tablespace usage with graph
REM
REM
clear columns
column tablespace format a33
column total_mb format 999,999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
set linesize 350 trimspool on
set pages 300
select total.ts tablespace,
dbat.status status,
total.mb total_mb,
NVL(total.mb - free.mb,total.mb) used_mb,
NVL(free.mb,0) free_mb,
DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE(free.mb,
null,'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']'
END as GRAPH
from
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
dba_tablespaces dbat
where total.ts=free.ts(+) and
total.ts=dbat.tablespace_name
UNION ALL
select sh.tablespace_name,
'TEMP',
SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
SUM(sh.bytes_used)/1024/1024 used_mb,
SUM(sh.bytes_free)/1024/1024 free_mb,
ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,
'['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'),
'--------------------'))||']'
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 1
/
ttitle off
clear columns
select cmd from
(
select file_name,'alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON NEXT '|| f.INCREMENT_BY*p.value/(1024*1024) || 'M MAXSIZE ' || f.MAXBYTES/(1024*1024) || 'M; rem existing' cmd
from dba_data_files f, v$parameter p where f.tablespace_name = upper('&&my_tablespace') and p.name='db_block_size'
union
select file_name,'alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON MAXSIZE 6001M;' cmd
from dba_data_files where tablespace_name = upper('&&my_tablespace')
)
union
select cmd from
(select file_name,'alter database tempfile ''' || file_name
|| ''' resize ' || bytes/1024/1024 || 'M;' cmd
from dba_temp_files where tablespace_name = upper('&&my_tablespace')
union
select file_name, 'alter tablespace ' || tablespace_name || ' add tempfile '''
|| file_name || ''' size ' || to_char(bytes/1024/1024 ) || 'M;' cmd
from dba_temp_files
where tablespace_name = upper('&&my_tablespace') )
order by cmd
;
top prev next
Add or Resize Datafile
egrep -i "ORA-01652|ORA-1652" alert_${ORACLE_SID}.log|sort -u
grep -i alter alert_${ORACLE_SID}.log|sort -u
-- max filesize
select distinct maxbytes/(1024*1024) xx from dba_data_files;
select maxbytes/(1024*1024), count(*) cnt from dba_data_files group by maxbytes/(1024*1024);
select * from dba_data_files where maxbytes/(1024*1024) = 8001;
define my_tablespace=USERS_TS
define my_tablespace=TEMP1
define my_tablespace=TEMP
set linesize 200 trimspool on
set pagesize 5000
-- using maxsize
define my_tablespace=USERS_TS
set linesize 200 trimspool on
set pagesize 5000
select cmd from
(
select file_name,'alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON NEXT '|| f.INCREMENT_BY*p.value/(1024*1024) || 'M MAXSIZE ' || f.MAXBYTES/(1024*1024) || 'M; rem existing' cmd
from dba_data_files f, v$parameter p where f.tablespace_name = upper('&my_tablespace') and p.name='db_block_size'
union all
select file_name,'alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON MAXSIZE ' || f.MAXBYTES/(1024*1024) ||'M; rem current AUTOEXTEND' cmd
from dba_data_files f where tablespace_name = upper('&my_tablespace')
union all
select file_name,'alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON NEXT 20M MAXSIZE 6010M; rem change both 20M and 6010M' cmd
from dba_data_files where tablespace_name = upper('&my_tablespace')
)
union all
select cmd from
(select file_name,'alter database tempfile ''' || file_name
|| ''' resize ' || bytes/1024/1024 || 'M;' cmd
from dba_temp_files where tablespace_name = upper('&my_tablespace')
union all
select file_name, 'alter tablespace ' || tablespace_name || ' add tempfile '''
|| file_name || ''' size ' || to_char(bytes/1024/1024 ) || 'M;' cmd
from dba_temp_files
where tablespace_name = upper('&my_tablespace') )
order by cmd
;
-- using maxsize
define my_tablespace=USERS_TS
set linesize 200 trimspool on
set pagesize 5000
select cmd from
(select file_name,'alter database datafile ''' || file_name
|| ''' resize ' || bytes/1024/1024 || 'M;' cmd
from dba_data_files where tablespace_name = upper('&my_tablespace')
union
select file_name, 'alter tablespace ' || tablespace_name || ' add datafile '''
|| file_name || ''' size ' || to_char(bytes/1024/1024 ) || 'M;' cmd
from dba_data_files
where tablespace_name = upper('&my_tablespace') )
union
select cmd from
(select file_name,'alter database tempfile ''' || file_name
|| ''' resize ' || bytes/1024/1024 || 'M;' cmd
from dba_temp_files where tablespace_name = upper('&my_tablespace')
union
select file_name, 'alter tablespace ' || tablespace_name || ' add tempfile '''
|| file_name || ''' size ' || to_char(bytes/1024/1024 ) || 'M;' cmd
from dba_temp_files
where tablespace_name = upper('&my_tablespace') )
order by cmd
;
rem !df -h
rem !df -h /db/p/bls/v*
rem !mkdir -p /db/p/pid/v7/index/PIDP2
select * from extents_left;
select distinct tablespace_name from extents_left;
delete from extents_left;
rem alter database datafile '/db/p/pr/v4/table/PRP1/web_serv_data_02.dbf' resize 600M;
rem alter tablespace PID_DATA add datafile '/db/p/pid/v7/table/PIDP2/pid_data_2x.dbf' size 1000M;
rem ALTER DATABASE DATAFILE '/db/p/my_datafile.dbf' AUTOEXTEND OFF;
rem ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';
rem ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
-- hwm
select 'alter database datafile ''' ||file_name || ''' resize ' || hwm*8192/(1024*1024) || 'M'
|| '; -- shrink amount ' || (blocks-hwm+1)*8192/(1024*1025) xx
from dba_data_files a,
( select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id
-- and tablespace_name = 'my_tablespace'
and file_name like '%v9%'
order by hwm;
select 'alter database datafile ''' ||file_name || ''' resize ' || hwm*8192/(1024*1024) || 'M'
from dba_data_files a,
( select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id
and tablespace_name = '&my_tablespace'
and file_name like '%v3%'
order by hwm;
set linesize 200 trimspool on
set pagesize 200
select 'alter database datafile ''' || file_name || ''' resize ' || to_char(bytes/1024/1024) || 'M;' resize
from dba_data_files where tablespace_name = upper('WPS_GLOINDEX_LARGE ');
select 'alter tablespace ' || tablespace_name || ' add datafile ''' || file_name || ''' size ' ||
to_char(bytes/1024/1024 ) || 'M;' add_file
from dba_data_files
where tablespace_name = upper('WPS_GLOINDEX_LARGE');
top prev next
Check Invalid Objects, Unusable Indexes, Disbaled Constraints, Disbaled Triggers
-- invalid object status
-- CheckInvalid
-- 1):
COLUMN owner format a12 heading 'OWNER' justify c word_wrapped
COLUMN object_type format a15 heading 'OBJECT_TYPE' justify c word_wrapped
COLUMN object_name format a30 heading 'OBJECT_NAME' justify c word_wrapped
COLUMN status format a8 heading 'STATUS' justify c word_wrapped
COLUMN table_name format a30 heading 'TABLE_NAME' justify c word_wrapped
COLUMN index_name format a30 heading 'INDEX_NAME' justify c word_wrapped
COLUMN constraint_name format a30 heading 'CONSTRAINT_NAME' justify c word_wrapped
COLUMN trigger format a30 heading 'TRIGGER' justify c word_wrapped
set linesize 380 trimspool on pagesize 5000
select owner owner
, OBJECT_TYPE object_type
, substr(OBJECT_NAME,1,30) object_name
, STATUS status
from dba_objects
where
STATUS = 'INVALID'
-- and rownum < 10
and not (owner = 'PUBLIC' and object_type = 'SYNONYM')
and not (object_type = 'MATERIALIZED VIEW')
and OBJECT_NAME <> 'STATSPACK'
order by owner, OBJECT_TYPE, OBJECT_NAME, STATUS;
-- 2a): non-partitioned indexes
select owner owner
, table_name table_name
, index_name index_name
, status status
from dba_indexes
where
-- status IN ('INVALID','UNUSABLE')
status NOT IN ('VALID')
and rownum < 10
order by 1,2,3;
-- 2b): partitioned indexes
set linesize 380 trimspool on pagesize 5000
select p.index_owner,p.index_name, p.partition_name, p.status
from dba_ind_partitions p, dba_indexes i
where
i.owner= p.index_owner
and i.index_name = p.index_name
-- p.status IN ('INVALID','UNUSABLE')
and p.status NOT IN ('USABLE')
order by p.index_owner, i.table_name, p.index_name
;
-- 2bb): rebuild partitioned index
-- select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' online;' cmd
-- from dba_ind_partitions where status='UNUSABLE';
-- 3): constraint disbaled
SELECT OWNER xown
, CONSTRAINT_TYPE xtyp
, CONSTRAINT_NAME xcon
, STATUS xstat
FROM DBA_CONSTRAINTS
WHERE STATUS = 'DISABLED'
AND owner||'.'||constraint_name not in ('SYSTEM.REPCAT$_GROUPED_COLUMN_PK'
,'SYSTEM.REPCAT$_PARAMETER_COLUMN_PK'
,'SYSTEM.SYS_C001163'
,'SYSTEM.SYS_C001164'
,'SYSTEM.SYS_C001177')
AND CONSTRAINT_NAME NOT LIKE 'LOGMNR%'
AND CONSTRAINT_NAME NOT LIKE 'REPCAT$%'
ORDER BY 1,2,3;
-- 4): trigger disabled
SELECT OWNER xown
, 'T' xtyp
, TRIGGER_NAME xtrg
, STATUS xstat
FROM DBA_TRIGGERS
WHERE STATUS IN ('DISABLED','INVALID')
AND owner||'.'||trigger_name not in ('SYS.AURORA$SERVER$SHUTDOWN'
,'SYS.AURORA$SERVER$STARTUP'
,'SYS.CDC_ALTER_CTABLE_BEFORE'
,'SYS.CDC_CREATE_CTABLE_AFTER'
,'SYS.CDC_CREATE_CTABLE_BEFORE'
,'SYS.CDC_DROP_CTABLE_BEFORE'
,'SYS.LOGMNRGGC_TRIGGER'
,'SYS.NO_VM_CREATE'
,'SYS.NO_VM_DROP'
,'SYS.SYS_LOGOFF'
,'SYS.SYS_LOGON'
,'WMSYS.NO_VM_DDL'
,'WMSYS.NO_VM_DROP_A')
AND trigger_name not like 'AURORA$SERVER$%'
ORDER BY 1,2,3;
top prev next
Recompile Invalid Objects
Check Invalid
@?/rdbms/admin/utlrp;
set serveroutput on
begin
DBMS_UTILITY.COMPILE_SCHEMA(user);
end;
/
define my_username=xxx
undef my_username
set linesize 250 trimspool on
set pagesize 5000
column object_name format a32
select owner, object_type,count(*) cnt
from dba_objects
where
owner=upper('&my_username')
and status='INVALID'
group by owner, object_type
order by owner, object_type;
-- select owner, object_name,object_type from dba_objects where status='INVALID';
set pagesize 5000 linesize 380 trimspool on
select owner, object_name,object_type
from dba_objects
where
owner=upper('&my_username')
and status='INVALID'
order by owner, object_type,object_Name;
set linesize 350 trimspool on
select 'alter '||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;')
from user_objects
where status like 'INVALID';
undefine MY_OWNER
select 'alter '||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||owner|| '.' ||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;')
from dba_objects
where status like 'INVALID' and owner='&MY_OWNER';
select 'alter '||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||owner|| '.' ||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;')
from dba_objects
where status like 'INVALID';
SELECT 'CREATE OR REPLACE PUBLIC SYNONYM ' || OBJECT_NAME || ' FOR DBPBR.' || OBJECT_NAME || ';'
FROM dba_objects
WHERE OBJECT_TYPE = 'SYNONYM'
AND Status <> 'VALID';
-- alter PUBLIC SYNONYM NCL_SPIRIT_5DAY_EMAIL_PROC COMPILE;
-- pl/sql object has debug flag on
set linesize 180 trimspool on
select owner, OBJECT_NAME, object_type, debuginfo from SYS.ALL_PROBE_OBJECTS where debuginfo = 'T' and owner not in 'TESTMR';
select 'alter ' || decode(object_type, 'PACKAGE BODY', 'PACKAGE', 'TYPE BODY', 'TYPE', object_type) || ' ' || owner || '.' || object_name ||' compile' || decode(object_type, 'PACKAGE BODY', ' BODY', 'TYPE BODY', ' BODY', '') || ';' xxx from SYS.ALL_PROBE_OBJECTS where debuginfo = 'T'
-- and owner not in 'TESTMR'
;
alter session set ddl_lock_timeout=10;
set pagesize 5000
set linesize 380 trimspool on
select 'alter '
||DECODE(owner, 'PUBLIC', 'PUBLIC ', '')
||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||DECODE(owner, 'PUBLIC', '', owner ||'.')
||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;')
from dba_objects
where
status like 'INVALID'
-- and owner='PUBLIC' and object_type='SYNONYM' and object_name = 'MY_SYNONYM_NAME'
-- and (owner, object_name) not in ( ('CRMODDM', 'PKG_OTHER_LEAD_SOURCES') )
and object_type not in ( 'MATERIALIZED VIEW')
order by
-- object_type, owner, object_name
-- owner, object_type, object_name
Decode(object_type,
'VIEW', 'ZVIEW',
'SYNONYM','ZSYNONYM',
object_type),
decode(owner,'PUBLIC', 'ZPUBLIC', object_type),
object_name
;
set pagesize 5000
set linesize 380 trimspool on
set serveroutput on
declare
cursor c1 is
select 'alter '
||DECODE(owner, 'PUBLIC', 'PUBLIC ', '')
||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||DECODE(owner, 'PUBLIC', '', owner ||'.')
||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY', ' COMPILE') cmd
from dba_objects
where
status like 'INVALID'
-- and owner='PUBLIC' and object_type='SYNONYM' and object_name = 'MY_SYNONYM_NAME'
-- and (owner, object_name) not in ( ('CRMODDM', 'PKG_OTHER_LEAD_SOURCES') )
and (owner, object_name) not in ( ('PERFSTAT', 'STATSPACK') )
and object_type not in ( 'MATERIALIZED VIEW')
order by object_type, owner, object_name
;
begin
for r1 in c1
loop
dbms_output.put_line ( r1.cmd || ';');
begin
execute immediate r1.cmd ;
exception
when others then
dbms_output.put_line ( '-- Error: ' ||r1.cmd || ';');
end;
end loop;
end;
/
-- local compilation
@?/rdbms/admin/utlrp;
-- recompile
set serveroutput on
begin
DBMS_UTILITY.COMPILE_SCHEMA(user);
end;
/
-- remote compilation for different version of Oracle
set trims on
set pages 0
col nl newline
-- quotation on object_name
select
'alter '||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' "'||object_name|| '"'|| DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;')
from user_objects
where status like 'INVALID';
-- no quotation
select
-- 'PROMPT Compiling '||object_name nl,
'alter '||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;')
from user_objects
where status like 'INVALID';
alter PUBLIC SYNONYM MY_PUBLIC_SYNONYM_NAME COMPILE;
rem right: alter public SYNONYM PROC_NYOCA_UPDATE_ORDER_DTL COMPILE;
rem wrong: alter SYNONYM PUBLIC.PROC_NYOCA_UPDATE_ORDER_DTL COMPILE
-- compile and use in debug mode
grant DEBUG CONNECT SESSION TO MYUSER;
alter procedure myschema.myproc compile debug;
-- pl/sql object has debug flag on
set linesize 180 trimspool on
select owner, OBJECT_NAME, object_type, debuginfo from SYS.ALL_PROBE_OBJECTS where debuginfo = 'T' and owner not in 'TESTMR';
-- turn off the debug flag
select 'alter ' || decode(object_type, 'PACKAGE BODY', 'PACKAGE', 'TYPE BODY', 'TYPE', object_type) || ' ' || owner || '.' || object_name ||' compile' || decode(object_type, 'PACKAGE BODY', ' BODY', 'TYPE BODY', ' BODY', '') || ';' xxx from SYS.ALL_PROBE_OBJECTS where debuginfo = 'T'
-- and owner not in 'TESTMR'
;
select status,count(*) from dba_objects group by status;
select owner,status,count(*) from dba_objects group by owner,status;
select owner, object_name, object_type from dba_objects where status='INVALID';
alter session set ddl_lock_timeout=10;
set pagesize 5000
set linesize 380 trimspool on
select 'alter '
||DECODE(owner, 'PUBLIC', 'PUBLIC ', '')
||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||DECODE(owner, 'PUBLIC', '', owner ||'.')
||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;')
from dba_objects
where
status like 'INVALID'
-- and owner='PUBLIC' and object_type='SYNONYM' and object_name = 'MY_SYNONYM_NAME'
-- and (owner, object_name) not in ( ('CRMODDM', 'PKG_OTHER_SOURCES') )
and object_type not in ( 'MATERIALIZED VIEW')
order by object_type, owner, object_name
;
-- using stored procedure
set pagesize 5000
set linesize 380 trimspool on
set serveroutput on
declare
cursor c1 is
select 'alter '
||DECODE(owner, 'PUBLIC', 'PUBLIC ', '')
||DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type)
||' '||DECODE(owner, 'PUBLIC', '', owner ||'.')
||object_name||DECODE(object_type, 'PACKAGE BODY', ' COMPILE BODY', ' COMPILE') cmd
from dba_objects
where
status like 'INVALID'
-- and owner='PUBLIC' and object_type='SYNONYM' and object_name = 'MY_SYNONYM_NAME'
-- and (owner, object_name) not in ( ('CRMODDM', 'PKG_OTHER_LEAD_SOURCES') )
and (owner, object_name) not in ( ('PERFSTAT', 'STATSPACK') )
and object_type not in ( 'MATERIALIZED VIEW')
order by object_type, owner, object_name
;
begin
for r1 in c1
loop
dbms_output.put_line ( r1.cmd || ';');
begin
execute immediate r1.cmd ;
exception
when others then
dbms_output.put_line ( '-- Error: ' ||r1.cmd || ';');
end;
end loop;
end;
/
set serveroutput on
declare
cursor myCursor is select owner,object_type, object_name
from DBA_OBJECTS
where status = 'INVALID'
and rownum < 30000
order by 1, 2;
sqlString varchar2(2000);
begin
for r1 in myCursor loop
begin
if r1.object_type = 'PACKAGE BODY' then
sqlString := 'alter PACKAGE '||r1.owner || '.' ||r1.object_name||' compile BODY';
elsif r1.object_type = 'TYPE BODY' then
sqlString := 'alter TYPE '||r1.owner || '.' ||r1.object_name||' compile BODY';
else
sqlString := 'alter '||r1.object_type||' '||r1.owner || '.' ||r1.object_name||' compile';
end if;
dbms_output.put_line ( sqlString );
execute immediate sqlString;
-- dbms_output.put_line(r1.object_type||' '||r1.object_name||' compiled successfully');
dbms_output.put_line ( sqlString || ' successfully' );
exception
when others then
dbms_output.put_line(SQLERRM||' on '||sqlString);
end;
end loop;
end;
/
top prev next
Active Session and Transaction
set linesize 350 trimspool on
set pagesize 5000
column start_time format a20
column sid format 99999
column serial# format 999999
column username format a12
column status format a10
column schemaname format a10
column osuser format a16
column process format a12
column machine format a32
column terminal format a12
column program format a42
column module format a42
column logon format a20
column SCHEMANAME noprint
column terminal noprint
column module noprint
select s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,
t.start_time,
to_char(s.logon_time,'MM/DD/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
-- and status = 'ACTIVE'
-- and rownum < 50
order by status,s.machine,start_time;
select 'alter system kill session '''||sid||','||serial#||''' immediate;'
from v$transaction t,v$session s
where s.saddr = t.ses_addr
-- and status = 'ACTIVE'
-- and rownum < 50
order by sid,serial#;
top prev next
Locked Objects
set linesize 250 trimspool on
set pagesize 1000
column OBJECT_NAME format A32
set long 300000
set longchunk 300000
SELECT d.OBJECT_ID, d.OBJECT_NAME, l.SESSION_ID, l.ORACLE_USERNAME,
l.LOCKED_MODE, s.machine, s.osuser, s.program, s.status, s.sql_id, s.prev_sql_id, s.FINAL_BLOCKING_SESSION
from v$locked_object l, dba_objects d, v$session s
where d.OBJECT_ID=l.OBJECT_ID and l.session_id = s.sid
top prev next
Kill A Session
select * from v$session where sid=2168;
alter system kill session '1736,9936' immediate;
set linesize 250 trimspool on
set pagesize 5000
select machine,status,count(*) from v$session where type='USER' group by status,machine order by 2,1;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' xxx from v$session where machine = 'myhostname1' and type='USER';
-- kill session by sid
set linesize 180 trimspool on
define my_sid=999
select sid,serial#,type,machine,program,sql_id from v$session where sid='&&my_sid';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' xxx from v$session where sid='&&my_sid';
-- kill session by sql_id
set linesize 180 trimspool on
define my_sql_id=fg67bdk67u001
select sid,serial#,type,machine,status,terminal,osuser,username,program from v$session where sql_id in ('&&my_sql_id');
select sid,serial#,type,machine,status,terminal,osuser,username,program from v$session where sql_id='&&my_sql_id';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' xxx from v$session where sql_id='&&my_sql_id';
select * from v$session where status='ACTIVE' and type='USER';
select sid, serial#,username, command,status,server,osuser,process, MACHINE,port,terminal,sql_id from v$session
-- where
-- machine like '%11%' and
-- status = 'ACTIVE' and
-- type='USER'
;
-- blocking session
set linesize 350 trimspool on
column machine format a20
select username, sid,serial#,BLOCKING_SESSION, machine,program,sql_id from v$session where type='USER' and status='ACTIVE';
rem alter system kill session '1030,2892' immediate;
select * from v$session where sid=2168;
alter system kill session '1736,9936' immediate;
set linesize 250 trimspool on
set pagesize 5000
select machine,status,count(*) from v$session where type='USER' group by status,machine order by 2,1;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' xxx from v$session where machine = 'myhostname1' and type='USER';
select * from v$session where status='ACTIVE' and type='USER';
select sid,serial#, machine,status,terminal,osuser,username,program from v$session where sql_id in ('fg67bdk67u001');
select sid, serial#,username, command,status,server,osuser,process, MACHINE,port,terminal,sql_id from v$session
-- where
-- machine like '%11%' and
-- status = 'ACTIVE' and
-- type='USER'
;
set linesize 350 trimspool on
column machine format a20
select username, sid,serial#,BLOCKING_SESSION, machine,program,sql_id from v$session where type='USER' and status='ACTIVE';
rem alter system kill session '1030,2892' immediate;
rem right: alter public SYNONYM PROC_NYOCA_UPDATE_ORDER_DTL COMPILE;
rem wrong: alter SYNONYM PUBLIC.PROC_NYOCA_UPDATE_ORDER_DTL COMPILE
select * from v$session where sid=2168;
alter system kill session '1736,9936' immediate;
set linesize 250 trimspool on
set pagesize 5000
select machine,status,count(*) from v$session where type='USER' group by status,machine order by 2,1;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' xxx from v$session where machine = 'myhostname1' and type='USER';
top prev next
Kill Multiple Sessions
select sid,serial# from v$session where sid in ( 319, 243 );
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where sid in ( 319, 243 );
select 'alter system kill session '''||sid||','||serial#||''' immediate;'
from v$transaction t,v$session s
where s.saddr = t.ses_addr
-- and status = 'ACTIVE'
-- and rownum < 50
order by sid,serial#;
alter system kill session '243,545' immediate;
top prev next
Active Session
select COUNT(*) from v$session where status='ACTIVE' and type='USER' and sid not in ( select sid from v$mystat );
-- current blocking session
select 'SID ' || blocker.sid || ' running ' || blocker.program ||
' from machine ' || blocker.machine ||
' logged in as ' || blocker.username ||
' has been blocking SID ' || blocked.sid ||
' for ' || blocked.last_call_et || ' seconds.' "Message", blocked.last_call_et "Seconds Blocked"
from v$session blocked
inner join v$session blocker on blocker.sid = blocked.blocking_session
where blocker.program not like ('ORACLE.EXE%')
order by blocked.blocking_session;
-- current blocking sql
-- ---------------------------
select sql_id from v$session where blocking_session is not null;
-- ---------------------------
select sid, sql_id from v$session s, v$sqlarea st where s.sql_id is not null and s.sql_id = st.sql_id ;
-- ---------------------------
set pagesize 5000
-- current sql text bi session id(sid)
select s.sid, s.sql_id, st.sql_text
from v$session s, v$sqlarea st
where s.sql_id is not null and s.sql_id = st.sql_id (+)
and sid <> sys_context('USERENV','SID') ;
-- cuurent sql text
set pagesize 5000
-- current sql text
select distinct st.sql_text
from v$session s, v$sqlarea st
where s.sql_id is not null and s.sql_id = st.sql_id (+)
and sid <> sys_context('USERENV','SID') ;
-- ---------------------------------
top prev next
My Session ID
select sys_context('USERENV','SID') from dual;
select sid from v$mystat where rownum <=1;
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
select distinct sid from v$mystat;
top prev next
Session Blocker
set linesize 250 trimspool on
set pagesize 1000
column OBJECT_NAME format A32
set long 300000
set longchunk 300000
SELECT distinct l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE
l.id1 = o.object_id (+)
AND l.sid = s.sid
and s.type='USER'
and S.EVENT not in ( 'SQL*Net message from client', 'SQL*Net message to client', 'pipe get' )
ORDER BY blocker,sid, type;
-- blocking session by count
select blocking_session, count(*) cnt
from v$session where blocking_session is not NULL
group by blocking_session
order by cnt desc, blocking_session
;
-- kill blocking session
select 'alter system kill session ''' || sid || ',' || serial# ||''' immediate;' || '-- ' || machine || ' ' || program xxx
from v$session where
sid in (
SELECT s.blocking_session blocker
FROM v$lock l, dba_objects o, v$session s
WHERE
l.id1 = o.object_id (+)
AND l.sid = s.sid
and s.type='USER'
and S.EVENT not in ( 'SQL*Net message from client', 'SQL*Net message to client', 'pipe get' )
);
set linesize 250 trimspool on
set pagesize 1000
select
(select username||'-'||machine||'-'||sql_id from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username||'-'||machine||'-'||sql_id from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
select
(select username from v$session where sid=a.sid) blocker_username,
a.sid,
(select sql_id from v$session where sid=a.sid ) blocker_sql_id,
' is blocking ',
(select username from v$session where sid=b.sid) blockee_username,
b.sid,
(select sql_id from v$session where sid=b.sid ) blockee_sql_id
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
select d.*, l.* from v$locked_object l, dba_objects d where l.object_id=d.object_id;
SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE
l.id1 = o.object_id (+)
AND l.sid = s.sid
and s.type='USER'
and S.EVENT not in ( 'SQL*Net message from client', 'SQL*Net message to client', 'pipe get' )
ORDER BY sid, type;
select * from v$session where blocking_session is not null;
select * from v$session where sid in (
select blocking_session from v$session where blocking_session is not null);
select sql_fulltext from v$sqlarea where sql_id = '2tudndn5z1j4g';
select * from v$locked_object where session_id in ( 1597, 1110 );
select * from dba_objects where object_id = 50127;
-- ----------------------------------------------------------------------------------------
SELECT distinct p.username username ,
p.pid pid ,
s.sid sid ,
s.serial# serial ,
p.spid spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request),
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2 ,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
AND S.TYPE = 'USER'
AND S.EVENT not in ( 'SQL*Net message from client', 'SQL*Net message to client', 'pipe get' )
-- AND u.name = 'GME'
-- AND (:USER_NAME is null or s.username LIKE upper(:USER_NAME))
ORDER BY p.username, p.pid, p.spid, ora, DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type );
-- ----------------------------------------------------------------------------------------
(TM with LMODE=3)
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
full table locks (TM)
row-level locks (TX)
row exclusive lock (RX)
subexclusive table lock (SX)
row share lock (RS)
subshare table lock (SS)
share table lock (S)
share row exclusive table lock (SRX)
share-subexclusive table lock (SSX)
exclusive table lock (X)
set echo off
col sid form 9999
col id1 form 9999999999
col id2 form 999999999
col lmode head "Lock Held" form a14
col request1 head "Lock Request" form a16
col type head "Lock Type" form a15
col ctime head "Time|Held" form 999,999,999,999,999
col block head "No Of |Sessions|Waiting|For This|Lock" form 99999
set linesize 350 trimspool on
column sid format 999999
set pagesize 5000
select sid,
DECODE(TYPE,
'BL','Buffer hash table',
'CF','Control File Transaction',
'CI','Cross Instance Call',
'CS','Control File Schema',
'CU','Bind Enqueue',
'DF','Data File',
'DL','Direct-loader index-creation',
'DM','Mount/startup db primary/secondary instance',
'DR','Distributed Recovery Process',
'DX','Distributed Transaction Entry',
'FI','SGA Open-File Information',
'FS','File Set',
'IN','Instance Number',
'IR','Instance Recovery Serialization',
'IS','Instance State',
'IV','Library Cache InValidation',
'JQ','Job Queue',
'KK','Redo Log "Kick"',
'LS','Log Start/Log Switch',
'MB','Master Buffer hash table',
'MM','Mount Definition',
'MR','Media Recovery',
'PF','Password File',
'PI','Parallel Slaves',
'PR','Process Startup',
'PS','Parallel Slaves Synchronization',
'RE','USE_ROW_ENQUEUE Enforcement',
'RT','Redo Thread',
'RW','Row Wait',
'SC','System Commit Number',
'SH','System Commit Number HWM',
'SM','SMON',
'SQ','Sequence Number',
'SR','Synchronized Replication',
'SS','Sort Segment',
'ST','Space Transaction',
'SV','Sequence Number Value',
'TA','Transaction Recovery',
'TD','DDL enqueue',
'TE','Extend-segment enqueue',
'TM','DML enqueue',
'TS','Temporary Segment',
'TT','Temporary Table',
'TX','Transaction',
'UL','User-defined Lock',
'UN','User Name',
'US','Undo Segment Serialization',
'WL','Being-written redo log instance',
'WS','Write-atomic-log-switch global enqueue',
'XA','Instance Attribute',
'XI','Instance Registration',
decode(substr(TYPE,1,1),
'L','Library Cache ('||substr(TYPE,2,1)||')',
'N','Library Cache Pin ('||substr(TYPE,2,1)||')',
'Q','Row Cache ('||substr(TYPE,2,1)||')',
'????')) TYPE,
id1,id2,
decode(lmode,
0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)') lmode,
decode(request,
0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)') request1,
ctime, block
from
v$lock
where sid>5
and type not in ('MR','RT')
order by decode(request,0,0,2),block,5
;
-- ----------------------------------------------------------------------------------------
select /*+ ORDERED */
blocker.sid blocker_sid
, waiting.sid waiting_sid
, TRUNC(waiting.ctime/60) min_waiting
, waiting.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock waiting
where waiting.type='TX'
and waiting.block = 0
and waiting.id1 = blocker.id1;
-- ----------------------------------------------------------------------------------------
top prev next
Parallel Queries
ALTER SESSION ENABLE PARALLEL QUERY parallel 32;
ALTER SESSION FORCE PARALLEL QUERY parallel 32;
ALTER SESSION DISABLE PARALLEL QUERY;
ALTER SESSION ENABLE PARALLEL QUERY parallel 32;
ALTER SESSION ENABLE PARALLEL DML parallel 32;
ALTER SESSION ENABLE PARALLEL DDL parallel 32;
ALTER SESSION FORCE PARALLEL QUERY parallel 32;
ALTER SESSION FORCE PARALLEL DML parallel 32;
ALTER SESSION FORCE PARALLEL DDL parallel 32;
ALTER SESSION DISABLE PARALLEL QUERY;
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DDL;
top prev next
Execution Plan
set autotrace traceonly
select INST_ID,SQL_ID,POSITION,NAME,VALUE_STRING
from GV$SQL_BIND_CAPTURE
where sql_id='xxxxxxxx' and inst_id=1 order by POSITION;
set linesize 180 pagesize 500 trimspool on
define MY_TABLENAME=CONTENT_DATA
column owner new_value MY_TABLEOWNER
select owner, table_name, last_analyzed, num_rows from dba_tables where table_name = '&&MY_TABLENAME';
select count(*) from &&MY_TABLEOWNER..&&MY_TABLENAME;
define my_sql_id=950s3vptuyc79
set linesize 2000 trimspool on
set pagesize 1000
set long 300000000
set longchunk 300000000
select * from TABLE(dbms_xplan.display_cursor('&&my_sql_id',null,'ALL ALLSTATS LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
select * from TABLE(dbms_xplan.DISPLAY_AWR('&&my_sql_id',null,null,'ALL ALLSTATS LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
define my_sample_id=99999
column max_sample_id new_value my_sample_id
select max(sample_id) max_sample_id from V$ACTIVE_SESSION_HISTORY;
define my_sample_id
SELECT a.sql_id, count(*) cnt, max(sample_id) max_sample_id, max(sample_time) max_sample_time , min(sample_id) min_sample_id, min(sample_time) min_sample_time
FROM V$ACTIVE_SESSION_HISTORY a ,v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and SAMPLE_ID > &&my_sample_id - 10000
and SAMPLE_ID < &&my_sample_id
group by a.sql_id
;
select sql_id, first_load_time, last_load_time, last_active_time, executions
from v$sqlarea
where executions > 1000
and to_date(first_load_time, 'YYYY-MM-DD/hh24:mi:ss') > sysdate - 3/24
order by executions desc;
explain plan for SELECT ....
set linesize 180 trimspool on
set pagesize 5000
set long 300000000
set longchunk 300000000
select * from TABLE(dbms_xplan.display());
select * from TABLE(dbms_xplan.display_cursor('my_sql_id',null,'TYPICAL'));
select * from TABLE(dbms_xplan.display_cursor('my_sql_id',null,'ALL ALLSTATS LAST +PEEKED_BINDS -PROJECTION -ALIAS'));
select * from TABLE(dbms_xplan.DISPLAY_AWR('dwwtvrs26mbcv',null,null,'TYPICAL'));
in sqlplus
set timing on
set linesize 250 trimspool on
set autotrace traceonly
set autotrace traceonly explain
set autotrace traceonly statistics
-- sql in shared_pool
define my_sql_id=caz985ctdm9dk
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&my_sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&my_sql_id.' ORDER BY 1, 2;
-- purge sql in shared_pool
define my_sql_id=caz985ctdm9dk
BEGIN
FOR i IN (SELECT address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&my_sql_id.')
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
END LOOP;
END;
/
top prev next
export and import
to expdp/impdp
select object_type, count(*) from dba_objects where owner = 'AFFAIRWHERE' group by object_Type;
set head off
set pagesize 0
set verify off
set feedback off
set linesize 180 trimspool on
select 'drop '||object_type||' '||owner||'.'|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE;',';')
from dba_objects
where owner='AFFAIRWHERE' and object_type not in ( 'INDEX', 'CONSTRAINT', 'LOB', 'LOB PARTITION' );
spool drop_objects.sql
/
spool off
-- suppress the questionable statistics messages
-- EXP-00091: Exporting questionable statistics.
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
for windows
set NLS_LANG=ENGLISH_UNITED KINGDOM.WE8ISO8859P1
set NLS_LANG=ENGLISH_UNITED^ KINGDOM.WE8ISO8859P1
set NLS_LANG=_.WE8ISO8859P1
exp userid='/ as sysdba ' buffer=10000000 feedback=10000 file=AFFAIRWHERE.dmp log=AFFAIRWHERE.log owner=AFFAIRWHERE
exp userid='/ as sysdba' buffer=10000000 feedback=10000 file=10g_prod_db_20140902.dmp log=10g_prod_db_20140902.log full=y
imp userid='/ as sysdba ' buffer=10000000 feedback=10000 file=AFFAIRWHERE.dmp log=AFFAIRWHERE_imp.log fromuser=AFFAIRWHERE touser=AFFAIRWHERE ignore=y
-- EXP-00091: Exporting questionable statistics.
select 'export NLS_LANG=' || a.value || '_' || b.value || '.' || c.value NLS_LANG
from
NLS_DATABASE_PARAMETERS a,
NLS_DATABASE_PARAMETERS b,
NLS_DATABASE_PARAMETERS c
where
a.PARAMETER = 'NLS_LANGUAGE' and
b.PARAMETER = 'NLS_TERRITORY' and
c.PARAMETER = 'NLS_CHARACTERSET';
select table_name from dict where table_name like '%NLS%';
select * from NLS_DATABASE_PARAMETERS;
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
echo "purge dba_recyclebin;"|sqlplus "/ as sysdba"
touch ${ORACLE_SID}_`date +'%y%m%d'`.txt|ls -lart ${ORACLE_SID}_`date +'%y%m%d'`.txt
exp userid=\'/ as sysdba \' buffer=10000000 feedback=10000 file=${ORACLE_SID}_`date +'%y%m%d'`.dmp log=${ORACLE_SID}_`date +'%y%m%d'`.log full=y
exp help=y
-- full
exp userid=\'/ as sysdba \' file=mysid.dmp log=mysid.log full=y
exp userid=\'/ as sysdba \' buffer=10000000 feedback=10000 file=ntmkd1.dmp log=ntmkd1.log full=y
-- multiple owners or schemas
exp userid=\'/ as sysdba \' file=xxx.dmp log=xxx.log owner=bocadev,datana_dev,bocaqa,datana_qa
exp userid=\'/ as sysdba \' buffer=10000000 feedback=10000 file=xxx.dmp log=xxx.log owner=bocadev,datana_dev,bocaqa,datana_qa
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
exp userid=\'/ as sysdba \' buffer=10000000 feedback=10000 file=cleanup_20140421.dmp log=cleanup_20140421.dmp.log tables=CPRO.CL_GROUP_BLOCK,CPRO.CL_GUEST_INFO,CPRO.CL_RESORT_INFO
imp userid=\'/ as sysdba \' buffer=10000000 feedback=10000 file=xxx.dmp log=xxx_imp.log fromuser=bocadev,datana_dev,bocaqa,datana_qa touser=bocadev,datana_dev,bocaqa,datana_qa ignore=y
imp userid=\'/ as sysdba \' file=xxx.dmp log=xxx_imp.log fromuser=bocadev,datana_dev,bocaqa,datana_qa touser=bocadev,datana_dev,bocaqa,datana_qa ignore=y
imp ignore=y feedback=1000 commit=y
set long 300000000
set longchunk 300000000
select s.sql_id, s.serial#,t.sql_fulltext
from v$sqlarea t, v$session s
where
s.sql_id=t.sql_id and
s.program like '%imp@%';
-- monitoring space issues when doing the import
alter system set undo_retention=2400;
alter system set undo_retention=0;
-- Unexpired undo can be secured by setting the RETENTION GUARANTEE option
-- when creating the Undo tablespace (see Undo Sizing).
set long 300000000
set longchunk 300000000
select dbms_metadata.get_ddl('TABLESPACE', 'UNDOTBS') from dual;
select sum(UNXPSTEALCNT),sum(SSOLDERRCNT) from V$UNDOSTAT;
alter system set resumable_timeout 36000;
alter system set resumable_timeout 3600;
-- permission
select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE from dba_tab_privs where owner='MYUSER' and TABLE_NAME='MY_TABLE';
select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE from dba_tab_privs where owner='HPSUP' and TABLE_NAME='STG_BI_NR_USE_CHRG_INT'
select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE from dba_tab_privs where owner='HPSUP' and TABLE_NAME='STG_BI_NR_ACTV_CHRG_INT'
grant select on myschema.mytable to myschema1;
grant select on hpsup.STG_BI_NR_USE_CHRG_INT to hpsup_select;
grant select on hpsup.STG_BI_NR_actv_CHRG_INT to hpsup_select;
-- count specific tables and/or index for progress
select owner, index_name from dba_indexes where table_name='MY_TABLE';
select owner, index_name from dba_indexes where table_name='STG_BI_NR_ACTV_CHRG_INT';
select count(*) from hpsup.STG_BI_NR_ACTV_CHRG_INT;
-- permission issues when doing the import
-- possible grant users need to be created:
select * from (
select username, 'user' otype from dba_users
where username in (
select distinct grantee from dba_tab_privs where owner in ('DBPBR', 'WEBVC', 'BIRCH', 'BIRCH_APP'))
union
select role, 'role' otype from dba_roles
where role in (
select distinct grantee from dba_tab_privs where owner in ('DBPBR', 'WEBVC', 'BIRCH', 'BIRCH_APP'))
)
where username not in ('DBPBR', 'WEBVC', 'BIRCH', 'BIRCH_APP')
order by otype,username;
# -- norow or metadata only
exp userid=\'/ as sysdba \' buffer=10000000 feedback=10000 file=seat_solaris_20160310.dmp log=seat_solaris_20160310.log full=y ROWS=n
top prev next
expdp and impdp
to export/import
-- clean up the last failed expdp file
column object_name format a32
select owner, object_name, created
from dba_objects
where
-- owner='MYEXPDPUSER' and
table_name like 'SYS_EXPORT_FULL_%' and
object_type='TABLE'
order by created;
set linesize 380 trimspool on pagesize 100
-- define MYUSERNAME=MYUSER
-- list tables
SELECT o.owner || '.' || object_name || ';' cmd
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner = '&&MYUSERNAME'
AND o.owner=j.owner_name
AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%'
AND j.job_name LIKE 'SYS_EXPORT_FULL%'
AND J.STATE = 'NOT RUNNING'
AND O.CREATED < SYSDATE - 1
order by o.created;
-- drop table statements
SELECT 'DROP TABLE '|| o.owner||'.'||object_name||' ;' xdrop ,' -- ', O.CREATED
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner = '&&MYUSERNAME'
AND o.owner=j.owner_name
AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%'
AND j.job_name LIKE 'SYS_EXPORT_FULL%'
AND J.STATE = 'NOT RUNNING'
AND O.CREATED < SYSDATE - 1
ORDER BY O.CREATED;
set linesize 380 trimspool on
set pagesize 500
column DIRECTORY_PATH format A62
select * from dba_directories;
nohup ./expdp_sss_schema_all.sh>expdp_sss_schema_all.sh.nohup.out 2>&1 &
# database level: full=y
expdp dba/test DIRECTORY=data_pump_compress DUMPFILE=sss.dmp LOGFILE=sss_exp.log full=y
# undocumented DataPump parameters
KEEP_MASTER=YES
METRICS=Y
ACCESS_METHOD
ACCESS_METHOD=DIRECT_PATH
ACCESS_METHOD=EXTERNAL_TABLE
TRACE=1FF0300
10300 SHDW: To trace the Shadow process
20300 KUPV: To trace Fixed table
40300 'div' To trace Process services
80300 KUPM: To trace Master Control Process
100300 KUPF: To trace File Manager
200300 KUPC: To trace Queue services
400300 KUPW: To trace Worker process(es)
800300 KUPD: To trace Data Package
1000300 META: To trace Metadata Package
1FF0300 'all' To trace all components, full tracing
-- table level over db link
CREATE PUBLIC DATABASE LINK my_db_link
CONNECT TO DBAS
IDENTIFIED BY "dba_pass1"
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=999.999.999.999)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))';
impdp xxx/yyyy directory=MYDIR LOGFILE=dblink_transfer.log network_link=MY_DB_LINK remap_schema=myschema:myschema TABLES=myschema.mytable
# table level
-- expdp single table, and impdp into difference schema
expdp userid=\"/ as sysdba\" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_A.dmp LOGFILE=EXPDP_A.dmp.log TABLES=mysrcschema.mytable
expdp userid=\"/ as sysdba\" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_A.dmp LOGFILE=EXPDP_A.dmp.log TABLES=mysrcschema.mytable1,mysrcschema.mytable2
# ksh, expdp table level
(\
MY_DATE=`date +'%Y%m%d_%H%M%S'`;\
MY_DB_DIR=CLIENTIMP;\
MY_DB_FILE=DBA_SEGMENT;\
MY_TABLES=DBAS.DBA_SEGMENT_20170928_AF,DBAS.DBA_SEGMENT_20170928_BF;\
echo ${MY_DB_DIR};\
echo "expdp \\"
echo "userid=\\\"/ as sysdba\\\" \\"
echo "DIRECTORY=${MY_DB_DIR} \\"
echo "DUMPFILE=${MY_DB_FILE}_${MY_DATE}.dmp \\"
echo "LOGFILE=${MY_DB_FILE}_${MY_DATE}.dmp.log \\"
echo "TABLES=${MY_TABLES}"
)
# ksh, expdp full level
(\
MY_DATE=`date +'%Y%m%d_%H%M%S'`
MY_DB_DIR=DATA_PUMP_U50
MY_DB_FILE=DBA_SEGMENT
echo "# ${MY_DB_DIR};"
echo "CREATE OR REPLACE DIRECTORY $MY_DB_DIR AS '/u50/oracle/exports/${ORACLE_SID}';"
echo "grant read,write on directory $MY_DB_DIR to DBAS;"
echo "set pagesize 1000"
echo "select * from dba_directories;"
echo "expdp \\"
echo "userid=\\\"/ as sysdba\\\" \\"
echo "DIRECTORY=${MY_DB_DIR} \\"
echo "DUMPFILE=\${ORACLE_SID}_FULL_${MY_DATE}.dmp \\"
echo "LOGFILE=\${ORACLE_SID}_FULL_${MY_DATE}.dmp.log \\"
echo "full=Y"
)
impdp userid=\"/ as sysdba\" DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDP_A.dmp LOGFILE=IMPDP_A.dmp.log TABLES=mysrcschema.mytable REMAP_SCHEMA=mysrcschema:mytgtschema TABLE_EXISTS_ACTION=APPEND
# -- Single Table
select * from dba_directories;
expdp help=y
impdp help=y
expdp userid=\"/ as sysdba\" DIRECTORY=CLIENTIMP \
DUMPFILE=myschema.mytable.dmp \
LOGFILE=myschema.mytable.dmp.log \
TABLES=myschema.mytable
impdp userid=\"/ as sysdba\" DIRECTORY=CLIENTIMP \
REMAP_SCHEMA=nclsea:nclsea \
TABLE_EXISTS_ACTION=replace \
DUMPFILE=myschema.mytable.dmp \
LOGFILE=myschema.mytable_imp.dmp.log \
TABLES=myschema.mytable
# schema level: schemas=MYSCHEMA
expdp dba/test DIRECTORY=data_pump_compress DUMPFILE=sss.dmp LOGFILE=sss_exp.log SCHEMAS=MYSCHEMA
expdp userid=\"/ as sysdba\" DIRECTORY=DATA_PUMP_U01 DUMPFILE=EXPDP_USER_MYSCHEMA_20160511.dmp LOGFILE=EXPDP_USER_MYSCHEMA_20160511.dmp.log SCHEMAS=MYSCHEMA
expdp userid=\"/ as sysdba\" DIRECTORY=DATA_PUMP_U01 DUMPFILE=EXPDP_USER_MYSCHEMA_20160511.dmp LOGFILE=EXPDP_USER_MYSCHEMA_20160511.dmp.log SCHEMAS=MYSCHEMA
# Multi-Schema
expdp userid=\"/ as sysdba\" DIRECTORY=DATA_PUMP_U01 DUMPFILE=EXPDP_USER_MYSCHEMA_20160511.dmp LOGFILE=EXPDP_USER_MYSCHEMA_20160511.dmp.log SCHEMAS=MYSCHEMA,MYSCHEMA1
export ORACLE_SID=ORCL
date
expdp username/password dumpfile=orcl_noprallel.dmp logfile=impdp_orcl_noprallel.log schemas=xxx directory=MYDIR
# exclude=statistics
impdp username/password dumpfile=orcl_noprallel.dmp logfile=impdp_orcl_noprallel.log schemas=xxx REMAP_SCHEMA=xxx:xxx directory=MYDIR
# metrics=y logtime=all exclude=statistics
date
-- impdp monitoring status
-- check_archive_log_switching and alert_sid.log: select log_mode from v$database;
-- --
set pagesize 4000
set pagesize 1000
select table_name from dba_tables where table_name like '%PORT%' and owner='MYIMPUSER';
select * from dba_tables where table_name = 'SYS_IMPORT_SCHEMA_01';
impdp MYIMPUSER/MYIMPPASS attach=SYS_IMPORT_SCHEMA_01
>STATUS
echo "exit"|impdp MYUSERNAME/MYPASSWORD attach=SYS_IMPORT_SCHEMA_01 2>&1|egrep -i "object|Completed Objects|Bytes Processed|Percent Done"
select table_name from dba_tables where owner='&&USERNAME' order by 1;
select table_name from dba_tables where owner='&&USERNAME' and table_name like '%IMP%' order by 1;
select * from "MYUSERNAME"."SYS_IMPORT_SCHEMA_01";
select last_update x, a.* from "DBAS"."SYS_IMPORT_SCHEMA_01" a where last_update is not null order by a.last_update desc;
# impdp attach=SYS_IMPORT_SCHEMA_01
# exit
-- -- disable ddl_triggers on both dbas and fscm schema
-- alter trigger XXX.ddl_trigger disable;
-- set pagesize 5000
-- select owner, trigger_name, status from dba_triggers where trigger_name='DDL_TRIGGER';
set pagesize 5000
select * from dba_directories;
select owner, count(*) cnt from dba_objects group by owner order by owner;
show parameter resum
set pagesize 5000
select * from dba_resumable;
alter system set resumable_timeout=36000;
select count(*) from dba_indexes where owner='XXX';
comments in parfile:
Table names specified on the command line cannot include a pound sign (#),
unless the table name is enclosed in quotation marks.
Similarly, in the parameter file,
if a table name includes a pound sign (#),
then the Export utility interprets the rest of the line as a comment,
unless the table name is enclosed in quotation marks.
TABLES=(emp#, dept, mydata)
TABLES=('"emp#"', dept, mydata)
In command-line mode:
TABLES='\"Emp\"'
In parameter file mode:
TABLES='"Emp"'
-- check expdp and impdp job status
SELECT * FROM DBA_DATAPUMP_JOBS;
SELECT owner_name, job_name, state, operation FROM DBA_DATAPUMP_JOBS;
# one liner
echo exit|expdp \" / as sysdba \" attach=SYS_EXPORT_TABLE_01
echo exit|impdp \" / as sysdba \" attach=SYS_IMPORT_TABLE_01
impdp attach=SYS_IMPORT_TABLE_01
impdp scott/tiger parfile my_imp.par attach=job_name
Import> status
Import> stop_job=immediate
Import> kill_job
Import> start_job
Import> status
impdp userid=\"/ as sysdba\" DIRECTORY=DATA_PUMP_U01 DUMPFILE=EXPDP_USER_NCLFARE_20160511.dmp SCHEMAS=MYSCHEMA LOGFILE=IMPDP_USER_MYSCHEMA_20160511.log CONTENT=ALL
nohup ./impdp_sss_schema_all.sh>impdp_sss_schema_all.sh.nohup.out 2>&1 &
impdp dba/test DUMPFILE=data_pump_compress:xxx.dmp LOGFILE=sss_imp.log SCHEMAS=sss REMAP_SCHEMA=sss:sss
impdp dba/test DUMPFILE=data_pump_compress:xxx.dmp LOGFILE=sss_imp.log SCHEMAS=sss REMAP_SCHEMA=sss:sss1
#To Old Schema:
impdp \"/ as sysdba\" dumpfile=expdp_myschema_20161130.dmp DIRECTORY=DATA_PUMP_U50 logfile=expdp_myschema_20161130_impdp.dmp.log SCHEMAS=MYSCHEMA REMAP_SCHEMA=MYSCHEMA:MYSCHEMAO
# -----------------------------------expdp impdp -----------------------------------------
set pagesize 5000
set linesize 350 trimspool on
set long 300000000
set longchunk 300000000
set serveroutput on
select * from dba_directories;
select dbms_metadata.get_ddl ( 'DIRECTORY', 'XMLDIR' ) from dual;
CREATE OR REPLACE DIRECTORY "DATA_PUMP_U50" AS '/u50/oracle/exports/cprm'
grant read,write on directory DATA_PUMP_U50 to DBAS;
-- tables
expdp userid=dbas dumpfile=expdp_cleanup_20140421.dmp DIRECTORY=DATA_PUMP_U50 logfile=expdp_cleanup_20140421.dmp.log tables=CPRO.CL_GROUP_BLOCK,CPRO.CL_GUEST_INFO,CPRO.CL_RESORT_INFO,CPRO.CL_RES_ITEM,CPRO.PR_RES,CPRO.CL_PRICING_CONTROLS,CPRO.CL_GROUP_MASTER,CPRO.PR_HORIZON,CPRO.CL_NON_COMMISSIONABLE_FARE,CPRO.CL_SAILING_CALENDAR
-- schemas
expdp "'/ as sysdba'" dumpfile=expdp_NCLCPROP_20150528.dmp DIRECTORY=TEST logfile=expdp_NCLCPROP_20150528.dmp.log SCHEMAS=NCLCPROP
impdp "'/ as sysdba'" dumpfile=expdp_NCLCPROP_20150528.dmp DIRECTORY=CLIENTIMP logfile=impdp_NCLCPROP_20150528.dmp.log SCHEMAS=NCLCPROP REMAP_SCHEMA=NCLCPROP:NCLCPROP
select sum(bytes) from dba_segments where owner='NCLFARE';
expdp "'/ as sysdba'" dumpfile=expdp_NCLFARE.dmp DIRECTORY=CLIENTIMP logfile=expdp_NCLFARE_20160310.dmp.log SCHEMAS=NCLFARE,NCLFAREEXEC
impdp "'/ as sysdba'" dumpfile=expdp_NCLFARE.dmp DIRECTORY=CLIENTIMP logfile=impdp_expdp_NCLFARE.dmp.log SCHEMAS=NCLFARE,NCLFAREEXEC REMAP_SCHEMA=NCLFARE:NCLFARE,NCLFAREEXEC:NCLFAREEXEC
-- compression, multiple files and file size limitation
expdp userid=dbas dumpfile=expdp_sea_20150729_%u.dmp filesize=10G DIRECTORY=DATA_PUMP_SWTR logfile=expdp_sea_20150729.log COMPRESSION=ALL CONTENT=ALL SCHEMAS=SEA
-- full db
expdp \
"'/ as sysdba'" \
DIRECTORY=DATA_PUMP_U50 \
DUMPFILE=full_${T_SID}_`date +%Y%m%d`_expdp.dmp \
LOGFILE=full_${T_SID}_`date +%Y%m%d`_expdp.log \
FULL=Y \
CONTENT=metadata_only \
EXCLUDE=STATISTICS
consistent=y is back
expdp userid=dbas dumpfile=expdp_cleanup_20140421_CL_SAILING_CALENDAR.dmp DIRECTORY=DATA_PUMP_U50 logfile=CPRO.CL_SAILING_CALENDAR
expdp userid=dbas dumpfile=expdp_20140421_CALENDAR.dmp logfile=expdp_20140421_CALENDAR.dmp.log DIRECTORY=DATA_PUMP_U50 tables=CPRO.CL_SAILING_CALENDAR
impdp userid=dbas/dbay_webm_14 DIRECTORY=WORK_DIR dumpfile=expdp_webr_NCLWEB_201407132345_01.dmp logfile=expdp_webr_NCLWEB_201407132345_01.dmp.log schemas=NCLWEB REMAP_SCHEMA=NCLWEB:NCLWEB
set pagesize 1000
set linesize 1000
select * from DBA_DATAPUMP_JOBS;
select * from DBA_DATAPUMP_SESSIONS;
expdp attach=
drop table DBAS.SYS_EXPORT_FULL_01;
drop table DBAS.SYS_EXPORT_FULL_02;
drop table SYS.SYS_EXPORT_FULL_01;
create directory test as '/u50/oracle';
select * from dba_directories;
expdp \"/ as sysdba\" dumpfile=expdp_NCLCPROP_20150702.dmp DIRECTORY=TEST logfile=expdp_NCLCPROP_20150702.dmp.log SCHEMAS=NCLCPROP
SYS CLIENTIMP
/u50/oracle/20150120
impdp \"/ as sysdba\" dumpfile=expdp_NCLCPROP_20150702.dmp DIRECTORY=CLIENTIMP logfile=expdp_NCLCPROP_20150702_imp.dmp.log SCHEMAS=NCLCPROP REMAP_SCHEMA=NCLCPROP:NCLCPROP
impdp \"/ as sysdba\" dumpfile=expdp_NCLCPROP_20150702.dmp DIRECTORY=CLIENTIMP logfile=expdp_NCLCPROP_20150702_imp.dmp.log SCHEMAS=NCLCPROP REMAP_SCHEMA=NCLCPROP:NCLCPROP
top prev next
Oracle Jobs
exec sys.dbms_ijob.remove(1246);
exec sys.dbms_ijob.BROKEN(1286, FALSE );
Howevere there is undocumented package called DBMS_IJOB that enables you to manipulate
other users jobs (provided that you have EXECUTE ANY PROCEDURE privilege).
For example, you can remove any job (not only those owned by you) from the job queue
by executing procedure SYS.DBMS_IJOB.REMOVE(:job_id)
select table_name from dict where table_name like '%JOB%';
set pagesize 2000
set feedback on
set linesize 350 trimspool on
select table_name from dict where table_name like '%JOB%';
select * from V$SCHEDULER_RUNNING_JOBS;
select * from DBA_JOBS_RUNNING;
-- select * from DBA_SCHEDULER_JOB_RUN_DETAILS;
select * from v$session where type='USER' and status='ACTIVE';
select * from dba_jobs;
select * from DBA_JOBS_RUNNING;
select BROKEN from dba_jobs where job=1246;
1): Don't forget commit; after job submit or job change.
2): run against time migration
2a): if a job can finished in a minute or in an hour
exec dbms_job.INTERVAL(162, 'trunc(SYSDATE+1/48,''MI'')');
exec dbms_job.INTERVAL(162, 'trunc(SYSDATE+1/48,''HH'')');
2b): if a job can finished in 15 or 30 minutes
exec dbms_job.INTERVAL(162, 'trunc(sysdate, ''hh'') + ceil(to_char(sysdate+1/1440, ''mi'')/15)*15/1440');
exec dbms_job.INTERVAL(162, 'trunc(sysdate, ''hh'') + ceil(to_char(sysdate+1/1440, ''mi'')/30)*30/1440');
select sysdate t,sysdate+1/48,
trunc(sysdate, 'hh') + ceil(to_char(sysdate, 'mi')/30)*30/1440 nearest_30_min
from dual;
select sysdate t,sysdate+1/48,
trunc(sysdate+1/48, 'hh') + ceil(to_char(sysdate+1/48, 'mi')/30)*30/1440 nearest_30_min
from dual;
alter system set job_queue_processes=20;
ALTER SYSTEM SET AQ_TM_PROCESSES=0
select BROKEN,job, log_user,priv_user,schema_user, what,INTERVAl, next_date, next_sec from dba_jobs where job=19;
select job,broken, LAST_DATE, LAST_SEC, THIS_DATE, THIS_SEC, NEXT_DATE, NEXT_SEC,INTERVAL from user_jobs;
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;
SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j WHERE r.JOB = j.JOB;
select j.what, j.job, r.sid from dba_jobs j, dba_jobs_running r where j.job in (select job from dba_jobs_running) and j.job=r.job order by j.what;
select * from dba_jobs_running order by job;
select * from dba_jobs where job=162;
set pagesize 3000
SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ';
exec dbms_job.BROKEN(19,true);
exec dbms_job.remove(19);
exec dbms_job.RUN(162);
exec dbms_job.INTERVAL(162, 'sysdate + 1/48');
exec dbms_job.next_date(162, '07-MAY-13 13:30:00');
VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,'begin update users set lndirect_login_flag = ''Y'' where lndirect_login_flag = ''N'' and contact_uid > 20; commit; end;',sysdate,'trunc(sysdate,''hh'') + 1/24 + 10 /24/60');
commit;
dbms_output.put_line(v_jobnum);
END;
/
PRINT jobno
dbms_job.submit(:jobno, 'statspack.snap;', sysdate, 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);
-- dealing with job owned by other users
dbms_ijob.remove(jobnum);
dbms_ijob.broken(jobnum,true);
dbms_ijob.run(jobnum);
-- disable job queue
show parameter pfile
job_queue_processes
aq_tm_processes
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
select * from dba_jobs;
select * from DBA_SCHEDULER_JOBS;
1): Don't forget commit; after job submit or job change.
2): run against time migration
2a): if a job can finished in a minute or in an hour
exec dbms_job.INTERVAL(162, 'trunc(SYSDATE+1/48,''MI'')');
exec dbms_job.INTERVAL(162, 'trunc(SYSDATE+1/48,''HH'')');
2b): if a job can finished in 15 or 30 minutes
exec dbms_job.INTERVAL(162, 'trunc(sysdate, ''hh'') + ceil(to_char(sysdate+1/1440, ''mi'')/15)*15/1440');
exec dbms_job.INTERVAL(162, 'trunc(sysdate, ''hh'') + ceil(to_char(sysdate+1/1440, ''mi'')/30)*30/1440');
select sysdate t,sysdate+1/48,
trunc(sysdate, 'hh') + ceil(to_char(sysdate, 'mi')/30)*30/1440 nearest_30_min
from dual;
select sysdate t,sysdate+1/48,
trunc(sysdate+1/48, 'hh') + ceil(to_char(sysdate+1/48, 'mi')/30)*30/1440 nearest_30_min
from dual;
alter system set job_queue_processes=20;
ALTER SYSTEM SET AQ_TM_PROCESSES=0
select BROKEN,job, log_user,priv_user,schema_user, what,INTERVAl, next_date, next_sec from dba_jobs where job=19;
select job,broken, LAST_DATE, LAST_SEC, THIS_DATE, THIS_SEC, NEXT_DATE, NEXT_SEC,INTERVAL from user_jobs;
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;
SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j WHERE r.JOB = j.JOB;
select j.what, j.job, r.sid from dba_jobs j, dba_jobs_running r where j.job in (select job from dba_jobs_running) and j.job=r.job order by j.what;
select * from dba_jobs_running order by job;
select * from dba_jobs where job=162;
set pagesize 3000
SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ';
exec dbms_job.BROKEN(19,true);
exec dbms_job.remove(19);
exec dbms_job.RUN(162);
exec dbms_job.INTERVAL(162, 'sysdate + 1/48');
exec dbms_job.next_date(162, '07-MAY-13 13:30:00');
VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,'begin update users set lndirect_login_flag = ''Y'' where lndirect_login_flag = ''N'' and contact_uid > 20; commit; end;',sysdate,'trunc(sysdate,''hh'') + 1/24 + 10 /24/60');
commit;
dbms_output.put_line(v_jobnum);
END;
/
PRINT jobno
dbms_job.submit(:jobno, 'statspack.snap;', sysdate, 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);
top prev next
utl_mail
alter system set SMTP_OUT_SERVER=forwader.xxxmiami.xxx.com;
alter session set SMTP_OUT_SERVER='forwarder.xxxmiami.xxx.com';
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
exec UTL_MAIL.SEND ( sender => 'myuser@xxx.com', recipients => 'myuser@xxx.com', subject => 'test from fmst', message => 'This is the test' );
top prev next
oraenv mysid
-- oraenv using /etc/oratab or /var/opt/oracle/oratab
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv
-- ---------------------------------------------------------------------
. db orcl
-- ---------------------------------------------------------------------
export ORAENV_ASK=NO
if [ ! -z "$1" ]
then
export ORACLE_SID=$1
. oraenv
fi
export LD_LIBRARY_PATH=$ORACLE_HOME/network/lib
export ORACLE_DOC=$ORACLE_HOME/oradoc
export LIBPATH=$ORACLE_HOME/lib64:$ORACLE_HOME/lib:/usr/lib/cobol/coblib
if [[ -d /u01/oracle/DDRIVERS_odbc32v53 ]] ; then
export LIBPATH=$ORACLE_HOME/lib64:$ORACLE_HOME/lib:/usr/lib/cobol/coblib:/u01/oracle/DDRIVERS_odbc32v53/lib
fi
top prev next
tnsping
1): dos
tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=167.118.123.234)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ratd)))"
tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=167.118.123.234)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ratd)))"
set TNS_HOSTNAME=167.118.123.234
set TNS_PORT=1521
set TNS_SID=ratd
tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=167.118.123.234)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=%TNS_SID%)))"
echo tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=%TNS_HOSTNAME%)(PORT=%TNS_PORT%)))(CONNECT_DATA=(SERVICE_NAME=%TNS_SID%)))"
top prev next
segment space
-- Segment Space: Single Table
undefine my_owner
undefine my_table
define my_owner=MYOWNER
define my_table=MYTABLE
set verify off
set linesize 132 trimspool on
set pagesize 500
column SEGMENT_NAME format A32
column SEGMENT_SIZE_IN_GB format 999.99
select owner, segment_name,segment_type,sum(bytes)/(1024*1024*1024) segment_size_in_gb
from dba_segments
where
owner = '&&my_owner' and
segment_name in ( '&&my_table' )
group by owner, segment_name,segment_type
union
select owner, segment_name,segment_type,sum(bytes)/(1024*1024*1024) segment_size_in_gb
from dba_segments
where
(owner, segment_name ) in ( select t.owner, i.index_name
from dba_tables t, dba_indexes i
where
t.owner = '&&my_owner' and
t.table_name = '&&my_table' and
i.table_name=t.table_name and
i.table_owner=t.owner
)
group by owner, segment_name,segment_type
order by 4 desc, owner, segment_name,segment_type;
-- segment space: multiple tables
set linesize 380 trimspool on pagesize 5000
column owner format a12
column table_name format a32
column index_name format a32
break on owner on table_name skip 1
COMPUTE SUM LABEL 'TotalByTable' OF BEFORESIZEINMB AFTERSIZEINMB DIFF (BEFORESIZEINMB-AFTERSIZEINMB)/BEFORESIZEINMB ON owner, table_name
-- break on report
-- COMPUTE SUM LABEL 'TotalForAll' OF BEFORESIZEINMB AFTERSIZEINMB DIFF ON REPORT
with tl as
(select owner, table_name from (
select '&&MYOWNER1' owner, '&&MYTABLE1' table_name from dual
union all
select '&&MYOWNER2' owner, '&&MYTABLE2' table_name from dual
)
),
bf as
(
select *
from DBAS.DBA_SEGMENTS_20191013_BF_REORG
),
af as
(
select *
from DBA_SEGMENTS
)
select After.Owner,Before.table_name, Before.index_name, Before.segment_type,
Before.tablespace_name BEFORE_TSP,
After.Tablespace_name AFTER_TSP,
round(before.SIZEINMB,0) BEFORESIZEINMB ,
round(After.SIZEINMB,0) AFTERSIZEINMB,
round(before.SIZEINMB - After.SIZEINMB,0) Diff,
round((Before.SIZEINMB-After.SIZEINMB)*100/Before.SIZEINMB,0) DiffPct
from
(
select 'After' SEGMENT_TIME, a.owner,A.bytes/(1024*1024 ) SIZEINMB ,
A.segment_name table_name, '----' index_name, A.tablespace_name, A.segment_type
from AF A
where (a.owner,A.segment_name) in ( select owner, table_name from tl )
union
select 'After' SEGMENT_TIME, a.owner,A.bytes/(1024*1024 ) SIZEINMB ,
I.table_name table_name, i.index_name, A.tablespace_name, A.segment_type
from AF A, dba_indexes I
where
a.owner=i.owner and a.segment_name=i.index_name and
(a.owner,A.segment_name) in
( select owner, index_name from dba_indexes where (owner,table_name)
in ( select owner, table_name from tl ))
) After ,
(
select 'BEFORE' SEGMENT_TIME, a.owner,A.bytes/(1024*1024 ) SIZEINMB ,
A.segment_name table_name, '----' index_name, A.tablespace_name, A.segment_type
from BF A
where (a.owner,A.segment_name) in ( select owner, table_name from tl )
union
select 'BEFORE' SEGMENT_TIME, a.owner,A.bytes/(1024*1024 ) SIZEINMB ,
I.table_name table_name, i.index_name, A.tablespace_name, A.segment_type
from BF A, dba_indexes I
where
a.owner=i.owner and a.segment_name=i.index_name and
(a.owner,A.segment_name) in
( select owner,index_name
from dba_indexes where (owner,table_name) in
(select owner, table_name from tl )
)
) BEFORE
WHERE Before.table_name = After.table_name
AND Before.index_name = After.index_name
AND Before.segment_type = After.segment_type
-- AND Before.SIZEINMB <> After.SIZEINMB
order by owner, table_name, index_name
;
top prev next
Get DB Links
set linesize 380 trimspool on pagesize 5000
column owner format a32
column db_link format a32
column username format a32
column host format a32
select owner, db_link, username, host, created from dba_db_links;
set long 300000
set longchunk 300000
select dbms_metadata.get_ddl('DB_LINK', 'XXX', 'PUBLIC') from dual;
CREATE PUBLIC DATABASE LINK my_db_link_name
CONNECT TO my_remote_db_username
IDENTIFIED BY "my_remote_db_password"
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=999.999.999.999)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';
more get_dblinks.sh
#!/usr/bin/ksh
sqlplus -S / as sysdba<<EOF|sed 's/^ //g'|sed 's/^ //g'
set pagesize 0 echo on term on feedback
set linesize 1000 trimspool on
set long 3000000
set longchunk 3000000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
-- SELECT dbms_metadata.get_ddl('DB_LINK',db_link, owner) FROM dba_db_links order by owner;
SELECT
'-- ' || '/* ' || owner || ' */' || ' ' || chr(10) || ' ' ||
'-- ' || 'DROP' || decode(owner, 'PUBLIC', ' PUBLIC', '') || ' DATABASE LINK ' ||
decode(owner, 'PUBLIC', '', '') || db_link || ';' || chr(10) || ' ' ||
to_char(dbms_metadata.get_ddl('DB_LINK',db_link,owner)) stmt FROM dba_db_links
-- where owner not in ( 'PUBLIC' )
order by owner,db_link;
EOF
-- verify
select * from dba_db_links order by db_link, owner;
select * from dba_db_links;
set long 300000
set longchunk 300000
set linesize 380 trimspool on
set pagesize 5000
select dbms_metadata.get_ddl('DB_LINK', '&MY_DB_LINK', 'PUBLIC' ) from dual;
set pagesize 0
set linesize 3000 trimspool on
set feedback off
set long 3000000
set longchunk 30000000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
-- This is wrong for create private db link
select
'DROP' || decode(owner, 'PUBLIC', ' PUBLIC', '') || ' DATABASE LINK ' || decode(owner, 'PUBLIC', '', OWNER ||'.') || db_link || ';',
replace(dbms_metadata.get_ddl('DB_LINK',db_link, owner), 'CREATE DATABASE LINK ', 'CREATE DATABASE LINK ' || owner ||'.') xxx
from dba_db_links;
Please create a DB link between two databases for the same user
select * from user_tables@myremotedb;
connect myusername/mypassword
set linesize 350 trimspool on
set pagesize
set long 300000
set longchunk 300000
column db_link format a12
column host format a12
select * from dba_db_links order by db_link,owner,username;
select dbms_metadata.get_ddl('DB_LINK', 'XXX', 'PUBLIC') from dual;
select dbms_metadata.get_ddl('DB_LINK', 'XXX', 'MYSCHEMA') from dual;
CREATE PUBLIC DATABASE LINK "MYREMOTEDB"
CONNECT TO "MYUSERNAME" IDENTIFIED BY "MYPASSWORD"
USING 'myremotedb';
top
top prev next
Recreate Control File
alter database backup controfile to trace;
alter database backup controfile to trace noresetlogs;
alter database backup controfile to trace resetlogs;
alter database backup controfile to trace as '/home/oracle/recreatecontrolfile.sql' noresetlogs;
alter database backup controfile to trace as '/home/oracle/recreatecontrolfile.sql' resetlogs;
alter database backup controlfile to trace as 'my_controlfile.sql' resetlogs;
-- default location $ORACLE_HOME/dbs/my_file_name
alter database backup controlfile to trace as 'my_controlfile.sql' reuse resetlogs;
alter database backup controlfile to trace as 'xxx.sql';
alter database backup controlfile to trace as 'xxx_r.sql' resetlogs;
top
top prev next
rename database file
-- 12c: move datafile online
ALTER DATABASE MOVE DATAFILE '/u03/oracle/data/mysid/users01.dbf' to '/u03/oracle/data/mysid/mysidusers01.dbf';
set linesize 380 trimspool on
select name from v$datafile where name like '%mdb%';
select 'alter database move datafile '''||name || ''' to '''||name || ''';' cmd from v$datafile where name like '%mdb%';
-- gen_rename.sql
set pagesize 0
set linesize 380 trimspool on
set head off
set feedback off
set veri on
set newpage 0
-- spool real_rename.sql
-- ALTER DATABASE RENAME FILE 'file_name_01' to 'file_name_02';
-- alter database rename file '/u50/oracle/data/mydg/mydgtools01.dbf' to '/u50/oracle/data/mydg/mydgtools01.dbf';
select 'alter database rename file '''||name || ''' to '''||replace(name, '/u50', '/u03')|| ''';'
from (
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
);
-- spool off
-- -------------------------------------------------------------------------------------------------
set pagesize 1000
set linesize 380 trimspool on
select 'alter database rename file ''' || name || ''' to ''' || replace(name,'/u03', '/u50') || ''';' cmd
from (
select name from v$datafile union select name from v$tempfile union select member from v$logfile
);
define S_DB=ssid
define T_DB=tsid
set linesize 380 trimspool on feedback off verify off head off newpage 0 pagesize 0 echo on term on
-- -------------------------------------------------------------------------------------------------
select 'mv '|| name || ' ' || replace(name,'&S_DB','&T_DB') from
(
select name from v$datafile
union all
select name from v$tempfile
)
;
top
top prev next
redo logfile member and group
alter database backup controlfile to trace;
ALTER DATABASE ADD LOGFILE MEMBER '/u12/oracle/data/schq/schqlog1b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/u13/oracle/data/schq/schqlog2b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u14/oracle/data/schq/schqlog3b.log' TO GROUP 3;
-- check archivelog group status, switch logfile can change status
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
alter system switch logfile;
-- drop unused redolog group
-- ALTER DATABASE DROP LOGFILE GROUP 1;
-- ALTER DATABASE DROP LOGFILE GROUP 2;
-- ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE
ADD LOGFILE GROUP 4 (
'/u14/oracle/data/mysid/mysidlog4a.log',
'/u11/oracle/data/mysid/mysidlog4b.log'
) SIZE 50M BLOCKSIZE 512;
set linesize 380 pagesize 100 trimspool on
-- select * from v$logfile;
select member, group# from v$logfile order by group#, member;
select
case when member like '%a.log' then 'alter database add logfile group '
|| to_char(a.group#) || '(' end ||
''''|| member || '''' ||
case when member like '%a.log' then ','
else ') SIZE ' || to_char(round(BYTES/(1024*1024),0))
||'M BLOCKSIZE '|| b.BLOCKSIZE||';' end
from v$logfile a, v$log b where a.group# = b.group# order by a.group#, member;
select
case when member like '%a.log' then 'alter database add logfile group ' || to_char(group#) || '(' end ||
''''|| member || '''' ||
case when member like '%a.log' then ','
else ') SIZE 50M BLOCKSIZE 512;' end
from v$logfile order by group#, member;
top
top prev next
init.ora
How To Query And Change The Oracle Hidden Parameters In Oracle 10g ,11g and 12c 315631.1
set linesize 380 trimspool on
column Parameter format A46
column Default_Value format A32
column Session_Value format A32
column Instance_Value format A32
column IS_SESSION_MODIFIABLE format A32
column IS_SYSTEM_MODIFIABLE format A32
set pagesize 5000
SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default_Value",
b.ksppstvl "Session_Value",
c.ksppstvl "Instance_Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
-- AND ksppinm like '%ignore%'
AND b.KSPPSTDF<>'TRUE' /* Default Value */
order by 1
;
SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default_Value",
b.ksppstvl "Session_Value",
c.ksppstvl "Instance_Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
AND ksppinm like '%ignore%'
-- AND b.KSPPSTDF<>'TRUE' /* Default Value */
order by 1;
column IS_SESSION_MODIFIABLE noprint
column IS_SYSTEM_MODIFIABLE noprint
_ignore_desc_in_index
_optimizer_compute_index_stats
_b_tree_bitmap_plans
_enable_minscn_cr
_gby_hash_aggregation_enabled
_library_cache_advice
_optimizer_ignore_hints
_optimizer_invalidation_period
_optimizer_use_feedback
_unnest_subquery
ALTER SYSTEM SET "_optimizer_compute_index_stats"=FALSE SCOPE=MEMORY SID='*';
ALTER SYSTEM RESET "_optimizer_compute_index_stats" SCOPE=MEMORY SID='*';
-- ------------------------------------------------------------------
-- for finding ISPDB_MODIFIABLE :
SELECT a.ksppinm "Parameter",
decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE') ISPDB_MODIFIABLE
FROM x$ksppi a
WHERE a.ksppinm LIKE '/_clusterwide_global_transactions' escape '/';
-- ------------------------------------------------------------------
SET PAGESIZE 60
SET LINESIZE 300
COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50
COLUMN default_value FORMAT A16
COLUMN default_tf FORMAT A16
SELECT ksppinm, ksppstvl,
b.ksppstdfl Default_Value,
b.ksppstdf default_tf
FROM x$ksppi a, x$ksppsv b
WHERE a.indx=b.indx AND substr(ksppinm,1,1) = '_' AND ksppinm like '%ignore%'
ORDER BY ksppinm;
-- ------------------------------------------------------------------
COLUMN name FORMAT A50
COLUMN value FORMAT A50
select
a.ksppinm name,
b.ksppstvl value,
b.ksppstdfl Default_Value,
b.ksppstdf Default_TF,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type
-- ,
-- a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%' escape '\'
order by
name;
-- ------------------------------------------------------------------
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50
SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b
WHERE a.indx=b.indx AND substr(ksppinm,1,1) = '_' AND ksppinm like '%corrupt%'
ORDER BY ksppinm;
-- no default init.ora value
column name format A32
column display_value format A62
set linesize 180 trimspool on pagesize 100
select name, DISPLAY_VALUE from V$PARAMETER where isdefault='FALSE' order by name;
create pfile='pfile_20160713.ora' from spfile;
select table_name from dict where table_name like 'V%PARAM%';
select distinct isdefault from V$PARAMETER;
select isdefault, count(*) from v$parameter group by isdefault;
set linesize 180 trimspool on set pagesize 5000
column name format a32
column value format a72
column display_value format a32
select name, value, decode(display_value, value,null,display_value) display_value
from v$parameter where isdefault='FALSE' order by name;
select ISSPECIFIED, count(*) from v$spparameter group by ISSPECIFIED;
select name, value, decode(display_value, value,null,display_value) display_value
from v$spparameter where ISSPECIFIED='TRUE' order by name;
alter system set sec_case_sensitive_logon=FALSE scope=both;
alter system set deferred_segment_creation=FALSE scope=both;
select name,value,DISPLAY_VALUE,ISDEFAULT,ISMODIFIED from v$parameter where name = 'deferred_segment_creation';
select name,value,DISPLAY_VALUE,ISDEFAULT,ISMODIFIED from v$parameter where name = 'sec_case_sensitive_logon';
select * from v$parameter where name = 'deferred_segment_creation';
select * from v$parameter where name = 'sec_case_sensitive_logon';
alter system set sga_target=5G scope=spfile;
alter system set sga_max_size=6G scope=spfile;
alter system set shared_pool_size=1G scope=spfile;
alter system set db_keep_cache_size=2G scope=spfile;
alter system set db_cache_size=1700M scope=spfile;
show parameter sga_target
show parameter sga_max_size
show parameter shared_pool_size
show parameter db_cache_size
show parameter db_keep_cache_size
create pfile='$ORACLE_HME/dbs/initORCL_before.ora' from spfile;
create pfile='$ORACLE_HME/dbs/initORCL_after.ora' from spfile;
show parameter
show parameter sga_target
show parameter sga_max_size
show parameter shared_pool_size
show parameter db_cache_size
show parameter db_keep_cache_size
alter system set sga_target=5G scope=spfile;
create pfile='$ORACLE_HME/dbs/initORCL_before.ora' from spfile;
create pfile='$ORACLE_HME/dbs/initORCL_after.ora' from spfile;
-- query hidden, shown or not shown,
alter system reset "_TRANSACTION_AUDITING" sid='*' scope=spfile;
select name,value from v$parameter where upper(name) = '_TRANSACTION_AUDITING';
select name,value from v$spparameter where upper(name) = '_TRANSACTION_AUDITING';
-- list non-default parameter
set linesize 180 trimspool on pagesize 5000
column name format A32
column display_value format A40
select name, display_value from v$parameter where isdefault = 'FALSE';
-- hidden parameter
set linesize 180 trimspool on pagesize 5000
column name format A32
column value format A40
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%' escape '\'
order by
name;
top prev next
Network ACL
-- @?/rdbms/admin/catqm.sql xdb sysaux temp NO
-- included in catqm @?/rdbms/admin/dbmsnacl.sql
@?/rdbms/admin/utlrp.sql
GRANT EXECUTE ON utl_file TO xdb;
GRANT EXECUTE ON dbms_lob TO xdb;
-- -----------
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL ( acl => 'orcl_acl.xml');
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL => 'orcl_acl.xml', description=>'ACL access ACL ', principal=> 'MYUSERNAME', is_grant=>TRUE, privilege=>'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL=>'orcl_acl.xml',host=>'192.168.*.*', lower_port=> 7000, upper_port=> 9790);
-- added for fix farecache issue
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL=>'orcl_acl.xml',host=>'amadeuspifprod.ncl.com', lower_port=> 7000, upper_port=> 9790);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'orcl_acl.xml', principal => 'MYUSERNAME', is_grant => TRUE, privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'orcl_acl.xml', principal => 'MYUSERNAME', is_grant => TRUE, privilege => 'resolve');
COMMIT;
END;
/
commit;
set serveroutput on
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'seap_acl.xml', principal => 'MYUSERNAME', is_grant => TRUE, privilege => 'connect');
COMMIT;
END;
/
set serveroutput on
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'seap_acl.xml', principal => 'MYUSERNAME', is_grant => TRUE, privilege => 'resolve');
COMMIT;
END;
/
-- query results:
set pagesize 100 linesize 1000 trimspool on
column host format a32
column acl format a50
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
set pagesize 100 linesize 1000 trimspool on
column acl format a50
column PRINCIPAL format a40
select acl, principal, PRIVILEGE, IS_GRANT, INVERT FROM DBA_NETWORK_ACL_PRIVILEGES
order by acl,principal,privilege,is_grant,invert;
select * from DBA_NETWORK_ACL_PRIVILEGES where PRINCIPAL='MYUSERNAME';
select * from DBA_NETWORK_ACL_PRIVILEGES;
sample error:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line
1130
ORA-24247: network access denied by access control list (ACL)
top prev next
Opatch
export DISPLAY=999.999.999.999:0
export AWT_TOOLKIT=XToolkit ; For Motif Error libXm.so.4: --> missing X Motif package
ln -s /usr/bin/which $ORACLE_HOME/bin/whereis
/sbin/fuser /u01/app/oracle/product/11.2.0.3/lib/libclntsh.so.11.1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ORACLE_HOME/OPatch/opatch
$ORACLE_HOME/OPatch/opatch -help
$ORACLE_HOME/OPatch/opatch version
$ORACLE_HOME/OPatch/opatch lspatches
$ORACLE_HOME/OPatch/opatch lsinventory -help
$ORACLE_HOME/OPatch/opatch lsinventory --> default oraInst.loc location /var/opt/oraInventory /etc/oraInventory
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc
wrong: $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME #need specific oraInst.loc file name
$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 'DATABASE P'
-- check conflict
opatch prereq CheckConflictAgainstOHWithDetail -oh $ORACLE_HOME -phBaseDir /patch_stage_dir/patchset/july2014/18706488/18522512
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
-- opatch apply
-- inside the database
$ORACLE_HOME/OPatch/opatch lsinventory
-- inside the database, pre-12c
set linesize 380 trimspool on
select action_time, id, version, bundle_series,comments from dba_registry_history order by action_time;
SELECT action_time, id, version, bundle_series,comments FROM sys.registry$history order by ACTION_TIME asc;
SET LINES 380 TRIMSPOOL ON PAGES 1000
col ACTION_TIME format a28
col comments format a45
select ACTION_TIME,action, NAMESPACE, VERSION , comments from registry$history;
-- inside the database, 12c or 18c
SELECT * FROM dba_registry_sqlpatch;
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN bundle_series FORMAT A4
COLUMN comments FORMAT A30
COLUMN description FORMAT A40
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
COLUMN version FORMAT A10
SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
select CON_ID,
TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;
set line 200 trimspool on
set pages 1000
col COMP_ID format a8
col COMP_NAME format a34
col SCHEMA format a12
col STATUS format a10
col VERSION format a12
col CON_ID format 99
select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;
select COMP_ID, comp_name, schema, status, version from DBA_REGISTRY order by 1,2;
https://minimalistic-oracle.blogspot.com/2017/09/what-is-12c-utility-datapatch.html
https://blog.oracle-ninja.com/2017/03/12/parallelizing-datapatch-execution/
# Oracle 12c: datapatch
cd $ORACLE_HOME/OPatch
./datapatch -prereq
./datapatch -verbose
set lines 200 trimspool on
col ACTION_TIME for A30
col DESCRIPTION for A75
select PATCH_ID, VERSION, ACTION, STATUS, ACTION_TIME, DESCRIPTION from DBA_REGISTRY_SQLPATCH;
# using runInstaller and gridSetup -applyRU -applyOneOffs to apply patches
Oracle 19c ( version 6 ) : Upgrade the PSU(version) software and apply the patch at the same time
-applyRU 1
-applyRU 2
Create the future Oracle Home directory:
Download and unzip the software release into the Oracle Home directory:
Create /patch subdirectory for:
Download and unzip the patch(es) into the patch subdirectory:
all the installer and let it do its work:
# $ ./runInstaller -applyRU [patch-id] -applyOneOffs [patch-id1],[patch-id2],...
# $ ./runInstaller -applyRU patch/31281355/31281355 -applyOneOffs patch/29041775/29041775
For Grid Infrastructur, using gridSetup instead of runInstaller
./gridSetup -applyRU 30899722
opatchauto
opatchauto 1
opatchauto 2
opatchauto -analyze
eg: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatchauto apply /u01/27468957 -oh /u01/app/oracle/product/12.1.0.2/db_1 -analyze
opatchauto -generateSteps
eg: opatchauto apply /u01/patches/20132450 -oh /u01/app/12.1.0.2/grid -generateSteps -ocmrf /u01/patches/ocm.rsp
top