WIXLIB

Oracle DBA scripts All in one pdfMore than 30 real-time scripts used byDBAs everydayMust have pdf guide to perform your daily DBA tasksDBA Genesis

IndexHow to check users, roles and privileges in OracleHow to check high resource intensive SQL in OracleHow to check execution plan of a queryHow to backup archivelog for specific sequence RMANHow to check last CPU applied in OracleHow to check biggest table in OracleHow to check database backups via sqlplusHow to display date and time in query outputHow to check scheduler jobs in OracleHow to check datapump export progressHow to drop all schema objects in OracleHow to find memory used by OracleHow to check last user login OracleHow to check CPU cores in LinuxHow to delete files older than X days in LinuxHow to analyze wait events in OracleHow to set DISPLAY variable in LinuxCrontab error - Permission DeniedHow to check FRA location utilization in OracleHow to check last modified table in OracleHow to check single table size in oracleHow to check database PITR after refreshHow to check archive generation in OracleHow to disable firewall in Linux 7How to check database lock conflict in OracleHow to check database size in OracleHow to configure yum server in linuxHow to check query plan change in oracleHow to force users change password on first login LinuxHow to check datafile utilization in OracleHow to estimate flashback destination spaceHow to check temp tablespace utilizationDBA Genesis How to check users, roles and privileges in Oracle

How to check users, roles and privileges inOracleQuery to check the granted roles to a userSELECT *FROM DBA ROLE PRIVSWHERE GRANTEE '&USER';Query to check privileges granted to a userSELECT *FROM DBA TAB PRIVSWHERE GRANTEE 'USER';Privileges granted to a role which is granted to a userSELECT * FROM DBA TAB PRIVS WHERE GRANTEE IN(SELECT granted role FROM DBA ROLE PRIVS WHERE GRANTEE '&USER') order by 3;Query to check if user is having system privilegesSELECT *FROM DBA SYS PRIVSWHERE GRANTEE '&USER';Query to check permissions granted to a roleselect * from ROLE ROLE PRIVS where ROLE '&ROLE NAME';select * from ROLE TAB PRIVS where ROLE '&ROLE NAME';select * from ROLE SYS PRIVS where ROLE '&ROLE NAME';DBA Genesis How to check users, roles and privileges in Oracle

How to check high resource intensive SQL inOracleDatabase performance is a major concern for a DBA. SQLs are the oneswhich needs proper DB management in order to execute well. At times theapplication team might tell you that the database is running slow. You canrun below query to get the top 5 resource intensive SQL with SQL ID andthen give it to application team to optimize them.col Rank for a4SELECT *FROM (SELECT RANK () OVER(PARTITION BY "Snap Day" ORDER BY "Buffer Gets" "Disk Reads" DESC) AS "Rank", i1.*FROM (SELECT TO CHAR (hs.begin interval time, 'MM/DD/YY' ) "Snap Day",SUM (shs.executions delta) "Execs",SUM (shs.buffer gets delta) "Buffer Gets",SUM (shs.disk reads delta) "Disk Reads",ROUND ( (SUM (shs.buffer gets delta)) / SUM (shs.executions delta), 1 ) "Gets/Exec",ROUND ( (SUM (shs.cpu time delta) / 1000000) / SUM (shs.executions delta), 1 ) "CPU/Exec(S)",ROUND ( (SUM (shs.iowait delta) / 1000000) / SUM (shs.executions delta), 1 ) "IO/Exec(S)",shs.sql id "Sql id",REPLACE (CAST (DBMS LOB.SUBSTR (sht.sql text, 50) AS VARCHAR (50) ), CHR (10), '' ) "Sql"FROM dba hist sqlstat shs INNER JOIN dba hist sqltext shtON (sht.sql id shs.sql id)INNER JOIN dba hist snapshot hsON (shs.snap id hs.snap id)HAVING SUM (shs.executions delta) 0GROUP BY shs.sql id, TO CHAR (hs.begin interval time, 'MM/DD/YY'),CAST (DBMS LOB.SUBSTR (sht.sql text, 50) AS VARCHAR (50) )ORDER BY "Snap Day" DESC) i1ORDER BY "Snap Day" DESC)WHERE "Rank" 5 AND "Snap Day" TO CHAR (SYSDATE, 'MM/DD/YY');DBA Genesis How to check high resource intensive SQL in Oracle

