Schema Object Names and Qualifiers - Oracle Help Center

Rules for naming schema objects and schema object location qualifiers ... Each period separator, as well as any surrounding double quotation marks, ... Gotomaincontent 19/479 SchemaObjectNamesandQualifiers Someschemaobjectsaremadeupofpartsthatyoucanormustname,suchasthecolumnsinatableorview,indexandtablepartitionsandsubpartitions,integrityconstraintsonatable,andobjectsthatarestoredwithinapackage,includingproceduresandstoredfunctions.Thissectionprovides: Rulesfornamingschemaobjectsandschemaobjectlocationqualifiers Guidelinesfornamingschemaobjectsandqualifiers SchemaObjectNamingRules Everydatabaseobjecthasaname.InaSQLstatement,yourepresentthenameofanobjectwithaquotedidentifieroranonquotedidentifier. Aquotedidentifierbeginsandendswithdoublequotationmarks(").Ifyounameaschemaobjectusingaquotedidentifier,thenyoumustusethedoublequotationmarkswheneveryourefertothatobject. Anonquotedidentifierisnotsurroundedbyanypunctuation. Youcanuseeitherquotedornonquotedidentifierstonameanydatabaseobject.However,databasenames,globaldatabasenames,anddatabaselinknamesarealwayscaseinsensitiveandarestoredasuppercase.Ifyouspecifysuchnamesasquotedidentifiers,thenthequotationmarksaresilentlyignored.PleaserefertoCREATEUSERforadditionalrulesfornamingusersandpasswords. Thefollowinglistofrulesappliestobothquotedandnonquotedidentifiersunlessotherwiseindicated: Namesmustbefrom1to30byteslongwiththeseexceptions: Namesofdatabasesarelimitedto8bytes. Namesofdatabaselinkscanbeaslongas128bytes. Ifanidentifierincludesmultiplepartsseparatedbyperiods,theneachattributecanbeupto30byteslong.Eachperiodseparator,aswellasanysurroundingdoublequotationmarks,countsasonebyte.Forexample,supposeyouidentifyacolumnlikethis: "schema"."table"."column" Theschemanamecanbe30bytes,thetablenamecanby30bytes,andthecolumnnamecanbe30bytes.Eachofthequotationmarksandperiodsisasingle-bytecharacter,sothetotallengthoftheidentifierinthisexamplecanbeupto98bytes. NonquotedidentifierscannotbeOracleDatabasereservedwords.Quotedidentifierscanbereservedwords,althoughthisisnotrecommended. DependingontheOracleproductyouplantousetoaccessadatabaseobject,namesmightbefurtherrestrictedbyotherproduct-specificreservedwords. Note: ThereservedwordROWIDisanexceptiontothisrule.YoucannotusetheuppercasewordROWID,eitherquotedornonquoted,asacolumnname.However,youcanusetheuppercasewordasaquotedidentifierthatisnotacolumnname,andyoucanusethewordwithoneormorelowercaseletters(forexample,"Rowid"or"rowid")asanyquotedidentifier,includingacolumnname. SeeAlso: AppendixD,"OracleDatabaseReservedWords"foralistingofallOracleDatabasereservedwords Themanualforaspecificproduct,suchasPL/SQLUser'sGuideandReference,foralistofthereservedwordsofthatproduct TheOracleSQLlanguagecontainsotherwordsthathavespecialmeanings.Thesewordsincludedatatypes,schemanames,functionnames,thedummysystemtableDUAL,andkeywords(theuppercasewordsinSQLstatements,suchasDIMENSION,SEGMENT,ALLOCATE,DISABLE,andsoforth).Thesewordsarenotreserved.However,Oracleusestheminternallyinspecificways.Therefore,ifyouusethesewordsasnamesforobjectsandobjectparts,thenyourSQLstatementsmaybemoredifficulttoreadandmayleadtounpredictableresults. Inparticular,donotusewordsbeginningwithSYS_asschemaobjectnames,anddonotusethenamesofSQLbuilt-infunctionsforthenamesofschemaobjectsoruser-definedfunctions. SeeAlso: "Datatypes","SQLFunctions",and"SelectingfromtheDUALTable" YoushoulduseASCIIcharactersindatabasenames,globaldatabasenames,anddatabaselinknames,becauseASCIIcharactersprovideoptimalcompatibilityacrossdifferentplatformsandoperatingsystems. Note: OraclerecommendsthatusernamesandpasswordsbeencodedinASCIIorEBCDICcharactersonly,dependingonyourplatform.PleaserefertoOracleDatabaseAdministrator'sGuideformoreinformationaboutthisrecommendation. Nonquotedidentifiersmustbeginwithanalphabeticcharacterfromyourdatabasecharacterset.Quotedidentifierscanbeginwithanycharacter. Nonquotedidentifierscancontainonlyalphanumericcharactersfromyourdatabasecharactersetandtheunderscore(_),dollarsign($),andpoundsign(#).Databaselinkscanalsocontainperiods(.)and"at"signs(@).Oraclestronglydiscouragesyoufromusing$and#innonquotedidentifiers. Quotedidentifierscancontainanycharactersandpunctuationsmarksaswellasspaces.However,neitherquotednornonquotedidentifierscancontaindoublequotationmarksorthenullcharacter(\0). Withinanamespace,notwoobjectscanhavethesamename. Thefollowingschemaobjectsshareonenamespace: Tables Views Sequences Privatesynonyms Stand-aloneprocedures Stand-alonestoredfunctions Packages Materializedviews User-definedtypes Eachofthefollowingschemaobjectshasitsownnamespace: Indexes Constraints Clusters Databasetriggers Privatedatabaselinks Dimensions Becausetablesandviewsareinthesamenamespace,atableandaviewinthesameschemacannothavethesamename.However,tablesandindexesareindifferentnamespaces.Therefore,atableandanindexinthesameschemacanhavethesamename. Eachschemainthedatabasehasitsownnamespacesfortheobjectsitcontains.Thismeans,forexample,thattwotablesindifferentschemasareindifferentnamespacesandcanhavethesamename. Eachofthefollowingnonschemaobjectsalsohasitsownnamespace: Userroles Publicsynonyms Publicdatabaselinks Tablespaces Profiles Parameterfiles(PFILEs)andserverparameterfiles(SPFILEs) Becausetheobjectsinthesenamespacesarenotcontainedinschemas,thesenamespacesspantheentiredatabase. Nonquotedidentifiersarenotcasesensitive.Oracleinterpretsthemasuppercase.Quotedidentifiersarecasesensitive. Byenclosingnamesindoublequotationmarks,youcangivethefollowingnamestodifferentobjectsinthesamenamespace: employees "employees" "Employees" "EMPLOYEES" NotethatOracleinterpretsthefollowingnamesthesame,sotheycannotbeusedfordifferentobjectsinthesamenamespace: employees EMPLOYEES "EMPLOYEES" Columnsinthesametableorviewcannothavethesamename.However,columnsindifferenttablesorviewscanhavethesamename. Proceduresorfunctionscontainedinthesamepackagecanhavethesamename,iftheirargumentsarenotofthesamenumberanddatatypes.Creatingmultipleproceduresorfunctionswiththesamenameinthesamepackagewithdifferentargumentsiscalledoverloadingtheprocedureorfunction. SchemaObjectNamingExamples Thefollowingexamplesarevalidschemaobjectnames: last_name horse hr.hire_date "EVENTHIS&THAT!" a_very_long_and_valid_name Alloftheseexamplesadheretotheruleslistedin"SchemaObjectNamingRules".Thefollowingexampleisnotvalid,becauseitexceeds30characters: a_very_very_long_and_valid_name Althoughcolumnaliases,tablealiases,usernames,andpasswordsarenotobjectsorpartsofobjects,theymustalsofollowthesenamingrulesunlessotherwisespecifiedintherulesthemselves. SchemaObjectNamingGuidelines Hereareseveralhelpfulguidelinesfornamingobjectsandtheirparts: Usefull,descriptive,pronounceablenames(orwell-knownabbreviations). Useconsistentnamingrules. Usethesamenametodescribethesameentityorattributeacrosstables. Whennamingobjects,balancetheobjectiveofkeepingnamesshortandeasytousewiththeobjectiveofmakingnamesasdescriptiveaspossible.Whenindoubt,choosethemoredescriptivename,becausetheobjectsinthedatabasemaybeusedbymanypeopleoveraperiodoftime.Yourcounterparttenyearsfromnowmayhavedifficultyunderstandingatablecolumnwithanamelikepmddinsteadofpayment_due_date. Usingconsistentnamingruleshelpsusersunderstandthepartthateachtableplaysinyourapplication.OnesuchrulemightbetobeginthenamesofalltablesbelongingtotheFINANCEapplicationwithfin_. Usethesamenamestodescribethesamethingsacrosstables.Forexample,thedepartmentnumbercolumnsofthesampleemployeesanddepartmentstablesarebothnameddepartment_id.