Making Oracle simple for you!

Simplifying Oracle learning by sharing knowledge with professionals across the globe

How to check datapump export progress

SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;

READ MORE

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...

READ MORE

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);

READ MORE

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...

READ MORE

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...

READ MORE

How to delete files older than X days in Linux

Find files older than X days and save ouput into a file The below Linux command will help you to find files older than 35 days in a specific directory path and save the ouput in backupfiles.log Here the directory we are searching is /backup/logs and -mtime specifies the modified time of a file....

READ MORE

How to check Oracle SGA component size

A very simple query to display SGA components in Oracle database SELECT * FROM v$sgainfo;

READ MORE

How to analyze wait events in Oracle

User below query to get the top wait classes in Oracle database Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits) Sum_Waits From v$system_wait_class Group by wait_class Order by 3 desc; From the above query, supply each wait class into below query to get the top wait events...

READ MORE

How to set DISPLAY variable in Linux

Whenever you want to invoke graphical interface in Linux, You must know how to set DISPLAY variable in order to open the GUI. Linux by default does not allow you to open any GUI (Linux Oracle Installer) until you enable the GUI display. Enabling DISPLAY Variable Use below command to enable Linux...

READ MORE

Crontab error - permission denied

When you try to schedule backups under corntab as Oracle user, you might encounter crontab permission error [[email protected] ~]$ crontab -l cron/oracle: Permission denied Resolving Crontab Permission Error The error is because of permission issues on /usr/bin/crontab file. Login as root user...

READ MORE