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

quick link

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

# 64-bit odbc-administrator-tool
C:\Windows\System32\Odbcad32.exe

# 32-bit odbc-administrator-tool
C:\Windows\SysWoW64\Odbcad32.exe

DBA_RESUMABLE

set pagesize 500 linesize 380 trimspool on

select status, count(*) cnt from dba_resumable group by status;

select * from v$sort_usage;

select * from v$sort_usage order by blocks desc;

select sid,serial# from v$session where serial# in (
select session_num from v$sort_usage where username='&&MYUSERNAME'
);

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 /u01/oracle/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

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

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

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

sqlplus generate html email with customerized style

1): header
echo "MIME-Version: 1.0
From:${FROM_EMAIL}
To: ${TO_EMAIL}
Cc: ${CC_EMAIL}
Subject:  My Report Title
Content-Type: multipart/mixed; boundary=NextPart_000
Content-Transfer-Encoding: 7bit
--NextPart_000
Content-Type: text/html
">${XHTML_FILE}

2): spool and select 
set pagesize 1000 linesize 380 trimspool on
set sqlprompt ''
set head on verify off echo on feedback off term off 
-- SET MARKUP HTML ON SPOOL OFF PREFORMAT OFF ENTMAP ON
-- SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
set markup HTML ON -
HEAD "<style type='text/css'> -
body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} - 
p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
table {font:10pt Arial,Helvetica,sans-serif; color:Black; background:White; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; box-sizing:border-box; border-collapse:collapse} -
tr {font:10pt Arial,Helvetica,sans-serif; color:Black; background:White; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:White; padding:1px 0px 0px 0px; margin:0px 0px 0px 0px; border-top-width: 1pt,border-bottom-width: 1pt,border-left-width: 1pt,border-right-width: 1pt} -
th {font:bold 10pt Arial,Helvetica,sans-serif; color:White; background:SteelBlue; padding:0px 0px 0px 0px;} -
h1 {text-align:center; font:bold 16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 {text-align:center; font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
</style> -
<title>My Report Title</title> "-
BODY "" -
TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL ON ENTMAP OFF PREFORMAT OFF

2a):
<table border="1" cellspacing="0" cellpadding="0" align="left" width="50%" 
style="width:50.38%;
border-collapse:collapse;
margin-left:6.75pt;margin-right:6.75pt">

<tr style="height:10.5pt">

<td width="19%" style="width:19.98%;background:steelblue;padding:.75pt .75pt .75pt .75pt;height:10.5pt">
<td width="11%" style="width:11.22%;padding:.75pt .75pt .75pt .75pt;height:10.5pt">
<p align="right" style="text-align:right;">
<span style="font-size:7.5pt;font-family:"Arial",sans-serif;color:red">2.82</span>
</p>
</td>

<td width="8%" style="width:8.0%;padding:.75pt .75pt .75pt .75pt;height:10.5pt">
<p align="right" style="text-align:right;">
<span style="font-size:7.5pt;font-family:"Arial",sans-serif">97.18</span>
</p>
</td>

</tr>
</table>

3):
/usr/lib/sendmail -t<${XHTML_FILE_PRETTY}

sqlplus command

-- spool command may be slow inside sqlplus in linux
-- spool xxx.txt
-- spool off


sqlplus syntax 

Starting Command-line SQL*Plus

-L Logon
-S Silent

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

SQLPLUS> help copy
SQLPLUS> COPY supports CHAR, DATE, LONG, NUMBER, LOB and VARCHAR2

The preferred method of doing this is to use SQL*Plus on the host where the database resides.

