PART II in Software

Printer Code-39 in Software PART II

A schema object is owned by a user and must conform to certain rules: The name may be between 1 to 30 characters long (with the exception of database link names that may be up to 128 characters long) Reserved words (such as SELECT) cannot be used as object names All names must begin with a letter of the alphabet Object names can only include letters, numbers, the underscore (_), the dollar sign ($), or the hash symbol (#) Lowercase letters will be automatically converted to uppercase By enclosing the name within double quotes, all these rules (with the exception of the length) can be broken, but to get to the object subsequently, it must always be specified with double quotes, as in the examples in Figure 7-1 Note that the same restrictions also apply to column names EXAM TIP Object names must be no more than 30 characters The characters can be letters, digits, underscore, dollar, or hash

Figure 7-1

Although tools such as SQL*Plus and SQL Developer will automatically convert lowercase letters to uppercase unless the name is enclosed within double quotes, remember that object names are always case sensitive In this example, the two tables are completely different:

SQL> create table lower(c1 date); Table created SQL> create table "lower"(col1 varchar2(2)); Table created SQL> select table_name from dba_tables where lower(table_name) = 'lower'; TABLE_NAME -----------------------------lower LOWER

TIP While it is possible to use lowercase names and nonstandard characters (even spaces), it is considered bad practice because of the confusion it can cause

It is often said that the unique identifier for an object is the object name, prefixed with the schema name While this is generally true, for a full understanding of naming it is necessary to introduce the concept of a namespace A namespace defines a group of object types, within which all names must be uniquely identified by schema and name Objects in different namespaces can share the same name These object types all share the same namespace:

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

Thus it is impossible to create a view with the same name as a table at least, it is impossible if they are in the same schema And once created, SQL statements can address a view as though it were a table The fact that tables, views, and private synonyms share the same namespace means that you can set up several layers of abstraction between what the users see and the actual tables, which can be invaluable for both security and for simplifying application development These object types each have their own namespace: PART II

Thus it is possible (though perhaps not a very good idea) for an index to have the same name as a table, even within the same schema EXAM TIP Within a schema, tables, views, and synonyms cannot have the same names Exercise 7-1: Determine What Objects Are Accessible to Your Session In this exercise, query various data dictionary views as user HR to determine what objects are in the HR schema and what objects in other schemas HR has access to 1 Connect to the database with SQL*Plus or SQL Developer as user HR 2 Determine how many objects of each type are in the HR schema:

select object_type,count(*) from user_objects group by object_type;

The USER_OBJECTS view lists all objects owned by the schema to which the current session is connected, in this case HR 3 Determine how many objects in total HR has permissions on:

select object_type,count(*) from all_objects group by object_type;

The ALL_OBJECTS view lists all objects to which the user has some sort of access 4 Determine who owns the objects HR can see:

select distinct owner from all_objects;