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 […]
Category: SCRIPTS
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 […]
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 […]
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, […]
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 TEST1 ACTIVE DEDICATED 08-JAN-18 15:07 18664 TEST1 ACTIVE DEDICATED 08-JAN-18 15:07 23691 TEST1 ACTIVE DEDICATED 08-JAN-18 […]
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 […]
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 […]
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 […]
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;
How to check hidden parameter in Oracle
Check hidden parameter in Oracle set lines 200 col name for a50 col value for a40 select ksppinm name , ksppstvl value from x$ksppi a, x$ksppsv b where a.indx=b.indx and substr(ksppinm,1,1) = ‘_’;