Description: We are restricting the logon time for a particular database user so the user can only access the database within defined periods. If the user attempts to logon during…
Read Moreset pages 5000 SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON – HEAD “<TITLE>EMPLOYEE REPORT</TITLE> – <STYLE type=’text/css’> – <!– BODY {background: #FFFFC6} –> – </STYLE>” – BODY…
Read Moreselect ‘alter database datafile’||’ ”’||file_name||””||’ resize ‘||round(highwater+2)||’ ‘||’m’||’;’ from ( select /*+ rule */ a.tablespace_name, a.file_name, a.bytes/1024/1024 file_size_MB, (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater from dba_data_files a , (select file_id,max(block_id) maximum from dba_extents group…
Read MoreSet lines 2000 col NAME for a45 col DESCRIPTION for a100 SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’;
Read Moreset 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 MoreDescription: 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 MoreTO 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 Moreset 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 MoreIn 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 Morecol 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 More