Using Wrap Procedure ONLOGN Trigger

1. Make a script file with following code
CREATE OR REPLACE PROCEDURE V_AUTH_PROG
IS
V_PROGR_NAME VARCHAR2(200);
BEGIN
 SELECT UPPER(sys_context('USERENV', 'MODULE')) INTO V_PROGR_NAME
 FROM DUAL;
 IF V_PROGR_NAME <> 'SQLPLUS.EXE' THEN
  RAISE_APPLICATION_ERROR(-20121,'THIRD PARTY TOOL NOT ALLOWED');
 END IF;
END;

2. save the file with v_auth_prog.sql

3. Switch to Command Prompt

4. write the following command on command prompt

D:\WRAP C:\V_AUTH_PROG.SQL TO C:\V_AUTH_PROG_W.SQL

5. Now open the SQLPLUS session and connect with SYS user as Sysdba

6. Run the above scritp

    SQL>@c:\v_auth_prog_w.sql

7.Now create the following code  for onlogon Trigger

   CREATE OR REPLACE TRIGGER DB_LOGON AFTER LOGON ON DATABASE

   CALL V_AUTH_PROG

  /

do not need to add ; or other things, its available for all users who are trying to accesss database with third parties tools. You can inculde formbuilder (builder and runing) program name in procedure.

Best of Luck and remember me in your prayer.

Changes the Default Profile.

As you can seee the default behaviour for Oracle User to change the password after limiting time, here is the following solution to use Oracle user without changes a password.

sql>conn sys/oracle as sysdba

sql>select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from  dba_users where USERNAME in ('HR');

-- Chech the PASSWORD_LIFE_TIME LIMIT in dba_profiles tables for 'DEFAULT' profile..

sql>select * from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME' ;

-- set 'PASSWORD_LIFE_TIME' limit to 'UNLIMITED'
sql>alter profile DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

sql>select * from dba_profiles where PROFILE='DEFAULT' and 
RESOURCE_NAME='PASSWORD_LIFE_TIME' ;

sql>select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from  dba_users where USERNAME in ('HR');

The Unknown But Not Hidden: THINGS YOU SHOULD NEVER SHARE OF FACEBOOK

The Unknown But Not Hidden: THINGS YOU SHOULD NEVER SHARE OF FACEBOOK: 5 THINGS YOU SHOULD NEVER SHARE OF FACEBOOK: [By Rahul Tandon, @ The Unknown but not hidden.] Be aware of what and how much y...

Misc Queries

Find the Duplicate Salaries
---------------------------------
select a.empno,a.ename,a.sal,a.deptno,b.empno dup_empno,b.ename dup_ename,b.sal dup_sal,b.deptno dup_deptno
from emp a, emp b
where a.sal=b.sal
and   a.empno<>b.empno
and   a.deptno=b.deptno
and   a.rowid>b.rowid;

EMPNO	ENAME	SAL	DEPTNO	DUP_EMPNO	DUP_ENAME	DUP_SAL	DUP_DEPTNO
7654	MARTIN	1,250.00	30	7521	WARD	1,250.00	30
7902	FORD	3,000.00	20	7788	SCOTT	3,000.00	20
7789	SCOTT	5,000.00	10	7839	KING	5,000.00	10

Nth Value

------------------

select level, max(sal) from emp

connect by prior sal > sal

group by level

order by 1 desc;

Free Space

------------------

SELECT   s.tablespace_name TABLESPACE,

         ROUND (((s.ublocks * 8) * 1024) / (1024 * 1024), 0) \"USEDMB\",

         ROUND ((((f.fblocks * 8) * 1024) / (1024 * 1024)), 0) \"FREEMB\",

         ublocks + fblocks \"TOTAL BLOCKS\",

         ROUND (((((ublocks + fblocks) * 8) * 1024) / (1024 * 1024)),

                0

               ) \"TOTAL MB\"

    FROM (SELECT   tablespace_name, SUM (blocks) ublocks

              FROM dba_segments

          GROUP BY tablespace_name) s,

         (SELECT   tablespace_name, SUM (blocks) fblocks

              FROM dba_free_space

          GROUP BY tablespace_name) f

   WHERE s.tablespace_name = f.tablespace_name

ORDER BY TABLESPACE;

User Privileges and Roles

------------------------------

select

  lpad('  ', 3*level) || granted_role "Users and roles privileges"

