Oracle SQL Tips

1.      Using SQL dummy table

This query will subtract first number from second. Dual table is used as dummy table to complete the sql statement, do nothing though.

select 36649-36598 from dual

2.      Audit details

get audit details

select * from DBA_AUDIT_TRAIL WHERE OBJ_NAME='course_material'

get audit count

SELECT * FROM all_tab_modifications  where TABLE_NAME = 'COURSE_MATERIAL'

3.      Constraint details

get constraint details

select * from ALL_CONSTRAINTS where CONSTRAINT_NAME = 'SYS_C0012069';

get column name of constratint

SELECT * FROM all_cons_columns where CONSTRAINT_NAME = 'SYS_C0012069';

see what constraints are referencing a table

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

disable constraints

alter table "COURSE" DISABLE constraint "FK_PARENT_ID";

get referecing table names

 select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name

  from all_constraints

 where constraint_type='R'

 AND OWNER ='CTGPORTAL_CERT'

   and r_constraint_name in (select constraint_name

                               from all_constraints

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

                                and table_name='COURSE');         

          

delete with foreign key constraints

delete from mytable     

4.      Accessible Tables

get the list of oracle tables can access

select * from all_tables order by tablespace_name, table_name

5.      Get active users in the db

SELECT * FROM v$session

WHERE username IS NOT NULL and status = 'ACTIVE'

ORDER BY username ASC; 

6.      Get triggers linked to a table

SELECT * FROM USER_TRIGGERS WHERE TABLE_NAME = 'COURSE_MEMBERSHIP';

7.      Rename column name

alter table a rename column id to new_id;

8.      Create new schema user

create user ctgportal identified by ctgportal ;

grant connect, unlimited tablespace, imp_full_database to ctgportal;

set dba role to new user to have full db access

9.      Get current schema

 select sys_context('userenv','current_schema') x from dual

10. Read via a datalink

Read from SMS database course table

    SELECT * From COURSE@smsportal    

11. Get first 10 rows

select * from COURSE where rownum <10

12. Get child tables

Select a.table_name, b.table_name, a.CONSTRAINT_NAME, b.CONSTRAINT_NAME

from sys.all_constraints a

inner join sys.all_constraints b on a.constraint_name=b.r_constraint_name

where  a.owner = 'CTGPORTAL'

and b.r_owner = 'CTGPORTAL'

and a.table_name = 'COURSE'

13. Common sql

Records created for last 180 minutes

select Entity_id  from TRANSACTION_TRACKER where date_created >  sysdate - interval '180' minute;

14. Creating a sequeance

DDL for Sequence CatalogChangeLogID_SEQ

   CREATE SEQUENCE  "CatalogChangeLogID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

15. Joins

Get records not in table 2

SELECT  *

FROM    common

LEFT JOIN

        table1 t1

ON      t1.common_id = common.common_id

WHERE   t1.common_id IS NULL

    LEFT JOIN / IS NULL:

    SELECT  *

    FROM    common

    LEFT JOIN

            table1 t1

    ON      t1.common_id = common.common_id

    WHERE   t1.common_id IS NULL

    NOT EXISTS:

    SELECT  *

    FROM    common

    WHERE   NOT EXISTS

            (

            SELECT  NULL

            FROM    table1 t1

            WHERE   t1.common_id = common.common_id

            )

    NOT IN:

    SELECT  *

    FROM    common

    WHERE   common_id NOT IN

            (

            SELECT  common_id

            FROM    table1 t1

            )

16. creating a global temp table

CREATE GLOBAL TEMPORARY TABLE Table_name3

    (namea varCHAR(20),

     address varCHAR(20),

     phone varCHAR(20))

  ON COMMIT PRESERVE ROWS;

17. SQL Plus #connecting to a remote server

  

 sqlplus "ctgportal_cert/ctgportal_cert7@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.52.215.11)(Port=1521))(CONNECT_DATA=(SID=vuptdev)))"

     18.Alter Session

alter session set current_schema = ctgportal ;