Prevent 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 MoreOracle Database 19c Patch Release Update – Jan 2021(Patch Id – 32218454)
DESCRIPTION This article we are going to see steps to apply the latest Oracle 19c Database Release Update Patch 32218454 DOWNLOAD THE PATCH FROM ORACLE SUPPORT CHECK THE CURRENT OPTACH…
Read MoreDROP AND RECREATE UNDO
INTRODUCTION: The below steps shows how to drop and recreate undo tablespace, by doing this activity we can gain more space in mount points and the database size will be reduced. STEP 1: Check the DB size using the below query, col “Database Size” format a20 col “Free space” format a20 col “Used space” format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size” , round(sum(used.bytes) / 1024 / 1024 / 1024 ) – round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space” , round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space” from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p / STEP 2: Check the list of undo tablespaces using the below query, column tablespace format a20…
Read More