SQL

# grant permission to for tables to an user

BEGIN

FOR T IN (SELECT * FROM USER_TABLES)

LOOP

    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || T.TABLE_NAME || ' TO <SOME_USER>';

END LOOP;

END;

/

# accessing another database from current (usage of DB Link)

CREATE DATABASE LINK <my_db_link>

CONNECT TO <user_name> IDENTIFIED BY <password>

USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxipaddrxx / xxhostxx )(PORT=xxportxx))(CONNECT_DATA=(SID=xxsidxx)))';

SELECT * FROM <table_name>@<my_db_link>;

# detect last updated time of a table

SELECT TO_CHAR(SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)), 'DD-MON-YYYY, HH24:MM:SS') AS LAST_UPDATE_TIME FROM SUMDB.TF_ADCHOICE_SYNC_DATA;

SELECT TABLE_OWNER||'.'||TABLE_NAME AS TAB, TO_CHAR(TIMESTAMP, 'DD-MON-YYYY, HH24:MM:SS') AS LAST_UPDATE_TIME FROM ALL_TAB_MODIFICATIONS WHERE TABLE_OWNER||'.'||TABLE_NAME = 'SUMDB.TEMP_SITE_AP_PRICE';

# detect if SQL hangs

--Query 1: to know Sid, machine, sql_id etc.

SELECT

  SESS.SID, SESS.SCHEMANAME, SESS.MACHINE, SESS.SQL_ID, SESS.SERIAL#,

  LO.ORACLE_USERNAME, LO.OS_USER_NAME, AO.OBJECT_NAME, LO.LOCKED_MODE

FROM

  V$LOCKED_OBJECT LO,

  DBA_OBJECTS AO,

  V$SESSION SESS

WHERE

  AO.OBJECT_ID = LO.OBJECT_ID

  AND LO.SESSION_ID = SESS.SID

  AND LOWER(SESS.MACHINE) IN ('vmtfr-loader-vdx-dg3', 'vmtfr-web-01-zone1');

--Query 2: to know Sid, machine, sql_id etc.

SELECT

  S.SCHEMANAME, S.MACHINE, S.SID, S.BLOCKING_SESSION,

  S.SQL_ID, S.STATUS, S.OSUSER, S.MACHINE,

  P.PID, P.SPID

FROM

  V$SESSION S,

  V$PROCESS P

WHERE

  S.PADDR=P.ADDR

  AND LOWER(S.MACHINE) IN ('vmtfr-loader-vdx-dg3', 'vmtfr-web-01-zone1')

  AND S.BLOCKING_SESSION > 0;

--Query 3: to know Sid, machine, sql_id etc.

SELECT

  S.BLOCKING_SESSION, S.SCHEMANAME, S.MACHINE, S.SID, S.SERIAL#, S.SECONDS_IN_WAIT, S.SQL_ID

FROM

  GV$SESSION S

WHERE

  BLOCKING_SESSION IS NOT NULL

  AND LOWER(MACHINE) IN ('vmtfr-loader-vdx-dg3', 'vmtfr-web-01-zone1');

--Query 4: to know Sid, machine, sql_id etc again, and then kill the session

SELECT

  SCHEMANAME, SID, SERIAL#, SQL_ID, STATUS, MACHINE, SERVER

FROM

  V$SESSION

WHERE

  MACHINE LIKE '%web-01%';

--

ALTER SYSTEM KILL SESSION '_sid_, _serial#_';

--Query to know full SQL text.

SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID ='8f6wx3sassdp2';

SELECT OBJECT_ID, OBJECT_NAME FROM DBA_OBJECTS / ALL_OBJECTS WHERE OBJECT_NAME='_table_name_';

SELECT SID FROM V$LOCK WHERE ID1=7401242;

SELECT SID, SERIAL# FROM V$SESSION WHERE SID=3434;

# SQL query to find DDL

SELECT DBMS_METADATA.GET_DDL('object_type', 'object_name', 'schema_name') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('TABLE','TF_ALERT_CONFIG','TFR_REP') FROM DUAL;

# SQL query to show epoch into human readable time

SELECT

  JOB_NAME,

  TO_CHAR(TO_DATE('19700101','YYYYMMDD')+NUMTODSINTERVAL(PREV_FIRE_TIME/1000,'SECOND'),'YYYY-MON-DD,HH24:MI:SS') PREV_FIRE_TIME,

  TO_CHAR(TO_DATE('19700101','YYYYMMDD')+NUMTODSINTERVAL(NEXT_FIRE_TIME/1000,'SECOND'),'YYYY-MON-DD,HH24:MI:SS') NEXT_FIRE_TIME

FROM QRTZ_TRIGGERS;

# How to fix SQLDeveloper for TimeZone related issues

Add "AddVMOption -Duser.timezone=IST" in "SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf"

# How can I find which tables reference a given table in Oracle SQL Developer?

To add this to SQL Developer as an extension do the following:

