-- /www/doc/sql/useful_misc.sql
-- $Id: useful-misc.sql,v 1.1.3.1 2001/08/29 05:31:19 andy Exp $
--
-- Useful stuff culled largely from Joe Trezzo's Oracle PL/SQL book.
-- See also the other useful_*.sql files, for the longer code.
-- 
-- by atp@arsdigita.com, 2000-07-17

-- Note that a lot (but not all) of this stuff is already covered by
-- the current version of Cassandracle, but I haven't made any attempt
-- to compare the two or pare down this list.


-- References:
--
-- Trezzo, Joseph C.; _Oracle PL/SQL Tips and Techniques_;
-- Osborne/McGraw-Hill, 1999, www.osborne.com; ISBN: 0-07-882439-9.
-- [Very useful book, though fat and poorly organized.]
--
-- http://www.tusc.com/tusc/nonhtml/plsql_bookcode.zip
-- Code from Trezzo's book.
--
-- Greenspun, Philip; "Tips for Using Oracle";
-- http://www.arsdigita.com/asj/oracle-tips


-- Useful Oracle shells:
-- $ORACLE_HOME/bin/sqlplus
-- $ORACLE_HOME/bin/svrmgrl
-- $ORACLE_HOME/bin/lsnrctl
-- At aD, do NOT use lsnrctl to restart the listener!  Instead, use:
--   sudo /etc/init.d/ora8-net8
-- If you do not, Oracle will not be able to find any external
-- libraries you may be using.  (Something to do with Environment
-- variables, but I haven't looked into it.)

-- Oracle Error Messgage Index:
--   http://oradoc.photo.net/ora816/server.816/a76999/index.htm

-- You probably want to put these in your login.sql file.
-- (When you start up sqlplus, it will execute any login.sql file it
-- find in your current directory.)
set linesize 200
set pagesize 1000
set serveroutput on size 1000000
begin dbms_output.enable(1000000); end;
/


-- To turn off interpretation of & in Sqlplus:
set def off

-- To retrieve a view definition from the database:
-- Sqlplus truncates long type columns, so do:
--
select VIEW_NAME, TEXT_LENGTH from user_views where view_name = upper('your_view_name_here');
--
-- Then, do "set long n" in Sqlplus.  Make sure n is >= TEXT_LENGTH.
set long 1000;
select TEXT from user_views where view_name = upper('your_view_name_here');


-- ** Regular Expressions **
-- The OWA_PATTERN pl/sql regexp package is part of the Oracle PL/SQL Web
-- Toolkit.  Install the whole toolkit with:
--   $ORACLE_HOME/webdb30/admin/plsql/owa/owains.sql
-- or read about OWA_PATTERN here:
--   http://technet.oracle.com/files/search/search.htm?OWA_PATTERN
--   $ORACLE_HOME/webdb30/admin/plsql/owa/pubpat.sql
--   $ORACLE_HOME/webdb30/admin/plsql/owa/privpat.sql


-- Find the table, column, etc. for a constraint.
select
  uc.constraint_name
 ,uc.table_name
 ,ucc.column_name
 ,uc.SEARCH_CONDITION
from user_constraints uc, user_cons_columns ucc
where uc.constraint_name = ucc.constraint_name
  and ucc.column_name = 'LIMIT_PRICE'
  --and uc.table_name in ('ORDERS','ORDERS_AUDIT')
;


-- Get list of invalid objects.
--   Trezzo c. pg. 207, 451
select 
  STATUS ,OBJECT_TYPE ,CREATED ,LAST_DDL_TIME ,TIMESTAMP ,OBJECT_NAME
from user_objects
--from dba_objects
where status != 'VALID'
order by
  timestamp
  --object_name
;
	

-- Get list of disabled triggers.
--   Trezzo pg. 452
-- File: 9_5.sql
SELECT owner, trigger_name, trigger_type, triggering_event, table_owner||'.'||table_name
FROM dba_triggers
WHERE status <> 'ENABLED'
--  AND owner  = 'PLSQL_USER'
ORDER BY owner, trigger_name;



-- According to Xuequn (Robert) Xu xux@arsdigita.com 2000/12/01 in:
--   http://www.arsdigita.com/ticket/issue-view.tcl?msg_id=74326
-- The below queries do NOT show locks on packages.  However you can
-- check the v$access view like this:
select
 username, osuser, process, type, terminal,
 to_char(logon_time, 'YYYY-MM-DD HH24:MI') as logon_time,
 (sysdate-logon_time)*24 as hours_ago
from v$session
order by hours_ago desc, process ;


-- Find Oracle locks:
--   Originally from Philip's Oracle tips document.
-- Usually very fast, but I've seen it hang on a really hosed Oracle
-- with hundreds of resource locks.
set linesize 180
  column username  format a18
  column program   format a32
  column machine   format a12
  column n_seconds format a6
select
  s.username ,s.sid ,s.serial#
  ,ltrim(w.seconds_in_wait) as n_seconds 
  ,process ,machine ,terminal ,program
from  v$session s, v$session_wait w
where s.sid = w.sid
  and s.sid in (select sid from v$lock where lmode=6)
order by s.username ,s.sid ,s.serial#
;

-- alter system kill session 'SID,SERIAL#';

-- Find Oracle locks and what they're doing.
set linesize 180
  column username  format a18
  column program   format a32
  column machine   format a12
  column n_seconds format a6
select
  s.username ,s.sid ,s.serial#
  ,ltrim(w.seconds_in_wait) as n_seconds 
  ,process ,machine ,terminal ,program
  ,sql.sql_text
from  v$session s, v$session_wait w
  ,v$sqltext sql
where s.sid = w.sid
  and s.sid in (select sid from v$lock where lmode=6)
  and sql.address    = s.sql_address
  and sql.hash_value = s.sql_hash_value
  and upper(username) like 'MUNIVERSAL%'
order by s.username ,s.sid ,s.serial# ,sql.piece
;

-- See which users are waiting for a lock.
-- This is fast.
SELECT s.username, s.serial#, s.lockwait
FROM   v$session s
where s.lockwait is not null
order by s.lockwait, s.username ;

-- Who's waiting for a lock, and what SQL is he executing?
-- This is fast.
SELECT s.username, s.serial#, s.lockwait
  ,sql.sql_text
FROM   v$session s
  ,v$sqltext sql
where s.lockwait is not null
  and sql.address    = s.sql_address
  and sql.hash_value = s.sql_hash_value
  and upper(username) like 'MUNIVERSAL%'
order by s.lockwait, s.username ,sql.piece ;




-- What are users currently doing?
set linesize 180
SELECT s.username, s.serial#, sql.sql_text
FROM   v$session s, v$sqltext sql
WHERE  sql.address    = s.sql_address
  AND  sql.hash_value = s.sql_hash_value
  --and upper(s.username) like 'USERNAME%'
order by s.username ,s.sid ,s.serial# ,sql.piece ;


-- What users are currently locked, and what are they doing?
-- Really slow, I never let it finish.
--   Trezzo pg. 475
-- File: 9_21.sql
SELECT s.username, s.serial#, l.id1, st.sql_text
FROM   v$session s, v$lock l, v$sqltext st
  -- It's the joining v$session to v$lock that seems to really slow things down.
WHERE  s.lockwait   = l.kaddr
AND    st.address    = s.sql_address
AND    st.hash_value = s.sql_hash_value;

-- What users are currently locked?
-- Really slow.
SELECT s.username, s.serial#, l.id1
FROM   v$session s, v$lock l
WHERE  s.lockwait   = l.kaddr;

-- Who is CAUSING a user to be locked?
-- Really slow - took almost 8 minutes to return "no rows selected".
--   Trezzo pg. 476
-- File: 9_22.sql
SELECT a.serial#, a.sid, a.username, b.id1, c.sql_text
FROM   v$session a, v$lock b, v$sqltext c
WHERE  b.id1 IN (
    SELECT DISTINCT e.id1
    FROM v$session d, v$lock e
    WHERE d.lockwait   = e.kaddr )
  AND     a.sid        = b.sid
  AND     c.hash_value = a.sql_hash_value
  AND     b.request    = 0;


-- Check init.ora parameters.
--   Trezzo pg. 453
-- File: 9_6.sql
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM v$parameter
ORDER BY name;

-- Check some particular DBMS_JOB related init.ora parameters.
--   Trezzo pg. 576
-- File: 12_9.sql
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM   v$parameter
WHERE UPPER(name) IN ('JOB_QUEUE_PROCESSES','JOB_QUEUE_INTERVAL')
ORDER BY NAME;


-- What database objects are being accessed right now?
-- Returns a potentially massive list of stuff.
--   Trezzo pg. 474
-- File: 9_20.sql
SELECT s.sid, s.username, a.owner, a.object, a.type
FROM   v$session s, v$access a
WHERE  s.sid = a.sid;


-- What statements are executing in each rollback segment?
--   Trezzo pg. 477
-- File: 9_23.sql
SELECT a.name, b.xacts tr, c.sid, c.serial#, c.username, d.sql_text 
FROM   v$rollname a, v$rollstat b, v$session c, 
       v$sqltext d,v$transaction e 
WHERE  a.usn            = b.usn    
AND    b.usn            = e.xidusn 
AND    c.taddr          = e.addr 
AND    c.sql_address    = d.address 
AND    c.sql_hash_value = d.hash_value 
ORDER BY a.name, c.sid, d.piece;


-- Show all open cursors  :
column user_name format a15
column osuser    format a15
column machine   format a15
select user_name ,status ,osuser --,machine
  ,a.sql_text 
from v$session b, v$open_cursor a 
where a.sid = b.sid
order by status ,user_name ,osuser ,a.sql_text ;

-- Show all open cursors, with full text of SQL - slower:
select user_name ,status ,osuser --,machine
  ,c.sql_text 
from v$session b, v$open_cursor a,  v$sqlarea c
where a.sid = b.sid
  and c.address = a.address ;
order by status ,user_name ,osuser ,c.sql_text ;

-- Show how many open cursors are currently in use:
select a.value, b.name 
from v$mystat a, v$statname b 
where a.statistic# = b.statistic# 
  and a.statistic#= 3 ;


-- A list of some useful Oracle V$ views
--   Trezzo pg. 486


-- Jobs running - useful views:
--   Trezzo pg. 577
-- DBA_JOBS, USER_JOBS, DBA_JOBS_RUNNING

-- Submit and control jobs with DBMS_JOB.
--   Trezzo pg. 579 - 583

-- What jobs exist?
--   Trezzo pg. 578
-- File: 12_10.sql
SELECT what, job, priv_user,
       TO_CHAR(last_date, 'MM/DD/YYYY HH24:MI:SS') last,
       DECODE(this_date, NULL, 'NO', 'YES') running,
       TO_CHAR(next_date, 'MM/DD/YYYY HH24:MI:SS') next,
       interval, total_time, broken
FROM   dba_jobs
ORDER BY what;

-- What jobs are running right now?
--   Trezzo pg. 579
-- File: 12_11.sql
SELECT a.job, what,
       TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') now,
       TO_CHAR(a.this_date, 'mm/dd/yyyy hh24:mi:ss') this
FROM   dba_jobs_running a, dba_jobs b
WHERE  a.job = b.job;


-- Stuff about the Data Dictionary
--   Trezzo pg. 675 - 697


-- Go into svrmgrl, connect internal, and do these for your username,
-- so that you can run all the various queries above.  FYI, our Oracle
-- DBA says this is ok to do, to wit:
--
--     "Granting select privileges on dynamic performance views to
--     users is harmless. The "ad_cassandracle" role has almost all of
--     the read privileges on system tables and data dictionary
--     views. You can certainly do the same for [our ACS Oracle user
--     name]."

grant select on dba_objects      to your_oracle_username;
grant select on dba_triggers     to your_oracle_username;
grant select on dba_jobs_running to your_oracle_username;
grant select on dba_jobs         to your_oracle_username;
grant select on dba_dependencies to your_oracle_username;
grant select on v_$parameter     to your_oracle_username;
grant select on v_$session       to your_oracle_username;
grant select on v_$session_wait  to your_oracle_username;
grant select on v_$access        to your_oracle_username;
grant select on v_$lock          to your_oracle_username;
grant select on v_$sqltext       to your_oracle_username;
grant select on v_$rollname      to your_oracle_username;
grant select on v_$rollstat      to your_oracle_username;
grant select on v_$sqltext       to your_oracle_username;
grant select on v_$transaction   to your_oracle_username;
grant select on SYS.FILEXT$      to muniversaldev;
grant select on DBA_FREE_SPACE   to muniversaldev;
grant select on DBA_DATA_FILES   to muniversaldev;
grant select on SYS.DBA_SOURCE   to muniversaldev;


-- Data Dictionary Tables about Columns:
--user_coll_types
--user_tab_columns
--user_clu_columns
--user_col_comments
--user_col_privs
--user_cons_columns
--user_ind_columns
--user_tab_col_statistics
--user_trigger_cols
--user_updatable_columns

connect internal;
-- Grant a user read privileges on all tables in the database:
grant exp_full_database to username;
-- Grant read access to all v$ views, etc.:
grant select_catalog_role to username;


-- Note that you can use bind variables in pl/sql.  This makes using
-- queries out of an ACS 4.x AOLserver error log much easier.  E.g.:
variable user_id         number
variable db_code         varchar2(20)
variable db_access_level varchar2(20)
variable proteome_code   varchar2(20)
variable ip_address      varchar2(20)
variable hostname        varchar2(50)
begin
  :user_id         := 95321;
  :db_code         := 'human';
  :db_access_level := 'lite';
  :proteome_code   := 'proteome';
  :ip_address      := '207.46.131.91';
  :hostname        := 'microsoft.com';
end;
/
show errors


-- Here's how to escape the wildcard meanings of _ with like in:
select object_type  from acs_objects  where object_type like 'p\_%'  escape '\';


-- Heres something out of the BBoard, to find all contraints
-- relationships:
--
-- make a table containing on row per relationship
create table tmp_relations as 
select 
  c1.table_name parent, 
  c1.constraint_name pk,
  c2.table_name child, 
  c2.constraint_name fk,
  c2.r_constraint_name parent_pk
from 
  user_constraints c1,
  user_constraints c2
where c1.constraint_type = 'P'
  and c2.constraint_type = 'R'
  and c2.r_constraint_name = c1.constraint_name
;
-- do a tree query to get complete depth
select child, level  from tmp_relations
start with parent = 'ACS_OBJECTS'
connect by parent = PRIOR child ;