Script to view hidden parameter setting
Set lines 2000 col NAME for a45 col DESCRIPTION for a100 SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’;
Read MoreScript to find rollback segment used
set lines 1000 pages 1000 col RBS format a20 col sid format 9999 col user format a28 col status format a12 SELECT r.name “RBS”, s.sid, s.serial#, s.username “USER”, t.status, t.cr_get,…
Read MorePrevent users from log into a database within defined period
Description: It shows how to set up the database security so that users cannot log into the database outside defined time-window. Here we are using Event Triggers. Steps to be…
Read MoreRECOVER TABLE USING RMAN
TO FIND THE CURRENT SCN ======================== select current_scn from v$database; TO FIND THE SCN FROM TIMESTAMP =============================== select timestamp_to_scn(to_timestamp(’05-09-2018 12:46:21′,’dd-mm-yyyy hh24:mi:ss’)) scn from dual; TO FIND THE TIMESTAMP FROM SCN…
Read MoreScript to check index column positons
set pages 1000 lines 1000 col column_name for a40 col index_name for a45 col column_expression for a40 set lines 120 pages 50000 select c.index_name, c.column_name “COLUMN_NAME”, c.column_position, a.column_expression from dba_ind_columns…
Read MoreOracle Flashback Query: Recovering at the Row Level
In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 4:25…
Read MoreScript to find the session events
col sql format a35 col username format a20 col child format 999 col secs format 9999 col machine format a12 col event format a25 col state format a10 select /*+…
Read MoreInvalid OLAP Objects GENMETADATAPROVIDERINTERFACE
Environment: Oracle OLAP – Version 11.2.0.3 and later Symptoms: OLAP is valid in dba_registry but the following OLAP objects are invalid: SQL> select owner,object_name,object_type from dba_objects where status=’INVALID’ order by…
Read Morequery to check ORA errors
This query is used to check the ORA error in past one hour. query: COLUMN ORIGINATING_TIMESTAMP FORMAT A40 COLUMN message_text FORMAT A100 set linesize 300 SELECT ORIGINATING_TIMESTAMP , message_text FROM…
Read Morescript to copy and import schema backup from prod to uat
This script is useful to import monthly backup of specific schema from prod to uat after taking necessary schema backup in uat. In prod every month the schema is exported…
Read More