Get All Table Space syntax

SQL>set heading off;
SQL>set echo off;
SQL>Set pages 999;
SQL>set long 90000;
SQL>spool ddl_list.sql
SQL>select dbms_metadata.get_ddl(’TABLESPACE’,tb.tablespace_name) from dba_tablespaces tb;
SQL>spool off

To Check Parent & Child tables..........

select
  child.owner        || '.' ||
  child.table_name   "Child table",
  'is child of'      " ",
  parent.owner       || '.' ||
  parent.table_name  "Parent table"
from
  dba_constraints child join dba_constraints parent on
    child.r_constraint_name = parent.constraint_name and
    child.r_owner           = parent.owner
where
  parent.table_name like 'RI\_%' escape '\' --';

TO Check The Frag % On tables........

select a.owner,a.table_name,round(b.bytes/1024/1024) "currentsize in MB",round((a.num_rows*a.avg_row_len)/1024/1024) "actualsize in MB",round(((b.bytes/1024/1024-((a.num_rows*a.avg_row_len)/1024/1024))/(b.bytes/1024/1024))*(100-a.pct_free),2) "frag percent" from dba_segments b,dba_tables a where a.owner=b.owner
and b.bytes/1024/1024>100 and b.segment_name=a.table_name and a.owner='DSI_DPIPE';

TO enable the TRACE session level .................

column spid format a5
column sid format 9999
column ora_user format a10
column unix_user format a10
column osuser format a10
column server for a12
column machine for a15
col MACHINE for a10
column whenon format a18 heading "WHEN USER LOGGED ON"
column whendo format a18 heading "WHEN LAST ACTIVITY"
set lin 150
select p.pid, p.spid, s.sid, s.machine,s.serial#,lower(s.username) ora_user, p.username unix_user,Status,
s.osuser,
       to_char(s.logon_time,'mm/dd/yy hh24:mi:ss') whenon,
       to_char(sysdate - (s.last_call_et) / 86400,'mm/dd/yy hh24:mi:ss') whendo
from v$process p, v$session s
where s.paddr(+) = p.addr  and s.sid=1764
order by s.logon_time,s.status;


2. Start the debug session with the SPID of the process that needs traced.

SQL> oradebug setospid 26966

3. Select the appropriate trace level. There are four different options when specifying a tracing level:

• Level 1 – provides “base set” tracing information. Bind variables are displayed as variables (:b1).

• Level 4 – provides Level 1 data and the actual data values of bind variables.

• Level 8 – provides Level 1 data and information on wait events when the elapsed time is greater than the CPU time.

• Level 12 – combines levels 1, 4 and 8 tracing information.

A Level 12 trace contains base set, bind variable values and wait events.
The oradebug command below will enable the maximum tracing possible:

SQL> oradebug event 10046 trace name context forever, level 12

4. Turn tracing off.

SQL> oradebug event 10046 trace name context off

5. Obtain the trace file name. The oradebug facility provides an easy way to obtain the file name:

SQL> oradebug tracefile_name
c:\oracle9i\admin\ORCL92\udump\mooracle_ora_2280.trc


How to Trace(Reference)
----------------

Note:75713.1   Important Customer information about using Numeric Events
Note:21235.1   EVENT: 10262 "Do not check for memory leaks"
Note:21154.1   EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:160178.1  How to set EVENTS in the SPFILE
Note:1058210.6 HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING ORADEBUG
Note:29062.1   SUPTOOL:  ORAMBX (VMS) - Quick Reference

TO Check the Oracle JOB status

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

To check the user Privilages and Roles........

select  privilege||' '||table_name  "Privileges for N514012" from  dba_tab_privs where  grantee='&data'
union
select  privilege from  dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where  grantee='&data';


select 'grant select on  '||owner||'.'||object_name||' to xxint;' from dba_objects where owner='XXDW' and object_type='TABLE';