Oracle Apps DBA

set pagesize 0 set long 90000 set feedback off set echo off Define ownernm='&owner' accept pwd prompt "Enter Password for user &ownernm : " hide spool schema.sql connect &ownernm/&pwd SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u; SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u; SELECT DBMS_METADATA.GET_DDL('VIEW',u.view_name) FROM USER_VIEWS u; spool off;

set linesize 132 set verify off -- set feedback off set pagesize 60 col grantee for a20 wrap col owner for a20 wrap col table_name for a30 wrap col column_name for a30 wrap col privilege for a30 wrap col granted_rol for a20 wrap col grantable for a10 wrap heading 'WITH GRANT' col admin_option for a10 wrap heading 'WITH ADMIN' accept grantee_nm prompt 'User or Role Name > ' prompt prompt Object Privileges for &&grantee_nm.... select grantee,owner,table_name,privilege,grantable from sys.DBA_TAB_PRIVS where grantee=upper('&&grantee_nm') order by 2, 3, 1, 4; prompt prompt Column privileges for &&grantee_nm.... select grantee,owner,table_name,column_name,privilege,grantable from sys.DBA_COL_PRIVS where grantee=upper('&&grantee_nm') order by 2, 3, 4, 5, 1; prompt prompt System privileges for &&grantee_nm.... select grantee,privilege,admin_option from sys.DBA_SYS_PRIVS where grantee=upper('&&grantee

SET LINESIZE 200 SET PAGESIZE 9999 COLUMN owner FORMAT A15 HEADING "OWNER" COLUMN tablespace_name FORMAT a15 HEADING "TABLESPACE NAME" COLUMN segment_type FORMAT A10 HEADING "SEGMENT TYPE" COLUMN segment_name FORMAT A20 HEADING "SEGMENT NAME" COLUMN bytes FORMAT 9,999,999,999,999 HEADING "SIZE (IN BYTES)" COLUMN seg_count FORMAT 9,999,999,999 HEADING "SEGMENT COUNT" break on report on owner skip 2 --compute sum label "" of seg_count bytes on owner compute sum label "Grand Total: " of seg_count bytes on report SELECT owner , tablespace_name , segment_name , segment_type , sum(bytes) bytes , count(*) seg_count FROM dba_segments where lower(owner)='&owner' GROUP BY owner , tablespace_name , segment_type , segment_name ORDER BY owner , tablespace

set ver off set linesize 60 col table_name format a15 col last_analyzed format a40 select TABLE_NAME "Table Name",to_char(LAST_ANALYZED,'DD-MON-YY HH24:MI:SS') "Date and Time" from dba_TABLES where lower(TABLE_NAME)='&tname';

prompt accept days prompt 'Enter no of days before' set verify off set linesize 80 select to_char(first_time,'YYYY/MM/DD HH24') "Date - HOfDay",count(1) from V$LOG_HISTORY where first_time > sysdate -&days group by to_char(first_time,'YYYY/MM/DD HH24') order by to_date(to_char(first_time,'YYYY/MM/DD HH24'),'YYYY/MM/DD HH24');

set linesize 150 col owner format a10 col object_name format a15 col object_type format a10 col sid format 99999 col serial# format 99999999 col status format a10 col osuser format a15 col machine format a20 select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;

set serveroutput on size 10000 declare cursor c1 is select * from v$lock where request != 0 order by id1, id2; wid1 number := -999999; wid2 number := -999999; wholder_detail varchar2(120); v_err_msg varchar2(80); wsid number(5); wstep number(2); wtype varchar2(10); wobject_name varchar2(180); wobject_name1 varchar2(80); wlock_type varchar2(50); begin for c1_rec in c1 loop if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then null; else wstep := 10; select sid , type into wsid , wtype from v$lock where id1 = c1_rec.id1 and id2 = c1_rec.id2 and request = 0 and lmode != 4; dbms_output.put_line(' '); wstep := 20; select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||' SID: '|| s.sid||&

set pagesize 23 set pause on set pause 'Hit any key...' set linesize 132 col sid format 999999 col serial# format 999999 col username format a12 trunc col process format a8 trunc col terminal format a12 trunc col type format a12 trunc col lmode format a4 trunc col lrequest format a4 trunc col object format a25 trunc select s.sid, s.serial#, decode(s.process, null, decode(substr(p.username,1,1), '?', upper(s.osuser), p.username), decode( p.username, 'ORACUSR ', upper(s.osuser), s.process) ) process, nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username, decode(s.terminal, null, rtrim(p.terminal, chr(0)), upper(s.terminal)) terminal, decode(l.type, -- Long locks 'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ', 'UL', 'PLS USR LOCK', -- Short locks

set pagesize 60 set linesize 140 col reads_per_exec format 99999999 heading "Reads Per Exec" col disk_reads format 99999999 heading "Disk Reads" col executions format 999999999 heading "Executions" col sql_text format a70 heading "Sql Text" select cast(disk_reads/(executions+1) as integer) as reads_per_exec,disk_reads,executions,sql_text from V$SQLAREA where disk_reads/(executions+1) > 1 and executions > 1 and rownum<10 order by disk_reads/(executions+1) desc;

set pagesize 999 set linesize 132 col username format a10 col os_user format a10 col pid format 9999 col sid format 99999 column serial# format 9999999 col PHYSICAL_READS format 9999999 col BLOCK_GETS format 999999999 col CONSISTENT_GETS format 9999999999 col BLOCK_CHANGES format 99999999 col CONSISTENT_CHANGES format 9999999 select username, OSUSER os_user, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_GETS, CONSISTENT_GETS, BLOCK_CHANGES, CONSISTENT_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID order by PHYSICAL_READS, ses.USERNAME /

set linesize 132 col INSTANCE_NAME format a10 col HOST_NAME format a15 col VERSION format a10 col STATUS format a7 col LOG_SWITCH_WAIT format a10 col LOGINS format a10 col BLOCKED format a5 select INSTANCE_NAME,HOST_NAME,VERSION,STATUS,LOG_SWITCH_WAIT,LOGINS,BLOCKED from v$instance;

SET LINESIZE 200 SET VERIFY OFF COLUMN OWNER FORMAT A8 heading "Index|Owner" COLUMN TABLE_OWNER FORMAT A8 heading "Table|Owner" COLUMN INDEX_NAME FORMAT A30 heading "Index Name" COLUMN COLUMN_NAME FORMAT A30 heading "Column Name" COLUMN COLUMN_POSITION FORMAT 9999 heading "Pos" BREAK ON CONSTRAINT_NAME SKIP PAGE SELECT IND.OWNER, IND.TABLE_OWNER, IND.INDEX_NAME, IND.UNIQUENESS, COL.COLUMN_NAME, COL.COLUMN_POSITION FROM SYS.DBA_INDEXES IND, SYS.DBA_IND_COLUMNS COL WHERE IND.TABLE_NAME = upper('&table') AND IND.TABLE_OWNER = upper('&owner') AND IND.TABLE_NAME = COL.TABLE_NAME AND IND.OWNER = COL.INDEX_OWNER AND IND.TABLE_OWNER = COL.TABLE_OWNER AND IND.INDEX_NAME = COL.INDEX_NAME;

set linesize 150 COLUMN file_name format A60 COLUMN free_space_mb format 999999.90 COLUMN allocated_mb format 999999.90 COLUMN used_mb format 999999.90 SELECT SUBSTR (df.NAME, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes UNION ALL select file_name, bytes/1024/1024 allocated_mb,user_bytes/1024/1024 used_mb, ((bytes/1024/1024) - (user_bytes/1024/1024)) free_space_mb from dba_temp_files; )

set linesize 200 set verify off set pagesize 40 break on owner on table_name on constraint_name on r_constraint_name column owner format a10 column r_owner format a10 column column_name format a15 column tt noprint column position heading P format 9 column table_name format a15 column r_table_name format a15 column constraint_name format a15 column r_constraint_name format a15 select a.tt, a.owner, b.table_name, a.constraint_name, b.column_name, b.position, a.r_constraint_name, c.column_name, c.position, c.table_name r_table_name, a.r_owner from (select owner, constraint_name, r_constraint_name, r_owner,1 tt from dba_constraints where owner=upper('&&owner') and table_name=upper('&&table_name') and constraint_type!=&#

set head off set feed off select instance_name||' Database UP Time ' from v$instance; select 'CURRENT DATE ===>> '|| to_char(sysdate,'dd-mon-yyyy hh24:mi') from dual; select 'DB START DATE ===>> '|| to_char(startup_time,'dd-mon-yyyy hh24:mi') from v$instance; select 'Database UP Time in Days Hours Minutes Seconds' from dual; set head on select rtrim(ltrim( (cast(sysdate as timestamp) - cast (STARTUP_TIME as timestamp)),'+000000000'),'000000') "DD HH:MI:SS" from v$instance;

COLUMN "Total Mb" FORMAT 999,999,999.0 COLUMN "Redo Mb" FORMAT 999,999,999.0 COLUMN "Temp Mb" FORMAT 999,999,999.0 COLUMN "Data Mb" FORMAT 999,999,999.0 Prompt Prompt "Database Size" select (select sum(bytes/1048576) from dba_data_files) "Data Mb", (select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb", (select sum(bytes/1048576)*max(members) from v$log) "Redo Mb", (select sum(bytes/1048576) from dba_data_files) + (select NVL(sum(bytes/1048576),0) from dba_temp_files) + (select sum(bytes/1048576)*max(members) from v$log) "Total Mb" from dual;

set linesize 150 col owner format a15 col db_link format a50 col username format a15 col host format a15 select owner,db_link,username,host,created from dba_db_links;

set pagesize 999 set linesize 150 col cpu_time format 9999999999 heading "Cputime" col sql_text format a80 heading "Sqltext" select cpu_time,sql_text from v$sqlarea where rownum<10 order by cpu_time desc;

SELECT substr(a.sid,1,5) "Sid", substr(a.process,1,7) "Process", nvl(a.USERNAME,'ORACLE PROC') "user", v.value "CPU used by this session" FROM v$statname s, v$sesstat v, v$session a WHERE s.name = 'CPU used by this session' and v.statistic#=s.statistic# AND v.sid = a.sid and v.value > 0 ORDER BY v.value DESC;

SET VERIFY OFF set linesize 200 col owner format a15 col table_name format a15 col constraint_name format a25 select OWNER,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE, STATUS,VALIDATED,GENERATED,LAST_CHANGE,INVALID from user_constraints where lower(table_name)='&table_name';

set linesize 120 col owner format a15 col object_name format a35 col object_type format a15 col status format a10 select lower(owner),status,count(status) from dba_objects where status='INVALID' group by owner,status; select owner,object_name,object_type,status from dba_objects where LOWER(owner)='&owner' and status='INVALID';

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads' /

set linesize 150 set head off set feed off col NAME format a25 col VALUE format a75 select 'Parameter ==>> '||NAME ,'Location ==>> '|| VALUE from v$parameter where name in('background_dump_dest','user_dump_dest','core_dump_dest');