More Oracle Hints and Tips

• ALL_CONSTRAINTS
• ALL_CONS_COLUMNS
• ALL_DEPENDENCIES
• ALL_IND_COLUMNS
• ALL_INDEXES
• USER_CONSTRAINTS
• USER_CONS_COLUMNS
• USER_DEPENDENCIES
• USER_INDEXES
• USER_IND_COLUMNS
• 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 CONSTRAINT_NAME, SEARCH_CONDITION, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = ‘USER_TYPE’

ALTER TABLE USER_TYPE
DROP PRIMARY KEY CASCADE

select name, pipe_size from v$db_pipes order by name;

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

select username, machine, program from vsession/[v_session]
where username IS NOT NULL
order by username, machine

---

Character sets

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

SELECT * FROM NLS_DATABASE_PARAMETERS;

• 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 dbms_pipe to [owner_account];

Add a column to an existing table

ALTER TABLE MY_TABLE ADD (MY_NEW_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.dbms_system.set_sql_trace_in_session(<sid>, <serial#>, true)

Stop tracing:

• exec sys.dbms_system.set_sql_trace_in_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:

• \\db_svr_name\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.COLUMN_POSITION, aid.COLUMN_NAME
FROM USER_IND_COLUMNS aid, USER_CONSTRAINTS con
=WHERE con.CONSTRAINT_NAME = aid.INDEX_NAME AND=
=con.TABLE_NAME = aid.TABLE_NAME AND=
=con.CONSTRAINT_TYPE = ‘P’ AND=
con.TABLE_NAME‘MY_TABLE’=
/

Show foreign keys for table

SELECT u1.CONSTRAINT_NAME, col.POSITION, col.COLUMN_NAME, col.TABLE_NAME, u1.STATUS
FROM USER_CONSTRAINTS u1, USER_CONS_COLUMNS col
=WHERE u1.R_CONSTRAINT_NAME = col.CONSTRAINT_NAME AND=
=u1.CONSTRAINT_TYPE = ‘R’ AND=
=u1.TABLE_NAME = ‘MY_TABLE’=
/

Show all indexes for table

SELECT INDEX_NAME, TABLE_NAME, INDEX_TYPE, TABLE_TYPE, UNIQUENESS, STATUS
FROM USER_INDEXES
=WHERE TABLE_OWNER = ‘my_user_name’ AND=
=TABLE_NAME = ‘my_table_name’=
ORDER BY INDEX_NAME
/

---

Constraints

Show a table’s constraints

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE OWNER‘MY_USERNAME’ AND=
=TABLE_NAME = ‘MY_TABLE’=

Shows which table a constraint belongs to:

SELECT TABLE_NAME
FROM USER_CONSTRAINTS
=WHERE CONSTRAINT_NAME = ‘MY_PK’=
/

Dates

Current system date

• SELECT SYSDATE FROM DUAL

Format date

• SELECT TO_DATE(‘2002-06-19’, ‘YYYY-MM-DD’) FROM DUAL
• SELECT TO_CHAR(TO_DATE(‘2002-06-14’, ‘YYYY-MM-DD’), ‘DD-MON-YYYY’) FROM DUAL

Date arithmetic

• SELECT TO_DATE(‘04/06/2002’, ‘DD/MM/YYYY’) - TO_DATE(‘29/05/2002’, ‘DD/MM/YYYY’) DAYS_BETWEEN FROM DUAL

• SELECT ROUND(SYSDATE, ‘MONTH’) FIRST_OF_MONTH, LAST_DAY(SYSDATE) END_OF_MONTH FROM DUAL

• SELECT ROUND(SYSDATE, ‘YEAR’) NEW_YEAR_DAY, ADD_MONTHS(ROUND(SYSDATE, ‘YEAR’), 12)-1 NEW_YEARS_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

• Business
• Tech
• Viki