Category Archives: SQL Scripts For DBA

How to Check Oracle Database Current Number of Sessions / Processes and Maximum Utilization

1)From V$LICENSE View. It shows USER sessions only excluding database system sessions. SQL> select SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license; SESSIONS_CURRENT SESSIONS_HIGHWATER —————- —————— 771 1346 — SESSIONS_CURRENT :Current number of concurrent user sessions — SESSIONS_HIGHWATER :Highest number of concurrent user sessions since the instance … Continue reading

Posted in OEM12c, OEM13c, SQL Scripts For DBA | Tagged , , , , , , , , , , , , , | Leave a comment

Database Size History Report From OEM

A DBA is always asked about the database size growth trend for coming months  of one database  or databases, which possibly face storage or performance issue.  We can only get current database size from the database, or the size of … Continue reading

Posted in OEM12c, OEM13c, SQL Scripts For DBA | Tagged , , | Leave a comment

Query Alert Log Contents From Database

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 table to query to get alert log contents for current instance: SQL> DESC X$DBGALERTEXT … Continue reading

Posted in SQL Scripts For DBA | Tagged , , | Leave a comment

How Long a Session Has been Idle or Inactive In Oracle Database ?

SYMPTOMS 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 … Continue reading

Posted in SQL Scripts For DBA | Tagged , | Leave a comment

SP2-0027: Input is too long (> 2499 characters) – line ignored

Sometimes execute SQL scripts from SQL*PLUS, then  get below error: SP2-0027: Input is too long (> 2499 characters) – line ignored Workaround: To make one line sql script into different lines, which have less than 2500 characters fir each line. … Continue reading

Posted in Client, SQL Net, SQL Scripts For DBA | Tagged , | Leave a comment

ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

SQL> CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1 MAXVALUE 10;  Sequence created. SQL> select test_seq.currval from dual; select test_seq.currval from dual * ERROR at line 1: ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session SQL> select … Continue reading

Posted in ORA- ERRORS, SQL Scripts For DBA | Tagged , , , , , , , , , | Leave a comment

Tablespace Usage History Report

Please make sure you are licensed to run sqls against DBA_HIST_*, like  dba_hist_tbspc_space_usage tblusage. The length of the report depends on  AWR retention. Please refer to  “Change AWR Retention & Interval” for how to change AWR retention. SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS … Continue reading

Posted in AWR, SQL Scripts For DBA | Tagged , , , , , , , | Leave a comment

How to Kill Oracle RAC Sessions

We know the syntax for killing a session is : SQL>alter system kill session ‘SID, SERIAL#’ immediate; In RAC environment, the syntax will be : SQL>alter system kill session ‘SID, SERIAL#,@INST_ID’ immediate; Kill all the sessions from username ‘JAMESH’: SQL> … Continue reading

Posted in SQL Scripts For DBA | Tagged , , , , , , , | Leave a comment

How To Flush an Object Out The Library Cache by Using DBMS_SHARED_POOL Package

We know how to flash all shared pool by : SQL>alter system flush shared_pool; Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan.  DBMS_SHARED_POOL can do … Continue reading

Posted in Performance Tunning, SQL Scripts For DBA | Tagged , , , , , | Leave a comment

CREATE INDEX COMPUTE STATISTICS

When creating an index, “COMPUTE STATISTICS” option is no longer required for 10g onwards databases. Here is a quick demonstration: Create a table and insert a couple of sample records: SQL> create table test ( id number, name varchar(20)); Table … Continue reading

Posted in Performance Tunning, SQL Scripts For DBA | Tagged , , , , , , | Leave a comment