Save the below code into an xml file (e.g. fk_ref.xml):

<items>

    <item type="editor" node="TableNode" vertical="true">

    <title><![CDATA[FK References]]></title>

    <query>

        <sql>

            <![CDATA[select a.owner,

                            a.table_name,

                            a.constraint_name,

                            a.status

                     from all_constraints a

                     where a.constraint_type = 'R'

                            and exists(

                             select 1

                             from all_constraints

                             where constraint_name=a.r_constraint_name

                                     and constraint_type in ('P', 'U')

                                     and table_name = :OBJECT_NAME

                                     and owner = :OBJECT_OWNER)

                             order by table_name, constraint_name]]>

        </sql>

    </query>

    </item>

</items>

Add the extension to SQL Developer:

  -1) Tools --> Preferences --> Database --> User Defined Extensions

  -2) Click "Add Row" button

  -3) In Type choose "EDITOR", Location is where you saved the xml file above

  -4) Click "Ok" then restart SQL Developer

  -5) Navigate to any table and you should now see an additional tab next to "SQL" one, named "FK References", which displays the new FK information.

One can use the information shown in this tab to *enable/disable* constraints on a table by executing the following command:

    ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name>;

Also, we can use another xml like the following to have another tab for Partition Columns:

<items>

    <item type="editor" node="TableNode" vertical="true">

    <title><![CDATA[Partition Columns]]></title>

    <query>

        <sql>

            <![CDATA[SELECT * FROM ALL_PART_KEY_COLUMNS WHERE OWNER = :OBJECT_OWNER AND NAME = :OBJECT_NAME]]>

        </sql>

    </query>

    </item>

</items>

# sample unix commands to change permission of the directory where one can create postgres tablespaces

sudo chown postgres:_postgres /Users/tom/software/postgres_edb/installation/table_space