How to check execution plan of a queryFirst get the sql ID and then you can use below command to generateexecution plan of a query in oracleSELECT * FROM table(DBMS XPLAN.DISPLAY CURSOR('2t3nwk8h97vph',0));In case you have more IDs, use below command to supply sql id every timeyou run the querySELECT * FROM table(DBMS XPLAN.DISPLAY CURSOR('&sql id',0));DBA Genesis How to check execution plan of a query

How to backup archivelog for specificsequence RMANWhen you issue archive backup commands via RAMN, it will backup all thearchive logs. Sometimes, you might need to backup only a particulararchive log sequence. Below command will help you backup archive logsbetween specific sequenceRMAN BACKUP ARCHIVELOG FROM SEQUENCE 288 UNTIL SEQUENCE 388 DELETE INPUT;The above command will backup archive logs from 288 to 388 sequencenumber.DBA Genesis How to backup archivelog for specific sequence RMAN

How to check last CPU applied in OracleGenerally if you have one single database install then checking thedatabase inventory will give you the latest patch details. But! if we havemultiple database in single oracle home then it might not give correctresults. There might be a chance that one DB is applied with latest patchesand others are not. In such cases, we need to check last CPU applied bylogging into the database using below query:Query to Check Last CPU Applied on a Database:col VERSION for a15;col COMMENTS for a50;col ACTION for a10;set lines 500;select ACTION,VERSION,COMMENTS,BUNDLE SERIES from registry history;What are Critical Patch Updates (CPUs)?Critical Patch Updates are sets of patches containing xes for securityaws in Oracle products. The Critical Patch Update program (CPU) wasintroduced in January 2005 to provide security xes on a xed, publiclyavailable schedule to help customers lower their security managementcosts.DBA Genesis How to check last CPU applied in Oracle

How to check biggest table in OracleAs a DBA, you must keep an eye on the largest tables in the database.There are many things that get impacted with the largest objects like DBperformance, growth, index rebuild etc. The below query gives you the top10 largest tables in oracle database.Query to check top 10 largest tables in OracleSELECT * FROM(selectSEGMENT NAME,SEGMENT TYPE,BYTES/1024/1024/1024 GB,TABLESPACE NAMEfromdba segmentsorder by 3 desc ) WHEREROWNUM 10DBA Genesis How to check biggest table in Oracle

How to check database backups via sqlplusChecking Database backups are one of the main focus areas of a DBA.Time to time, DBA needs to check database backup status and see if itscompleted, failed, running etc. Also, DBA must be able to get the backupstart time, end time and even the backup size for reference purpose. Thebelow query gives answers to all the backup details in oracleQuery to check database backup statusset linesize 500col BACKUP SIZE for a20SELECTINPUT TYPE "BACKUP TYPE",--NVL(INPUT BYTES/(1024*1024),0)"INPUT BYTES(MB)",--NVL(OUTPUT BYTES/(1024*1024),0) "OUTPUT BYTES(MB)",STATUS,TO CHAR(START TIME,'MM/DD/YYYY:hh24:mi:ss') as START TIME,TO CHAR(END TIME,'MM/DD/YYYY:hh24:mi:ss') as END TIME,TRUNC((ELAPSED SECONDS/60),2) "ELAPSED TIME(Min)",--ROUND(COMPRESSION RATIO,3)"COMPRESSION RATIO",--ROUND(INPUT BYTES PER SEC/(1024*1024),2) "INPUT BYTES PER SEC(MB)",--ROUND(OUTPUT BYTES PER SEC/(1024*1024),2) "OUTPUT BYTES PER SEC(MB)",--INPUT BYTES DISPLAY "INPUT BYTES DISPLAY",OUTPUT BYTES DISPLAY "BACKUP SIZE",OUTPUT DEVICE TYPE "OUTPUT DEVICE"--INPUT BYTES PER SEC DISPLAY "INPUT BYTES PER SEC DIS",--OUTPUT BYTES PER SEC DISPLAY "OUTPUT BYTES PER SEC DIS"FROM V RMAN BACKUP JOB DETAILSwhere start time SYSDATE -10and INPUT TYPE ! 'ARCHIVELOG'ORDER BY END TIME DESC/Query to check archive Backup statusIn the 3rd last line and INPUT TYPE ! 'ARCHIVELOG' , just remove '!' toget archivelog backup detailsDBA Genesis How to check database backups via sqlplus

