Category Script

How to check CPU cores in Linux


Command to check CPU info on Linux cat /proc/cpuinfo|grep processor|wc -l OR nproc –all OR getconf _NPROCESSORS_ONLN Command to check CPU info on Solaris psrinfo -v|grep “Status of processor”|wc -l Command to check CPU info on AIX lsdev -C|grep Process|wc -l Command to check CPU info on HP/UX ioscan -C processor | grep processor | […]

How to check last user login Oracle


While performing database audits, you might need to check who logged in last into the database. The query will help you find out last user who logged in to database select username, timestamp, action_name from dba_audit_session where action_name=’LOGON’ and rownum<10 and username not in (‘SYS’,’DBSNMP’,’DUMMY’,’SYSTEM’,’RMAN’);

How to find memory used by Oracle


select decode( grouping(nm), 1, ‘total’, nm ) nm, round(sum(val/1024/1024)) mb from ( select ‘sga’ nm, sum(value) val from v$sga union all select ‘pga’, sum(a.value) from v$sesstat a, v$statname b where b.name = ‘session pga memory’ and a.statistic# = b.statistic# ) group by rollup(nm);

How to find memory used by Oracle


select decode( grouping(nm), 1, ‘total’, nm ) nm, round(sum(val/1024/1024)) mb from ( select ‘sga’ nm, sum(value) val from v$sga union all select ‘pga’, sum(a.value) from v$sesstat a, v$statname b where b.name = ‘session pga memory’ and a.statistic# = b.statistic# ) group by rollup(nm);

How to drop all schema objects in Oracle


The below script will drop all the objects owned by a schema. This will not delete the user but only deletes the objects SET SERVEROUTPUT ON SIZE 1000000 set verify off BEGIN FOR c1 IN (SELECT OWNER,table_name, constraint_name FROM dba_constraints WHERE constraint_type = ‘R’ and owner=upper(‘&shema_name’)) LOOP EXECUTE IMMEDIATE ‘ALTER TABLE ‘||’ “‘||c1.owner||'”.”‘||c1.table_name||'” DROP CONSTRAINT […]

How to check datapump export progress


Sometimes when you run datapump export, it might take a lot of time. Meanwhile client might ask you for the % of export completed. Use below query 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 […]

How to check scheduler jobs in Oracle


Below command will help you check Scheduler jobs which are configured inside database SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS where job_name=’RMAN_BACKUP’; Query to check currently running scheduler jobs SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS; All the DBA Scheduler jobs create logs. You can query below and check the details of job logs select log_id, log_date, owner, job_name from […]

Query to check database backup status


set linesize 500 col BACKUP_SIZE for a20 SELECT INPUT_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_DETAILS where start_time > SYSDATE -10 and INPUT_TYPE != ‘ARCHIVELOG’ ORDER BY END_TIME DESC /

Query to check database backup status


set linesize 500 col BACKUP_SIZE for a20 SELECT INPUT_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_DETAILS where start_time > SYSDATE -10 and INPUT_TYPE != ‘ARCHIVELOG’ ORDER BY END_TIME DESC /

Query to check top 10 largest tables in Oracle


SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 10

How to check high resource intensive SQL in Oracle


Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is running slow. You can run below query to get the top 5 resource intensive SQL with SQL ID and then […]

enable_novalidate_constraint


select ‘ alter table ‘||table_name|| ‘ enable novalidate constraint ‘ ||constraint_name ||’;’ from user_constraints where table_name =’TABLE_NAME’

create a new user (with privs) like an existing


set pages 0 feed off veri off lines 500 accept oldname prompt “Enter user to model new user to: ” accept newname prompt “Enter new user name: ” — accept psw prompt “Enter new user’s password: ” — Create user… select ‘create user &&newname identified by values ”’||password||””|| — select ‘create user &&newname identified by […]

Blocking Sessions in Oracle RAC


select blocking_session,blocked_session,script from ( select distinct s1.username || ‘@’ || s1.machine || ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s1.sid || ‘ ET=’ || s1.last_call_et || ‘sn. STATUS=’ || s1.status || ‘ EVENT=’ || s1.event || ‘ ACTION= ‘ || s1.action || ‘ PROGRAM=’ || s1.program || ‘ MODULE=’ || s1.module || ‘)’ […]

Automatic Statistics Collection script


prompt set feed off prompt Auto tasks (11G) prompt accept x_days prompt “Days [1] : ” default 1 prompt select decode( b.instance_number, a.inst_id, ‘–>’, null ) “Current”, a.instance_name, a.host_name, a.version, a.startup_time, a.status, a.parallel, a.thread#, a.archiver, a.log_switch_wait, a.logins, a.shutdown_pending, a.database_status, a.instance_role, a.active_state, a.blocked, a.instance_number from gv$instance a, v$instance b where b.instance_number (+)= a.inst_id order by a.inst_id; […]

SHUTDOWN THE DATABASE WITH IMMEDIATE MODE / Shell script


echo “—————————————————————————-” echo Report by Anar Godjaev Oracle 9i/10g/11g OCA,OCP,SQL Expert, PL/SQL Prof,RAC echo “—————————————————————————-” #!/bin/ksh # This shell script performs the following steps # shutdown the database with immediate mode # log file ORACLE_HOME=/data1/oracle/product/8.1.7 ; export ORACLE_HOME ORACLE_SID=ORCL export ORACLE_SID LOGFILE=/data1/oracle/admin/ORCL/log/anar.log clear echo “Obase-1 the database is closed.” echo ” Are you sure you? […]

Apache Tomcat –shutdown/startup script (shell)


startup.sh/Shutdown.sh   /  Script is useful for you !!!!!!!!! #!/bin/sh # —————————————————————————– # Stop script for the CATALINA Server # # $Id: shutdown.sh,v 1.3 2002/08/04 18:19:43 patrickl Exp $ # —————————————————————————– # resolve links – $0 may be a softlink PRG=”$0″ while [ -h “$PRG” ] ; do   ls=`ls -ld “$PRG”`   link=`expr “$ls” : […]

View all currently locked objects:


SELECT username U_NAME, owner OBJ_OWNER, object_name, object_type, s.osuser, DECODE(l.block, 0, ‘Not Blocking’, 1, ‘Blocking’, 2, ‘Global’) STATUS, DECODE(v.locked_mode, 0, ‘None’, 1, ‘Null’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive’, TO_CHAR(lmode) ) MODE_HELD FROM gv$locked_object v, dba_objects d, gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) […]

how to find different objects to different other database used by database link


select index_name as “ANARDB indexes” from dba_indexes where owner like ‘OC_%’ minus select index_name from dba_indexes@ANARDB1 where owner like ‘OC_%’; select sequence_name as “ANARDB sequences” from dba_sequences where sequence_owner like ‘OC_%’ minus select sequence_name from dba_sequences@ANARDB1 where sequence_owner like ‘OC_%’ select table_name “ANARDB tables” from dba_tables where owner like ‘OC_%’ minus select table_name from dba_tables@ANARDB1 […]

Create Public Synonym


SELECT ‘Create or replace public synonym  ‘||object_name || ‘ for ‘                                || OWNER || ‘.’ || object_name || ‘;’ FROM ALL_OBJECTS Where object_type  in (‘VIEW’,’PACKAGE’) and OWNER = ‘<USER_NAME>’;