Oracle Docs Your Oracle Document Library

unnamed 0

Oracle Critical Patch Update October 2012

00Oracle Critical Patch Update October 2012 The Critical Patch Update for October 2012 and The Oracle Java SE Critical Patch Update for October 2012 were released on October 16th, 2012. Oracle strongly recommends applying...

unnamed 0

Useful SQL Statements Part 1

00Startup time select to_char(startup_time, ‘HH24:MI DD-MON-YY’) “Startup time” from v$instance / How large is the database col “Database Size” format a20 col “Free space” format a20 col “Used space” format a20 select round(sum(used.bytes) /...

unnamed 0

Oracle Critical Patch Update January 2012

00Description A Critical Patch Update is a collection of patches for multiple security vulnerabilities. Critical Patch Update patches are usually cumulative but each advisory describes only the security fixes added since the previous Critical...

unnamed 0

Recompiling Invalid Schema Objects

00Recompiling Invalid Schema Objects Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don’t cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this...

unnamed 0

Oracle Naming Conventions

00Oracle Naming Conventions Nothing is carved in stone, but these are the sort of rules I follow: Entities & Tables Aliasing Attributes & Columns Keys & Their Columns Indexes Triggers Other Objects PL/SQL Variables...

unnamed 0

LAG and LEAD Analytic Functions

00LAG and LEAD Analytic Functions The LAG and LEAD analytic functions were introduced in 8.1.6 to give access to multiple rows within a table, without the need for a self-join. Introduction LAG LEAD  ...

unnamed 0

Handy SQL for the Oracle Applications

001. Database 1.1 General Objects / Tables / Columns PROMPT Find Object by Type, Name, Status select object_name, object_type, owner, status from dba_objects where upper(object_name) like upper(‘%&object_name%’) and object_type like upper(‘%’|| NVL(‘&object_type’, ‘TABLE’)|| ‘%’) and status like upper(‘%’|| NVL(‘&Status’, ‘%’)|| ‘%’) order by object_name, object_type; PROMPT Find table indexes, keys, columns select index_name,column_position,column_name from all_ind_columns where table_name = ‘&EnterTableName’ order by index_name, column_position; PROMPT Find tables and columns that include a table and/or column name specified. select table_name, column_name from  dba_tab_columns where column_name like NVL(UPPER(‘&COLUMN_NAME’), column_name) and table_name like NVL(UPPER(‘&TABLE_NAME’), table_name);   1.2 Invalids / Compiling PROMPT Find Invalids select object_name, object_type, owner, status from dba_objects...