fishScript.com Script con annotazioni in italiano e inglese

Oracle

Home >Oracle > Quick guide on oracle database objects and namespaces naming rules and guidelines

It is of the utmost importance to follow some naming standards and guidelines in the process of designing Oracle database. Frequently a database with poorly naming standards unveils bad design, planning and organization.
Good naming policy reflects strong design and helps programmer to work faster and smoother.
It is even more important when the database containing hundreds of tables and thousands of other objects. The project leader should always adopt a naming convention, write it down and enforce it.
Naming characters length rules. One byte is usually means one character.
Database name max length is 8 bytes, database link up to 128 bytes, all other database objects (schema/user, table, column, view, procedure, etc.) up to 30 chars and no more, try to keep anyway schema names shorter as much as you can.
When trying to create an object with a name longer then allowed, Oracle returns:

ORA-00972: identifier is too long

Object	Max Length bytes
Database (SID)	8
database links	128
All other oracle database objects; Schema/user Table, Column, Index, View , Trigger , Procedure, etc.	30

Only letters, numbers, and the underscore are allowed in names. Although Oracle allows special chars such as $, #, % they are not necessary and may cause unexpected problems. If an object identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long:

"schema"."table"."column"

If Objects share a namespace they cannot have the same name. The following schema objects share one namespace:

Tables
Views
Sequences
Private synonyms
Stand-alone procedures
Stand-alone stored functions
Packages
Materialized views (snapshots)
User-defined types

Within the same schema there cannot exist two objects of the same namespace with same name.

SQL> create view  t1 as select * from all_objects;

View created.

SQL> create table t1 (n number);
create table t1 (n number)
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Each of the following schema objects has its own namespace:

Clusters
Constraints
Db links
Dimensions
Indexes
Triggers

So these objects can be named as objects in different namespace. Thought it is not recommended, in the same schema you may have table and index with same name:

SQL> create table test.x1 (n number);
Table created.
SQL> create index test.x1 on x1(n);
Index created.

Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

    -- in the example customer and catalog are schema names

    create table customer.t1 (n number);
    
     create table catalog.t1 (n number);

The following non schema objects also have their own individual namespace:

Parameter files (PFILEs) and server parameter files (SPFILEs)
Profiles
Public synonyms
Public database links
User roles
Tablespaces

Naming conventions

Generally, db objects naming policy should refer to common programming principles, objects and variables naming rules.
In Oracle usually, object name cannot begin with numbers, with special chars:


CREATE TABLE 1t (n number)
              *
ERROR at line 1:
ORA-00903: invalid table name

By default object name is case insensitive and oracle store names in its catalogue all in upper case

SQL> select object_name from all_objects order by 1;

OBJECT_NAME
------------------------------
BOOTSTRAP$
CON$
C_TS#
IND$
I_COL2
I_CON2
 (...)

By standards, when an object is created it is stored as uppercase. So it is very convenient retrieve in the catalogue views info about the object without worrying about upper or lower case.

SQL> create table My_numbers  (n number);

select table_name  from user_tables where table_name = 'MY_NUMBERS';

Table created.

SQL> 
TABLE_NAME
------------------------------
MY_NUMBERS

Forcing naming standard (not recommended)

