All DBA QUERIES

 user below :-

select owner,object_name,object_type,timestamp from dba_objects where object_name=upper('&object_name') and owner=upper('&owner');
select owner,segment_name,segment_type,(bytes/power(1024,3)) "size_GB" from dba_segments where segment_name=upper('&object_name') and owner=upper('&owner');

to find object level privileges :-
dba_role_privs, dba_sys_privs and dba_tab_privs 

set lines 400;
col owner for a20;
col grantor for a20;
col grantee for a20;
col table_name for a50;
col privilege for a20;
col grantable for a20;
col hierarchy for a20;
select owner,grantor,grantee,table_name,privilege,grantable,hierarchy from dba_tab_privs where table_name=upper('&table_name') and grantee=upper('&grantee');

************************************************************

#!/bin/sh

sysdate=$(date +'%b %d')

ls -lrt /pac/ipagi2d1/bkup/*dbf* | awk '{print $6,$7}'  | while read bkpdates; 

do

if [[ $bkpdates = $sysdate ]]

then 

echo $bkpdates

else

echo "you can remove : $(ls -lrt 

done;

#!/usr/bin/python2.7

import os, time, fnmatch

# raw_input() will do type conversion from console to python interpreter

path=raw_input("Enter absolute path :")

days  = input("Enter number of days older files want to search:")

now = time.time()

for filename in os.listdir(path):

    filestamp = os.stat(os.path.join(path, filename)).st_mtime

    n_days_ago = now - days * 86400

    if os.path.isfile(filename):

      if  filestamp <= n_days_ago :

          if fnmatch.fnmatchcase(filename,'*dbf*'):

                 print(filename)

          else:

                print("not this")

***************************************************************

Network bandwidth for archive log transfer :-

select round(((value/0.70)*8)/1000000) Bandwidth_required_in_MBPS 

from (select 

to_char(begin_time,'DD-MONTH-YYYY HH:MI:SS') begin_time,

to_char(end_time,'DD-MONTH-YYYY HH:MI:SS') end_time,

metric_name,metric_id,metric_unit,round(value) value

from gv$sysmetric_history where metric_name='Redo Generated Per Sec' and 

to_char(begin_time,'DD-MONTH-YYYY HH:MI:SS') <= to_char(sysdate,'DD-MONTH-YYYY HH:MI:SS') and rownum<2 order by begin_time desc);

****************************************************************

KB MB TB ZB 

select (select host_name from v$instance) "Host", 

(select instance_name from v$instance) "instance",

owner,segment_name,

(select to_char(cast(created as timestamp with local time zone), 'DD-MONTH-YYYY hh24:mi:ss TZD yyyy') "created" from dba_objects where object_name='&object_name' and owner='&schema_name') "created",

(select to_char( cast(sysdate as timestamp with local time zone), 'DD-MONTH-YYYY hh24:mi:ss TZD yyyy') from dual) "sysdate",

case 

when bytes between 0 and 1023 then bytes || 'bytes'

when bytes < power(1024,2) then round(bytes / power(1024,1),4) || 'KB'

when bytes < power(1024,3) then round(bytes / power(1024,2),4) || 'MB'

when bytes < power(1024,4) then round(bytes / power(1024,3),4) || 'GB'

when bytes < power(1024,5) then round(bytes / power(1024,4),4) || 'TB'

when bytes < power(1024,6) then round(bytes / power(1024,5),4) || 'PB'

when bytes < power(1024,7) then round(bytes / power(1024,6),4) || 'EB'

when bytes < power(1024,8) then round(bytes / power(1024,7),4) || 'ZB'

when bytes < power(1024,9) then round(bytes / power(1024,8),4) || 'YB'

else 'INVALID VALUE FOR BYTES'

end as bytes_sizes

from dba_segments

where segment_name='&object_name' and owner='&schema_name';

**********************************************************************

DDL DML changes query:-

select owner,object_name,object_type,created,last_ddl_time,timestamp from 

dba_objects where object_name='&table_name' ;

select table_owner,table_name,inserts,updates,deletes,timestamp 

from all_tab_modifications where table_name='&table';

1) To Find Desired Views In Oracle Database :-

select owner,view_name from all_views where view_name like upper('%&view%');

2) choose the desired view from above and then trigger below :-

desc view_name;

**************************************************************************

grants to new user:-

create user books_admin identified by password;

grant below :

grant connect to books_admin;

grant connect, resource, dba to books_admin;  /* if needed */

