|Lesson 8 ||Using Schema Manager |
| Objective || Use Schema Manager to view information about various objects |
Schema Manager functionality
Schema Manager is perhaps the most complex of the DBA Management Pack applications. That’s because Schema Manager is used to manage
database objects such as tables, indexes, and views, and there are a large number of object types to be managed. Use Schema Manager whenever
you want to create, modify, or delete any database object that a user can own. You can perform the following tasks with Schema Manager:
- Create, modify, or delete a table
- Create, modify, or delete an index
- Create, modify, or delete stored procedures, functions, and packages
- Create, modify, or delete triggers
- Create, modify, or delete views
- Create or delete synonyms
Creating Multiple Tables and Views in Single Operation
You can 1) create several tables and views and 2) grant privileges in one operation using the CREATE SCHEMA statement.
The CREATE SCHEMA statement is useful if you want to guarantee the creation of several a) tables, b) views, and c) grants in one operation.
If an individual table, view or grant fails, the entire statement is rolled back.
None of the objects are created, nor are the privileges granted.
Specifically, the CREATE SCHEMA statement can include only
statements. You must have the privileges necessary to issue the included statements. You are not actually creating a schema, that is done
when the user is created with a CREATE USER statement. Rather, you are populating the schema.
- CREATE TABLE,
- CREATE VIEW, and
The following statement creates two tables and a view that joins data from the two tables:
CREATE SCHEMA AUTHORIZATION scott
CREATE TABLE dept (
deptno NUMBER(3,0) PRIMARY KEY,
CREATE TABLE emp (
empno NUMBER(5,0) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
hiredate DATE DEFAULT (sysdate),
deptno NUMBER(3,0) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
CREATE VIEW sales_staff AS
SELECT empno, ename, sal, comm
WHERE deptno = 30
WITH CHECK OPTION CONSTRAINT sales_staff_cnst
GRANT SELECT ON sales_staff TO human_resources;
The CREATE SCHEMA statement does not support Oracle Database extensions to the
ANSI CREATE TABLE and CREATE VIEW statements, including the STORAGE clause.
Of course, Schema Manager lets you modify and delete objects as well as create them. Go through the following simulation to explore some of Schema Manager'a functionality.
Exploring Schema Manager