QUERY - BACKUP

QUERY - BACKUP



Oracle Home and Inventory Backup


tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz



Note: 
tar -cvf <destination_location> <source_location>


ORACLE_HOME & ORACLE_CRS_HOME backup’s (for all nodes in RAC)
cd /u01/before_patch_backup
tar -cvf oracle_base_jun30.tar $ORACLE_BASE
tar -cvf oracle_home_jun30.tar $ORACLE_HOME
tar -cvf oracle_crs_home_jun30.tar $ORACLE_CRS_HOME




All Users passwords backup


spool ./users_pwd_backup.log
select 'alter user '||username||' identified by values '||password||';' from dba_users;  ( For 10g database)
select 'alter user '||name||' identified by values '||password||';' from SYS.USER$; ( For 11g database)
spool off


Database links backup


All DBlinks backup:
set pages 0
set long 90000
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;


For a specific DBlink backup:
set pages 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('DB_LINK','DB_LINK_NAME','USER_NAME') FROM dba_db_links;


Controlfile Backup


alter database backup controlfile to trace; 


show parameter user_dump_dest
(go to udump dest and make the note of controlfile trace)



"Hope for the best, prepare for the worst"
-Dharmendra

QUERY - DDL OF PACKAGE

PACKAGE DDL


spool packagebackup.sql
select text from dba_source where owner = 'OWNERNAME' and name = 'PACKAGENAME' and type = 'PACKAGE BODY' order by line;
spool 0ff
(OR)
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 50000
select dbms_metadata.get_ddl('PACKAGE','PACKAGENAME') from dual ;


PACKAGE DROP


col object_name for a30
col object_type for a25
col owner for a25
spool packagedrop.sql
select object_name,object_type,owner from dba_objects where object_name='objectname';
drop package <owner>.<package_name>;
spool 0ff



"Hope for the best, prepare for the worst"
-Dharmendra

Query - DBA views

Query - DBA views

DBA Views Queries

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

select * from dict;

select * from cat;

TABLE COLUMNS

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

select * from dba_tab_columns where table_name = 'table_name';

CONSTRAINTS

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

select table_name, constraint_name, constraint_type, status from dba_constraints where owner in('', '')

select constraint_name, r_constraint_name, constraint_type from dba_constraints where constraint_name = ''; 

select constraint_name, constraint_type from dba_constraints where r_constraint_name = '';

DATAFILES

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

select substr(file_name,1,70) from dba_data_files order by file_name;

DEPENDENCIES

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

select * from user_dependencies where referenced_name = ''

INDEXES

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

select index_name, column_name from dba_ind_columns where table_name = '' order by index_name, column_position

select index_name from dba_indexes where table_name = '';

JOBS

--------

select job, this_date, this_sec, next_date,  next_sec, interval, what,  failures, broken from dba_jobs;

OBJECTS

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

select object_name from dba_objects where status <> 'VALID';

TABLE PRIVILEGES

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

desc dba_tab_privs 

select * from dba_tab_privs where grantee = '';

select * from dba_tab_privs where table_name = '';

ROLE PRIVILEGES

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

select * from dba_role_privs where grantee='';

SYSTEM PRIVILEGES

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

select * from dba_sys_privs where grantee='';

OTHERS

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

DBA_COL_PRIVS

DBA_PRIV_AUDIT_OPTS

SEGMENTS

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

select segment_name, count(*), sum(bytes) from dba_extents where segment_name = '' group by segment_name ;

select * from dba_segments where segment_name = '';

select * from dba_segments@p1aqcis where segment_name = '';

select segment_name, count(*), sum(bytes) from dba_extents group by segment_name having count(*) > 1;

select * from dba_tables where table_name = '';

select segment_name, segment_type, bytes from dba_segments where segment_name in ('','','','','');

select sum(bytes) from dba_segments where segment_name in ('','','','','');

SELECT substr(segment_name, 1, 30) "OBJECT", max(extents) FROM dba_segments group by segment_name having max(extents) > &max_extents_greater_than;

  

SOURCE CODE

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

select owner,name,type,text from dba_source where upper(text) like '%%';

select name,type,text from dba_source where upper(text) like '%%';

select name,type,text from dba_source where upper(text) like '%%';

select substr(line,1,4) line, text from dba_source where type = 'PACKAGE BODY' and name = ''

select name,type,text from dba_source where upper(text) like '%%';

TABLES

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

space allocation for tables using computed statistics

select table_name, num_rows, avg_row_len, (num_rows * avg_row_len) from dba_tables where owner in ('','');

TABLESPACES

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

select tablespace_name, bytes, file_name from dba_data_files order by tablespace_name;

USERS

----------

set pagesize 0

set echo off

select 'ROLE '||GRANTED_ROLE from dba_role_privs where grantee='';

select 'SYSTEM PRIVILEGE '||privilege from dba_sys_privs where grantee='';

select privilege||' ON '||owner||'.'||table_name from dba_tab_privs where grantee='';

set echo on

select * from dba_users where username = '';

VIEWS

----------

select view_name from dba_views where view_name like '%%';

"Hope for the best, prepare for the worst"
-Dharmendra