grant create session grant any privilege to books_admin; 

grant unlimited tablespace to books_admin;

table level privilege:

grant select, insert, update, delete on schema.books to books_admin;

*************************************************************************

old records in tables :-

select created 

from dba_objects/all_objects  

where object_name = <<your_table_name>>

and owner = 'table_owner'

and object_type = 'table_name';

SELECT scn_to_timestamp( ora_rowscn ) last_modified_date,

       ora_rowscn last_modified_scn,<<other columns>>

  FROM <<your table>>; 

SQL> select current_scn from v$database;

SQL> SELECT SCN_TO_TIMESTAMP('1404377619968') FROM dual;

SCN_TO_TIMESTAMP('1404377619968')

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

25.01.19 07:32:12,000000000

***************************************************************************

constraints :-

set lines 400

col constraint_name for a20;

col constraint_type for a20;

col search_condition for a20;

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where table_name in ('DELTA_SERVICEOBJECT')

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where owner='GGS_USER';

col owner for a20;

col constraint_name for a20;

col constraint_type for a5;

col table_name for a20;

col status for a20;

col validated for a5;

col invalid for a5;

col index_owner for a20;

col index_name for a20;

select owner,constraint_name,constraint_type,table_name,status,validated,invalid,index_owner,index_name from all_constraints where owner='&username' and table_name='&table';

************************************************************************

ARCHIVE LOG GENERATION :-

************Daily Archive Log Generation****************

SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#, 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from gv$archived_log 

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

DAY                     THREAD#         GB ARCHIVES_GENERATED

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

10-OCT-2016 00:00:00          1         19                233

11-OCT-2016 00:00:00          1         34                417

12-OCT-2016 00:00:00          1         42                522

*************Hourly Archive Log Generation****************

set pages 1000

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives from gv$archived_log 

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

HOUR         THREAD#         GB   ARCHIVES

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

18-MAR-23          1          0          1

18-MAR-23          1          0          1

18-MAR-23          1          2          5

18-MAR-23          1          0          2

*****************************************************

DELETE OLD RECORDS FROM TABLES :-

DELETE FROM country WHERE trunc(RUN_DATE) = To_date('21-NOV-17','DD-MON-YY'); --------------------------> ignores index time

DELETE FROM country WHERE RUN_DATE >= date '2017-11-21' and RUN_DATE < date '2017-11-22';  ---------------------------------> best for performance 

DELETE FROM country WHERE RUN_DATE  BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND TO_DATE('20/06/2014', 'DD/MM/YYYY');

***************************************************************

User Grants :-

User creation:

select dbms_metadata.get_ddl( 'USER', 'PHIL' ) from dual;

Default role:

select dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', 'PHIL' ) from dual;

System grants:

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'PHIL' ) from  dual;

Object grants:

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'PHIL' ) from dual;

Role grants:

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'PHIL' ) from dual;

Quotas:

select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;

******************************************************************************

There are 3 options for SCOPE parameter:

1. MEMORY – the change is immediate but will not be available post instance restart

2. SPFILE – the changes will be made to spfile, however it will be applied the database, post instance restart

3. BOTH – Changes will be applied to database immediate, And it dont needs database restart

FOR RAC:

alter system set job_queue_processes=500 scope=both sid='ORCL1';

alter system set job_queue_processes=500 scope=both sid='*';  ------------------------------> for all instances in RAC DB