It is possible forcing default naming rules using double quotes (") around the object name.
Double quotes allow you to specify upper and lower case, and having object name beginning with number or special char.

SQL> Drop table MY_NUMBERS;

SQL> create table "My_Numbers"  (n number);

SQL> select table_name  from user_tables where table_name = 'My_Numbers'; 

TABLE_NAME
------------------------------
My_Numbers


SQL> Create table "%t" (n number);

Table created.

SQL> Create table “$t" (n number);

Table created.

SQL> create table "1t" (n number);
Table created.

Note that If you use doublequotes (") and user chars lower case to create an object, when searching its info in the dictionary it is necessary referring to the object exactly by its name (case sensitive) :

  	-- the praeviously created table My_Numbers must be referred case sensitive 
 		--  otherwise not results  from dictionary’s queries 

SQL> select table_name  from user_tables where table_name = 'MY_NUMBERS';
no rows selected

Also objects created with " if mentioned in ddl or dml statements, double quotes are mandatory:

 ---  or if you like to drop it
SQL> Drop table MY_NUMBERS;
Drop table MY_NUMBERS
           *
ERROR at line 1:
ORA-00942: table or view does not exist

-- to drop it use 
drop table "My_Numbers";
SQL> insert into "My_Numbers"  values (2);

1 row created.

Suggestion: keep everything in the old standard way, do not use " when create an objects, search and management it easy and clean, unless you have real reasons to differentiate within lower and upper case. Try to keep object name short (around 20 chars) while meaningfully. Rarely there is the need to name and referring objects with lower and uppers or with special chars.
To differentiate class of objects in the same namespace us, for examples, prefixes: US_LOG, AP_LOG for the user and application log tables. Most of it, as always, keep it simple.

Warning: include(ads/text468x15.html): failed to open stream: No such file or directory in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Warning: include(): Failed opening 'ads/text468x15.html' for inclusion (include_path='.;C:\php\pear') in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Tutorial

Flashback database to timestamp ripristinare il database ad una data determinata [Oracle]

Auditing for wrong sql How to intercept wrong dml sql statement [Oracle]

Using data pump in interactive mode [Oracle]

Alter session set current_schema How to query other user tables without specify the schema name [Oracle]

Procedura conteggio righe tutte le tabelle di uno schema esempio utilizzo NDS [PLSQL]

Inserire righe in tabelle utilizzando %rowtype scrivere codice pl/sql compatto [PLSQL]

Generazione di numeri casuali dbms_random.value [PLSQL]

Eseguire comando truncate di tutte le tabelle di uno schema atraverso dizionario dati e istruzione sql dinamica [PLSQL]

Dropping a database [Oracle]

Create new user basic sql statements [Oracle]

Viste note e appunti [Oracle]

NOT EXISTS clause basic example [Oracle]

Creazione di un database Oracle 10g da linea di comando esempio su sistemi Windows Server / XP: comandi e script [Oracle]

Clean up audit tables remove auditing logs [Oracle]

Nomenclatura oracle tipologie oggetti catalogo [Oracle]
Script

dbms_output.put_line Stampare spazi bianchi [Oracle]

Opzione DEFAULT per il valore di una colonna [Oracle]

Cercare le colonne in uno schema utilizzare le tabelle del dizionario dati di Oracle [Oracle]

Esempio script dos per eseguire export di schema automatizzare procedure di export e rinominare il file in base alla data corrente [Oracle]

Dato il nome di una vista ricava informazioni su tipo e attributi della colonna di riferimenti tabelle dizionario dati [Oracle]

Redo log ricollocare/trasferire i file di Redo [Oracle]

Scrivere su un file esempio con UTL_FILE package [PLSQL]

Leggere un file di testo [PLSQL]

Esempio funzione MOD in un loop ... [PLSQL]

Record sintassi di base: definizione, dichiarazione, assegnazione e stampa [PLSQL]

Errori intercettare codice e descrizione errori sql [PLSQL]

Esempio inserimento righe all'interno di un loop [PLSQL]

Cursore implicito SQL esempio con SQL%ROWCOUNT [PLSQL]

Esempio tipo VARRAY gestione array [PLSQL]

TYPE TABLE utilizzare le collections [PLSQL]
Comandi

Modificare il formato di default [Oracle]

systimestamp [Oracle]

CURRENT_TIMESTAMP [Oracle]

Spostare un indice in un diverso tablespace [Oracle]

analyze [Oracle]

Costruire istruzioni sql dinamiche il carattere speciale & [Oracle]

Exp utility esempio export di singole tabelle [Oracle]

Group by mese di colonna di tipo data [Oracle]

ALTER SEQUENCE Modificare una sequenza [Oracle]

Spazio libero nei tablespace utilizzo tabella DBA_FREE_SPACE [Oracle]

NLS_DATABASE_PARAMETERS ricavare i parametri del database [Oracle]

Lista control files [Oracle]

Spostare una tabella in un diverso tablespace [Oracle]

Creazione di una directory in ambiente oracle [Oracle]

Funzione TRUNC esempio visualizzazione primo giorno del mese corrente [Oracle]
Codici errore

Errore connessione client ORA-12638 Credential retreival failed [Oracle]

impdp / expdp importare in sql metadata del database datapump per esportare ed importare metatadati [Oracle]

ORA-06553: PLS-213,ORA-06553: PLS-213,ORA-06553: PLS-213 Errori in fase di creazione database, occorre eseguire gli script di catalog [ERRORI]

Operazioni sulle date utilizzando il nome dei mesi NLS_DATE_LANGUAGE ORA-01843: mese non valido / not a valid month [Oracle]

Argomenti

Oracle

Naming conventions

Forcing naming standard (not recommended)

Cerca