Category: Knowledge Base

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...

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...

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...

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  ...

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...