Oracle, SQL Server ( MSSQL ), MySQL, Postgres, Sybase, Sap HANA, MongoDB, DB2

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  
-- convert constants into table rows
SELECT column_value FROM TABLE(SYS.ODCIVARCHAR2LIST('USERS', 'SYSAUX', 'SYSTEM', 'TOOLS'));

SELECT * FROM TABLE(sys.OdciNumberList('42','43'));


SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10;

select *  from TABLE(CAST (MULTISET(SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10) as SYS.OdciNumberList ));


top   prev   next  

Tablespace Statistics

REM
REM Script: tablespace_stats.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

-- max file size info
set arraysize 15
set linesize 180 trimspool on
set pagesize 60
set trimout on
set verify off
set termout on
set feedback off
COLUMN xnam      format a18     heading   'TABLESPACE'           justify c
COLUMN xalloc    format 999,999 heading   'MB_ALLOC'             justify c
COLUMN xused     format 999,999 heading   'MB_USED'              justify c
COLUMN xfree     format 999,999 heading   'MB_FREE'              justify c
COLUMN xlargfree format 999,999 heading   'MB|LARGEST|FREE'      justify c
COLUMN xpctused  format 999,999 heading   'PCT|USED'             justify c
COLUMN mxpctused format 999,999 heading   'MX PCT|USED'          justify c
COLUMN xmaxsize  format 999,999 heading   'MAX MB|SIZE'          justify c
COLUMN xgrowth   format 999,999 heading   'MAX MB|FREE'          justify c
COLUMN sid       format a18     heading   'HOSTNAME|SID'         justify c

select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) xnam,
       MB_MAX_SIZE                                            xmaxsize,
       MB_ALLOC                                               xalloc,
       MB_ALLOC - nvl(MB_FREE,0)                              xused,
       MB_MAX_SIZE-(MB_ALLOC - nvl(MB_FREE,0) )               xgrowth,
       nvl(MB_FREE,0)                                         xfree,
--       nvl(MB_LARG_FREE,0)                                  xlargfree,
       (MB_ALLOC - nvl(MB_FREE,0))/MB_ALLOC*100               xpctused,
       (MB_ALLOC - nvl(MB_FREE,0))*100/MB_MAX_SIZE            mxpctused,
       HOST_NAME || ' ' || INSTANCE_NAME sid                 
from ( select sum(bytes)/1024/1024      MB_FREE,
              max(bytes)/1024/1024      MB_LARG_FREE,
              tablespace_name
       from sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024      MB_ALLOC,
              sum(maxbytes)/1024/1024   MB_MAX_SIZE,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name ) b, v$instance vi
where a.tablespace_name (+) = b.tablespace_name
union
select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) xnam,
       MB_MAX_SIZE                                            xmaxsize,
       MB_ALLOC                                               xalloc,
       MB_ALLOC - nvl(MB_FREE,0)                              xused,
       MB_MAX_SIZE -  (MB_ALLOC - nvl(MB_FREE,0))             xgrowth,
       nvl(MB_FREE,0)                                         xfree,
--       nvl(MB_LARG_FREE,0)                                  xlargfree,
       (MB_ALLOC - nvl(MB_FREE,0))/MB_ALLOC*100               xpctused,
       (MB_ALLOC - nvl(MB_FREE,0))*100/MB_MAX_SIZE            mxpctused,
       HOST_NAME || ' ' || INSTANCE_NAME sid
from ( select sum(bytes)/1024/1024      MB_FREE,
              max(bytes)/1024/1024      MB_LARG_FREE,
              tablespace_name
       from sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024      MB_ALLOC,
              sum(maxbytes)/1024/1024   MB_MAX_SIZE,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name ) b, v$instance vi
where a.tablespace_name (+) = b.tablespace_name
-- order by 5 desc
order by 8 asc
;

top   prev   next  

Add or Resize Datafile

-- change file size
define my_tablespace=USERS_TS  
define my_tablespace=TEMP1
define my_tablespace=TEMP 
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
;

-- change max file size
egrep -i "ORA-01652|ORA-1652" alert_${ORACLE_SID}.log|sort -u
grep  -i  alter               alert_${ORACLE_SID}.log|sort -u

define my_tablespace=USERS_TS  
define my_tablespace=TEMP1
define my_tablespace=TEMP 
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
select file_name,'alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON NEXT 20M MAXSIZE 6010M;' 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
;

Shrink Datafile Size

