Contents
All We Want is to Create a Table
This text on SQL is based on the Oracle Database using the EP-European Parliament as Data Model.
The European Parliament (EP) is the parliamentary institution of the European Union (EU).
The European Parliament is elected by the citizens of the European Union to represent their interests.
Its origins go back to the 1950s and the founding Treaties. Since 1979 its members have been directly elected by the citizens of the EU.
Elections are held every five years, and every EU citizen is entitled to vote, and to stand as a candidate, wherever they live in the EU. Parliament thus expresses the democratic will of the European Union's nearly 500 million citizens and it represents their interests in discussions with the other EU institutions.
The European Parliament model and data used in this SQL text, are based on the elections which were held in June 2004. The European Parliament of 2004 elections had 785 members from all 27 EU countries.
The sources for the data, which are all freely obtainable from the European Union website, and from several leaflets of the EU institutions, are listed in the References section.
These sources are used to create the fictitious Business Narrative of the "Data Modelling and Normalization" Chapter.
We called this Business Narrative; "the Story of the European Union" which explains about the EU and EP, for purposes of Data Modeling.
The Story of the European Union is the last Chapter of Kaye is Learning SQL.
The knowledge in the following sections are based on my personal work experience, as well as on several Oracle Documentation, which are freely downloadable from Oracle websites.
You can find a list of these documents in the References.
The name of the documents may change in time.
You can refer to the Oracle Documentation if you need more detailed information on any of the topics in this text.
In Oracle, TABLES are objects stored within a User area in an allocated tablespace on the Oracle Server.
Each TABLE belonging to a USER must have a Unique Name.
Each object, including a TABLE, that is created by a User is stored under that User's SCHEMA.
A SCHEMA is a collection of database objects.
A SCHEMA is owned by a Database User and has the Same Name as that User.
Schema objects are user-created structures that directly refer to the data in the database.
The database supports many types of schema objects, the most important of which are tables and indexes.
Schema objects such as tables or indexes hold data.
Some schema objects can consist of a definition only, such as a view or a synonym.
Generally, all of the objects that belong to a single application are placed in the same schema.
Data Definition Language (DDL) statements define, structurally change, and drop schema objects.
DDL enables you to alter attributes of an object without altering the applications that access the object.
For example, you can add a column to a table accessed by an application without rewriting the application.
You can also use DDL to alter the structure of objects while database users are performing work in the database.
Some DDL statements are seen below.
Create, alter, and drop schema objects and other database structures, including the database itself and database users:
CREATE ALTER DROP
Delete all the data in schema objects without removing the structure of these objects:
TRUNCATE
Grant and revoke privileges and roles:
GRANT REVOKE
Turn auditing options on and off:
AUDIT NOAUDIT
Add a comment to the Data Dictionary:
COMMENT
An implicit COMMIT occurs immediately before the database executes a DDL statement, and a COMMIT or ROLLBACK occurs immediately afterward the DDL statement.
CREATE TABLE statement is used to create a table.
Tables are created with no data unless a subquery is specified.
You can add rows to a table with the INSERT statement.
After creating a table, you can define additional columns and integrity constraints with the ADD clause of the ALTER TABLE statement.
You can change the definition of an existing column with the MODIFY clause of the ALTER TABLE statement.
To create a table in your own schema, you must have the CREATE TABLE system privilege
To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege.
Also, the owner of the schema to contain the table must have;
We will create a USER, and then we will create a table in this User's Schema.
We can use the SQL code seen below, to create users.
CREATE USER misket IDENTIFIED BY oracle;
The username is misket.
The password is oracle.
The default tablespace assigned to this user is "USERS".
It can be changed to some other tablespace name if it does not exist in your database, or if you want to assign a different tablespace to this user.
ALTER USER misket DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
The temporary tablespace assigned to this user is "TEMP".
You can change it to some other tablespace name if you want to assign a different temporary tablespace to this user.
The SQL codes above, create a new user called MISKET and grant this user CREATE SESSION privilege.
Using this privilege, user MISKET can log on to the database, to her own schema. This schema which is also called MISKET, has yet no objects in it. It is empty.
However, MISKET is also granted many other privileges which will enable her to create all the objects she needs in her schema. Some of these privileges are;
MISKET can create the tables and the sequences she needs in her schema after she logs on.
To enable a Unique or Primary Key Constraint, you must have the privileges necessary to create an INDEX on the TABLE.
You need these privileges because Oracle Database creates an INDEX on the columns of the Unique or Primary Key in the schema containing the TABLE.
Please note that user MISKET has CREATE ANY INDEX privilege.
Before creating a table and other related objects in our new user's schema, we will now have a look at Schema Object Naming Rules.
Every database object has a name.
In a SQL statement, you represent the name of an object with a Quoted Identifier or a Nonquoted Identifier.
You can use either quoted or nonquoted identifiers to name any database object.
However, database names and database link names are always case insensitive and are stored as uppercase.
If you specify such names as quoted identifiers, then the quotation marks are silently ignored.
The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated.
Names must be from 1 to 30 bytes long with these exceptions:
If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long.
Each period separator, as well as any surrounding double quotation marks, counts as one byte.
For example, suppose you identify a column like this:
"schema_name"."table_name"."column_name"
"schema_name"."table_name"."column_name"
So, the total length of the identifier in this example can be up to 98 bytes.
Nonquoted Identifiers CANNOT BE Oracle Database reserved words.
Quoted Identifiers can be reserved words, although this is NOT recommended.
Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.
Nonquoted Identifiers must begin with an alphabetic character from your database character set.
Quoted Identifiers can begin with any character.
Nonquoted Identifiers can contain only;
Database links can also contain periods(.) and "at" signs (@).
Oracle strongly discourages you from using $ and # in Nonquoted Identifiers.
Within a Namespace no two objects can have the same name.
Each Schema in the database has its own Namespaces for the objects it contains.
There are also Non-Schema objects with their own namespaces. These namespaces span the entire database.
Within a Namespace no two objects can have the same name.
The "Namespace" concept is illustrated in the diagram below — courtesy of Şeyma Bağdemir Güven who was my student in Bahçeşehir University, Istanbul, Fall 2006.
A picture says a thousand words.
Every circle/ellipse in the picture is a namespace.
There are more namespaces than seen in this picture, in a database.
Every circle/ellipse in the picture is a namespace.
There are more namespaces than seen in this picture, in a database.
Within a Namespace no two objects can have the same name.
In reference to the illustration above, this means that, two tables in different schemas are in different namespaces, and can have the same name.
Because tables and sequences are in the same namespace, a table and a sequence in the same schema cannot have the same name.
However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.
The following schema objects share one namespace:
Each of the following schema objects has its own namespace:
Each of the following non-schema objects also has its own namespace:
Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.
Because this illustration explains the Namespace concept so well, I am just putting it here again 😄
Thank you very much Şeyma Bağdemir Güven 😇
The "Namespace" concept is illustrated in the diagram below — courtesy of Şeyma Bağdemir Güven who was my student in Bahçeşehir University, Istanbul, Fall 2006.
A picture says a thousand words.
Every circle/ellipse in the picture is a namespace.
There are more namespaces than seen in this picture, in a database.
Every circle/ellipse in the picture is a namespace.
There are more namespaces than seen in this picture, in a database.
Nonquoted Identifiers are NOT Case Sensitive.
Oracle interprets them as UPPERCASE.
Quoted Identifiers are Case Sensitive.
By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:
Note that Oracle interprets the following names the SAME, so they CANNOT be used for different objects in the same namespace:
Note: You can find more detailed information on "Identifiers and UPPERCASE" in the Oracle Documents listed in the References section.
Columns in the same table or view cannot have the same name.
However, columns in different tables or views can have the same name.
Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes.
Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.
Here are examples of valid object names:
Below is an invalid object name which is more than 30 characters:
I call this an early example, because although the example is on "how to create a table", it contains many other SQL concepts we have not learned yet.
The comments of the SQL codes are pretty much explanatory, although we will learn about these codes later in different chapters.
The SQL code below;
We connect to the database as the newly created user Misket, and run the SQL codes below, in the schema Misket.
Therefore all the objects that are created by the SQL code below, will be in the schema Misket, and they will be user Misket's objects.
SQL code to CREATE the SEQUENCE which will be used for the Primary Key Column.
/* Create the sequence which is called:
seq_default_example
However, initially drop the sequence in case
there is already a sequence with the same name.
*/
DROP SEQUENCE seq_default_example;
CREATE SEQUENCE seq_default_example
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
NOCACHE NOCYCLE;
SQL code to CREATE a TABLE.
/* Create a table which is called:
default_example_table
However, initially drop the table in case
there is already a table with the same name.
*/
DROP TABLE default_example_table;
CREATE TABLE default_example_table (
table_pk NUMBER(6)
CONSTRAINT pk_default_example_table PRIMARY KEY,
first_name VARCHAR2(30) DEFAULT user,
last_name VARCHAR2(30),
logtimestamp TIMESTAMP DEFAULT systimestamp
);
SQL code to INSERT rows into a TABLE.
/* INSERT a row */
INSERT INTO default_example_table (
table_pk,
last_name
) VALUES (
seq_default_example.NEXTVAL,
'Ilsql'
);
/* INSERT another row */
INSERT INTO default_example_table (
table_pk,
first_name,
last_name
) VALUES (
seq_default_example.NEXTVAL,
'Kaye',
'Ilsql'
);
/* INSERT another row */
INSERT INTO default_example_table (
table_pk,
first_name,
last_name,
logtimestamp
) VALUES (
seq_default_example.NEXTVAL,
'Kaye',
'Ilsql',
systimestamp + 1
);
SQL code to COMMIT the INSERTED rows.
We need to commit any DML(Data Manipulation Language) command to the database.
These are also explained in the Transaction Processing chapter.
/* Commit inserted rows */
COMMIT;
SQL code to SELECT the data from the table.
/* SELECT the data from the table. */
SELECT
*
FROM
default_example_table;
TABLE_PK column is the Primary Key of the table.
It is created by the SQL code of the CREATE TABLE command we have used:
table_pk NUMBER(6)
CONSTRAINT pk_default_example_table PRIMARY KEY,
The clause
"CONSTRAINT pk_default_example_table PRIMARY KEY"designates this column as the Primary Key of the table.
We will learn more about this in the Constraints Chapter.
Data type of the TABLE_PK column is NUMBER.
This column is populated by the SEQUENCE object called seq_default_example we have already created:
CREATE SEQUENCE seq_default_example START WITH 1 INCREMENT BY 1 ...
This sequence starts with 1 and is incremented by 1.
Unless sequence values are lost in between,
the values of this column will be 1, 2 and 3.
Each value of the sequence is created by the code:
seq_default_example.NEXTVAL
This value is inserted into the table, using the INSERT command.
Because a sequence always generates a unique value, it can be used for the Primary Key of a table.
The column FIRST_NAME is created by the following SQL code, which we have used when creating the table default_example_table:
first_name VARCHAR2(30) DEFAULT user,
This code uses the DEFAULT clause.
The DEFAULT clause lets you specify a value to be assigned to the column if a subsequent INSERT statement omits that column.
In our example, the code "DEFAULT user" makes sure that, if the FIRST_NAME column is omitted in an INSERT statement, then the value of this column will be the USER we connected to the database with — i.e. the current user.
In our case, the current user is MISKET.
The very first INSERT statement we used, which is seen below, implicitly uses the DEFAULT clause. Because the column FIRST_NAME is omitted in this INSERT statement, the column is assigned the value of the current user — i.e. MISKET.
/* INSERT a row */
INSERT INTO default_example_table (
table_pk,
last_name
) VALUES (
seq_default_example.NEXTVAL,
'Ilsql'
);
We can see the result of this sql statement in the first row of the table.
In the second and the third INSERT statements the value of the column FIRST_NAME is specified as 'Kaye' in both cases.
This value overrides the DEFAULT value which is the current user.
We can see the SQL INSERT Statements and the result below.
/* INSERT another row */
INSERT INTO default_example_table (
table_pk,
first_name,
last_name
) VALUES (
seq_default_example.NEXTVAL,
'Kaye',
'Ilsql'
);
/* INSERT another row */
INSERT INTO default_example_table (
table_pk,
first_name,
last_name,
logtimestamp
) VALUES (
seq_default_example.NEXTVAL,
'Kaye',
'Ilsql',
systimestamp + 1
);
As we have seen in the example above, the DEFAULT clause lets you specify a value to be assigned to the column if a subsequent INSERT statement omits a value for the column.
In our case, the datatype of the FIRST_NAME column is VARCHAR2 which is the same as the datatype of the expression USER.
We have already used USER in FIRST_NAME, and we will be using SYSTIMESTAMP in the column LOGTIMESTAMP of our table.
the pseudocolumns like;
Values for the column LAST_NAME are explicitly specified in each INSERT statement.
The column LOGTIMESTAMP is created by the following SQL code, which we have used when creating the table default_example_table:
logtimestamp TIMESTAMP DEFAULT systimestamp
This code uses the DEFAULT clause.
Default value for this column is an SQL function SYSTIMESTAMP which gives current date and time.
Both the column LOGTIMESTAMP and the default value SYSTIMESTAMP have datatype TIMESTAMP.
In the first two INSERT Statements, the column LOGTIMESTAMP is omitted, hence, the default value SYSTIMESTAMP is used.
/* INSERT a row */
INSERT INTO default_example_table (
table_pk,
last_name
) VALUES (
seq_default_example.NEXTVAL,
'Ilsql'
);
/* INSERT another row */
INSERT INTO default_example_table (
table_pk,
first_name,
last_name
) VALUES (
seq_default_example.NEXTVAL,
'Kaye',
'Ilsql'
);
The value of systimestamp at the moment of the first two INSERT statements was '19-JUL-22 06.07.48' with some fractional seconds.
The third INSERT statement uses the specified value of systimestamp + 1, which is current date and time plus 1 day. Hence the value will be '20-JUL-22 06.07.48'.
/* INSERT another row */
INSERT INTO default_example_table (
table_pk,
first_name,
last_name,
logtimestamp
) VALUES (
seq_default_example.NEXTVAL,
'Kaye',
'Ilsql',
systimestamp + 1
);
One of the most important parts of an Oracle Database is its Data Dictionary.
Just like an English Dictionary holds information "about the words" in the English language, an Oracle Data Dictionary holds information "about the data" in an Oracle database.
Metadata is "information about data" or "data about data".
We can say that, the Oracle Data Dictionary provides the Metadata of the database.
Oracle Database stores data dictionary data in tables and views.
Therefore, just like any other data in tables/views, users can query the data dictionary data with SQL.
For example, users can run SELECT statements to determine;
Below, we will see some examples.
One of the Data Dictionary views is USER_OBJECTS.
Through this view, we can find out the objects in a user's schema.
It has columns indicating the types and the names of the objects.
Logged on to the database as the user Misket, we can run the following query to find out the objects in Misket's schema:
SELECT OBJECT_TYPE
, OBJECT_NAME
FROM USER_OBJECTS
ORDER BY OBJECT_TYPE, OBJECT_NAME;
USER_TAB_COLUMNS is another Data Dictionary view.
Through this view, we can access data about the columns of tables.
We can run the following query to find out the columns, and information on the columns of the table DEFAULT_EXAMPLE_TABLE.
SELECT TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
, DATA_LENGTH
, DATA_PRECISION
, DATA_SCALE
, DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME='DEFAULT_EXAMPLE_TABLE'
ORDER BY COLUMN_NAME;
As we can see in the query output above, USER_TAB_COLUMNS have useful information like the data type, data length, default value of a column.
A user can create comments on the tables, and on the columns of the tables in her schema.
This information is immediately written into the Data Dictionary and we can retrieve this information from the views USER_TAB_COMMENTS (for tables) and USER_COL_COMMENTS (for columns).
Create a comment on the table DEFAULT_EXAMPLE_TABLE.
COMMENT ON TABLE DEFAULT_EXAMPLE_TABLE IS 'This table is created as an example to column default values';
The SQL statement above, created a comment on the table DEFAULT_EXAMPLE_TABLE.
This information is immediately written to the Data Dictionary as Metadata.
This new piece of metadata, can be queried through the Data Dictionary view USER_TAB_COMMENTS as seen below.
SELECT TABLE_NAME
, COMMENTS
FROM USER_TAB_COMMENTS
WHERE TABLE_NAME='DEFAULT_EXAMPLE_TABLE';
Create a comment on the LOGTIMESTAMP column of the table DEFAULT_EXAMPLE_TABLE.
COMMENT ON COLUMN DEFAULT_EXAMPLE_TABLE.LOGTIMESTAMP IS 'This column is defaulted to systimestamp and its datatype is timestamp(6)';
The SQL statement above, created a comment on the LOGTIMESTAMP column of the table DEFAULT_EXAMPLE_TABLE.
This information is immediately written to the Data Dictionary as Metadata.
This new piece of metadata, can be queried through the Data Dictionary view USER_COL_COMMENTS as seen below.
SELECT *
FROM USER_COL_COMMENTS
WHERE TABLE_NAME='DEFAULT_EXAMPLE_TABLE';
The data dictionary consists of Base Tables and Views.
These objects are defined as follows:
Typically, data dictionary views are grouped in sets. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes, as shown in the following table. By querying the appropriate views, you can access only the information relevant for you.
| Prefix | User Access | Contents | Notes |
|---|---|---|---|
| DBA_ | Database Administrators | All Objects | Some DBA_ views have additional columns containing information useful to the administrator. |
| ALL_ | All Users | Objects to which the Current User has Privileges | Includes objects owned by the user. These views obey the current set of enabled roles. |
| USER_ | All Users | Objects Owned by the Current User | Views with the prefix USER_ usually exclude the column OWNER. This column is implied in the USER_ views to be the user issuing the query, i.e. the Current User. |
Not all view sets have three members.
For example, the data dictionary contains a DBA_LOCK view but no ALL_LOCK view.
The system-supplied DICTIONARY view contains the names and abbreviated descriptions of all the Data Dictionary views.
It is one of the most useful data dictionary views, because it can help you with what you need quickly without searcing any documentation or browsing the web.
Here are a couple of queries on this view.
What are the tables in the Data Dictionary
which start with 'USER_TAB' and what do these tables contain?
The COMMENTS column gives a good explanation of the
table's content.
The query is run in sqlplus.
column TABLE_NAME format a30
column COMMENTS format a120
set linesize 300
set pagesize 60
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE 'USER_TAB%' ORDER BY TABLE_NAME;
What are the tables in the Data Dictionary which start with 'ALL_TAB' and what do these tables contain?
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE 'ALL_TAB%' ORDER BY TABLE_NAME;
Which tables in the Data Dictionary hold data about Constraints?
SELECT * FROM DICTIONARY
WHERE TABLE_NAME LIKE '%CONS%' OR UPPER(COMMENTS) LIKE '%CONSTRA%'
ORDER BY TABLE_NAME;
DBA_ views in the Data Dictionary on Constraints:
ALL_ views in the Data Dictionary on Constraints:
USER_ views in the Data Dictionary on Constraints:
Note: If you need more information on CDBs (Multitenant Container Database) and PDBs (Pluggable Database), you can check Oracle Documentation on "Oracle Multitenant Architecture".
In a CDB, for every DBA_ view, a corresponding CDB_ view exists.
The owner of a CDB_ view is the owner of the corresponding DBA_ view.
The following picture shows the relationships among the different categories of Dictionary Views in a CDB.
When the current container is a PDB, a user can view data dictionary information for the current PDB only.
When the current container is the CDB root, a common user can query CDB_ views to see metadata for the CDB root and for PDBs for which this user is privileged.
The SYSTEM user, which is common to all containers in the CDB, connects to the CDB root.
C:\Users\HP>sqlplus
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Aug 2 13:46:04 2022
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> CONNECT SYSTEM
Enter password:
Connected.
The SYSTEM user queries CDB_USERS to obtain the number of common users in the CDB root.
CDB root has the CON_ID=1.
The output indicates that 35 common users exist in CDB root.
select count(*) from cdb_users where con_id=1;
The SYSTEM user queries CDB_USERS to determine the number of distinct containers with users in the multitenant container database.
select count(distinct(con_id)) from cdb_users;
We can see from the output that there are two containers with USERS in them.
Whic containers are they?
The SYSTEM user queries CDB_USERS to find out the CON_ID of the containers with USERS in them.
select distinct con_id from cdb_users;
We can see from the output that the containers with CON_ID=1 and CON_ID=3, have USERS in them.
We already know that CON_ID = 1 is the CDB.
Which one is the other container?
We have to query for CON_ID=3.
The SYSTEM user queries the view v$container to find out the name of the container with CON_ID=3.
select name from v$containers where con_id=3;
We can see from the output of the query, that the other container with USERS in it, is the PDB —Pluggable Database, named XEPDB1.
We can now, query the users in this PDB, XEPDB1.
The SYSTEM user now connects to the PDB named XEPDB1 using SQL Developer.
SYSTEM queries CDB_USERS.
The output indicates that 39 common and local users exist in the current container, which is XEPDB1.
SELECT COUNT(*)
FROM CDB_USERS;
SYSTEM queries DBA_USERS.
The output is the same as the previous query.
Because SYSTEM is not connected to the CDB root, the DBA_USERS view shows the same output as CDB_USERS.
Because the DBA_USERS view only shows the users in the current container, it shows 39.
SELECT COUNT(*) FROM DBA_USERS;
Views with the prefix DBA_ show all relevant information in the entire database.
DBA_ views are intended only for administrators.
The following sample query shows information about all objects in the database:
SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
Views with the prefix ALL_ refer to the user's overall perspective of the database.
These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns.
For example, the following query returns information about all the objects to which you have access:
SELECT OWNER, OBJECT_TYPE, OBJECT_NAME
FROM ALL_OBJECTS
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
The views most likely to be of interest to typical database users are those with the prefix USER_.
These views:
For example, the following query returns all the objects contained in your schema:
SELECT OBJECT_TYPE, OBJECT_NAME
FROM USER_OBJECTS
ORDER BY OBJECT_TYPE, OBJECT_NAME;
We can see in the figure above that the DUAL table has one column only called DUMMY.
We can see in the figure above that the DUAL table has one row only containing the value X.
DEFAULT_EXAMPLE_TABLE is the table we created earlier.
We have also inserted three rows into this table.
Let's check it out:
SELECT * FROM DEFAULT_EXAMPLE_TABLE;
Here are the three rows we have inserted earlier:
We can also find out the number of rows in the table as below:
SELECT COUNT(*) FROM DEFAULT_EXAMPLE_TABLE;
If we select an expression from this table, the expression will be returned as many times as the number of rows.
Hence, we expect to see 3 rows of the same expression:
SELECT SYSTIMESTAMP FROM DEFAULT_EXAMPLE_TABLE;
The result is as just we expected. We have three rows of the same expression — three times.
However, sometimes we might need the result in one row only.
We want the result only once.
This is when we need to use the DUAL table.
Because DUAL has only one row, the expression will be returned one and only one time.
SELECT SYSTIMESTAMP FROM DUAL;
The result is displayed in one row only, exactly as we wanted.
This is the use for the DUAL table.
Here is another example of using the DUAL table.
In this example, we perform an arithmetical operation, and we need to get the result once only — in just one row.
SELECT ((2 * 3) + 4) / 5 FROM DUAL;
This example resumes the need for and the use of the DUAL table.
The Data Dictionary is a CENTRAL PART of Data Management for every Oracle Database.
Data in the Base Tables of the Data Dictionary is necessary for Oracle Database to FUNCTION.
During Database Operations, the Oracle Database performs the following actions:
During Database Operations, Oracle Database updates/modifies/writes to the Data Dictionary continuously to reflect changes in database structures, auditing, grants, and data.
Oracle Database updates/modifies/writes to the Data Dictionary whenever a Data Definition Language (DDL) statement is issued.
For example, if User MISKET creates a table named DEFAULT_EXAMPLE_TABLE, then the Oracle Database adds new rows to the Data Dictionary that reflect the new table, columns, segment, extents, and the privileges that MISKET has on the table.
This new information is visible the next time the Data Dictionary Views are queried.
In the previous sections, our Data Definition Language (DDL) actions like creating a sequence, creating a table, creating comments, caused information to be written into the Data Dictionary Base Tables.
Therefore, we were able to query the Data Dictionary Views on the Base Tables like;
These are only some of the examples on how the Oracle Database MODIFIED the Data Dictionary during Database Operations.
During database operation, Oracle Database reads the Data Dictionary to make sure that schema objects exist and that users have proper access to them.
In this example, a table named 'DEFAULT_EXAMPLE_TAB' is queried, and an error message (ORA-00942) is received.
This is because, when the user queries the table 'DEFAULT_EXAMPLE_TAB', the Oracle Database accesses the Data Dictionary and checks out if such a table exists.
If the table exists and if the user issuing the query has SELECT privileges on the table, then Oracle Database will return the requested rows from the table.
However, in this case, no such table exists.
Therefore, the Oracle Database returns an error message.
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 2 Column: 6
In this example, two columns from the table 'DEFAULT_EXAMPLE_TAB' are queried, and an error message (ORA-00904) is received.
When the user issued the query, the Oracle Database accessed the Data Dictionary and checked out if the table and the columns specified in the query exist.
Although the table 'DEFAULT_EXAMPLE_TAB' exists, one of the columns is spelled wrong:
LST_NAME
Oracle Database CANNOT FIND the column LST_NAME in the Data Dictionary.
Hence, the error.
ORA-00904: "LST_NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 1 Column: 20
"How the Data Dictionary Works" is briefly explained by examples above.
However, you can find in-depth information on this topic in Oracle Documentation in the References section if you need to.
Throughout its operation, Oracle Database maintains a set of virtual tables that record current database activity.
These tables are called Dynamic Performance Tables.
They are called Dynamic Performance Tables, because they are continuously updated while a database is open and in use.
Their contents relate primarily to performance.Dynamic Performance Tables are NOT true tables
Although they appear to be regular database tables, they are not.
They are VIEWS which provide data on internal disk structures and memory structures.
You can select from these views, but you can never update or alter them.
However, Database Administrators CAN query and create views on the tables and grant access to those views to other users.
The Dynamic Performance Tables are sometimes called FIXED VIEWS because they cannot be altered or removed by the Database Administrator.
One such view is the V$FIXED_TABLE.
The V$FIXED_TABLE view contains information about all of the Dynamic Performance Tables and Views .
Let's have a look at the V$FIXED_TABLE view using the Oracle SQL Developer tool.
To find out the structure of the V$FIXED_TABLE in Oracle SQL Developer, type V$FIXED_TABLE and then use (Shift + fn F4) when the cursor is on the text "V$FIXED_TABLE" .
My keys work this way, they may differ from one installation to the other.
You can also right click when on the text "V$FIXED_TABLE" and find "Popup Describe" and click on that.
User SYS owns the Dynamic Performance Tables.
Their names all begin with V_$.
VIEWS are created on these Dynamic Performance Tables, and then Public Synonyms are created for the Views.
The Public Synonym names begin with V$.
As we can see in the "Popup Describe" window opened for the view V$FIXED_TABLE, the name is displayed as V_$FIXED_TABLE.
V_$FIXED_TABLE is the name of the Dynamic Performance Table owned by the User SYS.
The VIEW V$FIXED_TABLE is created on the Dynamic Performance Table V_$FIXED_TABLE, and then the Public Synonym V$FIXED_TABLE is created for the View.
We can see this relationship in the Dependencies Tab of the Popup Describe window.
In the previous section, we have seen that the V$FIXED_TABLE is a public synonym for the Dynamic Performance Table V_$FIXED_TABLE owned by the User SYS, and it holds the names of all the Dynamic Performance Tables and Views in an Oracle Database.
Now, let's have a look at the contents of the V$FIXED_TABLE.
What types of objects does V$FIXED_TABLE hold?
SELECT DISTINCT TYPE
FROM V$FIXED_TABLE;
We can see from the results of the query that there are two types of objects as expected:
Let's display all the rows and all the columns of V$FIXED_TABLE.
SELECT *
FROM V$FIXED_TABLE
ORDER BY TYPE, NAME;
What are the objects that start with a 'V' in V$FIXED_TABLE?
SELECT *
FROM V$FIXED_TABLE
WHERE NAME LIKE 'V%'
ORDER BY TYPE, NAME;
Let's check the contents of the views in V$FIXED_TABLE that are marked with an arrow.
V$DATABASE view displays information about the database from the control file.
V$DATAFILE view contains information about data files.
Because the Dynamic Performance Tables are not true tables, the data depends on the state of the database and database instance.
For example, you cannot query V$DATAFILE until the database has been mounted. However, you can query V$INSTANCE when the database is started but not mounted.
V$INSTANCE displays the state of the current instance.
Because the Dynamic Performance Tables are not true tables, the data depends on the state of the database and database instance.
For example, you can query V$INSTANCE when the database is started but not mounted. However, you cannot query V$DATAFILE until the database has been mounted.
V$PARAMETER is another useful view.
It displays information about the initialization parameters that are currently in effect for the session, their format and whether they are modifiable at a session or system level.
A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.
SELECT NAME,
VALUE,
DEFAULT_VALUE,
ISDEFAULT,
ISSES_MODIFIABLE,
ISSYS_MODIFIABLE,
ISPDB_MODIFIABLE,
ISINSTANCE_MODIFIABLE,
DESCRIPTION
FROM V$PARAMETER;
We have seen only a few of the Dynamic Performance Views.
There are many more of them which are very useful for monitoring and debugging the Oracle Database performance.
The Dynamic Performance Views have the following primary uses:
Dynamic Performance Views contain information such as the following:
This resumes the information on the Dynamic Performance Tables and Views.
A synonym is an alias for a Schema Object.
For example, you can create a synonym for a table or view, sequence, PL/SQL program unit, user-defined object type, or another synonym.
Because a synonym is simply an alias, it requires no storage other than its definition in the Data Dictionary.
Synonyms can simplify SQL statements for database users.
Synonyms are also useful for hiding the identity and location of an underlying schema object:
You can create both Private and Public Synonyms.
A Private Synonym is in the Schema of a Specific User who has control over its availability to others.
A Public Synonym is owned by the User Group named PUBLIC and is accessible by every Database User.
The Database User KAYE queries the number of rows in table MEPS which is in her own schema.
SELECT COUNT(*) FROM MEPS;
The Database User MISKET queries the number of rows in table MEPS which is in KAYE's schema.
She gets the error ORA-01031 which informs her that she does not have privileges to select from the table MEPS which is in another user's schema.
SELECT COUNT(*) FROM KAYE.MEPS;
The Database Administrator creates a PUBLIC SYNONYM called MEPS.
CREATE PUBLIC SYNONYM MEPS FOR KAYE.MEPS;
The Database Administrator grants SELECT privileges to the User Group PUBLIC.
Every Database User automatically belongs to the PUBLIC User Group.
GRANT SELECT ON MEPS TO PUBLIC;
As every Database User automatically belongs to the PUBLIC User Group, MISKET can now query the number of rows in Kaye's table MEPS via the Public Synonym MEPS.
SELECT COUNT(*)
FROM MEPS;
MISKET also queries all the rows in Kaye's table MEPS via the Public Synonym MEPS.
SELECT *
FROM MEPS;
MISKET can also query Kaye's table MEPS directly as seen below, using the schema name in front of the object name as KAYE.MEPS.
This is because, as stated above in the Overview section:
SELECT * FROM KAYE.MEPS;
Database User KAYE creates a PRIVATE SYNONYM called EU_PARTIES for the table POLITICAL_GROUPS in her own schema.
If you omit the "PUBLIC" clause, then the Synonym will be created as a Private Synonym.
CREATE SYNONYM EU_PARTIES FOR POLITICAL_GROUPS;
Database User MISKET queries the new private synonym EU_PARTIES, but receives the Oracle error "ORA-00942: table or view does not exist".
SELECT * FROM EU_PARTIES;
Database User KAYE grants SELECT privilege to the Database User MISKET on her Private Synonym EU_PARTIES.
GRANT SELECT ON EU_PARTIES TO MISKET;
User MISKET queries KAYE'S Private Synonym EU_PARTIES again, and receives the same Oracle error:
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 2 Column: 6
SELECT *
FROM EU_PARTIES;
This is because EU_PARTIES is a Private Synonym which belongs to the User KAYE, it must be prefixed by the Schema Name — KAYE.
The Database User MISKET can now query EU_PARTIES with the Schema Name Prefix, "KAYE.EU_PARTIES".
SELECT *
FROM KAYE.EU_PARTIES;
DBA_SYNONYMS describes all synonyms in the database. Its columns are the same as those in ALL_SYNONYMS.
SELECT * FROM DBA_SYNONYMS WHERE 1=1 AND TABLE_OWNER='KAYE';
ALL_SYNONYMS describes the synonyms accessible to the current user.
SELECT * FROM ALL_SYNONYMS WHERE 1=1 AND TABLE_OWNER='KAYE';
USER_SYNONYMS describes the synonyms owned by the current user. This view does not display the OWNER column.
SELECT * FROM USER_SYNONYMS;
When you refer to an Object in an SQL statement, Oracle considers the Context of the SQL statement and locates the Object in the Appropriate Namespace.
After locating the Object, Oracle performs the operation specified by the Statement on the Object.
If the Named Object CANNOT be found in the Appropriate Namespace, then Oracle returns an error.
The following example illustrates how Oracle resolves references to Objects within SQL statements.
Consider this statement that adds a row of data to a table identified by the name "POLITICAL_GROUPS":
/* 'EPP-ED' */
insert into POLITICAL_GROUPS
(pg_id
,pg_code
,pg_name
,address
,postal_code
,city
,tel
,email
,internet
,about
,institute_id
)
values
(20
,'EPP-ED'
,'Group of the European People''s Party(Christian Democrats) and European Democrats'
,'Group of the European People''s Party (Christian Democrats) and
European Democrats in the European Parliament,
60, Rue Wiertz /Wiertzstraat
B-1047 Brussels
'
,'B-1047'
,'Brussels'
,'+32 2 2842234'
,'epp-ed@europarl.europa.eu'
,'http://www.epp-ed.eu/home/'
,'The Group of the European People''s Party (Christian Democrats) and European Democrats in the European Parliament (EPP-ED Group) is a major political force in the European Union; it is the direct heir of the tradition established in the 1950s by Robert Schuman, Konrad Adenauer and Alcide de Gasperi.
The EPP-ED Group has worked consistently and successfully to consolidate the European Union on the bases of the primacy of law and respect for fundamental rights, on the application of the principle of subsidiarity and an efficient sharing out of power, and on independent democratic institutions, in order to ensure that future progress serves the common interest of all Europeans.
As members of the EPP-ED Group, we believe in a Europe of values, united, open, more humane and embracing diversity. We want a Europe which creates opportunity and wealth within a single market, competitive at world level, and which at the same time promotes the wellbeing of everybody, not only in Europe, but also in the rest of the world, in accordance with the principle of sustainable development enshrined in the EU Treaty as one of the European Community''s aims.We wish to see the Union develop a consistent and effective common foreign and security policy and to carry out the institutional reforms necessary to make enlargement a success.The EPP-ED Group is the political centre in Europe. Moderation and dialogue are the tools our Group deploys. Using them, all our work as members of the EPP-ED Group reflects our unstinting commitment to building a Europe of opportunity, a better Europe for all.'
,300
)
;
Based on the Context of the SQL statement, Oracle determines that "POLITICAL_GROUPS" can be:
Oracle always attempts to resolve an Object Reference within the Namespaces in your Own Schema before considering Namespaces Outside Your Schema.
At this point in the text, let's revisit the Concept of Namespaces, by displaying the Namespace illustration below — yet again.
The "Namespace" concept is illustrated in the diagram below — courtesy of Şeyma Bağdemir Güven who was my student in Bahçeşehir University, Istanbul, Fall 2006.
A picture says a thousand words.
Every circle/ellipse in the picture is a namespace.
There are more namespaces than seen in this picture, in a database.
Every circle/ellipse in the picture is a namespace.
There are more namespaces than seen in this picture, in a database.
Oracle attempts to resolve the name "POLITICAL_GROUPS" as follows:
Step 1
First, Oracle attempts to locate the object in the Namespace in Your Own Schema containing Tables, Views, and Private Synonyms.
If the object is a Private Synonym, then Oracle locates the Object for which the Synonym stands.
This Object could be in your own schema, in another schema, or on another database.
The Object could also be Another Synonym, in which case Oracle locates the Object for which this Synonym stands.
Step 2
If the Object is in the Namespace, then Oracle attempts to perform the statement on the object.
In this example, Oracle attempts to add the row of data to POLITICAL_GROUPS.
If the object is NOT of the correct type for the statement, then Oracle returns an error.
In this example, POLITICAL_GROUPS must be a "Table", "View", or a "Private Synonym resolving to a Table or View".
If POLITICAL_GROUPS is a Sequence, then Oracle returns an error.
Step 3
If the Object is NOT in any Namespace searched in thus far, then Oracle searches the Namespace containing Public Synonyms.
If the Object is in that namespace, then Oracle attempts to perform the statement on it.
If the Object is NOT of the correct type for the statement, then Oracle returns an error.
In this example, if POLITICAL_GROUPS is a Public Synonym for a Sequence, then Oracle returns an error.
