Oracle - Show all sequence

SELECT us.sequence_name
              FROM USER_SEQUENCES us;

Oracle - Show all table name

SELECT ut.table_name
              FROM USER_TABLES ut;

Oracle - PL/SQL - Delete all table

DECLARE
BEGIN
    --Bye Tables!
  FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
  END LOOP;

END;

Oracle - PL/SQL - Delete all sequence

DECLARE
BEGIN
--Bye Sequences!
  FOR i IN (SELECT us.sequence_name
              FROM USER_SEQUENCES us) LOOP
    EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||'';
  END LOOP;


END;

Oracle : Check last analyzed after gather statistic of schema

select table_name, last_analyzed
from user_tables
order by last_analyzed desc nulls last;

It show all table that has been analyzed with last analyzed date..

Oracle : find duplicate row and total duplicate

select column_name, count(column_name)
from table
group by column_name
having count (column_name) > 1;

Oracle : display all date in month

Here some example for show all date in current/before/after month or specific month

select to_char( add_months(trunc(sysdate,'MM'),-1) + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(add_months(trunc(sysdate,'MM'),-1)) -  add_months(trunc(sysdate,'MM'),-1) + 1;

Above is before current month

select to_char( add_months(trunc(sysdate,'MM'),1) + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(add_months(trunc(sysdate,'MM'),1)) -  add_months(trunc(sysdate,'MM'),1) + 1;

Above is after current month

select to_char( trunc(sysdate,'MM') + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(trunc(sysdate,'MM')) -  trunc(sysdate,'MM') + 1;

Above is current month

select to_char( trunc(to_date('20140101','YYYYMMDD'),'MM') + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(trunc(to_date('20140101','YYYYMMDD'),'MM')) -  trunc(to_date('20140101','YYYYMMDD'),'MM') + 1;

Above is specific month but you must enter date.

Oracle : How to find all indexes under certain table.

How to find all indexes under certain table.

select index_name, column_name

 from user_ind_columns

 where table_name = '<table_name>';

Oracle : Database link

For example


(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=mydb)))


1. Connect your oracle.
     > sqlplus /nolog
     > conn user/pass@mydb


2. Create db link :
     > Create database link monitordblink
connect to monitor identified by monitor123
using 'mydb';

the syntax is :

CREATE {PUBLIC} DATABASE LINK <database link name>
{CONNECT TO <oracle user id>
IDENTIFIED BY <remote oracle user's password> }
USING ' <dbstring> ';

3.  > Commit;

4. Check your db link :
    > SELECT * FROM all_db_links;


5. You can check table by using below command :
select <column list> from <table>@<dblink name>;
> select * from usertbl@monitordblink;


Source

Oracle : Date to Second Function

Below is sample for oracle function Date to second.

CREATE OR REPLACE FUNCTION TGATE_NI.DATE2TS (m_date_real IN DATE)
RETURN NUMBER IS
                m_unix_start_date_real                              DATE;
                m_days_difference                                     NUMBER;
                timeoutput                                                  NUMBER;
BEGIN
                m_unix_start_date_real := TO_DATE('19700101 0000', 'YYYYMMDD HH24MI');
               m_days_difference := (m_date_real - m_unix_start_date_real);
                timeoutput := (m_days_difference * 86400) - 28800;
                return timeoutput;
END;
/