from

  (

  /* THE USERS */

    select

      null     grantee,

      username granted_role

    from

      dba_users

    where

      username in (select username from dba_users)

  /* THE ROLES TO ROLES RELATIONS */

  union

    select

      grantee,

      granted_role

    from

      dba_role_privs

  /* THE ROLES TO PRIVILEGE RELATIONS */

  union

    select

      grantee,

      privilege

    from

      dba_sys_privs

       )

start with grantee is null

connect by grantee = prior granted_role;

Generate dates
with
    start_and_end ( start_date, end_date ) as (
      select TO_DATE ('2003/04/01', 'yyyy/mm/dd'), TO_DATE ('2003/04/07', 'yyyy/mm/dd') from dual
    )
-- end of input data; actual solution begins below this line
select start_date + level - 1 as dt
from   start_and_end
connect by level <= end_date - start_date + 1;

DT
----------
01/04/2003
02/04/2003
03/04/2003
04/04/2003
05/04/2003
06/04/2003
07/04/2003

Convert Numbers

Package with functions to convert numbers between the  Decimal, Binary, Octal and Hexidecimal numbering 

set serveroutput on
 
CREATE OR REPLACE PACKAGE dbms_numsystem AS
   function bin2dec (binval in char  ) RETURN number;
   function dec2bin (N      in number) RETURN varchar2; 
   function oct2dec (octval in char  ) RETURN number;
   function dec2oct (N      in number) RETURN varchar2; 
   function hex2dec (hexval in char  ) RETURN number;
   function dec2hex (N      in number) RETURN varchar2; 
END dbms_numsystem;
/
show errors
 
CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS
 
