Display table column followed by data

 declare

   sql_stmt varchar2(200);

   BU varchar2(5);

   col_name varchar2(30);

   col_val  varchar2(4000);

begin

 ------cursor

   for x in (select COLUMN_NAME from user_tab_columns where TABLE_NAME=upper('ps_voucher') order by COLUMN_ID ) loop

     cnt:=cnt+1;

     sql_stmt:='select '''||x.COLUMN_NAME||''', to_char('|| x.COLUMN_NAME|| ') from ps_voucher where business_unit=:1 and voucher_id = :2'; 

   --DBMS_OUTPUT.PUT_LINE('sql='||sql_stmt);

     EXECUTE IMMEDIATE sql_stmt into col_name, col_val  USING 'BU', 'VI'

     DBMS_OUTPUT.PUT_LINE(col_name||'='||col_val);

    end loop;

 exception

    WHEN OTHERS then

     declare

        errcd  NUMBER := SQLCODE;

        errmsg VARCHAR2(300) := SQLERRM;

     begin

        DBMS_OUTPUT.PUT_LINE('Error: rec#'|| cnt || ' rc='|| errcd ||','||errmsg);

     end;

 end;

/

Open DB in RO

Read only oracle database tips

CREATE OR REPLACE TRIGGER
   manage_service
after startup on database
DECLARE
   role VARCHAR(30);
BEGIN
   SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
   IF role = 'PRIMARY' THEN
      DBMS_SERVICE.START_SERVICE('sales_rw');
   ELSE
      DBMS_SERVICE.START_SERVICE('sales_ro');
END IF;
END;

CONNECT BY Explained

 

 Source 

Result of each iteration is used as PRIOR in next iteration. 

Calculate Field Offset in a Record

Useful for generate load file:

select table_name, COLUMN_ID, COLUMN_NAME,DATA_TYPE, DATA_LENGTH, sum(DATA_LENGTH) 

over(PARTITION BY  table_name order by COLUMN_ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Running Length"

from all_tab_columns where table_name ='PS_REQ_HDR'

How many times a sql gets run?

select SQL_ID, CHILD_NUMBER, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME,module, action
from gv$sql where sql_text like 'select * from %'

CHILD_NUMBER: 0,1,2,...
EXECUTIONS: # of time a sql runs
ROWS_PROCESSED: # of rows fetched, can increase with more fetching
MODULE: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_MODULE
ACTION: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_ACTION

Source: V$SQL reference

Source: Why some sqls show up multiple times?  CHILD_NUMBER (unique, 0 ~...)

• caused by schema difference(owner), optimizer mode(ALL_ROWS,FIRST_ROWS), bind var(implicit conversion)...
• Why cursor IS_SHAREABLE = 'No' (reason: select * from v$SQL_SHARED_CURSOR where sql_id=...) 

Source: Different v$sql views

• v$sql the details -- if you have multiple copies of the query: "select * from T" in your shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.
• v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.
• v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.
• v$sqltext_with_newlines is v$sqltext without the whitespace replacment.
• 
  

Compare:

select sql_id, CHILD_NUMBER,EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME, module, action, sql_text from gv$sql where sql_text  ='....'

with:
select sql_id, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED,  LAST_LOAD_TIME, LAST_ACTIVE_TIME , module, action, sql_text  

from gv$sqlarea where sql_text ='..'

2nd cursor will aggregate info on all columns from 1st  cursor and sum up totals from  EXECUTIONS, ROWS_PROCESSED

Source: PS 8.50 uses DBMS_APPLICATION_INFO to Identify DB Sessions

For some components the action is set to ‘xyzzy’. This seems to be a default value set when the component is opened, but before any of the pages are processed.  Therefore, it refers to activity in the search dialogue, including processing of :

• look ups to obtain values for search criteria
• SQL issued during SearchSave PeopleCode to validate the search criteria.
• the query on the Component Search record

MODULE & ACTION are different in v$sql vs v$session.

Source: Histogram vs Stats

• improve skew condition - histograms are useful for improving cardinality estimates
• Histograms are created automatically when statistics are gathered using the SKEWONLY and AUTO options in METHOD_OPT: EXEC DBMS_STATS.GATHER_TABLE_STATS( … METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO' …)
• How does the Oracle Database know that a particular column is used in a query predicate or join? This information is gathered by the Oracle Optimizer at parse time and ultimately stored in the Oracle data dictionary in a table called SYS.COL_USAGE$. 

To manually flush monitoring info: dbms_stats.flush_database_monitoring_info()

Export / Import Oracle Table/Index Stats

Source - Why Export Import optimizer statistics: 

Importing and exporting statistics for the CBO and the systems stats (external system statistics for CPU, I/O. etc) and useful in a variety of areas:

• Export production into test to make test systems "look like" large systems for execution plan generation".
• Export/imports can be used to control execution plans by "freezing execution plans".
• Statistics are used as a backup before re-analyzing a schema.
• System stats can be moved to a smaller server to make it appear as if Oracle is executing on a large fast server.         

- System stats:  When migrating to a new server, you can export the old system statistics to ensure consistent execution plans until you are ready to use the "real" system stats.

- Systems reverse:  Conversely, you can migrate system stats from production to test to make a tiny server appear to be a larger server.  This will not improve SQL execution speed, but developers will see the same execution plans that they would see in production

- Backup stats:  Before making any production change to the CBO stats with dbms_stats, take a full schema backup and an backup of your dbms_stats system stats.  Remember, the primary reason for re-analyzing stats is to change SQL execution plans.

For example, here we export production table stats and backport them to the test database to make it appear to be a larger table

Source - Steps



Source - STATs stale?

EX: 
* when table was last analyzed: select table_name, to_char(last_analyzed,'MM-DD-YYYY HH24:MI'),stale_stats from all_tab_statistics where STALE_STATS='YES'

* what tables got modified recently(MONITORING attribute must be Yes): 
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, to_char(timestamp,'MM-DD-YYYY HH24:MI') from dba_tab_modifications where timestamp > trunc(sysdate) and TABLE_OWNER='SYSADM'  
(delayed flushing; for immediate flush:  
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;)

During the gather_*_stats, FLUSH_DATABASE_MONITORING_INFO would have flushed the information ; it could wipe out the entry from dba_tab_modifications  instead

Source - Rebuild TABLE stats include INDEX stats?

1. create INDEX....
2. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'

3. exec dbms_stats.gather_table_stats(....)

4. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'
union
select table_name,  num_rows, last_analyzed from user_tables where table_name ='A'

Source - Restore Previous Stats
dba_histograms  
dba_tab_stats_history

exec DBMS_STATS.DELETE_TABLE_STATS
exec dbms_stats.restore_table_stats(...)
exec dbms_stats.import_table_stats

Scan SQL history for reference to Object

Quick scan on DB for sqls used a certain objects:

set serveroutput on linesize 4000

declare 

   sqltxt clob;
   cnt integer;
   
begin

      cnt:=0;
      
      for x in (select sql_id, sql_fulltext from gv$sql where regexp_like (sql_fulltext ,'OBJECT_NAME','i') 
                and not regexp_like (sql_fulltext ,'^declare|sql_fulltext|sys.col','i')) 
      loop
      
      cnt:=cnt+1;
      
      DBMS_OUTPUT.PUT_LINE(lpad(cnt,5,0)||': '|| x.sql_id ||'-'||x.sql_fulltext );

      end loop;
   
exception
   
      WHEN OTHERS then
        declare
           errcd  NUMBER := SQLCODE;
           errmsg VARCHAR2(300) := SQLERRM;
        begin
           DBMS_OUTPUT.PUT_LINE('Error: rec#'|| cnt || ' rc='|| errcd ||','||errmsg);
        end;
   
end;
/

Find session record locks

select d.module, d.action, a.SID, USERNAME,OSUSER, machine, LOCK_TYPE,MODE_HELD "Lock Mode", OBJECT_TYPE "Object",OBJECT_NAME "Name",a.PROCESS,c.process||'-'||a.blocking_session "Blocked By", to_char(to_date(SECONDS_IN_WAIT,'sssss'),'hh24:mi:ss') "Wait Time" from ( 
        Select se.sql_id, lk.SID, se.username, to_single_byte(se.OSUser) osuser, to_single_byte(se.Machine) as machine, 
               DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
               DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held,
               DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested,
               ob.object_id, ob.object_type, ob.object_name, decode(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block, se.lockwait, se.process, se.blocking_session
        FROM   gv$lock lk, dba_objects ob, gv$session se
        WHERE  lk.TYPE IN ('TX', 'TM', 'UL') AND    lk.SID = se.SID AND    lk.id1 = ob.object_id (+)) a, gv$session_wait b, gV$LOCKED_OBJECT c, gv$sql d  
        where a.lockwait is not null and a.sid = b.sid and SECONDS_IN_WAIT>0
        and a.object_id=c.object_id and blocking_session is not null and a.blocking_session=c.session_id
        and LOCK_TYPE<>'Transaction'
        and a.sql_id=d.sql_id(+)
        

Oracle Table Time Stamp

Original Link

CREATED = date of creation of the object

LAST_DDL_TIME = last ddl on object, would include CREATE OR REPLACE (example below)

TIMESTAMP = last time the external "view" or "specification" of the object changed -- will be between created and last_ddl_time.

it starts with the timestamp = created = last_ddl_time. 

Read Only Table/Session/DB

Table
* make RO: ALTER TABLE table1 READ ONLY;

* check RO : select read_only from user_tables where table_name = 'XXX'

DB * make RO:  alter database open read only;

* check RO : select open_mode from v$database;

Session * make RO:  * set transaction read only;

Find table/view name & ddl from sql copybook

This works for PS cobol copybooks, assuming its formatting style, text sacn/grab change needed if not the same format.

1. get rec name: 

cut -c7- xxx.sql|  grep -v ^\* | sed  's/\-\-.*$//' | sed -n '/FROM/I,/WHERE/Ip'  | grep -iv WHERE | awk -F"[(), ]" 'BEGIN{IGNORECASE=1} {for(i = 1; i <= NF; i++) if (match ($i,"^PS")) print $i}'|sort|uniq

2. get ddl:

define obj=&1

declare 
   buf clob;
   typ varchar2(30);

begin 


   dbms_output.ENABLE(200000);

   select OBJECT_TYPE into typ from user_objects where object_name = upper('&obj') and OBJECT_TYPE in ('TABLE','VIEW');   

   select DBMS_METADATA.GET_DDL(typ,upper('&obj')) into buf from dual;
   

   DBMS_OUTPUT.PUT_LINE(buf);

 exception
         WHEN OTHERS then
           declare
              errcd  NUMBER := SQLCODE;
              errmsg VARCHAR2(300) := SQLERRM;
           begin
              DBMS_OUTPUT.PUT_LINE(errcd ||','||errmsg);
           end;

end;
/

Show NLS Parameters

source: https://ferhatsengonul.wordpress.com/2010/07/21/to-show-nls-parameters-for-session-database-instance-together-by-pivot-and-listagg-on-11gr2/

set linesize 200
col "PARAMETER" format a30

col "SESSION" format a30

col DATABASE  format a30

col INSTANCE  format a30

select * from

(select 'SESSION' SCOPE,nsp.* from nls_session_parameters nsp

union

select 'DATABASE' SCOPE,ndp.* from nls_database_parameters ndp

union

select 'INSTANCE' SCOPE,nip.* from nls_instance_parameters nip

) a

pivot  (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)

FOR SCOPE

in ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));

.

Session Time Out

* Source: http://www.dba-oracle.com/t_sqlnet_expire_time.htm

Detecting "dead connections" and disconnecting Oracle sessions in performed in two places, the PMON process, and via SQL*Net, by the sqlnet_expire_time

parameter.

* User Idle_Time  - 

select profile from dba_users where username='XXXXX'

select profile, resource_name, limit from dba_profiles where profile='YYYYY' and
resource_name ='IDLE_TIME' 


* Source: http://www.runningoracle.com/product_info.php?products_id=318

How to make an idle session get SNIPED 
You must set:
 
A. the initialization parameter resource_limit = TRUE in the init.ora

alter system set resource_limit=TRUE scope=both;

B. idle_time in the user profile

then you setup idle sessions to become sniped after x minutes.

With the following example the user session becomes sniped after 8 hours of idle time.

alter profile DEFAULT set idle_time=480;

* Source:http://www.programering.com/a/MzN2QzMwATI.html

ORACLE database INACTIVE, KILLED, ACTIVE, CACHED, SNIPED five kinds of state.  

List permissions

select * from USER_TAB_PRIVS

SELECT * FROM USER_SYS_PRIVS;

SELECT * FROM USER_ROLE_PRIVS;



select * from database_properties  where property_name like 'DEFAULT%TABLESPACE';

sqlplus misc

Display large row data with sqlplus

SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 50000;
set pagesize 50000;
set long 50000;

Login w/o tns entry using ip address: 

sqlplus user@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip_addr)(PORT=1551))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XXXX)))'

Return status from PL/SQL

#!/bin/ksh93

sqlplus  -s xxx/yyy <<EOF

set serveroutput on linesize 2000 feedback off
WHENEVER SQLERROR EXIT SQL.SQLCODE;

variable recordId number;

declare
    sql_ins      varchar (2000):='';
begin
    :recordId:=0;
    sql_ins:=q'{insert into xxx values (yyy)}';

execute immediate sql_ins;

exception
             WHEN OTHERS then
                   :recordId:=SQLCODE;
end;
/
exit :recordId;

EOF

echo 'rc='$?
~

11g / Rule Based

Set opt mode:

alter session set optimizer_mode=RULE/CHOOSE/ALL_ROWS;

show parameter optimizer_mode;
 
delete stats: 

exec dbms_stats.delete_table_stats('HR','EMPLOYEES',cascade_indexes=>true);

select last_analyzed from user_tables where table_name='EMPLOYEES'; (should be null);
select last_analyzed from user_indexes where table_name='EMPLOYEES'; (should be null);

Explain 

EXPLAIN PLAN FOR SELECT XXXXX.....;
SELECT * FROM TABLE(dbms_xplan.display); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3296909491
---------------------------------------------------------------
| Id  | Operation                       | Name                |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |
|*  1 |  FILTER                         |                     |
|*  2 |   TABLE ACCESS BY INDEX ROWID   | PS_x_xxx_xxx        |
|*  3 |    INDEX RANGE SCAN             | PS_X_XXX_XXX        |
|   4 |   CONCATENATION                 |                     |
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "PS_SPEEDCHART_HDR"
              "SYS_ALIAS_2","PS_H_PRC_ORDER_HDR3" "SYS_ALIAS_3")

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
:::::::::::::::::::::::::::::::

 
Note
-----
   - rule based optimizer used (consider using cbo)

114 rows selected.