More Oracle Hints and Tips

  • ALL_CONSTRAINTS
  • ALLCONSCOLUMNS
  • ALL_DEPENDENCIES
  • ALLINDCOLUMNS
  • ALL_INDEXES
  • USER_CONSTRAINTS
  • USERCONSCOLUMNS
  • USER_DEPENDENCIES
  • USER_INDEXES
  • USERINDCOLUMNS
  • USER_SEQUENCES

More useful tables listed as follows:

<code>
select VIEW_NAME from all_views
where view_name like 'USER_%'
ORDER BY VIEW_NAME
/
</code>

SELECT CONSTRAINTNAME, SEARCHCONDITION, RCONSTRAINTNAME
FROM USERCONSTRAINTS
WHERE TABLE
NAME = 'USER_TYPE'

ALTER TABLE USER_TYPE
DROP PRIMARY KEY CASCADE

select name, pipesize from v$dbpipes order by name;

ALTER TABLE [] DISABLE ALL TRIGGERS (only Oracle 8?)

select username, machine, program from v$session/[v$_session]
where username IS NOT NULL
order by username, machine


Character sets

Show all NLS setttings (currency, time & date formats, language)

SELECT * FROM NLSDATABASEPARAMETERS;

  • UTF8
  • WE8ISO8859P1 (Western European)

List invalid packages

  • select * from all_objects where status='INVALID' and owner='MyUser'

I think this is needed to write to dbms_pipe

  • grant execute on dbmspipe to [owneraccount];

Add a column to an existing table

ALTER TABLE MYTABLE ADD (MYNEW_COLUMN VARCHAR2(2));

Listing Packages

    SELECT DISTINCT OBJECT_TYPE FROM ALL_OBJECTS;
    DESC ALL_OBJECTS;

-- Frank Dean - 29 Sep 2012

List lines from source package

Display errors for package compilation:

SHOW ERRORS;

List source code for the lines referred to by the 'SHOW ERRORS' output:

<code>SELECT TEXT FROM ALL_SOURCE
    WHERE NAME = 'MY_PKG' AND
    TYPE='PACKAGE BODY' AND
    OWNER = 'MY_OWNER'
    ORDER BY LINE;
</code>

Replace 'PACKAGE BODY' with 'PACKAGE' to retrieve the header.

SQL Tracing

The following is from some old notes. I'm not too sure what else needs to be set up by a friendly dba to enable sql tracing, but the following is certainly part of the process.

Get the sid and serial# for the connection we're going to trace:

  • select sid, serial#, osuser, program from v$session;

Start tracing:

  • exec sys.dbmssystem.setsqltracein_session(<sid>, <serial#>, true)

Stop tracing:

  • exec sys.dbmssystem.setsqltracein_session(<sid>, <serial#>, false)

Can't remember what my old notes meant for the following command, but I think it should be run in a command shell:

  • \\dbsvrname\udump > tkprof ora00304.trc username.lis sys=nox

Killing a session

Find the sid and serial number of the session (may need to connect to SYSTEM)

  • select sid, username, osuser, terminal, program from V$session

Then kill the session

  • alter system kill session '<sid>,<serial#>';

Metadata

Table definition

<code>
COLUMN DATA_TYPE FORMAT A15
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME='my_table'
ORDER BY COLUMN_NAME
</code>

Show primary keys for table

SELECT aid.COLUMNPOSITION, aid.COLUMNNAME
FROM USERINDCOLUMNS aid, USERCONSTRAINTS con
=WHERE con.CONSTRAINT
NAME = aid.INDEXNAME AND=
=con.TABLE
NAME = aid.TABLENAME AND=
=con.CONSTRAINT
TYPE = 'P' AND=
con.TABLENAME'MYTABLE'=
/

Show foreign keys for table

SELECT u1.CONSTRAINTNAME, col.POSITION, col.COLUMNNAME, col.TABLENAME, u1.STATUS
FROM USER
CONSTRAINTS u1, USERCONSCOLUMNS col
=WHERE u1.RCONSTRAINTNAME = col.CONSTRAINTNAME AND=
=u1.CONSTRAINT
TYPE = 'R' AND=
=u1.TABLENAME = 'MYTABLE'=
/

Show all indexes for table

SELECT INDEXNAME, TABLENAME, INDEXTYPE, TABLETYPE, UNIQUENESS, STATUS
FROM USERINDEXES
=WHERE TABLE
OWNER = 'myusername' AND=
=TABLENAME = 'mytablename'=
ORDER BY INDEX
NAME
/


Constraints

Show a table's constraints

SELECT CONSTRAINTNAME, CONSTRAINTTYPE, STATUS, SEARCHCONDITION, ROWNER, RCONSTRAINTNAME
FROM USERCONSTRAINTS
WHERE OWNER'MY
USERNAME' AND=
=TABLENAME = 'MYTABLE'=

Shows which table a constraint belongs to:

SELECT TABLENAME
FROM USER
CONSTRAINTS
=WHERE CONSTRAINTNAME = 'MYPK'=
/

Dates

Current system date

  • SELECT SYSDATE FROM DUAL

Format date

  • SELECT TO_DATE('2002-06-19', 'YYYY-MM-DD') FROM DUAL
  • SELECT TOCHAR(TODATE('2002-06-14', 'YYYY-MM-DD'), 'DD-MON-YYYY') FROM DUAL

Date arithmetic

  • SELECT TODATE('04/06/2002', 'DD/MM/YYYY') - TODATE('29/05/2002', 'DD/MM/YYYY') DAYS_BETWEEN FROM DUAL

  • SELECT ROUND(SYSDATE, 'MONTH') FIRSTOFMONTH, LASTDAY(SYSDATE) ENDOF_MONTH FROM DUAL

  • SELECT ROUND(SYSDATE, 'YEAR') NEWYEARDAY, ADDMONTHS(ROUND(SYSDATE, 'YEAR'), 12)-1 NEWYEARS_EVE FROM DUAL


Sequences

Create Sequence

  CREATE SEQUENCE MY_SEQ

or 

  CREATE SEQUENCE MY_SEQ
  INCREMENT BY 1 START WITH 1
  NOMAXVALUE NOMINVALUE
  NOCYCLE NOORDER

Applying Sequence Number to Result Set

  select my_seq.nextval, my_column
  from dual, (
     select my_column
     from my_table
     order by my_column desc
  )

Optional Query Parameters

http://www.oracledba.co.uk/tips/nvl_smarts.htm

-- Frank Dean - 09 Dec 2013


-- Frank Dean - 17 Dec 2002

Related Topics: OracleSqlPlus, OracleHintsAndTips