************* alter system set parameter_name=value scope=both or spfile or memory  sid='instance_name or *'; **************************

******************************************************************************

RMAN BACKUP SHELL SCRIPT :-

#!/bin/bash

bkuploc=$(grep -o "\w*/[[:graph:]]*" bkuploc.txt)

files=$(date -d '-1 day' +%Y_%m_%d)

sqlplus / as sysdba << !!

spool bkuploc.txt

col value_col_plus_show_param for a80;

show parameter db_recovery_file_dest;

spool off

exit;

!!

process=$(ps -ef | grep -i rman | wc -l )

if [ $process -gt 2 ]

then

        ls $bkuploc/$files*

        mail -s "rman backup running" km00780716@techmahindra.com

else

        ls -lrt $bkuploc/$files* | mail -s "rman not  running" km00780716@techmahindra.com

fi

*************************************************************************

SYSTEM EVENTS IN ORACLE DATABASE:-

***********************************************************************************

select

     inst_id,

     event,

     total_waits,

     time_waited

  from

     gv$system_event

  where

     event in ('gc current block lost',

               'gc cr block lost')

  order by

     event,

     inst_id;

 

   INST_ID EVENT                          TOTAL_WAITS TIME_WAITED

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

         1 gc cr block lost                        50        3029

         2 gc cr block lost                        75        4516

         1 gc current block lost                   26        1467

         2 gc current block lost                   36        2060

 

*************************************************************************************

select

     sn.inst_id,

     sn.name,

     ss.value

  from

     gv$statname sn,

     gv$sysstat ss

  where

     sn.inst_id = ss.inst_id

     and

     sn.statistic# = ss.statistic#

     and

     sn.name = 'gc blocks lost'

  order by

  sn.inst_id;

 

   INST_ID NAME                 VALUE

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

         1 gc blocks lost               90

         2 gc blocks lost              164

***************************************************************

Check Current User Permission

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

select * from USER_ROLE_PRIVS where USERNAME=USER;

select * from USER_TAB_PRIVS where Grantee = USER;

select * from USER_SYS_PRIVS where USERNAME = USER;

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

To Check the roles granted to a user:

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

select * from dba_role_privs where grantee = 'username';

select * from dba_tab_privs where grantee = 'username';

select * from dba_sys_privs where grantee = 'username';

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

********************************************************************

GOLDEN GATE XID :-

The 3 part XID as in showtrans as these     ......................username: ggsuser Passwd: GOLDENGATE4U   ......on nmsis8d1        

select t.xidusn||'.'||t.xidslot||'.'||t.xidsqn xid from v$transaction t; .........................best query 

to find SID SERIAL:                 ..............................................................best query

select  t.start_time,t.status TSTATUS, s.status SSTATUS,s.sid, s.serial# ,s.machine , s.sql_id,s.prev_sql_id,s.process,t.XIDUSN||'.'||t.XIDSLOT||'.'||t.XIDSQN XID from gv$transaction t, gv$session s  where t.addr=s.taddr and t.inst_id=s.inst_id and t.start_date < (sysdate-1/142) order  by t.start_time;

###############################################################

-- if needed, you can go ahead and kill the sql (if it is not of a major impact)

alter system kill session '9871,167';

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

XID:                  8805.6.296139

Items:                1       

Extract:              EXRAJ   

Redo Thread:          2     

Start Time:           2013-05-10:21:24:33 

SCN:                  1586.3896755063 (6815714886519) 

Redo Seq:             79547

Redo RBA:             791874576         

Status:               Running           

SQL> select * from gv$transaction where xidusn=8805;

SQL> select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='0000001BFF3DEBD8';

 SQL> select

hash_value, address,

executions,buffer_gets, disk_reads,

round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,

round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,

last_load_time,

module,

sql_fulltext

from v$sqlarea

where sql_id='&sql_id';

SQL>

select logon_time,status,LAST_CALL_ET from gv$session where sid=9871 and inst_id=2;

**************************************************************************