Oracle Most Used Commands

I am in the process of putting my notes into web pages, please keep checking in later for more contents

quick link

sql one liner   unlock user   expired user  
oracle job   restricted session   locked_object   kill session   Oracle Deployment Hanging   Sort Usage  
kill_session   active_session   session_blocker   session_wait?  
sqlplus   Flashback Database   Archive Log  
Oracle Startup   find_objects   find_object_source   table index   index rebuild   Chained Rows   Materialized Views   seg space  
Oracle Basics   External Links  
get dblink   create_db_link   tnsping   network_acl  
parallel_query   sort_usage  
setup sid using oraenv   init.ora   Opatch   redo logfile member and group   rename database file  

top   prev   next  
set up oracle sqlnet.log location: 
in sqlnet.ora 
LOG_DIRECTORY_CLIENT=C:\oracle\product\10.2.0\client_1\network\log

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  

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'
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 '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  

Recompile Invalid Objects


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 
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
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;
/

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 );

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 ( unfinished )

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 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


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
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 
-- 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
(\
MY_DATE=`date +'%Y%m%d'`;\
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}"
)


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
-- 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

undefine my_owner
undefine my_table

define my_owner=SEA
define my_table=GL_TRANS

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;

top   prev   next  

Get DB Links

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;

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', 'SEAD', 'PUBLIC') from dual;

select dbms_metadata.get_ddl('DB_LINK', 'SEAD', 'NCLSEA') from dual;


CREATE PUBLIC DATABASE LINK "MYREMOTEDB"
   CONNECT TO "MYUSERNAME" IDENTIFIED BY "MYPASSWORD"
   USING 'myremotedb';

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%';


set pagesize 500 linesize 180 trimspool on

-- 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
);

-- -------------------------------------------------------------------------------------------------
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

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 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

$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
top