-- Using outer join for dba_extents is empty case
-- Still tuning
set pagesize 5000
set linesize 1000 
set trimspool on 
set term on verify off feedback off echo on
prompt set term on verify on feedback on echo on
select 'alter database datafile ''' ||a.file_name || ''' resize ' ||  
CEIL(nvl(b.hwm,a.increment_by+1)*t.block_size/(1024*1024)) || 'M' || ';' || chr(10) || 
-- for_dos_only chr(13) ||
'-- shrink amount ' || (blocks-nvl(b.hwm,a.increment_by+1))*t.block_size/(1024*1024) ||'M' xx 
from dba_data_files a,
       ( select file_id, max(block_id+blocks) hwm
           from dba_extents
          group by file_id ) b,
     dba_tablespaces  t
where 
t.tablespace_name = a.tablespace_name 
and a.file_id = b.file_id(+) 
and (a.blocks-nvl(b.hwm,a.increment_by)+1)*t.block_size/(1024*1024) > 1 + (increment_by+1)*t.block_size/(1024*1024) 
-- and a.tablespace_name = 'my_tablespace'
-- and upper(a.file_name) like '%v%'
order by b.hwm;
set term on verify on feedback on echo on

set pagesize 5000
set linesize 1000 
set trimspool on 
set term on verify off feedback off echo on
prompt set term on verify on feedback on echo on
select 'alter database datafile ''' ||file_name || ''' resize ' ||  
CEIL(hwm*t.block_size/(1024*1024)) || 'M' || ';' || chr(10) || 
-- for_dos_only chr(13) ||
'-- shrink amount ' || (blocks-hwm+1)*t.block_size/(1024*1024) xx 
from dba_data_files a,
       ( select file_id, max(block_id+blocks) hwm
           from dba_extents
          group by file_id ) b,
     dba_tablespaces  t
where 
t.tablespace_name = a.tablespace_name 
and a.file_id = b.file_id 
and (blocks-hwm+1)*t.block_size/(1024*1024) > 10
-- and a.tablespace_name = 'my_tablespace'
-- and upper(a.file_name) like '%v%'
order by hwm;
set term on verify on feedback on echo on



-- hwm
select 'alter database datafile ''' ||file_name || ''' resize ' || hwm*8192/(1024*1024) || 'M' 
|| '; -- shrink amount ' || (blocks-hwm+1)*8192/(1024*1024) 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  

Oracle Startup

emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

lsnrctl status
lsnrctl start
lsnrctl stop

sqlplus / as sysdba<<EOF
shutdown immediate;
startup;
EOF

cd /db/q/sprn/v1/product/11.2.0/miami_ORCL/sysman/config
emoms.properties

cd $ORACLE_HOME
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

Listener port number: 1521
Database SID: ORCL
Service name: ORCL
Email address for notification:(optinal)  
Email gateway for notification:(optional) mailhost
Password for dbsnmp: xxxxxxx
Password for sysman: xxxxxxx
Password for sys: xxxxxxx

top   prev   next  

Oracle Login

1):
sqlplus / as sysdba
sqlplus "/ as sysdba"

sqlplus
/ as sysdba
sqlplus /nolog
connect / as sysdba
connect sys/hr as sysdba
connect sys/hr@test2 as sysdba

2):
sqlplus sys/hr as sysdba
sqlplus sys/hr@test2 as sysdba
sqlplus /@test2 as sysdba

top   prev   next  

as.sql su.sql

select username, account_status from dba_users where username like '%BR%';

-- by alter session
alter session set current_schema=xxx;
alter session set current_schema=xxx;
alter session set nls_date_language=american;
alter session set nls_date_format='DD-MON-RR HH24:Mi:SS';

-- By Proxy User
select * from proxy_users;

alter user myuser grant connect through mydba;
select * from proxy_users;

SQL> connect mydba[myuser]/mydba_password
show user
select user from dual;

-- by change password
undef my_username
define my_username=xxx
select 'alter user &&my_username identified by tmppswd123;' || chr(10) ||  'connect &&my_username/tmppswd123' || chr(10) ||  'alter user &&my_username identified by values '''||password||''';' from sys.user$ where name = upper('&&my_username');


select 'alter user &&my_username identified by tmppswd123;' || chr(10) ||  'connect &&my_username/tmppswd123' || chr(10) ||  'alter user &&my_username identified by values '''||password||''';' from dba_users where username = upper('&&my_username');

set verify off 
set pagesize 0

select 'alter user &&my_username identified by tmppswd123;' from dual;
select 'connect &&my_username/tmppswd123' from dual;
select 'alter user &&my_username identified by values '''||password||''';'
from dba_users
where username = upper('&&my_username');


alter session set current_schema=xxx;

select username, account_status from dba_users where username like '%BR%';

undef my_username
define my_username=DW_EXEC

set linesize 120 trimspool on
set pagesize 3000

select 'alter session set current_schema=upper(''&&my_username'');' || chr(10) || chr(10) ||
       'CREATE PROFILE "DEFAULT1"  LIMIT  COMPOSITE_LIMIT UNLIMITED  ' || chr(10) || 
       'SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED '|| chr(10) || 
       'LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED '|| chr(10) || 
       'IDLE_TIME UNLIMITED CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED '|| chr(10) || 
       'FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME UNLIMITED '|| chr(10) || 
       'PASSWORD_REUSE_TIME UNLIMITED ' || chr(10) || 
       'PASSWORD_REUSE_MAX 8 PASSWORD_VERIFY_FUNCTION null PASSWORD_LOCK_TIME'|| chr(10) || 
       'UNLIMITED  PASSWORD_GRACE_TIME UNLIMITED;' || chr(10) || chr(10) || 
       'alter user &&my_username profile DEFAULT1;' || chr(10) || chr(10) || 
       'alter user &&my_username identified by tmppswd123;' || chr(10) || chr(10) ||  
       'connect &&my_username/tmppswd123' || chr(10) ||  chr(10) || 
       'alter user &&my_username identified by values '''||password||''';' cmd
  from sys.user$ where name = upper('&&my_username')
union
select 'alter user &&my_username profile ' || profile || ';' cmd from dba_users where username = upper('&&my_username');


undef my_username
define my_username=xxx
select 'alter user &&my_username identified by tmppswd123;' || chr(10) ||  'connect &&my_username/tmppswd123' || chr(10) ||  'alter user &&my_username identified by values '''||password||''';' from sys.user$ where name = upper('&&my_username');


select 'alter user &&my_username identified by tmppswd123;' || chr(10) ||  'connect &&my_username/tmppswd123' || chr(10) ||  'alter user &&my_username identified by values '''||password||''';' from dba_users where username = upper('&&my_username');

set verify off 
set pagesize 0

select 'alter user &&my_username identified by tmppswd123;' from dual;
select 'connect &&my_username/tmppswd123' from dual;
select 'alter user &&my_username identified by values '''||password||''';'
from dba_users
where username = upper('&&my_username');

alter session set current_schema=xxx;
alter session set nls_date_language=american;
alter session set nls_date_format='DD-MON-RR HH24:Mi:SS';


top   prev   next  

sqlplus command

-- output commands and output
set verify on term on feedback on echo on

-- turn on serveroutput
se serveroutput on

show define
set define off

-- define using single quote
define c_SwMileStone = '04_08_08.44.22a';

set linesize 380 trimspool on
colsep " "

whenever sqlerror exit
whenever sqlerror exit rollback;
whenever sqlerror exit failure rollback;

show user
show all
show parameter
define
column

set pagesize 0

set pagesize 5000
show pagesize

set linesize 250 trimspool on
show linesize

set long 30000
set longchunk 30000

set echo on|off
set verify on|off
set feedback  on|off
set verify on|off
set term on|off

SET SCAN OFF  -->  to disable substitution variables and parameters cf: set define on;

SET SCAN is obsolete but it was used to control whether or not it should scan for substitution params/variables.  OFF would prevent scanning for params/variables.

spool xxx
spool xxx.lst

spool xxx.log
spool off

save aaa
save aaa.sql
save aaa.sql replace

edit
edit aaa.sql
edit aaa

@aaa
@aaa.sql

@?/rdbms/admin/addmrpt;
@?/rdbms/admin/awrrpt;
@?/rdbms/admin/utlrp;

list

get aaa
get aaa.sql

In Linux
!ls -lart 
!pwd
!hostname
!hostname -s

In Widnows
host cmd -k
host cmd -k dir

column
column name format a30
column name noprint
column name new_value my_name 

col nl newline

break on column_name_1 
break on column_name_1 skip 1

define
define my_var=test
&my_var
&&my_var

-- @find_table_columns.sql  my_tablename
column data_type format a30
select table_name,column_name,data_type,column_id
from user_tab_columns
where table_name=upper('&1')

select sysdate from dual;
select user from dual;


top   prev   next  

Oracle Basics

select * from tab;

desc my_table_name             eg: desc employees
desc my_table_name;            eg: desc employees;

select table_name from dict;
select table_name from dict where table_name like '%USER_TABLES%';
select table_name from dict where table_name like upper('%user_tables%');

select owner, table_name from dba_tables;
select owner, table_name from dba_tables where table_name like '%EMPLOYEES%';
select owner, table_name from dba_tables where table_name like upper('%employees%');

select owner, object_name from dba_objects where object_name like '%EMPLOYEES%';
select owner, object_name from dba_objects where object_name like upper('%employees%');

select owner, index_name from dba_indexs where index_name like '%EMPLOYEES%';
select owner, index_name from dba_indexs where index_name like upper('%employees%');


top   prev   next  

sql one liner

echo "purge dba_recyclebin;"|sqlplus "/ as sysdba"
echo "select count(*) from dba_recyclebin;"|sqlplus -s / as sysdba

echo "select open_mode from v\$database;"|sqlplus "/ as sysdba"
(echo "set pagesize 0 newpage 0 feedback off;";echo "select status from v\$encryption_wallet;")|sqlplus -s "/ as sysdba"
while [ 1 ]; do echo "select count(*) from dba_recyclebin;"|sqlplus -s / as sysdba; sleep 10; done

echo -e "select * from v\$instance; \n exit; \n"|sqlplus "/ as sysdba"

-- 1):
(echo "set pagesize 0 newpage 0 feedback off; ";echo "select status from v\$encryption_wallet;")|sqlplus -s "/ as sysdba"

-- 2):
echo    "set pagesize 0 newpage 0 feedback off; \n select status from v\$encryption_wallet;"|sqlplus -s "/ as sysdba"
-- or
echo -e "set pagesize 0 newpage 0 feedback off; \n select status from v\$encryption_wallet;"|sqlplus -s "/ as sysdba"

-- 3):
^J: Control-v Control-j, putty copy is ascii, ^J will not work, but if type, will work
echo "set pagesize 0 newpage 0 feedback off; ^Jselect status from v\$encryption_wallet;"|sqlplus -s "/ as sysdba"


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  

Session (Stats) Info for CPU, Disk Usage

column sql_fulltext noprint

set long      30000
set longchunk 30000

column TABLE_SCANS_ROWS_GOTTEN noprint
column TABLE_FETCH_BY_ROWID    noprint
column SORTS_MEMORY            noprint
column SORTS_DISKS             noprint
column SORTS_ROWS              noprint
column EVENT                   noprint

set sqlblanklines on
-- set sqlblanklines off

set linesize 350 trimspool on
set pagesize 5000 


select s.sid,s.serial#,status,s.USERNAME,osuser,machine,logon_time,program,sql_fulltext,
(select value
 from v$sesstat st
 where statistic# = 13
       and st.sid = s.sid) as CPU_USED,
(select value
 from v$sesstat st
 where statistic# = 40
       and st.sid = s.sid) as PHYSICAL_READ_BYTES,
(select value
 from v$sesstat st
 where statistic# = 257
       and st.sid = s.sid) as table_scans_rows_gotten,
(select value
 from v$sesstat st
 where statistic# = 259
   and st.sid = s.sid) as table_fetch_by_rowid,
(select value
 from v$sesstat st
 where statistic# = 357
       and st.sid = s.sid) as sorts_memory,
(select value
 from v$sesstat st
 where statistic# = 358
       and st.sid = s.sid) as sorts_disks,
(select value
 from v$sesstat st
 where statistic# = 359
       and st.sid = s.sid) as sorts_rows,
w.event
from v$session s, v$sql l, v$session_wait w
where
username is not null and status = 'ACTIVE'
-- and osuser like 'SQLMain%'
and osuser like 'SQLMain%'
and s.SQL_ID = l.SQL_ID
and w.sid(+) = s.sid
and program in ('ReportingServicesService.exe', 'plsqldev.exe','devenv.exe')
order by sid,serial#,cpu_used desc, logon_time, sid
;


top   prev   next  

Session Blocker ( unfinished )

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


top   prev   next  

Session Long OPS

select sess.sid,sess.serial#,
    sess.username,
    sess.paddr,
    sess.machine,
    optimizer_mode,
    sess.schemaname,
    hash_value,
    address,
    sess.sql_address,
    cpu_time,
    elapsed_time,
    sess.LAST_CALL_ET
    sql_text
from v$sql sql, v$session sess
where 
        sess.sql_hash_value = sql.hash_value
    and     sess.sql_address = sql.address
    and     sess.username is not null
    and     elapsed_time > 1000000  * 5
order by    
    cpu_time desc;

select
'SID                 : '||a.sid||chr(10)||
'SERIAL#             : '||a.serial#||chr(10)||
'USERNAME            : '||a.username||chr(10)||
'PROGRAM             : '||a.program||chr(10)||
'MACHINE             : '||a.machine||chr(10)||
'OS User             : '||a.osuser||chr(10)||
'Operation           : '||e.opname||chr(10)||
'Target              : '||e.target||chr(10)||
'Total Work          : '||e.totalwork||chr(10)||
'Sofar               : '||e.sofar||chr(10)||
'Perc Complete       : '||round(( e.sofar / decode ( nvl2(e.totalwork,e.totalwork,1) ,0,1, nvl2(e.totalwork,e.totalwork,1))) * 100) ||chr(10)||
'Start Time          : '||e.start_time||chr(10)||
'Last Update Time    : '||e.last_update_time||chr(10)||
'Remaining Minutes   : '||round(e.time_remaining/60) ||chr(10)||
'Elapsed Minutes     : '||round(e.elapsed_seconds/60) ||chr(10)||
'Process             : '||a.Process ||chr(10)||
'SQL ID              : '||a.sql_id||chr(10)||
'Current Statement   : '||d.sql_text  "Long Operations w/SQL"
from v$session a, v$sess_io b,v$sesstat c,v$sql d,sys.v$session_longops e
where e.sid = a.sid (+)
and e.serial#=a.serial#(+)
and e.sofar != e.totalwork
and a.sid = b.sid(+)
and a.sid = c.sid(+) AND (c.statistic# = 12 OR c.statistic# IS NULL)
and a.sql_address = d.address(+) AND a.sql_hash_value = d.hash_value(+)
and (d.child_number = 0 OR d.child_number IS NULL)
ORDER BY a.username;

top   prev   next  

Parallel Queries

ALTER SESSION ENABLE PARALLEL QUERY  parallel 32;
ALTER SESSION FORCE  PARALLEL QUERY  parallel 32;

ALTER SESSION DISABLE PARALLEL QUERY;

Parallel Sessions

set lines 200

column child_wait  format a30
column parent_wait format a30
column server_name format a4  heading 'Name'
column x_status    format a10 heading 'Status'
column schemaname  format a10 heading 'Schema'
column x_sid format 9990 heading 'Sid'
column x_pid format 9990 heading 'Pid'
column p_sid format 9990 heading 'Parent'

break on p_sid skip 1

select x.server_name
     , x.status as x_status
     , x.pid as x_pid
     , x.sid as x_sid
     , w2.sid as p_sid
     , v.osuser
     , v.schemaname
     , w1.event as child_wait
     , w2.event as parent_wait
from  v$px_process x
    , v$lock l
    , v$session v
    , v$session_wait w1
    , v$session_wait w2
where x.sid <> l.sid(+)
and   to_number (substr(x.server_name,2)) = l.id2(+)
and   x.sid = w1.sid(+)
and   l.sid = w2.sid(+)
and   x.sid = v.sid(+)
and   nvl(l.type,'PS') = 'PS'
order by p_sid, x.server_name;


top   prev   next  

Session Wait

set lines 200 pages 80
col machine format a30

select s.sid, s.machine, s.username, w.event, w.p1text, w.p2text, w.p3text, w.wait_class, w.wait_time, w.SECONDS_IN_WAIT, w.state
from v$session_wait w, v$session s
where w.sid = s.sid
and w.sid = &sid
;


top   prev   next  

Sort Usage

select sum(s.blocks*p.value)/1024/1024 sort_usage_MB
from v$sort_usage s, v$parameter p
where p.name ='db_block_size';

SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

set pagesize 5000
set long 30000
set longchunk 30000

select ss.sid,ss.serial#,so.SESSION_NUM,ss.sql_id,so.blocks,so.SESSION_ADDR,so.tablespace 
from v$sort_usage so, v$session ss
where ss.saddr=so.session_addr;

select ss.sid,ss.serial#,so.SESSION_NUM,ss.sql_id,so.SESSION_ADDR,so.tablespace, so.blocks,chr(10) ||chr(13) ||st.sql_fulltext 
from (select session_num,session_addr,tablespace,sum(blocks) blocks from v$sort_usage so group by session_num,session_addr,tablespace) so, v$session ss, v$sqlarea st
where ss.saddr=so.session_addr
and st.sql_id=ss.sql_id;


top   prev   next  

index

set linesize 250 trimspool on
column column_name format a32
select owner, index_name, table_owner,table_name from dba_indexes where table_name = 'SYSTEM_NOTIFICATIONS';

select table_owner, table_name, index_owner, index_name, column_name
from dba_ind_columns
where table_name in ( 'SYSTEM_NOTIFICATIONS','SYSTEM_NOTIFICATION_SITES', 'WEBSITES', 'SYSTEM_NOTIFICATION_TYPES') 
order by table_owner, table_name, index_owner, index_name, column_position;

select dbms_metadata.get_ddl('INDEX', 'IDX_SYSTEM_NOTIFI_CUSTUSER', 'DBPTSN') from dual;

CREATE INDEX "DBPTSN"."IDX_SYSTEM_NOTIFI_CUSTUSER_EXT" ON "DBPTSN"."SYSTEM_NOTIFICATIONS" ("CUST_NB","GLOBAL_USER_ID",SN_DATE,SN_ID,SNT_ID) TABLESPACE "SM_TSN_IDX_TBLSP" online;


top   prev   next  

index rebuild

select index_name from dba_indexes where tablespace_name = '&MY_TABLESPACE';
alter index MYOWNER.MY_INDEX_NAME rebuild tablespace MY_TABLESPACE  online;

-- segment stats after index rebuild 
define MY_SEGMENT_OWNER=SEA
define MY_DIFF_SIZE_IN_GB=0.1

set pagesize 2000
set linesize 380 trimspool on
column df_pct format 999.99

column af_s format 999,999,999.99
column bf_s format 999,999,999.99
column df_s format 999,999,999.99

column tablespace_name format a32
select af.owner, af.segment_name,tablespace_name, 
round(bf.s_gb/(1024*1024*1024),2) bf_s, 
round(af.s_gb/(1024*1024*1024),2) af_s, 
round((bf.s_gb-af.s_gb)/(1024*1024*1024),2) df_s,
round((bf.s_gb-af.s_gb)*100/bf.s_gb,2) df_pct
from  
(select owner, segment_name, tablespace_name,sum(bytes) s_gb
from dbas.dba_segment_20170928_af
where owner = '&&MY_SEGMENT_OWNER'
group by owner, segment_name, tablespace_name ) af,
(select owner, segment_name, sum(bytes) s_gb
from dbas.dba_segment_20170928_bf
where owner = '&&MY_SEGMENT_OWNER'
group by owner, segment_name, tablespace_name ) bf
where af.owner=bf.owner and
      af.segment_name = bf.segment_name and 
      bf.s_gb-af.s_gb>&&MY_DIFF_SIZE_IN_GB*1024*1024*1024
      order by df_pct desc 
;

-- tablespace stats after index rebuild 
define MY_SEGMENT_OWNER=SEA
define MY_DIFF_SIZE_IN_GB=1
set pagesize 2000
set linesize 380 trimspool on

column df_pct format 999.99

column af_s format 999,999,999.99
column bf_s format 999,999,999.99
column df_s format 999,999,999.99

column tablespace_name format a32
column segment_name format a38
column owner format a16

select af.tablespace_name, 
round(bf.s_gb/(1024*1024*1024),2) bf_s, 
round(af.s_gb/(1024*1024*1024),2) af_s, 
round((bf.s_gb-af.s_gb)/(1024*1024*1024),2) df_s,
round((bf.s_gb-af.s_gb)*100/bf.s_gb,2) df_pct
from  
(select tablespace_name,sum(bytes) s_gb
from dbas.dba_segment_20170928_af
where owner = '&&MY_SEGMENT_OWNER'
group by tablespace_name ) af,
(select tablespace_name, sum(bytes) s_gb
from dbas.dba_segment_20170928_bf
where owner = '&&MY_SEGMENT_OWNER'
group by tablespace_name ) bf
where 
      af.tablespace_name = bf.tablespace_name and  
      bf.s_gb-af.s_gb>&&MY_DIFF_SIZE_IN_GB*1024*1024*1024 
order by df_pct desc   
;

top   prev   next  

Statistics


define MY_USERNAME=myusername
define MY_TABLENAME=mytablename

select '-- 1. method_opt default at database level ' || dbms_stats.get_param('METHOD_OPT') cmd  from dual 
union all
select '-- 10g exec dbms_stats.gather_schema_stats(upper(''&&MY_USERNAME''),options=>''GATHER'', '
     ||'estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, '
     ||'method_opt => ''FOR ALL INDEXED COLUMNS SIZE AUTO'',cascade => TRUE) ' cmd from dual
union all
select '-- 11g exec dbms_stats.gather_schema_stats(upper(''&&MY_USERNAME''),options=>''GATHER'', '
     ||'estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, '
     ||'method_opt => ''FOR ALL COLUMNS SIZE AUTO'',cascade => TRUE) ' cmd from dual
union all 
SELECT 
 'BEGIN dbms_stats.gather_table_stats(OWNNAME => '''||A.owner||''''||', TABNAME => '''
   ||A.TABLE_NAME||''''||','
   ||decode ( a.partitioned, 'YES', 'PARTNAME=>NULL,', 'NO', '', null, '','') 
   ||'ESTIMATE_PERCENT => '||
     (CASE WHEN B.BYTES/1024/1024 < 3000                   THEN 20
           WHEN B.BYTES/1024/1024 between 3000  and 5000   THEN 15
           WHEN B.BYTES/1024/1024 between 5001  and 8000   THEN 10
           WHEN B.BYTES/1024/1024 between 8001  and 10000  THEN 5
           WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN 3
           ELSE 2
      END ) ||
     (CASE WHEN B.BYTES/1024/1024 < 10000                  THEN ''
        WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN ', DEGREE => 2'
        ELSE ', DEGREE => 3'
      END ) ||
      ', CASCADE => TRUE); END;'  || chr(10) || '/' || chr(10) CMD 
from  DBA_TABLES A
     ,DBA_SEGMENTS B
WHERE A.OWNER = B.OWNER
  AND A.TABLE_NAME = B.SEGMENT_NAME
  and A.TEMPORARY <> 'Y'
  and (a.owner, a.table_name ) in ((upper('&&MY_USERNAME'),upper('&&MY_TABLENAME') )) 
union 
-- bugs here: index can be partitioned or not partitioned even if table is partitioned
-- result for one table have multiple stats collcetion statements
select 'BEGIN dbms_stats.gather_table_stats(OWNNAME => '''||A.owner
   ||''''||', TABNAME => '''||A.TABLE_NAME||''''||','  
   ||decode ( a.partitioned, 'YES', 'PARTNAME=>NULL,', 'NO', '', null, '','') 
   ||'ESTIMATE_PERCENT => '||
     (CASE WHEN B.BYTES/1024/1024 < 3000                   THEN 20
           WHEN B.BYTES/1024/1024 between 3000  and 5000   THEN 15
           WHEN B.BYTES/1024/1024 between 5001  and 8000   THEN 10
           WHEN B.BYTES/1024/1024 between 8001  and 10000  THEN 5
           WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN 3
           ELSE 2
      END ) ||
     (CASE WHEN B.BYTES/1024/1024 < 10000                  THEN ''
        WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN ', DEGREE => 2'
        ELSE ', DEGREE => 3'
      END ) ||
      ', CASCADE => TRUE); END;'  || chr(10) || '/' || chr(10) CMD 
from  DBA_INDEXES A
     ,DBA_SEGMENTS B
WHERE A.OWNER = B.OWNER
  AND A.INDEX_NAME = B.SEGMENT_NAME
  and A.TEMPORARY <> 'Y'
  and (a.owner, a.table_name ) in ((upper('&&MY_USERNAME'),upper('&&MY_TABLENAME'))) 
order by 1
;



-- for schema
set linesize 380 trimspool on pagesize 5000
define MY_USERNAME=DBAS
SELECT 
 'BEGIN dbms_stats.gather_table_stats(OWNNAME => '''||A.owner||''''||', TABNAME => '''
   ||A.TABLE_NAME||''''||','
   ||decode ( a.partitioned, 'YES', 'PARTNAME=>NULL,', 'NO', '', null, '','') 
   ||'ESTIMATE_PERCENT => '||
     (CASE WHEN B.BYTES/1024/1024 < 3000                   THEN 20
           WHEN B.BYTES/1024/1024 between 3000  and 5000   THEN 15
           WHEN B.BYTES/1024/1024 between 5001  and 8000   THEN 10
           WHEN B.BYTES/1024/1024 between 8001  and 10000  THEN 5
           WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN 3
           ELSE 2
      END ) ||
     (CASE WHEN B.BYTES/1024/1024 < 10000                  THEN ''
        WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN ', DEGREE => 2'
        ELSE ', DEGREE => 3'
      END ) ||
      ', CASCADE => TRUE); END;' CMD 
from  DBA_TABLES A
     ,DBA_SEGMENTS B
WHERE A.OWNER = B.OWNER
  AND A.TABLE_NAME = B.SEGMENT_NAME
  and A.TEMPORARY <> 'Y'
  and a.owner='&&MY_USERNAME' 
union 
select 'BEGIN dbms_stats.gather_table_stats(OWNNAME => '''||A.owner
   ||''''||', TABNAME => '''||A.TABLE_NAME||''''||','  
   ||decode ( a.partitioned, 'YES', 'PARTNAME=>NULL,', 'NO', '', null, '','') 
   ||'ESTIMATE_PERCENT => '||
     (CASE WHEN B.BYTES/1024/1024 < 3000                   THEN 20
           WHEN B.BYTES/1024/1024 between 3000  and 5000   THEN 15
           WHEN B.BYTES/1024/1024 between 5001  and 8000   THEN 10
           WHEN B.BYTES/1024/1024 between 8001  and 10000  THEN 5
           WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN 3
           ELSE 2
      END ) ||
     (CASE WHEN B.BYTES/1024/1024 < 10000                  THEN ''
        WHEN B.BYTES/1024/1024 between 10001 and 20000  THEN ', DEGREE => 2'
        ELSE ', DEGREE => 3'
      END ) ||
      ', CASCADE => TRUE); END;' CMD 
from  DBA_INDEXES A
     ,DBA_SEGMENTS B
WHERE A.OWNER = B.OWNER
  AND A.INDEX_NAME = B.SEGMENT_NAME
  and A.TEMPORARY <> 'Y'
  and a.owner = '&&MY_USERNAME'
order by 1
;


exec dbms_stats.GATHER_SCHEMA_STATS( 'MYSCHEMA', 30 );

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (NULL);
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

set linesize 250 trimspool on
set pagesize 5000
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
select owner, table_name, num_rows, last_analyzed from dba_tables where owner = 'BOCAQA' and last_analyzed < sysdate - 240 
order by 3 desc
;


select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY  from dual;

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
-- The default is 31 days.
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (14);

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where owner='FCONSOL';
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='FMS_PROCESS_TRACKING' and owner='FCONSOL';

execute dbms_stats.restore_table_stats('SYSTEM','MYOBJECTS','22-DEC-15 11.01.31.939185 PM -05:00');

select num_rows from dba_tables where table_name='MYOBJECTS' and owner='SYSTEM';

-- get default estimate percentage 
select DBMS_STATS.get_param('ESTIMATE_PERCENT') from dual;
select DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT', '&my_username') from dual;
select DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT', '&my_username', '&my_tablename') from dual;

-- at system level
select DBMS_STATS.get_param('ESTIMATE_PERCENT') from dual;
select DBMS_STATS.get_param('METHOD_OPT') from dual;
select DBMS_STATS.get_param('DEGREE') from dual;
select DBMS_STATS.get_param('GRANULARITY') from dual;
select DBMS_STATS.get_param('CASCADE') from dual;

-- at schema level
define my_username=MYSCHEMA
select DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT', '&my_username') from dual;
select DBMS_STATS.GET_PREFS('METHOD_OPT', '&my_username') from dual;
select DBMS_STATS.GET_PREFS('DEGREE', '&my_username') from dual;
select DBMS_STATS.GET_PREFS('GRANULARITY', '&my_username') from dual;
select DBMS_STATS.GET_PREFS('CASCADE', '&my_username') from dual;

-- at schema.table level 
define my_username=MYSCHEMA
define my_tablename=MYTABLE
select DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT', '&my_username', '&my_tablename') from dual;
select DBMS_STATS.GET_PREFS('METHOD_OPT', '&my_username', '&my_tablename') from dual;
select DBMS_STATS.GET_PREFS('DEGREE', '&my_username', '&my_tablename') from dual;
select DBMS_STATS.GET_PREFS('GRANULARITY', '&my_username', '&my_tablename') from dual;
select DBMS_STATS.GET_PREFS('CASCADE', '&my_username', '&my_tablename') from dual;

execute dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.set_param('DEGREE','NULL');

select owner,table_name, last_analyzed, num_rows 
from dba_tables where owner='HRMS' and num_rows > 5000 and rownum < 20;

DEFINE MY_USERNAME=MYUSER
DEFINE MY_TABLENAME=MYTAB
DEFINE MY_ESTIMATE_PERCENT=17

select DBMS_STATS.GET_PREFS    ('ESTIMATE_PERCENT', '&&MY_USERNAME', '&&MY_TABLENAME' ) from dual;
EXEC DBMS_STATS.SET_TABLE_PREFS('&&MY_USERNAME',    '&&MY_TABLENAME','&&ESTIMATE_PERCENT', '13');
select DBMS_STATS.GET_PREFS    ('ESTIMATE_PERCENT', '&&MY_USERNAME', '&&MY_TABLENAME' ) from dual;

set linesize 250 trimspool on
set pagesize 5000
select owner, table_name, num_rows, last_analyzed from dba_tables where owner = 'BOCAQA' and last_analyzed < sysdate - 240;

exec dbms_stats.gather_table_stats(ownname=>'PMRP',tabname=>'IDENTITY',estimate_percent=>0.5,block_sample=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS',cascade=>TRUE,force=>true);

execute dbms_stats.gather_table_stats( ownname => '<table owner>', tabname => '<table name>', method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', granularity => 'ALL', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE);

select 'exec dbms_stats.gather_table_stats(ownname=>'''|| owner ||''',tabname=>'''|| table_name || ''',estimate_percent=>20,block_sample=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS'',cascade=>TRUE,force=>true);' xx 
from dba_tables where owner = 'BOCAQA' and last_analyzed < sysdate - 240;


-- get and restore previous historical stats
select count(*), stats_update_time from dba_tab_stats_history   where owner='CRMODDM'    and table_name='CAS_CLT_PROFILE_INT_STAGING'  group by stats_update_time;

exec dbms_stats.restore_table_stats(ownname=>'CRMODDM',tabname=>'CAS_CLT_PROFILE_INT_STAGING', AS_OF_TIMESTAMP=>'27-MAY-16 07.01.07.242100 PM -04:00');

-- exec dbms_stats.restore_table_stats(ownname=>'SCHEMA1',tabname=>'TAB1',AS_OF_TIMESTAMP=>'22/02/11 10:20:07,587000 +01:00');

select owner, table_name, num_rows, last_analyzed from dba_tables where  owner='CRMODDM'    and table_name='CAS_CLT_PROFILE_INT_STAGING' ;


DBMS_STATS.AUTO_SAMPLE_SIZE 

SET_TABLE_PREFS
SET_SCHEMA_PREFS
SET_DATABASE_PREFS
SET_GLOBAL_PREFS

EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'STALE_PERCENT', '13');
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'SH', 'SALES') FROM   DUAL;

COL OWNER FORMAT a5
COL TABLE_NAME FORMAT a15
COL PREFERENCE_NAME FORMAT a20
COL PREFERENCE_VALUE FORMAT a30
SELECT * FROM DBA_TAB_STAT_PREFS;

-- stats collection history
select owner, table_name, partition_name, stats_update_time 
from dba_tab_stats_history 
where owner = 'DW' and table_name =  'TBL_PRICE_PROGRAMS_MD' and partition_name = 'CD'
-- and rownum < 10
order by owner, table_name, partition_name, stats_update_time 
;

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (  
    client_name  => 'auto space advisor'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (  
    client_name  => 'sql tuning advisor'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/

COL CLIENT_NAME FORMAT a31
SELECT CLIENT_NAME, STATUS
FROM   DBA_AUTOTASK_CLIENT
-- WHERE  CLIENT_NAME = 'auto optimizer stats collection'
;

BEGIN 
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    'MONDAY_WINDOW'
,   'repeat_interval'
,   'freq=daily;byday=MON;byhour=05;byminute=0;bysecond=0'
);
END;
/

top   prev   next  

Find Objects


-- find objects:

select table_name from dict where table_name like '%ENCRYPTION%';

select owner, count(*) cnt from dba_triggers group by owner;

select * from dba_objects where object_name = 'DDL_TRIGGER' order by last_ddl_time desc;

select * from dba_triggers where trigger_name like '%LOGON%';

top   prev   next  

Find Objects And Sources

select table_name from dict where table_name like '%ROL%';
select * from dba_role_privs where granted_role = 'NCL_FMS_ROLE';
select dbms_metadata.get_ddl ( 'PROCEDURE', 'myname', 'myowner' ) from dual;

set long      30000
set longchunk 30000
set pagesize  3000
set linesize 250 trimspool on
select dbms_metadata.get_ddl('TABLESPACE', '&my_tablespace') from dual;

select owner, object_type,count(*) 
from dba_objects where owner like 'SIEB%'
group by owner,object_type;

select dbms_metadata.get_ddl ( 'PROCEDURE', 'myname', 'myowner' ) from dual;

top   prev   next  

Materialized Views

select table_name from dict where table_name like '%MV%';

DBA_MVIEW_LOGS

DBA_MVIEWS

select * from DBA_MVIEW_LOGS where master = 'DATAITEM';

select * from DBA_MVIEWS where mview_name like '%DATAITEM%';

select * from DBA_MVIEWS where query like '%DATAITEM%';

job_queue_processes 10

alter system set job_queue_processes=0;

alter system set job_queue_processes=10;

exec dbms_refresh.refresh('"RPT_RT_USR"."RTWindowChoiceL_MV"');

exec dbms_mview.refresh('"RPT_RT_USR"."RTWindowChoiceL_MV"', 'C');

archivelog


top   prev   next  
show parameter archive
select name,value from v$parameter where name like '%archive%';
select table_name from dict where table_name like '%ARCHIVE%';

top   prev   next  

metadata get ddl

10g docs

set long 300000
set longchunk 300000
set serveroutput on

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

select dbms_metadata.get_ddl ( 'PROFILE', 'DEFAULT' ) from dual;

select dbms_metadata.get_ddl ('USER', 'DBPBR') from dual;
select dbms_metadata.get_ddl ('TABLE', 'ACCTS', 'DBPBR') from dual;
select dbms_metadata.get_ddl ('TABLESPACE', 'OTHER_DATA_TBLSP') from dual;

select dbms_metadata.get_ddl ( 'DB_LINK', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'MATERIALIZED_VIEW', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'TABLESPACE', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'DIRECTORY', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'JOB', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'USER', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'TYPE', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'TRIGGER', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'PROCEDURE', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'FUNCTION', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'PACKAGE', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'TABLE', 'MYNAME' ) from dual;
select dbms_metadata.get_ddl ( 'INDEX', 'MYNAME' ) from dual;

select text,line from dba_source where name = 'VENDOR_COMPLIANCE_WORKFLOW' and line > 3580 and line < 3630

select * from dba_views;

-- index from table:
select owner, index_name, index_type from dba_indexes 
where table_name = '&MYTABLENAME' and index_type='NORMAL';

DEFINE MYTABLENAME=MYTABLENAME
set linesize 2000 trimspool on
set pagesize 1000
set long 300000000
set longchunk 300000000

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('INDEX', index_name, owner) from dba_indexes 
where table_name = '&MYTABLENAME' and index_type='NORMAL';



top   prev   next  

Oracle Deployment Hanging

-- find hanging session for deploying stored procedure and package
select * from v$access  where object like '%SUPPORT%' and owner='ODS' order by sid;

select s.status, s.* from v$session s where status='ACTIVE' and sid in
(select sid from v$access  where object = 'PKG_SUPPORT' and owner='ODS') order by sid;

top   prev   next  

Restricted Session

alter system enable restricted session;

select logins from v$instance;
RESTRICTED

alter system disable restricted session;
select logins from v$instance;
ALLOWED

grant restricted session to my_username;

top   prev   next  

Resumable Timeout

set pagesize 100
select * from dba_resumable;
select user_id, session_id, suspend_time, resume_time from dba_resumable;

select sid, wait_time,SECONDS_IN_WAIT, state, event from  v$session_wait where event like '%suspend%' ;
while [ 1 ]; do echo "select * from dba_resumable;"|sqlplus -s "/ as sysdba"; date;sleep 10; done select sid, wait_time, SECONDS_IN_WAIT, state, event from v$session_wait where --event like '%statement suspended%' event like '%suspend%' ; grep -i -n ora alert_seap.log|grep -v oracle|grep -v column|grep -v ORA-12012 grep -i -n resum alert_seap.log grep -i -n resum alert_sead.log grep -i -n resum alert_swtr.log -- --------- ALTER SESSION ENABLE RESUMABLE ; dbms_resumable -- ------------ ALTER SYSTEM SET RESUMABLE_TIMEOUT=7200; ALTER SYATEM SET RESUMABLE_TIMEOUT=0 SCOPE=BOTH;

top   prev   next  

chained rows

SQL> @$ORACLE_HOME/rdbms/admin/utlchain.sql if chained_rows table is not created yet

sample sql:
SQL> ANALYZE TABLE  SEA.AGENT LIST CHAINED ROWS;

see result:
SQL> select * from chained_rows where owner_name='SEA' and TABLE_NAME='AGENT';
SQL> select owner,table_name,NUM_ROWS,LAST_ANALYZED,CHAIN_CNT from dba_tables where owner='SEA' and TABLE_NAME='AGENT';

set linesize 250 trimspool on
column name format A32
column VALUE format 999,999,999,999
select * from v$sysstat where name like 'table fetch %';

column regular format 999,999,999,999
column chained_rows format 999,999,999,999
column pct format 99.99
select a.value regular, b.value chained_rows, b.value*100/a.value pct
from v$sysstat a, v$sysstat b
where
a.name='table fetch by rowid' and
b.name='table fetch continued row';

top   prev   next  

flashback database

CREATE RESTORE POINT before_upgrade;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- Listing Restore Points
-- 1): 
set linesize 380 trimspool on
set pagesize 200 
set numwidth 20
col name format a40
col time format a32

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE 
FROM V$RESTORE_POINT;

-- 2): 
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT 
WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ;

-- DROP RESTORE POINT before_app_upgrade;

ref1 

select * from v$version;
select status from v$instance;

select log_mode,flashback_on from v$database;

pre step: archivelog must be on, select log_mode from v#database;
STARTUP MOUNT 
ALTER DATABASE ARCHIVELOG;
-- ALTER DATABASE NOARCHIVELOG;

init.ora
db_recovery_file_dest=/jobs/DBA/flashback
db_recovery_file_dest_size=500G
DB_FLASHBACK_RETENTION_TARGET=14400

STARTUP MOUNT 
-- ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FLASHBACK ON;
-- ALTER DATABASE FLASHBACK OFF;
ALTER DATABASE OPEN;

-- -------------
SHUTDOWN DATABASE: shutdown immediate
STARTUP MOUNT 
-- FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
FLASHBACK DATABASE TO SCN xxx;
FLASHBACK DATABASE TO BEFORE SCN xxx;
FLASHBACK DATABASE TO RESTORE POINT xxx;
-- alter database open resetlogs;
-- -------------
SELECT * FROM V$RECOVERY_FILE_DEST;
select * from V$FLASH_RECOVERY_AREA_USAGE ;


-- disabling the Flash Recovery Area
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";

-- -------------

flashback table:
flashback table xxx to scn;
flashback table xxx to restore point;
flashback table xxx to before  drop;
FLASHBACK TABLE xxx TO BEFORE DROP RENAME TO xxx_old;

SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

SELECT * FROM DBA_RECYCLEBIN;

ALTER TABLE employees_test  ENABLE ROW MOVEMENT;

FLASHBACK TABLE employees_test  TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '.1' minute);


top   prev   next  

Logon Trigger

-- logon trigger: 
-- For Specific user, set cursor sharing as force, leave other user using cursor_sharing=EXACT

CREATE OR REPLACE TRIGGER MYUSERSCHEMA.MYUSERSCHEMA_LOGON_TRIGGER_#01
  AFTER LOGON
  ON MYUSERSCHEMA.SCHEMA
  -- ON database 
BEGIN
  execute immediate  'alter session set cursor_sharing=''FORCE''';