sudo chown postgres:_postgres /Users/tom/software/postgres_edb/installation/table_space/*

# sample postgres commands to create tablespace

DROP TABLESPACE IF EXISTS SUM_S_TABLE;

DROP TABLESPACE IF EXISTS SUM_S_H_PART_TABLE;

DROP TABLESPACE IF EXISTS SUM_S_R_PART_TABLE;

DROP TABLESPACE IF EXISTS SUM_S_INDEX;

DROP TABLESPACE IF EXISTS SUM_S_H_PART_INDEX;

DROP TABLESPACE IF EXISTS SUM_S_R_PART_INDEX;

CREATE TABLESPACE SUM_S_TABLE LOCATION '/var/lib/edb/as11/table_space/sum_s_tab';

CREATE TABLESPACE SUM_S_H_PART_TABLE LOCATION '/var/lib/edb/as11/table_space/sum_s_h_part_tab';

CREATE TABLESPACE SUM_S_R_PART_TABLE LOCATION '/var/lib/edb/as11/table_space/sum_s_r_part_tab';

CREATE TABLESPACE SUM_S_INDEX LOCATION '/var/lib/edb/as11/table_space/sum_s_idx';

CREATE TABLESPACE SUM_S_H_PART_INDEX LOCATION '/var/lib/edb/as11/table_space/sum_s_h_part_idx';

CREATE TABLESPACE SUM_S_R_PART_INDEX LOCATION '/var/lib/edb/as11/table_space/sum_s_r_part_idx';

# sample unix commands to find space consumed by postgres-edb

sudo -u enterprisedb du -sh /var/lib/edb/as11/table_space/

sudo -u enterprisedb du -sh /var/lib/edb/as11/

# sample postgres command to create tablespace (make sure the directory exists)

CREATE TABLESPACE SUM_S_TABLE LOCATION '/var/lib/edb/as11/table_space/sum_s_tab'

# environment properties of postgres is mentioned in the config-file that can be seen by the following postgres command (something like: /Users/tom/software/postgres_edb/installation/data/postgresql.conf)

SHOW CONFIG_FILE;

--for example, to change property "max_locks_per_transaction" we can change the "/var/lib/edb/as11/data/postgresql.conf" file by following unix command:

sudo -u postgres vim /Users/tom/software/postgres_edb/installation/data/postgresql.conf

# sample command to find function-DDL in postgres

SELECT

    N.NSPNAME AS SCHEMA_NAME,

    P.PRONAME AS FUNCTION_NAME,

    PG_GET_FUNCTIONDEF(P.OID) AS FUNC_DEF,

    PG_GET_FUNCTION_ARGUMENTS(P.OID) AS ARGS,

    PG_GET_FUNCTION_RESULT(P.OID) AS RESULT

FROM

    PG_PROC P JOIN PG_NAMESPACE N ON N.OID = P.PRONAMESPACE

WHERE

    UPPER(P.PRONAME) = '<function_name_in_upper_case>';

# function to get table-DDL in postgres

--usage: SELECT GET_TABLE_DDL('<tablename>');

--to discard: DROP FUNCTION GET_TABLE_DDL(P_TABLE_NAME VARCHAR);

CREATE OR REPLACE FUNCTION GET_TABLE_DDL(P_TABLE_NAME VARCHAR) RETURNS TEXT AS

$BODY$

DECLARE

    V_TABLE_DDL TEXT;

    COLUMN_RECORD RECORD;

BEGIN

    FOR COLUMN_RECORD IN

        SELECT

            B.NSPNAME AS SCHEMA_NAME,

            B.RELNAME AS TABLE_NAME,

            A.ATTNAME AS COLUMN_NAME,

            PG_CATALOG.FORMAT_TYPE(A.ATTTYPID, A.ATTTYPMOD) AS COLUMN_TYPE,

            CASE

                WHEN

                    (SELECT SUBSTRING(PG_CATALOG.PG_GET_EXPR(D.ADBIN, D.ADRELID) FOR 128)

                     FROM PG_CATALOG.PG_ATTRDEF D

                     WHERE D.ADRELID = A.ATTRELID AND D.ADNUM = A.ATTNUM AND A.ATTHASDEF) IS NOT NULL THEN

                    'DEFAULT '|| (SELECT SUBSTRING(PG_CATALOG.PG_GET_EXPR(D.ADBIN, D.ADRELID) FOR 128)

                                 FROM PG_CATALOG.PG_ATTRDEF D

                                 WHERE D.ADRELID = A.ATTRELID AND D.ADNUM = A.ATTNUM AND A.ATTHASDEF)

                ELSE ''

            END AS COLUMN_DEFAULT_VALUE,

            CASE

                WHEN A.ATTNOTNULL = TRUE THEN 'NOT NULL'

                ELSE 'NULL'

            END AS COLUMN_NOT_NULL,

            A.ATTNUM AS ATTNUM,

            E.MAX_ATTNUM AS MAX_ATTNUM

        FROM

            PG_CATALOG.PG_ATTRIBUTE A

            INNER JOIN

             (SELECT C.OID,

                N.NSPNAME,

                C.RELNAME

             FROM PG_CATALOG.PG_CLASS C

                 LEFT JOIN PG_CATALOG.PG_NAMESPACE N ON N.OID = C.RELNAMESPACE

             WHERE LOWER(C.RELNAME) ~ ('^('||LOWER(P_TABLE_NAME)||')$')

                AND PG_CATALOG.PG_TABLE_IS_VISIBLE(C.OID)

             ORDER BY 2, 3) B

            ON A.ATTRELID = B.OID

            INNER JOIN

             (SELECT

                 A.ATTRELID,

                 MAX(A.ATTNUM) AS MAX_ATTNUM

             FROM PG_CATALOG.PG_ATTRIBUTE A

             WHERE A.ATTNUM > 0

                AND NOT A.ATTISDROPPED

             GROUP BY A.ATTRELID) E

            ON A.ATTRELID=E.ATTRELID

        WHERE A.ATTNUM > 0

         AND NOT A.ATTISDROPPED

        ORDER BY A.ATTNUM

    LOOP

        IF COLUMN_RECORD.ATTNUM = 1 THEN

            V_TABLE_DDL:='CREATE TABLE '||COLUMN_RECORD.SCHEMA_NAME||'.'||COLUMN_RECORD.TABLE_NAME||' (';

        ELSE

            V_TABLE_DDL:=V_TABLE_DDL||',';

        END IF;

        IF COLUMN_RECORD.ATTNUM <= COLUMN_RECORD.MAX_ATTNUM THEN

            V_TABLE_DDL:=V_TABLE_DDL||CHR(10)||

                     '    '||COLUMN_RECORD.COLUMN_NAME||' '||COLUMN_RECORD.COLUMN_TYPE||' '||COLUMN_RECORD.COLUMN_DEFAULT_VALUE||' '||COLUMN_RECORD.COLUMN_NOT_NULL;

        END IF;

    END LOOP;

    V_TABLE_DDL:=V_TABLE_DDL||');';

    RETURN V_TABLE_DDL;

END;

$BODY$

  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

# query to get table-indexes in postgres

SELECT

    T.RELNAME AS TABLE_NAME,

    I.RELNAME AS INDEX_NAME,

    A.ATTNAME AS COLUMN_NAME

FROM

    PG_CLASS T,

    PG_CLASS I,

    PG_INDEX IX,

    PG_ATTRIBUTE A

WHERE     T.OID = IX.INDRELID

    AND I.OID = IX.INDEXRELID

    AND A.ATTRELID = T.OID

    AND A.ATTNUM = ANY(IX.INDKEY)

    AND UPPER(T.RELKIND) = 'R'

    AND UPPER(T.RELNAME) = '<table_name_in_upper_case>'

ORDER BY

    T.RELNAME,

    I.RELNAME;

# Normal forms:

--1NF, 2NF, 3NF, 4NF, 5NF: https://www.youtube.com/watch?v=GFQaEYEc8_8

--BCNF: https://www.youtube.com/watch?v=VWnKUKH4tLg