FUNCTION bin2dec (binval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(binval);
  for i in 1..digits loop
     current_digit := SUBSTR(binval, i, 1);
     current_digit_dec := to_number(current_digit);
     result := (result * 2) + current_digit_dec;
  end loop;
  return result;
END bin2dec;
 
FUNCTION dec2bin (N in number) RETURN varchar2 IS
  binval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     binval := mod(N2, 2) || binval;
     N2 := trunc( N2 / 2 );
  end loop;
  return binval;
END dec2bin;
 
FUNCTION oct2dec (octval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(octval);
  for i in 1..digits loop
     current_digit := SUBSTR(octval, i, 1);
     current_digit_dec := to_number(current_digit);
     result := (result * 8) + current_digit_dec;
  end loop;
  return result;
END oct2dec;
 
FUNCTION dec2oct (N in number) RETURN varchar2 IS
  octval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     octval := mod(N2, 8) || octval;
     N2 := trunc( N2 / 8 );
  end loop;
  return octval;
END dec2oct;
 
FUNCTION hex2dec (hexval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(hexval);
  for i in 1..digits loop
     current_digit := SUBSTR(hexval, i, 1);
     if current_digit in ('A','B','C','D','E','F') then
        current_digit_dec := ascii(current_digit) - ascii('A') + 10;
     else
        current_digit_dec := to_number(current_digit);
     end if;
     result := (result * 16) + current_digit_dec;
  end loop;
  return result;
END hex2dec;
 
FUNCTION dec2hex (N in number) RETURN varchar2 IS
  hexval varchar2(64);
  N2     number := N;
  digit  number;
  hexdigit  char;
BEGIN
  while ( N2 > 0 ) loop
     digit := mod(N2, 16);
     if digit > 9 then 
        hexdigit := chr(ascii('A') + digit - 10);
     else
        hexdigit := to_char(digit);
     end if;
     hexval := hexdigit || hexval;
     N2 := trunc( N2 / 16 );
  end loop;
  return hexval;
END dec2hex;
 
END dbms_numsystem;

-- Examples:
select dbms_numsystem.dec2bin(22)      from dual;
select dbms_numsystem.bin2dec('10110') from dual;
select dbms_numsystem.dec2oct(44978)   from dual;
select dbms_numsystem.oct2dec(127662)  from dual;
select dbms_numsystem.dec2hex(44978)   from dual;

select dbms_numsystem.hex2dec('AFB2')  from dual;

Leap Year

Leap Year Query

select  extract(year from hiredate) years,

       decode( mod(extract(year from hiredate), 4), 0,

          decode( mod(extract(year from hiredate), 400), 0, 'Leap Year',

             decode( mod(extract(year from hiredate), 100), 0, 'Not a Leap Year', 'Leap Year')

          ), 'Not a Leap Year'

       ) as leap_year_indicator

from   emp;

Output Look Like

------------------------

YEARS	LEAP_YEAR_INDICATOR
1980	Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1987	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1987	Not a Leap Year
1981	Not a Leap Year
1981	Not a Leap Year
1982	Not a Leap Year
2003	Not a Leap Year

Unused Columns in Oracle Database

Find Unused Columns in Oracle Database

CREATE OR REPLACE FUNCTION GET_NULL_COLUMNS ( 

   IN_TABLE_NAME VARCHAR2 DEFAULT NULL) 

   RETURN SYS.ODCIVARCHAR2LIST 

 IS 

   L_NULL_COLS   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST (); 

   LN$ROW_COUNT  NUMBER; 

   LN$NULL_COUNT  NUMBER; 

   LN$INDEX    NUMBER := 1; 

   CURSOR LCUR$COLS (P_TABLE_NAME VARCHAR2) 

   IS 

    SELECT TABLE_NAME, COLUMN_NAME 

     FROM USER_TAB_COLS 

     WHERE DECODE (P_TABLE_NAME, TABLE_NAME, 1, NULL, 1, 0) = 1; 

 BEGIN 

   FOR LREC$COLS IN LCUR$COLS (IN_TABLE_NAME) 

   LOOP 

    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || LREC$COLS.TABLE_NAME 

      INTO LN$ROW_COUNT; 

    EXECUTE IMMEDIATE  'SELECT COUNT(1) FROM ' 

             || LREC$COLS.TABLE_NAME 

             || ' WHERE ' 

             || LREC$COLS.COLUMN_NAME 

             || ' IS NULL' 

      INTO LN$NULL_COUNT; 

    IF LN$ROW_COUNT = LN$NULL_COUNT 

    THEN 

      L_NULL_COLS.EXTEND; 

      L_NULL_COLS (LN$INDEX) := 

       LREC$COLS.TABLE_NAME || '.' || LREC$COLS.COLUMN_NAME; 

      LN$INDEX := LN$INDEX + 1; 

    END IF; 

   END LOOP; 

   RETURN L_NULL_COLS; 

 END;  

Time Calculation

Following Example shows how we calculate the time.

CREATING THE TABLE

----------------------------------------

CREATE TABLE KMH_1

(  PKKEY  NUMBER CONSTRAINT PK_PKEY PRIMARY KEY,

   ID     NUMBER,

   NAME   VARCHAR2(20 BYTE),

   DATE1  DATE);

DML STATEMENTS FOR TABLE

-----------------------------------------------------

INSERT INTO KMH_1

VALUES

(1,1,'A',TO_DATE('12/03/2013 07:30','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(2,1,'A',TO_dATE('12/03/2012  16:00','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(3,2,'B',TO_DATE('12/03/2013 07:30','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(4,2,'B',TO_dATE('12/03/2012  16:00','DD/MM/RRRR HH24:MI')); 

/

QUERY

------------

SELECT A.ID,A.NAME,MAX(A.START_TIME) ST,MAX(A.END_TIME) ET,

TRUNC(REPLACE(MAX(A.END_TIME),':','.')  - REPLACE(MAX(A.START_TIME),':','.'),0) NO_HRS

FROM 

(SELECT ID,NAME, 

CASE WHEN TO_NUMBER(TO_CHAR(DATE1,'HH24'))BETWEEN 0 AND 12 THEN TO_CHAR(DATE1,'HH24:MI') END START_TIME,

CASE WHEN TO_NUMBER(TO_CHAR(DATE1,'HH24'))BETWEEN 13 AND 23 THEN TO_CHAR(DATE1,'HH24:MI') END END_TIME

FROM KMH_1

ORDER BY PKKEY) A

GROUP BY A.ID,A.NAME

Bulk Collect

PROCEDURE process_all_rows
IS
   CURSOR table_with_227_rows_cur
   IS
      SELECT * FROM table_with_227_rows;

   TYPE table_with_227_rows_aat IS
      TABLE OF table_with_227_rows_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   l_table_with_227_rows table_with_227_rows_aat;
BEGIN  
   OPEN table_with_227_rows_cur;
   LOOP
      FETCH table_with_227_rows_cur
         BULK COLLECT INTO l_table_with_227_rows LIMIT 100;

         EXIT WHEN table_with_227_rows_cur%NOTFOUND;     /* cause of missing rows */

      FOR indx IN 1 .. l_table_with_227_rows.COUNT
      LOOP
         analyze_compensation (l_table_with_227_rows(indx));
      END LOOP;
   END LOOP;

   CLOSE table_with_227_rows_cur;
END process_all_rows;

DBA Commands

Show Oracle Reads And Writes By File Name In Oracle Database

Oracle can show us the reads and writes by datafile name.   This is done by stats kept ini v$filestat

set lines 200

set pages 200

select v$datafile.name "File Name", v$filestat.phyrds "Reads", v$filestat.phywrts "Writes" from v$filestat,v$datafile where v$filestat.file# = v$datafile.file# ;

Display All Oracle Online Redo Log Groups For A Database

The Oracle redo log is part the Oracle database. When an Oracle transaction is committed, the transaction's is recorded in the redo log buffer than written to a redo log file.

set lines 200

set pages 200

select * from v$log; 

Create Oracle Script To Count All Rows In Each Table

Create Script to count rows in each table.  

The Script Looks as follows:

set lines 200

set pages 200

select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name;

This will produce a script that looks like:

Select count(*) from XDBPM.REVISED_CHOICE_MODEL;

Select count(*) from XDBPM.REVISED_COLL_TYPES;

…

Select count(*) from XDBPM.XDBPM_INDEX_DDL_CACHE;

Query All Active Oracle Users

Posted January 24th, 2013 by admin & filed under Oracle Administration, Oracle Database Structures.

Show all the active Oracle users in the system.  Query the v$session table to see the username and machine user is on.

set lines 200

set pages 200

select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;

Display All The Oracle Tables In The Database

Posted January 22nd, 2013 by admin & filed under Oracle Administration, Oracle Database Structures.

Display All Oracle Tables in the database.  Shows all tables(less the system tables.   Change for your databases.  With where statement like  "where owner in 'YOUROWNERNAME'"

set lines 200

set pages 200

select owner, table_name, table_type, tablespace_name

from dba_all_tables

where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS')

order by owner, table_name, tablespace_name

Display All The Oracle Indexes In The Database

Display All Oracle Indexes in the database.  Shows all indexes.   Customize for your database.

set lines 200

set pages 200

Select owner, index_name, table_type, tablespace_name

From dba_indexes

where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS')

order by owner, index_name, tablespace_name

;

Upgrade Oracle Database from Oracle Standard Edition to Oracle Enterprise Edition

How to Convert/Upgrade an Oracle Database from Oracle Standard to Oracle Enterprise Edition

 The following steps should be done to convert your Oracle Standard Edition database to Oracle Enterprise Edition: 

1. Backup the database
2. UnInstall or De-install the Oracle Standard Edition software 
3. Install the Oracle Enterprise Edition software  
4. If you have an existing database, point your ORACLE_SID to this pre-existing database
5. Startup the database
6. Run the "catalog.sql" and "catproc.sql" scripts (This make sures all objects for Oracle Enterprise Edition are up to date)

That is pretty much it Oracle Enterprise Edition and ready to pay thoose yearly mainteance fees! 

Oracle Archive Log Mode Status or Status of Archive Log Subsystem

One can  can check to see if Oracle is in archive log mode or has logging turned on.  This means that the database is able to recover forward or backward in time.  Sometimes referred to as point in time recovery

Oracle Archive Log Mode Status or Status of Archive Log Subsystem

ARCHIVE LOG LIST;

Check the Oracle log mode of  the databases

SELECT name, log_mode FROM v$database;

Check the Oracle Archive mode in an  instance 

SELECT archiver FROM v$instance;

Select All Oracle Users Active In The System

Select All Oracle Users Active In The System

select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;

What version of Oracle is running

What version of Oracle is running?

select * from V$VERSION;

Another Option is to use the product table

set lines 200

column product format a50

column version format a15

column status format a20

SELECT * FROM product_component_version ;

SELECT * FROM V$VERSION;  to get the different versions Oracle. 

Show all Oracle Datafiles in a Tablespace dba_data_files

SQL query to show all the Oracle Datafiles that are in a Tablespace:

set lines 200

column file_name format a69

select file_name,tablespace_name,bytes / (1024 * 1024) "Size (MB)",autoextensible, maxbytes,increment_by

from dba_data_files

where tablespace_name like '&tbs' order by file_name ;

Show all the active Oracle Users on the system.

select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;

How does one drop an Oracle Table without putting it in the recycle bin. 

drop <table Name>  purge;

or drop and object

drop <object_type> <object_name> purge;

Show Contents Of The Recycle Bin

The following command will show the contents of the Oracle recycle bin.  This like the windows recycle bin where items can be recovered from.

show recyclebin;

Tags: recycle bin recyclebin

Empty Oracle Recycle Bin

Posted December 13th, 2012 by admin & filed under Oracle Administration.

How does one purge, empty, clean out the Oracle Recyclebin or Recycle Bin?

A new feature in Oracle 10g Release 2 was the Reycycle Bin. The recycle bin in the Oracle database is where all dropped objects reside. Underneath the covers the object are taking up the same space when they were created.  

 "DROP TABLE mytable", it doesn't really drop it. It instead renames it to e.g.: BIN$67815682942.

Dropped objects are not deleted they are just rename with a prefix of BIN$$. One can get access to the data in a dropeed table or even use a Flashback Query if you have this feature enabled on your version of Oracle.

To completely remove a table from the system and not keep in recycle been use PURGE TABLE command. So if table is called mytable.

The syntax would be PURGE TABLE mytable;

Other ways to purge the recycle bin are:

    PURGE TABLE mytable; (purges mytable from system and recycle bin)

    PURGE INDEX myindex; (purges myindex from system and recycle bin ) PURGE recyclebin;

Purge all object in recyclebin PURGE dba_recyclebin; (purge all objects / only sysdba can do this command)

To drop and purge a table in one command it would be: DROP TABLE mytable PURGE; So thats It.

Killing Oracle Sessions

Finding a Oracle session to kill starts with First finding the offending Query. Than kill it with the alter system kill command.

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program

FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

INST_ID SID SERIAL# SPID USERNAME PROGRAM

---------- ---------- ---------- ---------- ---------- ---------------------------------------------

1 69 99 4879 TEST billbprog@my.telly

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; or ALTER SYSTEM KILL SESSION '69,99' IMMEDIATE;

Security Grants

grant select on PERSON_TABLE to public with grant option;

select * from dba_tab_privs where TABLE_NAME = 'PERSON_TABLE'

select * from dba_role_privs where granted_role = 'PORTMAN_TABLE'

Resizing A Data File

alter database datafile '/u04/oradata/wpk/temp01.dbf' resize 500m;

Show All Product Information

select * from product_component_version;

Show Row Counts For All Tables That Have ANALYZE On

select owner table_name, num_rows from dba_tables where num_rows > 0;

Select All Users Active In The System

select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;

Show What A Current User Is Doing

select sid, serial#, status, server from v$session where username = 'BROWNBH';

Create Count For All Tables

select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name;

Show All Indexes

select owner, index_name, table_type, tablespace_name from dba_indexes where  owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, index_name, tablespace_name

Show All Tables

select owner, table_name, table_type, tablespace_name from dba_all_tables where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, table_name, tablespace_name

Show Space Used

select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/ Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from DBA_SEGMENTS where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM'

Sum Space By Owner

select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner

Sum Space by Tablespace

select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS  group by tablespace_name

Show Reads And Writes By File Name In Oracle DB

select v$datafile.name "File Name", v$filestat.phyrds "Reads", v$filestat.phywrts "Writes" from v$filestat,v$datafile where v$filestat.file# = v$datafile.file#

Show Versions Of Software

select * from V$VERSION

Identify Segments That Are Getting Close To Their Max-Extent Values

select owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments where extents*2 > max_extents

Identifies Segments That Are Getting Close To Running Out Of Contiguous Free Space

select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest from dba_segments s, dba_free_space f where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent having next_extent*2 >max(f.bytes)

Displays Archived Redo Log Information

select * from v$database  

Display Count Historical Archived Log Information From The Control File

select count(*) from v$archived_log 

select min(completion_time) from v$archived_log

Shows Current Archive Destinations

select * from v$archive_dest

Backups Of Archived Logs

select count(*) from v$backup_redolog

Display All Online Redo Log Groups For The database

select * from v$log 

Show All Datafiles For Tablespace And Oracle Stuff

select * from dba_data_files order by tablespace_name, file_name

DELETE THE DUPLICATE VALUES FROM THE TABLES.

DELETE FROM emp

WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);

Block developers from using TOAD and other tools on production databases

Today, I’ve seen a nice example at www.psoug.org on how to prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod

  AFTER LOGON ON DATABASE

DECLARE

  v_prog sys.v_$session.program%TYPE;

BEGIN

  SELECT program INTO v_prog

    FROM sys.v_$session

  WHERE  audsid = USERENV('SESSIONID')

    AND  audsid != 0  -- Don't Check SYS Connections

    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's

  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad

     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator

     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer

     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects

     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in

  THEN

     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');

  END IF;

END;

/