END;
/

constraint

select * from dba_constraints where owner='MY_USERNAME' and table_name = 'MY_PARENT_TABLE_NAME';

select * from dba_constraints where owner='MY_USERNAME' and table_name = 'MY_CHILD_TABLE_NAME';

select * from dba_constraints 
where 
owner='MY_USERNAME' and 
table_name = 'MY_CHILD_TABLE_NAME' and 
constraint_type='R';

select * from dba_constraints
where ( owner, constraint_name ) in (
select R_OWNER, R_CONSTRAINT_NAME 
from 
   dba_constraints 
where 
   owner='MY_USERNAME' and 
   table_name = 'MY_CHILD_TABLE_NAME' and 
   constraint_type='R');

-- from child, find parent 
select p.owner, p.table_name,
c.owner, c.table_name, c.constraint_name child_const, 
p.constraint_name parent_const 
from dba_constraints p, dba_constraints c
where 
p.owner='MY_USERNAME' and 
p.table_name = 'MY_CHILD_TABLE_NAME' and 
p.owner=c.r_owner and 
p.constraint_name = c.r_constraint_name and
c.constraint_type='R';

-- from parent, find child
select * from dba_constraints
where 
owner='MY_USERNAME' and 
table_name = 'MY_PARENT_TABLE_NAME' and 
( owner, constraint_name ) in (
select R_OWNER, R_CONSTRAINT_NAME 
from 
   dba_constraints 
where 
   constraint_type='R');
top

External Links

whatismyipaddress  oracle dba faq