How to find database growth on a Monthly wise

Today we are going to have look at the Database growth. With the help of the given query. DBA can find the monthly growth of there database. Sometimes little information is too much useful.       select to_char(CREATION_TIME,’RRRR’) year, to_char(CREATION_TIME,’MM’) month, round(sum(bytes)/1024/1024/1024) GB from   v$datafile group by  to_char(CREATION_TIME,’RRRR’),   to_char(CREATION_TIME,’MM’) order by   1, 2; Output […]

Read More

High Water Mark USAGE

Before start this article we must know about “HWM” . After did lots of R&D i found the technical definition Explanation in Detail: (http://www.dba-oracle.com/t_high_water_mark.html) . The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle […]

Read More

Check os processid of your session from sql prompt.

To check os processid of oracle session , we can check v$process view. SPID column of v$process view shows os processid. We can use following query to check processid, status for specific user. select p.spid, s.username, s.status, s.server, to_char(s.logon_time,’DD-MON-YY HH24:MI’) from v$process p, v$session s where p.addr = s.paddr and s.status=’ACTIVE’ ; I have connected […]

Read More

Find corrupted objects in Oracle using sql query

Find corrupted objects in Oracle using sql query : When you find block corruption in your database use validate database or validate check logical database command of RMAN, it will populate v$database_block_corruption view with corrupted blocks. Then you can check corrupted segments , its partition name and more details using following script. SELECT e.owner, e.segment_type, […]

Read More

check os process id of your session from sql prompt.

 Check os process id of your session from sql prompt. select p.spid, s.username, s.status, s.server, to_char(s.logon_time,’DD-MON-YY HH24:MI’) from v$process p, v$session s where p.addr = s.paddr and s.status=’ACTIVE’;  SPID USERNAME STATUS SERVER ———————— —————————— ——– ——— TO_CHAR(S.LOGON_TIME,’DD-MON-YYHH24:MI’) ————————————————————————— 18658 EASY_BANK ACTIVE DEDICATED 08-JAN-18 15:07 18664 EASY_BANK ACTIVE DEDICATED 08-JAN-18 15:07 23691 ACTIVE DEDICATED 08-JAN-18 15:15 […]

Read More

HOW TO CONVERT SCN TO DATE AND DATE INTO SCN

SQL>select current_scn from v$database; 7705798324 SQL> select timestamp_to_scn(to_timestamp(’24/09/2012 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual; SCN ———- 7705798324 SQL> SQL> select scn_to_timestamp(7705798324) as timestamp from dual; 24-SEP-12 02.24.52.000000000 PM Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or […]

Read More

Export backup with date and time

Data Pump Export (hereinafter referred to as Export for ease of reading) is a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility. The dump file set can be imported on the […]

Read More

Unified Audit Trail In Oracle 12c

This post is about Unified Audit Trail In Oracle 12c . Few starting lines give the overview of ” Unified Audit Trail ” .   With Oracle 12c, unified auditing has been introduced. It consolidates all audit trails into a single audit trail table. It will capture audit records from below sources. SYS audit records ( which was […]

Read More

FINDING SCHEMA SIZE FOR A ORACLE DATABASE

SQL> BREAK ON REPORT SQL> COMPUTE SUM LABEL TOTAL OF “Size of Each Segment in MB” ON REPORT SQL> select segment_type, sum(bytes/1024/1024) “Size of Each Segment in MB” from dba_segments where owner=’SYS’ group by segment_type order by 1;  

Read More