• More Oracle Hints and Tips
  • More useful tables listed as follows:
  • Character sets
• • Add a column to an existing table
  • Listing Packages
  • List lines from source package
  • SQL Tracing
  • Killing a session
  • Metadata
    • Table definition
    • Show primary keys for table
    • Show foreign keys for table
    • Show all indexes for table
  • Constraints
    • Show a table's constraints
    • Shows which table a constraint belongs to:
    • Dates
  • Sequences
    • Create Sequence
    • Applying Sequence Number to Result Set
  • Optional Query Parameters

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 TABLENAME = '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.CONSTRAINTNAME = aid.INDEXNAME AND=
=con.TABLENAME = aid.TABLENAME AND=
=con.CONSTRAINTTYPE = 'P' AND=
con.TABLENAME'MYTABLE'=
/

Show foreign keys for table

SELECT u1.CONSTRAINTNAME, col.POSITION, col.COLUMNNAME, col.TABLENAME, u1.STATUS
FROM USERCONSTRAINTS u1, USERCONSCOLUMNS col
=WHERE u1.RCONSTRAINTNAME = col.CONSTRAINTNAME AND=
=u1.CONSTRAINTTYPE = 'R' AND=
=u1.TABLENAME = 'MYTABLE'=
/

Show all indexes for table

SELECT INDEXNAME, TABLENAME, INDEXTYPE, TABLETYPE, UNIQUENESS, STATUS
FROM USERINDEXES
=WHERE TABLEOWNER = 'myusername' AND=
=TABLENAME = 'mytablename'=
ORDER BY INDEXNAME
/

---

Constraints

Show a table's constraints

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

Shows which table a constraint belongs to:

SELECT TABLENAME
FROM USERCONSTRAINTS
=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

• Viki
• Business
• Tech