Queries for debugging Oracle database issues

Accessing database as another user -
alter session set current_schema=schemaName;

Name of Users or Role having access to any database object
select * from USER_TAB_PRIVS_MADE where table_name='Object_name;
select * from DBA_TAB_PRIVS where table_name='Object_name';

Primary key columns of a table -
select * from all_cons_columns a join all_constraints c on a.constraint_name = c.constraint_name where c.table_name = 'table_name' and c.constraint_type = 'P'

Foreign key columns of a table -
select * from all_cons_columns a join all_constraings c on a.constraint_name = c.constraint_name where c.table_name = 'table_name' and c.constraint_type = 'R'

Query to compile the database objects
exec dbms_ddl.alter_compile ('Object_name','Schema_name','Object_name');

OR

alter object_type object_name compile;


Query to compile schema
exec dbms_utility.compile_schema('SchemaName');

Query to create compile statements for invalid objects in a schema-

Select  'alter '||object_type||'  '||object_name||' compile;'
From user_objects
Where status <> 'VALID'
And object_type IN ('VIEW','SYNONYM',
'PROCEDURE','FUNCTION',
'PACKAGE','TRIGGER');

SQL Block to empty the schema -

declare
  cursor c_obj
  is
    SELECT u1.object_type
    ,      u1.object_name
    FROM   user_objects u1
    where  u1.object_type not in ( 'PACKAGE BODY','TYPE BODY', 'INDEX', 'TRIGGER', 'UNDEFINED', 'LOB','TABLE PARTITION','LOB PARTITION','INDEX PARTITION')
    and    ( u1.object_type <> 'TABLE' or u1.object_name not like 'BIN%')
    and not EXISTS (select 'x' from  user_objects u2 where u1.object_type='TABLE' AND U2.OBJECT_TYPE='MATERIALIZED VIEW' AND U1.OBJECT_NAME=U2.OBJECT_NAME);

  l_execute varchar2(2000);
begin
  for r_obj in c_obj loop
     l_execute:= 'drop '||r_obj.object_type||' '||r_obj.object_name;
     if r_obj.object_type = 'TABLE'
     then
       l_execute:= l_execute || ' CASCADE CONSTRAINTS';
     end if;
     EXECUTE IMMEDIATE l_execute;
  end loop;
end;
/
purge recyclebin;

 SQL block to truncate all tables in schema

set serveroutput on size unlimited;
declare
begin

for c1 in (select y.table_name, y.constraint_name from user_constraints y, user_tables x where x.table_name = y.table_name and y.constraint_type='R') loop
    begin
        dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
        execute immediate  ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
    end;
end loop;

for t1 in (select table_name from user_tables) loop
    BEGIN
        dbms_output.put_line('truncate table '||t1.table_name || ';');
        execute immediate ('truncate table '||t1.table_name);
    end;
end loop;

for c2 in (select y.table_name, y.constraint_name from user_constraints y, user_tables x where x.table_name = y.table_name and y.constraint_type='R') loop
    BEGIN
        dbms_output.put_line('alter table '||c2.table_name||' enable constraint '||c2.constraint_name || ';');
       execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
    end;
END loop;

end;
/

 SQL block to rebuild all indexes in schema

set serveroutput on size unliminited
declare 
 v_index_name VARCHAR2(200);
begin
  for v_index_name in (select OBJECT_NAME  index_name from user_objects where object_type='INDEX')
   LOOP
      BEGIN
--      dbms_output.put_line('ALTER INDEX '|| v_index_name.index_name ||' REBUILD'||' ;' );
        execute immediate('ALTER INDEX '|| v_index_name.index_name ||' REBUILD' );
      EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line('ALTER INDEX '|| v_index_name.index_name ||' REBUILD'||' ; -------- failed');
        dbms_output.put_line('Error message = '||SQLERRM);
      END;
  end LOOP;
end;
/

 SQL block to check if any table in schema has records

set serveroutput on size unlimited;
DECLARE
v_record_count NUMBER;
begin
  for t1 in (select table_name from user_tables) loop
      BEGIN
          v_record_count:=0;
          EXECUTE IMMEDIATE ('select count(*) from '||t1.table_name) INTO v_record_count ;
          IF v_record_count>0
            THEN
            dbms_output.put_line('select count(*) from '||t1.table_name || ';');
            dbms_output.put_line( t1.table_name || ' has ' || v_record_count || ' records'); 
          END IF;
      end;
  end loop;
end;
/

 SQL Procedure, an alternate way to provide kill session privilege

create or replace procedure kill_session (pn_sid    number ,pn_serial number)
as
 lv_user varchar2(30);
begin
 select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
 if lv_user is not null and lv_user not in ('SYS','SYSTEM') then
  execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''';
 else
  raise_application_error(-20000,'Attempt to kill protected system session has been blocked.');
 end if;
end;
/

Checking the oracle version -  
select * from v$version where banner like 'Oracle%' ;

Checking available directory objects -
select * from all_directories 

Query to check the last refresh time of materialized view
SELECT owner, mview_name, to_char(last_refresh_date, 'dd/mm/yy hh24:mi:ss') Last_Refresh From   All_Mviews

Checking priviliges in directory object -
select  grantor, grantee, table_schema, table_name, privilege from all_tab_privs where table_name = upper('&directoryObjectName')

Checking the space occupied by a table -
select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS), 2) SIZE_BYTE, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) SIZE_KB,ROUND((AVG_ROW_LEN * NUM_ROWS /(1024*1024)), 2) SIZE_MB from USER_TABLES where NUM_ROWS is not null order by SIZE_KB desc

Other useful info for debugging issues -

1. Query to find blocking sessions
2. Oracle system tables 
3. Disable interactive prompts at SQL*Plus
4. Setting materilized view to refresh on demand (manually)
5. Refreshing materialized view manually
6. Monitoring and managing temporary table spaces  
7. Analyzing the table for faster insert and select
8. Setting oracle server output on with unlimited size
9. Managing oracle recycle bin and retrieve already dropped object
10. Recycle bin data dictionary table (USER_RECYCLEBIN)
11. When oracle index should be rebuild
12. Debugging oracle units using SQL Developer 

Giving output number format of a query - http://ss64.com/ora/syntax-numfmt.html

Link to refer to take DB dump -
Taking DB dump (using expdp) - http://www.oracle-base.com/articles/10g/OracleDataPump10g.php
Taking DB dump (using exp) -
http://www-it.desy.de/systems/services/databases/oracle/impexp/impexp.html.en
http://www.taooforacle.com/my-cool-tricks/how-to-handle-exp-00091-exporting-questionable-statistics-error.html
http://www.dba-oracle.com/t_exp_0002.htm

http://oisene.blogspot.co.uk/2011/12/exp-00003.html