DBA is always being asked about the database size growth trend for coming months by a client or reporting manager.
If you can present the following report, that would show your great service.
Now lets’ demonstrate how to get similar report for maximum up to two years period of time from OEM repository.
Read more of this content when you subscribe today.
Get alert log contents directly from database by using SQL query
We can get alert log contents directly from database by using SQL query, though the x$ view name is hard to remember.
The view x$dbgalertext is the view to query to get alert log contents for current instance:
SQL> DESC X$DBGALERTEXT Name Null? Type --------------------------- -------- ---------------------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER CON_ID NUMBER ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE ORGANIZATION_ID VARCHAR2(64) COMPONENT_ID VARCHAR2(64) HOST_ID VARCHAR2(64) HOST_ADDRESS VARCHAR2(46) MESSAGE_TYPE NUMBER MESSAGE_LEVEL NUMBER MESSAGE_ID VARCHAR2(64) MESSAGE_GROUP VARCHAR2(64) CLIENT_ID VARCHAR2(64) MODULE_ID VARCHAR2(64) PROCESS_ID VARCHAR2(32) THREAD_ID VARCHAR2(64) USER_ID VARCHAR2(64) INSTANCE_ID VARCHAR2(64) DETAILED_LOCATION VARCHAR2(160) PROBLEM_KEY VARCHAR2(550) UPSTREAM_COMP_ID VARCHAR2(100) DOWNSTREAM_COMP_ID VARCHAR2(100) EXECUTION_CONTEXT_ID VARCHAR2(100) EXECUTION_CONTEXT_SEQUENCE NUMBER ERROR_INSTANCE_ID NUMBER ERROR_INSTANCE_SEQUENCE NUMBER VERSION NUMBER MESSAGE_TEXT VARCHAR2(2048) MESSAGE_ARGUMENTS VARCHAR2(512) SUPPLEMENTAL_ATTRIBUTES VARCHAR2(512) SUPPLEMENTAL_DETAILS VARCHAR2(4000) PARTITION NUMBER RECORD_ID NUMBER
SQL> select
to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'),
MESSAGE_TEXT
from X$DBGALERTEXT
where ORIGINATING_TIMESTAMP>sysdate-5/24
order by RECORD_ID;
TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20171030-12:34:15 Thread 1 advanced to log sequence 1016 (LGWR switch)
20171030-12:34:15 Current log# 2 seq# 1016 mem# 0: +DATA2/RACTEST/ONLINELOG/group_2.1453.926116803
20171030-12:34:15 Current log# 2 seq# 1016 mem# 1: +FRA/RACTEST/ONLINELOG/group_2.20660.926116805
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_1_seq_844.2274.952747445
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_2_seq_875.15096.952747443
20171030-12:34:27 Archived Log entry 3854 added for thread 1 sequence 1015 ID 0x7553f3c0 dest 1:
6 rows selected.
SQL> select
to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'),
MESSAGE_TEXT
from X$DBGALERTEXT
order by RECORD_ID
fetch first 20 rows only;
TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20170825-16:13:10 Starting ORACLE instance (normal) (OS id: 10587)
20170825-16:13:10 CLI notifier numLatches:53 maxDescs:4998
20170825-16:13:10 **********************************************************************
20170825-16:13:10 Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
20170825-16:13:10 Per process system memlock (soft) limit = UNLIMITED
20170825-16:13:10 Expected per process system memlock (soft) limit to lock
20170825-16:13:10 SHARED GLOBAL AREA (SGA) into memory: 16G
20170825-16:13:10 Available system pagesizes:
20170825-16:13:10 4K, 2048K
20170825-16:13:10 Supported system pagesize(s):
20170825-16:13:10 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
20170825-16:13:10 4K Configured 10 10 NONE
20170825-16:13:10 2048K 68613 8193 8193 NONE
20170825-16:13:10 **********************************************************************
20170825-16:13:10 LICENSE_MAX_SESSION = 0
20170825-16:13:10 LICENSE_SESSIONS_WARNING = 0
20170825-16:13:10 Initial number of CPU is 32
20170825-16:13:10 Number of processor cores in the system is 16
20170825-16:13:10 Number of processor sockets in the system is 2
20170825-16:13:10 Private Interface 'bond1:1' configured from GPnP for use as a private interconnect.
20 rows selected.
SQL to find how long a session has been ACTIVE or IDLE, very handy.
There are up to one thousand sessions connected to Oracle Database from a Weblogic connection pool. User wanted to know how long those sessions have been idle ( INACTIVE), so the Weblogic connection pool can be reviewed and reconfigured properly.
The below query can be run to meet this requirement:
SQL>select USERNAME,
MACHINE,
STATUS,
LOGON_TIME,
LAST_CALL_ET INACTIVE_SECONDS
from gv$session
order by LAST_CALL_ET desc;
USERNAME MACHINE STATUS LOGON_TIME INACTIVE_SECONDS
---------- ---------- ------- ----------------- ----------------
USERAPP machine1 INACTIVE 20170917-17:04:17 3523
USERAPP machine2 INACTIVE 20170918-02:57:03 2068
USERAPP machine3 INACTIVE 20170917-17:52:09 1141
...
..
.
USERAPP machine1 INACTIVE 20170917-17:17:01 176
USERAPP machine1 I NACTIVE 20170917-17:17:01 176
According to Oracle doc :
LAST_CALL_ET | NUMBER | If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive. |
The below query can also get the results as per Oracle Doc ID 365693.1.
SQL>select sid, seconds_in_wait
from v$session_wait
where state='WAITING'
and event='SQL*Net message from client'
order by seconds_in_wait desc;
Maximum length of command line sql script is 2499 characters.
Sometimes execute SQL scripts from SQL*PLUS, then get below error:
SP2-0027: Input is too long (> 2499 characters) - line ignored
Useful report to show tablespace daily usage and space usage increasing ratio.
Please make sure you are licensed to run sqls against DBA_HIST_*, like dba_hist_tbspc_space_usage .
SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS NAME TYPE VALUE ------------------------------- -------- ----------------------- control_management_pack_access string DIAGNOSTIC+TUNING
The length of the report depends on AWR retention. Please refer to “Change AWR Retention & Interval” for how to change AWR retention.
select THE_DATE,
TABLESPACE,
SIZE_IN_GB_TD,
SIZE_IN_GB_YTD,
SIZE_IN_GB_TD - SIZE_IN_GB_YTD SIZE_IN_GB_INCREASED
from (
select
to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') THE_DATE,
tbl.name TABLESPACE,
round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2) SIZE_IN_GB_TD,
lag( round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2),1) over ( order by to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') ) SIZE_IN_GB_YTD
from
dba_hist_snapshot histsnap,
dba_hist_tbspc_space_usage tblusage,
v$tablespace tbl,
v$parameter pmt
where
histsnap.SNAP_ID = tblusage.SNAP_ID
and tbl.ts# = tblusage.TABLESPACE_ID
and tbl.name ='&TABLESPACENAME'
and pmt.name ='db_block_size'
group by
to_char( histsnap.begin_interval_time, 'YYYY-MM-DD'),
tbl.name,
pmt.value
order by
to_char( histsnap.begin_interval_time, 'YYYY-MM-DD')
);
Enter value for tablespacename: USERS
old 17: and tbl.name ='&TABLESPACENAME'
new 17: and tbl.name ='USERS'
THE_DATE TABLESPACE SIZE_IN_GB_TD SIZE_IN_GB_YTD SIZE_IN_GB..ASED
---------- ---------- ------------- -------------- -----------------
2017-05-10 USERS 4.17
2017-05-11 USERS 4.17 4.17 0
2017-05-12 USERS 4.45 4.17 .28
2017-05-13 USERS 4.55 4.45 .1
2017-05-14 USERS 4.55 4.55 0
2017-05-15 USERS 4.56 4.55 .01
2017-05-16 USERS 4.84 4.56 .28
2017-05-17 USERS 4.96 4.84 .12
2017-05-18 USERS 5.14 4.96 .18
2017-05-19 USERS 5.14 5.14 0
10 rows selected.
Make DBA Life Easy 