COPY {FROM database | TO database | FROM database TO database} 
{APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

-- ---
# The array size is a configuration variable which set the fetch size
SET ARRAYSIZE 15

SET COPYCOMMIT 10
The default value is 0, which means that SQL*Plus commits changes only 
when the COPY command is finished. 

The maximum value for this setting is 5000.
-- ---
eg 1:

COPY FROM HR/your_password@HQ TO JOHN/your_password@WEST -
REPLACE WESTEMPLOYEES -
USING SELECT * FROM EMPLOYEES;

eg 2:
COPY FROM HR/your_password@BOSTONDB -
TO TODD/your_password@CHICAGODB -
CREATE NEWDEPT (DEPARTMENT_ID, DEPARTMENT_NAME, CITY) -
USING SELECT * FROM EMP_DETAILS_VIEW


-- but the following command will be fast to get the output 
sqlplus -s scott/tiger<<EOF>test.lst
@test
exit
EOD

-- 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
set colsep " "
set colsep ,

whenever sqlerror exit
whenever sqlerror exit sql.sqlcode;
whenever sqlerror exit rollback;
whenever sqlerror exit failure rollback;

sqlplus 
10g 

sqlplus -L: Attempts to log on just once, instead of reprompting on error

sqlplus -S: silent, sometimes need silent, less messages, sometimes need more messages ("verbose") for output

SET  SHOW      OFF
SET  SPACE       1
set heading OFF
set trimspool on
set linesize 380 trimspool on
set long          300000
set longchunksize 300000
set newpage 0
set pagesize 0
set define off
set heading off
set feedback off
set echo off
set termout off

spool tmp.sql
spool off

set termout on
set heading on
set feedback on
set echo on
set define on
set pagesize 5000
set newpage 1
set heading ON
SET  EMBEDDED  ON

-- set  colsep  " | "
-- set header " | "

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  -->  SET DEFINE ON: 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.

SET  EMBEDDED  OFF -- Executing a SELECT statement will force a page break, and the page number will be reset to 1.

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;

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

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"

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

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
;

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;

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;

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;

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
;

Sort Usage

select * from v$sort_usage;

select sid, serial# from v$session where serial# in (select session_num from v$sort_usage);

select sid, serial# from v$session where serial# in (select session_num from v$sort_usage where username='&&MYUSERNAME');
-- alter system kill session 'sid,serial#' immediate;

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;

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;

define MY_TABLE_OWNER=my_username
define MY_TABLE_NAME=my_tablename
define MY_COLUMN_NAME=my_Columname

set pagesize 100
set long 300000
set longchunk 300000
select * from dba_ind_columns 
where 
table_owner='&&MY_TABLE_OWNER' and 
table_name ='&&MY_TABLE_NAME' and 
column_name = '&&MY_COLUMN_NAME' and 
rownum < 30;

select dbms_metadata.get_ddl('INDEX',index_name, index_owner) xxx
from dba_ind_columns 
where 
table_owner='&&MY_TABLE_OWNER' and 
table_name ='&&MY_TABLE_NAME' and 
column_name = '&&MY_COLUMN_NAME' and 
rownum < 30;


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;

Table Constraints and Related Indexes

define OWNER=MYUSERNAME

SELECT * FROM (
      SELECT c.table_name, cc.column_name, cc.position column_position
      FROM dba_constraints c, dba_cons_columns cc
      WHERE c.constraint_name = cc.constraint_name
      AND c.constraint_type = 'R'
      AND c.owner = upper('&&owner') and cc.owner = upper('&&owner')
      MINUS
      SELECT i.table_name, ic.column_name, ic.column_position
      FROM dba_indexes i, dba_ind_columns ic
      WHERE i.index_name = ic.index_name 
      AND i.owner = upper('&&owner') and ic.index_owner = upper('&&owner')
      )
ORDER BY table_name, column_position;

Initrans Setting For Tables and Indexes

set pagesize 1000

DEFINE MYUSER1=USER1
DEFINE MYUSER2=USER2

select * from 
(
SELECT 'ALTER index '||owner||'.'||index_name||' INITRANS 20 ;' CMD
from dba_indexes
where owner IN ('&&MYUSER1', '&&MYUSER2')
and index_type != 'LOB'
and ini_trans < 20
and table_name not in (select table_name
                       from dba_tables
                       where owner IN ('&&MYUSER1', '&&MYUSER2')
                         and TEMPORARY = 'Y')
order by 1
)
union all
select * from 
(
SELECT 'ALTER TABLE '||owner||'.'||table_name||' INITRANS 20 ;' CMD
from dba_tables
where owner IN ('&&MYUSER1', '&&MYUSER2')
  and TEMPORARY <> 'Y'
  and IOT_TYPE <> 'IOT' 
  and ini_trans < 20
order by 1);

Table Fragmentation And Table Reorg

-- You can find fragmented tables using below query 

set linesize 380 trimspool on pagesize 5000
select  owner,table_name,round(round((blocks*8),2)/1024,1) "size (mb)" ,
round((num_rows*avg_row_len/1024/1024),1) "actual_data (mb)",
(round(round((blocks*8),2)/1024,1) - round((num_rows*avg_row_len/1024/1024),2)) "wasted_space (mb)",
round((round(round((blocks*8),2)/1024,1) - 
round((num_rows*avg_row_len/1024/1024),2))*100/round(round((blocks*8),2)/1024,1),2) waste_pct
from  dba_tables
where round(round((blocks*8),2)/1024,1) - round((num_rows*avg_row_len/1024/1024),2) > 1000
and owner not in ( 'SYS', 'SYSTEM', 'SYSAUX')
-- order by 5 /* by size */ desc
order by 6 /* by pct */ desc;

rowid file_id block_id segment extent

DBMS_SPACE.SPACE_USAGE


dbms_rowid file_id block_id

-- dbms_rowid.rowid_relative_fno

select
emp_id
,dbms_rowid.rowid_relative_fno(rowid) file_num
,dbms_rowid.rowid_block_number(rowid) block_num
,dbms_rowid.rowid_row_number(rowid) row_num
from emp;

select
   file_id,
   block_id
from
   dba_extents -- currently the table is a single extent
where
   segment_name = 'MYTABLE'
and
   owner = 'MYOWNER';

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

select 
segment_name
from 
dba_extents
where 
 between block_id and (block_id + blocks - 1)
and 
file_id = ;

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

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN segment_name FORMAT A24
COLUMN segment_type FORMAT A24
 
SELECT segment_name, segment_type, block_id, blocks
FROM   dba_extents
WHERE
       file_id = &file_no
AND
( &block_value BETWEEN block_id AND ( block_id + blocks ) );

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

select 
count(*)  
from  

  where 
dbms_rowid.rowid_block_number(rowid)  = 
  and 
dbms_rowid.rowid_relative_fno (rowid)  = ;

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

select dbms_rowid.rowid_create
  (1,114154,11,target_block,target_row) ret_rowid
  from
  (select 172 target_block from dual) a,
   (select rownum-1 target_row from dual connect by level < 8) ;

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

object_no := DBMS_ROWID.ROWID_OBJECT(row_id);

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

v$bh and dba_objects

v$bh.objd dba_objects.data_object_id

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

SELECT o.owner, o.object_name, subobject_name, ct number_of_blocks 
FROM 
    DBA_OBJECTS o, 
    (select 
        /*+ no_merge */ 
        objd, count(*) ct 
    from    v$bh 
    where   objd < power(2,22) 
    and status != 'free' 
    group by 
        objd 
    )   bh 
 WHERE o.data_object_id  = bh.objd 
   AND o.owner     != 'SYS' 
 ORDER BY ct desc, o.owner, o.object_name, o.subobject_name 
;

clear breaks
break on report
Compute sum of mb on report
compute sum of pct on report
 
prompt Blocks > 100K
 
SELECT  o.object_name, ct number_of_blocks, ct*vp.value/1024/1024 MB,
   (ct*vp.value/1024/1024)/(vs.value/1024/1024)*100 pct
FROM
    v$parameter vp,
    v$sga vs,
    DBA_OBJECTS o,
    (select
        /*+ no_merge */
        obj, count(*) ct
    from    x$bh
    group by obj
    having count(*) > 100000)   bh
WHERE 1=1
and vp.name='db_block_size'
and vs.name='Database Buffers'
and o.data_object_id  = bh.obj
AND o.owner = 'MY_USER'
ORDER BY ct desc;

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID(+) = bh.OBJD AND o.DATA_OBJECT_ID IS NULL
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

SELECT segment_name, owner, tablespace_name, class#, count(class#)
FROM v$bh bh, dba_rollback_segs drs
WHERE NOT EXISTS (SELECT 1 FROM sys.obj$ o2 WHERE o2.dataobj# = bh.objd)
AND drs.segment_id = class#
GROUP BY segment_name, owner, tablespace_name, class#
HAVING count(class#) > 1;

SELECT class#, count(class#) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY class#
HAVING count(class#) > 1;

SELECT status, count(status) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY status;

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

-- table reorg
set linesize 80
set linesize 380 trimspool on pagesize 5000

set verify off
define MYUSERNAME=myowner
define MYTABLENAME=mytablename

define MYTEMPTBS=mytemptbs

define MYSHRINKTBSPROC=dbas.my_datafile_resize

select owner, table_name,PARTITIONED,TABLESPACE_NAME 
from dba_tables 
where owner='&&MYUSERNAME' and table_name = '&&MYTABLENAME';

select 'ALTER TABLE ' ||owner|| '.' || table_name || ' MOVE TABLESPACE &&MYTEMPTBS;' cmd 
from dba_tables 
where owner='&&MYUSERNAME' and table_name = '&&MYTABLENAME' and partitioned='NO' 
union all 
select 'execute &&MYSHRINKTBSPROC ( '''|| TABLESPACE_NAME  ||''' ) ;' cmd 
from dba_tables 
where owner='&&MYUSERNAME' and table_name = '&&MYTABLENAME' and partitioned='NO'
union all
select 'ALTER TABLE ' ||owner|| '.' || table_name || ' MOVE TABLESPACE ' || TABLESPACE_NAME  || ';' cmd 
from dba_tables 
where owner='&&MYUSERNAME' and table_name = '&&MYTABLENAME' and partitioned='NO';

ALTER TABLE MOVE LOB

-- NOTE:1394613.1 - How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)?

-- NOTE:1396120.1 ALTER TABLE...MOVE To Shrink A Table (BASICFILE/SECUREFILE LOBs)

-- define MYUSERNAME=myowner
-- define MYTABLENAME=mytablename

define MYUSERNAME=xxx
define MYTABLENAME=yyyy

set pagesize 5000 linesize 380 trimspool on

set long 300000
set longchunk 300000

select dbms_metadata.get_ddl( 'TABLE', '&&MYTABLENAME', '&&MYUSERNAME' ) from dual;
 
-- prompt alter table &&MYUSERNAME.&&MYTABLENAME move lob(LOBCOLUMNNAME1) store as ( tablespace NEWTABLESPACENAME1 );
-- prompt alter table &&MYUSERNAME.&&MYTABLENAME move lob(LOBCOLUMNNAME2) store as ( tablespace NEWTABLESPACENAME2 );

select l.table_name, l.column_name, s.tablespace_name lob_tablespace 
from DBA_lobs l, DBA_segments s, DBA_segments t
where 
l.owner = '&&MYUSERNAME' 
and l.table_name = '&&MYTABLENAME'
and l.segment_name = s.segment_name
and t.segment_name = '&&MYTABLENAME'
and t.segment_type = 'TABLE'
and l.owner=t.owner
and l.owner=s.owner;

select 'alter table ' || l.owner || '.' || l.table_name || ' move lob(' 
|| l.column_name || ') store as ( tablespace ' || s.tablespace_name || ');' cmd
from DBA_lobs l, DBA_segments s, DBA_segments t
where 
l.owner = '&&MYUSERNAME' 
and l.table_name = '&&MYTABLENAME'
and l.segment_name = s.segment_name
and t.segment_name = '&&MYTABLENAME'
and t.segment_type = 'TABLE'
and l.owner=t.owner
and l.owner=s.owner;

index rebuild

select to_char(min(LAST_DDL_TIME), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_objects where owner='MYUSERNAME';
select to_char(max(LAST_DDL_TIME), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_objects where owner='MYUSERNAME';

select to_char(max(LAST_DDL_TIME), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_objects where object_type='INDEX' and owner='MYUSERNAME';
select to_char(min(LAST_DDL_TIME), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_objects where object_type='INDEX' and owner='MYUSERNAME';

select distinct status from dba_indexes where status='UNUSABLE';
select * from dba_indexes where status='UNUSABLE';

select distinct index_type from dba_Indexes where owner='&&MYUSERNAME';
select index_type, count(*) cnt from dba_indexes group by index_type;

set pagesize 0
-- select distinct index_type from dba_indexes;
-- ALTER INDEX myowner.myindexname REBUILD TABLESPACE my_temp_tbs PARALLEL 2 ;
-- ALTER INDEX myowner.myindexname REBUILD TABLESPACE my_orig_tbs PARALLEL 2 ;
-- ALTER INDEX myowner.myindexname NOPARALLEL;


-- rebuild indexes for one table
set linesize 80
set linesize 380 trimspool on pagesize 5000

set verify off
define MYUSERNAME=myowner
define MYTABLENAME=mytablename

define MYTEMPTBS=mytemptbs

define MYSHRINKTBSPROC=dbas.my_datafile_resize

select owner, index_name,PARTITIONED,TABLESPACE_NAME 
from dba_indexes 
where table_owner='&&MYUSERNAME' and table_name='&&MYTABLENAME' and index_type not in ( 'LOB' ) 
order by tablespace_name, owner, index_name;

select * from (
select 'ALTER INDEX ' ||owner || '.' || index_name || ' REBUILD TABLESPACE &&MYTEMPTBS PARALLEL 2;'  CMD 
from dba_indexes 
where partitioned='NO' and table_owner='&&MYUSERNAME' and table_name='&&MYTABLENAME' and index_type not in ( 'LOB' ) 
order by tablespace_name, owner, index_name
)
union all
select ' ' from dual 
union all 
select 'execute &&MYSHRINKTBSPROC ( '''|| TABLESPACE_NAME  ||''' ) ;' cmd  from (
select distinct TABLESPACE_NAME  TABLESPACE_NAME
from dba_indexes 
where partitioned='NO' and table_owner='&&MYUSERNAME' and table_name='&&MYTABLENAME' and index_type not in ( 'LOB' ) 
order by tablespace_name
)
union all
select ' ' from dual 
union all
select * from (
select 'ALTER INDEX ' ||owner || '.' || index_name || ' REBUILD TABLESPACE ' || TABLESPACE_NAME || ' PARALLEL 2;'  CMD 
from dba_indexes 
where partitioned='NO' and table_owner='&&MYUSERNAME' and table_name='&&MYTABLENAME' and index_type not in ( 'LOB' ) 
order by tablespace_name, owner, index_name
)
union all
select ' ' from dual 
union all
select * from (
select 'ALTER INDEX ' ||owner || '.' || index_name || ' NOPARALLEL;'  CMD 
from dba_indexes 
where partitioned='NO' and table_owner='&&MYUSERNAME' and table_name='&&MYTABLENAME' and index_type not in ( 'LOB' ) 
order by tablespace_name, owner, index_name
)
;

-- for the whole schema
set pagesize 0
select 'alter index ' || owner || '.' || index_name || ' rebuild online;' cmd 
from dba_Indexes 
where 
owner='&&MYUSERNAME' and 
index_type not in ( 'LOB' ) 
order by index_name;

-- select index_name from dba_indexes where tablespace_name = '&MY_TABLESPACE';

-- alter index MYOWNER.MY_INDEX_NAME rebuild tablespace MY_TABLESPACE  online;
-- for the whole tablespace 
set pagesize 0
select 'alter index ' || owner || '.' || index_name || ' rebuild online;' cmd 
from dba_Indexes 
where 
tablespace_name='&&MYTABLESPACENAME' and 
index_type not in ( 'LOB' ) 
order by index_name;

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

Statistics

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;

COL CLIENT_NAME FORMAT A40
COL STATUS FORMAT A20
COL AUTOSTATS_TARGET FORMAT A20

SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT 
WHERE CLIENT_NAME LIKE
'auto optimizer stats collection';

select status from dba_autotask_client 
where client_name = 'auto optimizer stats collection';

exec dbms_auto_task_admin.enable('auto optimizer stats collection', null, null);

-- exec dbms_auto_task_admin.disable('auto optimizer stats collection', null, null);

exec dbms_stats.lock_table_stats('MYUSER','MYTABLE');
exec dbms_stats.unlock_table_stats('MYUSER','MYTABLE');

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;

SELECT 'exec DBMS_STATS.LOCK_TABLE_STATS (''SYSADM'', ''' ||TABLE_NAME||''');'
FROM DBA_TABLES 
WHERE 
OWNER='MYUSERNAME' 
AND TABLE_NAME LIKE '%MY_PATTERN%';

exec DBMS_STATS.CREATE_STAT_TABLE('DBAS','SYSTEM_STATS_TAB_20181210','TOOLS');
exec dbms_stats.EXPORT_DATABASE_STATS('SYSTEM_STATS_TAB_20181210','SYSTEM_STATS_TAB_20181210_1601','DBAS');


exec DBMS_STATS.CREATE_STAT_TABLE('MYUSER','MYSTATSTABLE','MYTABLESPACE');

exec dbms_stats.EXPORT_DATABASE_STATS('stats_table', 'identifier','MYUSER');
exec dbms_stats.EXPORT_DATABASE_STATS('MYSTATSTABLE','MYRELEASE2','MYUSER');

exec dbms_stats.IMPORT_DATABASE_STATS('MYSTATSTABLE','MYRELEASE2','MYUSER');

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS (
   ownname          => 'MYUSERNAME',
   tabname          => 'MYTABLENAME',
   estimate_percent => 5, /* DBMS_STATS.AUTO_SAMPLE_SIZE */ 
   method_opt       => 'FOR ALL INDEXED COLUMNS SIZE AUTO', /* FOR COLUMNS SIZE AUTO */
   granularity      => 'ALL',
   cascade          => TRUE,
   degree           => 8,
   no_invalidate    => FALSE);
END;
/

-- for table, by num_rows from last stats

-- select owner, table_name, num_rows, last_analyzed from dba_tables where table_name='mytablename';

set linesize 380 trimspool on pagesize 5000 feedback off verify off time off timing off

define MYUSERNAME=MYOWNER
define MYTABLENAME=MYTABLE

select 'set time on' cmd from dual
union all
select 'set timing on' cmd from dual
union all
select 'set echo on' cmd from dual
union all
select 'select to_char(sysdate,''YYYY/MM/DD HH:MI:SS'') begin_analyze from dual;' cmd from dual
union all
select 'execute dbms_stats.gather_table_stats(OWNNAME => ''&&MYUSERNAME'', TABNAME => ''&&MYTABLENAME'',PARTNAME=>NULL,ESTIMATE_PERCENT => ' || pct || ',CASCADE => TRUE);' cmd 
from (
select case 
        when cnt < 10000 then 100
        when cnt >= 10000 and cnt < 30000 then 80
        when cnt >= 30000 and cnt < 70000 then 60
        when cnt >= 70000 and cnt < 100000 then 50
        when cnt >= 100000 and cnt < 300000 then 40
        when cnt >= 300000 and cnt < 500000 then 30
        when cnt >= 500000 and cnt < 1000000 then 20
        when cnt >= 1000000 and cnt < 2000000 then 10
        when cnt >= 2000000 and cnt < 5000000 then 5
        when cnt >= 5000000 and cnt < 10000000 then 2
        when cnt >= 10000000 and cnt < 20000000 then 1
        when cnt >= 20000000 then 0.5 
       end pct from (
select num_rows cnt from dba_tables where owner='&&MYUSERNAME' and table_name = '&&MYTABLENAME' 
)) 
union all 
select 'select to_char(sysdate,''YYYY/MM/DD HH:MI:SS'') end_analyze from dual;' cmd from dual;

-- for table, by row count

-- execute dbms_stats.gather_table_stats(OWNNAME => 'MYUSERNAME', TABNAME => 'MYTABLENAME',PARTNAME=>NULL,ESTIMATE_PERCENT => 10,CASCADE => TRUE);

set linesize 380 trimspool on pagesize 5000 feedback off verify off time off timing off

define MYUSERNAME=myowner
define MYTABLENAME=mytablename

select 'set time on' cmd from dual
union all
select 'set timing on' cmd from dual
union all
select 'set echo on' cmd from dual
union all
select 'select to_char(sysdate,''YYYY/MM/DD HH:MI:SS'') begin_analyze from dual;' cmd from dual
union all
select 'execute dbms_stats.gather_table_stats(OWNNAME => ''&&MYUSERNAME'', TABNAME => ''&&MYTABLENAME'',PARTNAME=>NULL,ESTIMATE_PERCENT => ' || pct || ',CASCADE => TRUE);' cmd 
from (
select case 
        when cnt < 10000 then 100
        when cnt >= 10000 and cnt < 30000 then 80
        when cnt >= 30000 and cnt < 70000 then 60
        when cnt >= 70000 and cnt < 100000 then 50
        when cnt >= 100000 and cnt < 300000 then 40
        when cnt >= 300000 and cnt < 500000 then 30
        when cnt >= 500000 and cnt < 1000000 then 20
        when cnt >= 1000000 and cnt < 2000000 then 10
        when cnt >= 2000000 and cnt < 5000000 then 5
        when cnt >= 5000000 and cnt < 10000000 then 2
        when cnt >= 10000000 and cnt < 20000000 then 1
        when cnt >= 20000000 then 0.5 
       end pct from (
select count(*) cnt from &&MYUSERNAME..&&MYTABLENAME
)) 
union all 
select 'select to_char(sysdate,''YYYY/MM/DD HH:MI:SS'') end_analyze from dual;' cmd from dual;

-- for table, by segment size

-- for table, by segment size
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, by segment size
-- find schemas: select owner,count(*) from dba_objects group by owner having count(*) > 100 order by owner;


-- for schema, by segment size
set linesize 380 trimspool on pagesize 5000
define MY_USERNAME=DBAS
spool a.lst
prompt set verify on echo on term on feedback on
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(13) || chr(10) || '/' xxx  
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;' || chr(13) || chr(10) || '/' xxx  
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
;
spool off


set pagesize 5000
select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;

SELECT TABLE_NAME, LAST_ANALYZED, NUM_ROWS 
FROM DBA_TABLES 
WHERE 
LOWER(OWNER)=lower('&&MY_USERNAME') AND NUM_ROWS <> 0 
ORDER BY LAST_ANALYZED DESC;

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

-- Using default: DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the sample size
set serveroutput on
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MYUSERNAME');


-- NULL: actually consider every row and not just estimate the statistics
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MYUSERNAME', estimate_percent => NULL);

alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

column host_name format a32
column owner format a12
column table_name format a32
set pagesize 1000 linesize 380 trimspool on
select * from (
select host_name, sysdate PROCESSED_DATE , OWNER , table_name,
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C'))
 as count,
num_rows,
last_analyzed 
from DBA_tables , v$instance
where owner in ( 'MYUSERNAME' ) 
and TABLE_NAME like '%TMP%' or TABLE_NAME like '%TAO%' 
and num_rows <> 0 and rownum < 3000 
) 
where count = 0 and num_rows <> 0
order by last_analyzed;

select to_char(max(LAST_ANALYZED), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_tables where owner='MYUSERNAME';
select to_char(min(LAST_ANALYZED), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_tables where owner='MYUSERNAME';

select to_char(max(LAST_ANALYZED), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_tables where owner='MYUSERNAME' and last_analyzed > sysdate -1;
select to_char(min(LAST_ANALYZED), 'YYYY-MM-DD HH24:MI:SS' ) xxx from dba_tables where owner='MYUSERNAME'  and last_analyzed > sysdate -1;

set linesize 380 trimspool on pagesize 5000

column OWNER format a12
column table_name format a32
select owner, table_name, to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS' ) xxx, num_rows 
from dba_tables 
where last_analyzed + 8/24 > (select max(LAST_ANALYZED) from dba_tables where owner='MYUSERNAME' )
order by last_analyzed;

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 = 'MYUSERNAME' 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='MYUSERNAME';
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='MYUSERNAME' and owner='MYTABLENAME';

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

SET SERVEROUT ON TRIMS ON LINES 1000 PAGES 50000;

COL TARGET_BEFORE FORMAT A20
COL TARGET_AFTER FORMAT A20

SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') TARGET_BEFORE FROM DUAL;
EXEC DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET','AUTO');
-- EXEC DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET','ORACLE');
SELECT DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET') TARGET_AFTER FROM DUAL;

-- 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');
EXEC DBMS_STATS.SET_TABLE_PREFS('&&MY_USERNAME',    '&&MY_TABLENAME','DEGREE', '8');
EXEC DBMS_STATS.SET_TABLE_PREFS('&&MY_USERNAME',    '&&MY_TABLENAME','GRANULARITY', 'GLOBAL AND PARTITION');

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 = '&&MY_USERNAME' 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 = '&&MY_USERNAME' and last_analyzed < sysdate - 240;

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

exec dbms_stats.restore_table_stats(ownname=>'&MYUSERNAME',tabname=>'&MYTABLENAME', 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='&MYUSERNAME'    and table_name='&MYTABLENAME' ;

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

Histogram

https://raw.githubusercontent.com/oracle/oracle-db-examples/master/optimizer/histogram_history/h_hist.sql

Extended Statistics Multi-Column (Column Group) Statistics
https://blogs.oracle.com/optimizer/extended-statistics

https://oracle-base.com/articles/11g/statistics-collection-enhancements-11gr1#multi_column_statistics

https://oracle-base.com/articles/11g/extended-statistics-enhancements-11gr2

select dbms_stats.create_extended_stats(null, 'my_tablename', '(column_name1,column_name2)') from dual;

column extension format a30
select extension_name, extension from dba_stat_extensions where table_name='mytable';

exec dbms_stats.drop_extended_stats(ownname => 'myschema',tabname => 'my_tablename',extension => '(column_name1,column_name2)');

# ------------------------------------------------------------------------

http://www.br8dba.com/oracle-histograms/

-- histogram: Gather stats for column level is called histogram, A histogram sorts values into buckets (SIZE n )
   For columns that contain data skew, a histogram enables the optimizer to generate accurate cardinality 
   estimates for filter and join predicates that involve these columns
   NDV: Number of Distinct Value

-- select ......
-- select * from table(dbms_xplan.display_cursor());

-- no histogram created on the column group, need to regather stats
-- exec dbms_stats.gather_table_stats(null, 'customers', method_opt => 'for all columns size skewonly');
exec dbms_stats.gather_table_stats(null, 'customers');

exec  DBMS_STATS.GATHER_TABLE_STATS ( ownname=>'MYSCHEMA', tabname=>'MYTABLE', method_opt => 'FOR COLUMNS column_name1');

DBA_TAB_COL_STATISTICS.HISTOGRAM
USER_TAB_COL_STATISTICS.HISTOGRAM

-- SYS.COL_USAGE$

select column_name, num_distinct, num_nulls, histogram,notes 
from user_tab_col_statistics where table_name = 'mytablename';

SELECT column_id,
       column_name,
       histogram
FROM   user_tab_columns
WHERE  table_name = 'TAB1'
ORDER BY column_id;

-- histogram: FREQUENCY 'TOP FREQUENCY' 'HEIGHT BALANCED' HYBRID NONE  

FREQUENCY: Each key has its own value. 
           A frequency histogram is created when the number of distinct values (NDV) 
           for the column is less than or equal to the total number of histogram buckets, which 
           defaults to 254. This means each distinct value can be represented by its own bucket.

TOP FREQUENCY: the same as FREQUENCY, but ignore the insignificant value

HEIGHT BALANCED: Each bucket has the same number of values,skewed values occupy more buckets

HYBRID: 

NONE: No histogram present for the column


-- dba_tab_histograms, USER_TAB_HISTOGRAM: display information about the contents of histograms.
1. The ENDPOINT_VALUE column represents the highest distinct column value in the bucket 
and the ENDPOINT_NUMBER is a cumulative frequency of rows in the bucket.

2. If we want to get the actual frequency of the specific column value, 
we need to subtract the previous cumulative value. 

-- 2a):
SELECT endpoint_value,
       endpoint_number,
       endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS frequency
FROM   user_tab_histograms
WHERE  table_name  = 'TAB1'
AND    column_name = 'RECORD_TYPE'
ORDER BY endpoint_value;

-- 2b):
COLUMN range FORMAT A20

SELECT '<=' || endpoint_value AS range,
       endpoint_value - (LAG(endpoint_value, 1, -1) OVER (ORDER BY endpoint_value)+1) + 1 AS vals_in_range,
       endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS frequency
FROM   user_tab_histograms
WHERE  table_name  = 'TAB1'
AND    column_name = 'RECORD_TYPE'
ORDER BY endpoint_value;

-- 2c):
SELECT (LAG(endpoint_value, 1, 0) OVER (ORDER BY endpoint_value)+1) || '-' || endpoint_value AS range,
       endpoint_value - (LAG(endpoint_value, 1, 0) OVER (ORDER BY endpoint_value)+1) + 1 AS vals_in_range,
       endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS frequency,
       endpoint_repeat_count
FROM   user_tab_histograms
WHERE  table_name  = 'TAB1'
AND    column_name = 'RECORD_TYPE'
ORDER BY endpoint_value;

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

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_REPEAT_COUNT
FROM   USER_HISTOGRAMS
WHERE  TABLE_NAME='&MY_TABLE_NAME'
AND    COLUMN_NAME='&MY_COLUMN_NAME'
ORDER BY 1;

-- gather_..._stats()
-- estimate_percent => dbms_stats.auto_sample_size
-- estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
-- METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'
-- METHOD_OPT=>'FOR ALL COLUMNS SIZE REPEAT'
-- METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'
-- method_opt       => 'for all columns size auto' 
                       or 'for all indexed columns size 254'
-- cascade          => TRUE
-- no_invalidate    => FALSE, TRUE or DBMS_STATS.AUTO_INVALIDATE 
  if you set no_invalidate to false then all of the dependent cursors 
  in the shared pool will be automatically invalidated as soon as 
  the statistics gather command completes. 
  If you have a large number of dependent cursors this can result 
  in a large increase in the number of hard parses done on the system, 
  which could result in shared pool contention.

-- ------------------------------------------------------
-- select dbms_stats.get_prefs ('METHOD_OPT') from dual;
-- exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');
-- method_opt => 'for all columns size repeat' 
   only re-analyze histograms for indexes that have existing histograms.

-- AUTO_SAMPLE_SIZE (default)

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

-- Oracle Histograms
-- The table "monitoring" option
http://www.dba-oracle.com/t_histograms.htm

How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?
https://blogs.oracle.com/optimizer/how-do-i-drop-an-existing-histogram-on-a-column-and-stop-the-auto-stats-gathering-job-from-creating-it-in-the-future

When Oracle creates a histogram on a VARCHAR2 column it will only 
consider the first 64 characters of the column value in Oracle Database 12c 
or the first 32 characters of the column value in Oracle Database 11g. 

# delete column stats
exec dbms_stats.delete_column_stats( ownname=>'MYUSER', tabname=>'MYTABLE', colname=>'MYCOL', col_stat_type=>'HISTOGRAM');

# skip column stats
exec dbms_stats.set_table_prefs('MYUSER', 'MYTABLE','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 MYSKIPPEDCOLUMN_NAME');

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

https://adityanathoracledba.com/2017/09/05/oracle-histograms-simplified-part-1/

https://adityanathoracledba.com/2018/02/04/oracle-histograms-simplified-part-2/

http://www.br8dba.com/oracle-histograms/

https://oracle-base.com/articles/12c/optimizer-statistics-advisor-12cr2

EXEC DBMS_STATS.set_table_prefs('MY_SCHEMA', 'MY_TABLE', 'GRANULARITY', 'AUTO');
EXEC DBMS_STATS.set_table_prefs('MY_SCHEMA', 'MY_TABLE', 'ESTIMATE_PERCENT', DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.gather_table_stats('MY_SCHEMA', 'MY_TABLE', granularity => 'AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

--
--
-- List histogram history
--
--

define MY_TABLE_NAME=XXX
define MY_USERNAME=YYY

set linesize 250
set serveroutput on verify off

declare
  v_colnum     pls_integer;
  v_colname      varchar2(128);
  v_savetime         timestamp;
  v_savetime_text     varchar2(50);
  v_prevnumbuck     number(10);
  v_pprev_numbuck    number(10);
  v_tim_count  number(10);
  v_fmt        varchar2(100) := 'yyyy-dd-mm hh24:mi:ss';
  v_bucket_count_now      number(10);
  v_line       varchar2(1000);
  p_tabname  varchar2(200)  := upper('&&MY_TABLE_NAME.');
  p_ownname  varchar2(200) := upper('&&MY_USERNAME.');

  cursor current_cols is
     select c.column_name,
            c.column_id,
            decode(s.histogram,'NONE','[Current: No Histogram]','[Current: '||nvl(s.histogram,'No Stats')||']') histogram,
            to_char(s.LAST_ANALYZED,v_fmt) last_analyzed, 
            s.num_buckets
     from   dba_tab_col_statistics s,
            dba_tab_columns c
     where  c.table_name = p_tabname
     and    c.owner      = p_ownname
     and    s.table_name  (+) = c.table_name
     and    s.owner       (+) = c.owner
     and    s.column_name (+) = c.column_name
     order by column_id;

  cursor distinct_tim is
     select distinct savtime,SAMPLE_DISTCNT
     from   sys.WRI$_OPTSTAT_HISTHEAD_HISTORY
     where  intcol# = v_colnum
     and    obj# = (select object_id from dba_objects where object_name = p_tabname and owner=p_ownname and object_type = 'TABLE')
     order by savtime;

  cursor distinct_col is
     select distinct intcol#, colname
     from   sys.WRI$_OPTSTAT_HISTHEAD_HISTORY
     where  obj# = (select object_id from dba_objects where object_name = p_tabname and owner=p_ownname and object_type = 'TABLE');

  cursor hh is
     select count(*) buckets
     from   sys.WRI$_OPTSTAT_HISTGRM_HISTORY
     where  obj# = (select object_id from dba_objects where object_name = p_tabname and owner=p_ownname and object_type = 'TABLE')
     and    intcol# = v_colnum
     and    savtime = v_savetime;
begin
   select decode(p_ownname,'USER',user,p_ownname) into p_ownname from dual;

   dbms_output.put_line('Table : '||p_ownname||'.'||p_tabname);
   for r in current_cols
   loop
      v_bucket_count_now := r.num_buckets;
      dbms_output.put_line('Column: '||rpad(substr(r.column_name,1,40),41)||'  Last analyzed: '||r.last_analyzed||' '||r.histogram);
      v_colnum := r.column_id;
      v_colname := r.column_name;
      v_tim_count := 0;
      v_pprev_numbuck := 0;
      for t in distinct_tim
      loop
        v_savetime := t.savtime;
        v_savetime_text := to_char(v_savetime,v_fmt);
        for h in hh
        loop
           v_prevnumbuck := h.buckets;
           if (v_tim_count = 0)
           then
              v_line := '-     '||v_savetime_text||' ';
           else
              if (v_pprev_numbuck != v_prevnumbuck) 
              then
                 dbms_output.put_line(v_line||to_char(v_pprev_numbuck,9999)||' -> '||to_char(v_prevnumbuck,9999)||' buckets CHANGE');
              else
                 dbms_output.put_line(v_line||to_char(v_prevnumbuck,9999) ||'          buckets');
              end if;
              v_pprev_numbuck := v_prevnumbuck;
           end if;
           v_line := '-     '||v_savetime_text||' ';
        end loop; 
        v_tim_count := v_tim_count + 1;
      end loop; 
      if (v_bucket_count_now>1)
      then
         if (v_bucket_count_now!=v_prevnumbuck)
         then
            dbms_output.put_line('-     '||r.last_analyzed||' '||to_char(v_prevnumbuck,9999)||' -> '||to_char(v_bucket_count_now,9999)||' buckets CHANGE');
         else
            dbms_output.put_line('-     '||r.last_analyzed||' '||to_char(v_bucket_count_now,9999)||' buckets');
         end if;
      else
         if (v_prevnumbuck>1)
         then
            dbms_output.put_line('-     '||r.last_analyzed||' '||to_char(v_prevnumbuck,9999)||' ->     0 buckets'||' CHANGE');
         else
            dbms_output.put_line('-     '||r.last_analyzed||'     0          buckets');
         end if;
      end if;
   end loop;
end;
/

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

Stats Advisor Rules

-- 1):
alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm';

-- v$stats_advisor_rules

SET LINESIZE 200

COLUMN name FORMAT A35
COLUMN description FORMAT A75

SELECT * FROM   v$stats_advisor_rules ORDER BY rule_id;

select RULE_ID, NAME, RULE_TYPE, DESCRIPTION from V$STATS_ADVISOR_RULES order by 1;

-- 2):
-- The advisor task ('Statistics Advisor') runs automatically in the maintenance window. 

-- select RULE_ID, NAME, RULE_TYPE, DESCRIPTION from V$STATS_ADVISOR_RULES order by 1;

RULE_ID NAME				     RULE_TYPE	DESCRIPTION
------- ------------------------------------ ---------- ---------------------------------------------------------------------------
      0 				     SYSTEM
      1 UseAutoJob			     SYSTEM	Use Auto Job for Statistics Collection
      2 CompleteAutoJob 		     SYSTEM	Auto Statistics Gather Job should complete successfully
      3 MaintainStatsHistory		     SYSTEM	Maintain Statistics History
      4 UseConcurrent			     SYSTEM	Use Concurrent preference for Statistics Collection
      5 UseDefaultPreference		     SYSTEM	Use Default Preference for Stats Collection
      6 TurnOnSQLPlanDirective		     SYSTEM	SQL Plan Directives should not be disabled
      7 AvoidSetProcedures		     OPERATION	Avoid Set Statistics Procedures
      8 UseDefaultParams		     OPERATION	Use Default Parameters in Statistics Collection Procedures
      9 UseGatherSchemaStats		     OPERATION	Use gather_schema_stats procedure
     10 AvoidInefficientStatsOprSeq	     OPERATION	Avoid inefficient statistics operation sequences
     11 AvoidUnnecessaryStatsCollection      OBJECT	Avoid unnecessary statistics collection
     12 AvoidStaleStats 		     OBJECT	Avoid objects with stale or no statistics
     13 GatherStatsAfterBulkDML 	     OBJECT	Do not gather statistics right before bulk DML
     14 LockVolatileTable		     OBJECT	Statistics for objects with volatile data should be locked
     15 UnlockNonVolatileTable		     OBJECT	Statistics for objects with non-volatile should not be locked
     16 MaintainStatsConsistency	     OBJECT	Statistics of dependent objects should be consistent
     17 AvoidDropRecreate		     OBJECT	Avoid drop and recreate object seqauences
     18 UseIncremental			     OBJECT	Statistics should be maintained incrementally when it is beneficial
     19 NotUseIncremental		     OBJECT	Statistics should not be maintained incrementally when it is not beneficial
     20 AvoidOutOfRange 		     OBJECT	Avoid Out of Range Histogram endpoints
     21 UseAutoDegree			     OBJECT	Use Auto Degree for statistics collection
     22 UseDefaultObjectPreference	     OBJECT	Use Default Object Preference for statistics collection
     23 AvoidAnalyzeTable		     OBJECT	Avoid using analyze table commands for statistics collection

24 rows selected.

-- -------------------------------------------------------
-- check if the Optimizer Statistics Advisor has something to recommend:

SET LONG 1000000
SET LONGCHUNKSIZE 100000 
SET SERVEROUTPUT ON
SET LINE 300
SET PAGES 1000

DECLARE
  v_tname   VARCHAR2(128) := 'TEST_TASK_01';
  v_ename   VARCHAR2(128) := NULL;
  v_report  CLOB := NULL;
  v_script  CLOB := NULL;
BEGIN
  v_tname  := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  v_ename  := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
  v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
  DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

select inserts, updates, deletes from dba_tab_modifications 
where table_name in ('TEST1','TEST2');

TABLE_NAME  INSERTS UPDATES DELETES
----------- ------- ------- -------
TEST1       333030  0       9999
TEST2       219075  317123  0

select table_name, last_analyzed, stale_stats from dba_tab_statistics 
where table_name in ('TEST1','TEST2');

TABLE_NAME LAST_ANALYZED		   STA
---------- ------------------------------- ---
TEST1      21-Aug-2017 10:27:32am          YES
TEST2      21-Aug-2017 10:28:09am          YES

-- Let me check if the Optimizer Statistics Advisor has something to recommend:

SET LONG 1000000
SET LONGCHUNKSIZE 100000 
SET SERVEROUTPUT ON
SET LINE 300
SET PAGES 1000

DECLARE
  v_tname   VARCHAR2(128) := 'TEST_TASK_01';
  v_ename   VARCHAR2(128) := NULL;
  v_report  CLOB := NULL;
  v_script  CLOB := NULL;
BEGIN
  v_tname  := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  v_ename  := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
  v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
  DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

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

-- Implementing the advisor's recommendations

DECLARE
  v_tname   VARCHAR2(128) := 'TEST_TASK_01';
  v_implementation_result CLOB;
BEGIN
  v_implementation_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;
/

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

-- Instead of transforming the examples from the report you could simply implement the advisor's recommendations:

SQL> spool my_script_advisor_task.sql
SQL> select DBMS_STATS.SCRIPT_ADVISOR_TASK('TEST_TASK_01') from dual;
SQL> spool off

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

SQL PATCH

sql patch 

sys.dbms_sqldiag_internal.i_create_patch(sql_text=> 'select ... where deptno= :deptno',hint_text=> 'DYNAMIC_SAMPLING(4)',name => 'EMP_PATCH');

SYS.DBMS_SQLDIAG.create_diagnosis_task
exec DBMS_SQLDIAG.DROP_SQL_PATCH('EMP_PATCH');

SQL Profiles

select * from table(dbms_xplan.display(format=>'ADVANCED'));
select * from table( dbms_xplan.display_cursor(format=>'rowstats last ') );

set autotrace off
set autotrace traceonly explain
set autotrace traceonly
set autotrace on

list profiles:
SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM   DBA_SQL_PROFILES;

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

select name,version from v$sql_hint 
where upper(name) like '%'||upper(nvl('&hint',name))||'%'
order by name;

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

http://houseofbrick.com/manual-creation-of-a-sql-profile/

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE to implement the profile

begin
dbms_output.put_line(
dbms_sqltune.accept_sql_profile(
task_name => 'SQL_TUNING_01',
name => 'MY_SQL_PROFILE',
replace => true,
force_match => true
)
);
end;
/

ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST';


# SQL profiles are used to lock down execution plans

SQL Profiles are create by running Automatic SQL Tuning.
DECLARE
sql_stmt clob;
BEGIN
sql_stmt:=q'^select count(1) from perf_table where data_value1='ODD'^';
dbms_sqltune.import_sql_profile(
sql_text => sql_stmt,
name=>'SQL_PROFILE_PERF',
profile => sqlprof_attr(q'^FULL(@"SEL$1" "PERF_TABLE"@"SEL$1"))^',
q'^OUTLINE_LEAF(@"SEL$1")^',
q'^ALL_ROWS^',
q'^OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')^',
q'^OPT_PARAM('_optimizer_gather_feedback' 'false')^',
q'^OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')^',
q'^OPT_PARAM('_optimizer_adaptive_plans' 'false')^',
q'^OPT_PARAM('_optimizer_dsdir_usage_control' 0)^',
q'^OPT_PARAM('_px_adaptive_dist_method' 'off')^',
q'^OPT_PARAM('_optimizer_use_feedback' 'false')^',
q'^DB_VERSION('12.1.0.2')^',
q'^OPTIMIZER_FEATURES_ENABLE('12.1.0.2')^',
q'^IGNORE_OPTIM_EMBEDDED_HINTS^'),
force_match=>true);
end;
/

begin
dbms_sqltune.drop_sql_profile(
name=>'SQL_PROFILE_PERF',
ignore=>true);
end;
/

SQL Plan Management (SPM)

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

OPT_ESTIMATE hints

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

begin
 dbms_output.put_line('task id: '||
 dbms_sqltune.create_tuning_task(
  task_name=>'dbiInSite',
  description=>'dbi InSite workshop Oracle Tuning',
  scope=>dbms_sqltune.scope_comprehensive,
  time_limit=>30,
  sql_id=>'4fz1vtn0w8aak'
  )
 );
 dbms_sqltune.execute_tuning_task('dbiInSite');
end;
/

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

set long 1000000 longc 1000000
select dbms_sqltune.report_tuning_task('dbiInSite') FROM dual;

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

SQL Trace

http://structureddata.org

http://structureddata.org/2011/08/18/creating-optimizer-trace-files/

tkprof
trcsess

-- to invalid a cursor 
-- exec dbms_shared_pool.purge('address,hash_value','C') from v$sqlarea where address=rawtohex(v$sqlarea.address) 

oracle trcsess tkprof

The Hotsos Profiler is the tool that Hotsos developers have constructed
to maximize the value of event 10046 10053 data.

-- trace the optimizer event: 10053
alter session set tracefile_identifier='MY_10053';
alter session set events '10053 trace name context forever';
alter session set events '10053 trace name context off';

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60, waits => TRUE, binds => FALSE);
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);

ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET SQL_TRACE = FALSE;

TKPROF : format trace files

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]

SQL> -- SQL Trace (10046)
SQL> ALTER SYSTEM SET EVENTS 'sql_trace [sql:&&sql_id] bind=true, wait=true';
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQL> -- 10053
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION";

-- enable trace for a specific sql_id
alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:a8phhr6w1an53]';

alter system set events 'trace[rdbms.SQL_Optimizer.*]

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] 
         {pgadep: exactdepth 0} {callstack: fname opiexe}
         plan_stat=all_executions,wait=true,bind=true';

SQL> alter session set events 'trace[RDBMS.SQL_Transform] [SQL: 32cqz71gd8wy3]
          disk=high RDBMS.query_block_dump(1) processstate(1) callstack(1)';

SQL> oradebug
SQL> oradebug doc
SQL> oradebug doc component SQL_Compiler

SQL> alter system set events 'sql_trace[SQL:aua4zfxn0953q] plan_stat=all_executions,wait=true,bind=true';
SQL> alter system set events 'trace[rdbms.SQL_Execution.*] off';

SQL> alter system set events 'sql_trace[SQL:aua4zfxn0953q] plan_stat=all_executions,wait=true,bind=true';
SQL> alter system set events 'sql_trace[SQL:aua4zfxn0953q] off';

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

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;

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

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

metadata get ddl

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

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;

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;

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;

chained rows

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

sample sql:
SQL> ANALYZE TABLE  MYUSERNAME.MYTABLENAME LIST CHAINED ROWS;

set pagesize 500 linesize 380 trimspool on
-- select table_name from dba_tables where owner='MYUSERNAME' order by table_name;
select 'analyze table ' || owner || '.' || table_name || ' LIST CHAINED ROWS; ' cmd 
from dba_tables where owner='MYUSERNAME' 
order by table_name;

-- see result by table
set linesize 180 pagesize 500 trimspool on
select owner,table_name,NUM_ROWS,LAST_ANALYZED,CHAIN_CNT 
from dba_tables 
where owner='&&MYOWNER' and TABLE_NAME='&&MYTABLENAME';

-- see result by schema 
set linesize 180 pagesize 500 trimspool on
select owner,table_name,NUM_ROWS,LAST_ANALYZED,CHAIN_CNT, 
chain_cnt*100/num_rows c_pct 
from dba_tables 
where 
owner='&&MYOWNER' 
and chain_cnt > 10000 
and last_analyzed > sysdate - 10 
order by chain_cnt;

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

set linesize 380 trimspool on pagesize 5000

-- ANALYZE TABLE t COMPUTE STATISTICS;
-- Tables with migrated/chained rows
set pagesize 500 linesize 380 trimspool on
-- select table_name from dba_tables where owner='MYUSERNAME' order by table_name;
select 'analyze table ' || owner || '.' || table_name || ' COMPUTE STATISTICS; ' cmd 
from dba_tables 
where owner='MYUSERNAME' 
and rownum < 20 
order by table_name;

set linesize 380 trimspool on pagesize 5000

select
   owner owner,
   table_name table_name,
   pct_free pct_free,
   pct_used pct_used,
   avg_row_len avg_row_len, 
   num_rows num_rows,
   chain_cnt chain_cnt,
   chain_cnt/num_rows chain_pct
from dba_tables
where
owner not in ('SYS','SYSTEM')
and chain_cnt > 0
and num_rows > 0
order by chain_cnt desc
;

-- ------------------------------------------------------------------------------
SELECT owner, table_name, chain_cnt, num_rows,
       round(chain_cnt/num_rows*100,2) pct_chained
-- ,
--       avg_row_len, pct_free , pct_used
FROM dba_tables 
where num_rows is not null and chain_cnt > 0 and num_rows > 0
order by pct_chained;

-- This query will show how many chained (and migrated) rows each table has: 
SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0 order by chain_cnt;

-- v$mystat for my session
SELECT a.name, b.value
  FROM v$statname a, v$mystat b
 WHERE a.statistic# = b.statistic#
   AND lower(a.name) = 'table fetch continued row';

SELECT 'Chained or Migrated Rows = '||value FROM v$sysstat WHERE name = 'table fetch continued row';

SELECT name,value FROM v$sysstat WHERE name like '%table%';
 
-- table level: Analyze the table to verify the chain count of the table:
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
SELECT owner, table_name, chain_cnt
  FROM dba_tables
 WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

SELECT chain_cnt,
       round(chain_cnt/num_rows*100,2) pct_chained,
       avg_row_len, pct_free , pct_used
  FROM dba_tables
WHERE owner='XXX' and table_name = 'ROW_MIG_CHAIN_DEMO';

SELECT dbms_rowid.rowid_block_number(rowid) "Block_Number", count(*) "Rows" 
FROM my_test_table 
GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;

-- To see which rows are chained: CHAINED_ROWS $ORACLE_HOME/rdbms/admin/utlchain.sql or UTLCHN1.SQL
create table CHAINED_ROWS (
  owner_name         varchar2(30),
  table_name         varchar2(30),
  cluster_name       varchar2(30),
  partition_name     varchar2(30),
  subpartition_name  varchar2(30),
  head_rowid         rowid,
  analyze_timestamp  date
);

ANALYZE TABLE MY_TABLE LIST CHAINED ROWS;
ANALYZE TABLE MY_TABLE LIST CHAINED ROWS INTO CHAINED_ROWS;

SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'  FROM dba_tables where owner = 'XXX';
SELECT * FROM chained_rows;

flashback database

-- setup
-- init.ora
db_recovery_file_dest=/jobs/DBA/flashback
db_recovery_file_dest_size=500G
db_flashback_retention_target=14400

Also
Make sure archive logfile also have the same retention period

-- flashback retention period
-- init.ora DB_FLASHBACK_RETENTION_TARGET=14400  #unit:minutes, 14400 minutes equal 1 day
-- alter system set db_flashback_retention_target=14400; scope=both; # 10 days
-- or alter system set db_flashback_retention_target=2880; # 2 days
-- SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

-- query flashback earliest time
SELECT 
to_char(oldest_flashback_scn,'999,999,999,999') flashback_scn, 
to_char(oldest_flashback_time,'YYYY-MM-DD hh24:mi:ss') flashback_time
FROM  v$flashback_database_log;

-- Enable database flashback
shutdown immediate;
startup mount;
alter database archivelog;
alter database flashback on;
-- SQL> ALTER DATABASE FLASHBACK OFF;
alter database open;
SELECT flashback_on, log_mode FROM v$database;

-- flashback database to restore point
SQL> select current_scn from v$database;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select * from v$restore_point;

SQL> flashback database to restore point CLEAN_DB;
-- SQL> Flashback Database to timestamp (sysdate-2);
-- SQL> Flashback Database to scn 2034455;

SQL> alter database open resetlogs;
SQL> select current_scn from v$database;

-- create restore point
CREATE RESTORE POINT before_upgrade;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- Listing Restore Points
-- 1): 
rem set pause on
rem set pause 'Press Return to Continue'
set numwidth 20

set linesize 480 trimspool on
set pagesize 5000 
set verify off

column name format a46
column scn  format 999,999,999,999,999
column restore_point_time    format a32
column storage_size          format 999,999,999,999
column database_incarnation# format 99
column guarantee_flashback_database for a3

SELECT NAME, SCN, 
to_char(time,'YYYY-MM-DD hh24:mi:ss') restore_point_time,
DATABASE_INCARNATION#, 
GUARANTEE_FLASHBACK_DATABASE,
STORAGE_SIZE 
FROM V$RESTORE_POINT
ORDER BY restore_point_time;

rman> LIST RESTORE POINT ALL;

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

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 A.owner, A.table_name, A.column_name, A.constraint_name 
from dba_cons_columns A, dba_constraints B 
where A.table_name = B.table_name and B.constraint_type = 'R';

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

Random Password

-- using DBMS_RANDOM package 
SELECT 
DBMS_RANDOM.STRING('A',2) || 
TRUNC(DBMS_RANDOM.VALUE(5, 9)) || 
decode(TRUNC(DBMS_RANDOM.VALUE(0, 2)),0,'#', 1, '%', '%') || 
TRUNC(DBMS_RANDOM.VALUE(0, 4)) || 
DBMS_RANDOM.STRING('A',4) ||  
TRUNC(DBMS_RANDOM.VALUE(0, 4)) xx 
FROM DUAL;

sqlnet.ora tnsnames.ora listener.ora

sqlnet.ora client side
set up oracle sqlnet.log location: 
in sqlnet.ora 
LOG_DIRECTORY_CLIENT=C:\oracle\product\10.2.0\client_1\network\log

sqlnet.ora server side
SQLNET.INBOUND_CONNECT_TIMEOUT=240
SQLNET.EXPIRE_TIME=10
# SQLNET.EXPIRE_TIME=1   # this number is too low
NAMES.DIRECTORY_PATH= (TNSNAMES)

#tnsnames.ora client side, ENABLE=BROKEN
#Solution For: could not resolve the connect identifier "%s"

ORCL =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
(SERVICE_NAME = ORCL)
)
)  

-- toad debug
For toad pl/sql debugging:
GRANT DEBUG ANY PROCEDURE TO XXX;
GRANT DEBUG CONNECT SESSION TO XXX;

mv refresh: alter materialized view

dba_sys_privs

Find table name:
select table_name, owner from dba_tables where table_name like '%ERROR%' and  owner in ( 'USER1', 'USER2' );

dba_sys_privs

find unusable index:
select owner, index_name, status from dba_indexes where status='UNUSABLE' and owner in ( 'USER1', 'USER2' );

-- start database read only
startup mount ;
alter database open read only ;

-- start database read write
startup mount ;
alter database end backup;
recover database ;
alter database open ;

-- start and shutdown
startup mount ;
alter database end backup ;
recover database ;
alter database open ;
shutdown immediate ;

External Links