How to display date and time in query outputBy default, when you query a date column, oracle will only display datesand not time. Below query enables Oracle to display both date and time fora particular sessionalter session set nls date format 'dd-Mon-yyyy hh:mi:sspm';Note – this is only session level query.DBA Genesis How to display date and time in query output

How to check scheduler jobs in OracleBelow command will help you check Scheduler jobs which are con guredinside databaseSELECT JOB NAME, STATE FROM DBA SCHEDULER JOBS where job name 'RMAN BACKUP';Query to check currently running scheduler jobsSELECT * FROM ALL SCHEDULER RUNNING JOBS;All the DBA Scheduler jobs create logs. You can query below and check thedetails of job logsselect log id, log date, owner, job namefrom ALL SCHEDULER JOB LOGwhere job name like 'RMAN B%' and log date sysdate-2;select log id,log date, owner, job name, status, ADDITIONAL INFOfrom ALL SCHEDULER JOB LOGwhere log id 113708;DBA Genesis How to check scheduler jobs in Oracle

How to check datapump export progressSometimes when you run datapump export, it might take a lot of time.Meanwhile client might ask you for the % of export completed. Use belowquery to get the details of how much % export is done.SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"FROM V SESSION LONGOPS WHERE TOTALWORK ! 0 AND SOFAR TOTALWORK;DBA Genesis How to check datapump export progress

How to drop all schema objects in OracleThe below script will drop all the objects owned by a schema. This will notdelete the user but only deletes the objectsSET SERVEROUTPUT ON SIZE 1000000set verify offBEGINFOR c1 IN (SELECT OWNER,table name, constraint name FROM dba constraintsWHERE constraint type 'R' and owner upper('&shema name')) LOOPEXECUTE IMMEDIATE'ALTER TABLE ' ' "' c1.owner '"."' c1.table name '" DROP CONSTRAINT ' c1.constraint name;END LOOP;FOR c1 IN (SELECT owner,object name,object type FROM dba objectswhere owner upper('&shema name')) LOOPBEGINIF c1.object type 'TYPE' THENEXECUTE IMMEDIATE 'DROP ' c1.object type ' "' c1.owner '"."' c1.object name '" FORCE';END IF;IF c1.object type ! 'DATABASE LINK' THENEXECUTE IMMEDIATE 'DROP ' c1.object type ' "' c1.owner '"."' c1.object name '"';END IF;EXCEPTIONWHEN OTHERS THENNULL;END;END LOOP;EXECUTE IMMEDIATE('purge dba recyclebin');END;/DBA Genesis How to drop all schema objects in Oracle

How to find memory used by Oracleselect decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mbfrom(select 'sga' nm, sum(value) valfrom v sgaunion allselect 'pga', sum(a.value)from v sesstat a, v statname bwhere b.name 'session pga memory'and a.statistic# b.statistic#)group by rollup(nm);DBA Genesis How to find memory used by Oracle

How to check last user login OracleWhile performing database audits, you might need to check who logged inlast into the database. The query will help you find out last user who loggedin to databaseselect username, timestamp, action name from dba audit sessionwhere action name 'LOGON' andrownum 10 and username not in ('SYS','DBSNMP','DUMMY','SYSTEM','RMAN');DBA Genesis How to check last user login Oracle

How to check CPU cores in LinuxCommand to check CPU info on Linuxcat /proc/cpuinfo grep processor wc -lORnproc --allORgetconf NPROCESSORS ONLNCommand to check CPU info on Solarispsrinfo -v grep "Status of processor" wc -lCommand to check CPU info on AIXlsdev -C grep Process wc -lCommand to check CPU info on HP/UXioscan -C processor grep processor wc -lDBA Genesis How to check CPU cores in Linux