Oracle DBA - Techincal Guide for DBAs

- service check at OS level : -------------------------------------- srvctl status database -d DATABASE_NAME srvctl status database -d DATABASE_NAME -v

 - long Running Query: --------------------------------------

 select b.spid,a.sid, a.serial#,a.username, a.osuser from v$session a, v$process b where a.paddr= b.addr and a.sid='&sid' order by a.sid; select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text from v$sqltext_with_newlines t,V$SESSION s where t.address =s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE' and s.username <> 'SYSTEM' order by s.sid,t.piece /

 - EXPORT PARFILE --------------------------------------
userid="/ as sysdba" SCHEMAS=SPC_FV DIRECTORY=DATA_PUMP_DIR LOGFILE=SPC_FV.log DUMPFILE=SPC_FV_%u.dmp
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')" parallel=7

restore Point --------------------------------------

create restore point restore_point_name; - SCP scp - r destination_server_name:/Location 

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

 Privileges - user

 select privilege from dba_sys_privs where grantee='SAMPLE' order by 1;


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

 find & kill session :

select ' alter system kill session '||''''||SID||','||SERIAL#||''''||';' from gv$session where username IN ('Username');

 -------------------------------------- 
 SELECT s.* ,'alter system kill session ''' || s.sid || ', ' || s.serial# || ', @' || s.inst_id || ''';' FROM gv$session s WHERE status = 'INACTIVE' AND s.USERNAME = 'USERNAME' 

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

 select 'drop '||object_type ||' '|| owner||'.'||object_name||';' from dba_objects where owner in ('OWNERNAME');

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

 CREATE RESTORE POINT RESTOREPOINT_NAME;


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