Category Archives: SQL Scripts For DBA

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

Quick Checking Tablespace Usage by Querying DBA_TABLESPACE_USAGE_METRICS

DBA need query a tablespace usage.  There are a number of ways to do so by joining below different views: DBA_TABLESPACE DBA_DATA_FILES DBA_FREE_SPACE … .. . There is a quicker way to do so by querying DBA_TABLESPACE_USAGE_METRICS. SQL> desc DBA_TABLESPACE_USAGE_METRICS … Continue reading

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