Use Schema Manager to View information Objects

Schema Manager is perhaps the most complex of the DBA Management Pack applications. That is 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:

1. Create, modify, or delete a table
2. Create, modify, or delete an index
3. Create, modify, or delete stored procedures, functions, and packages
4. Create, modify, or delete triggers
5. Create, modify, or delete views
6. Create or delete synonyms

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

1. CREATE TABLE,
2. CREATE VIEW, and
3. GRANT

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.
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,
dname VARCHAR2(15),
loc VARCHAR2(25))
CREATE TABLE emp (
empno NUMBER(5,0) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5,0),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3,0) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
CREATE VIEW sales_staff AS
SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = 30
WITH CHECK OPTION CONSTRAINT sales_staff_cnst
GRANT SELECT ON sales_staff TO human_resources; 

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.

1. You need to log into a database. Enter “system” in the username field and press Tab to advance to the password field.
   Type “dogtoad” in the password field and press Tab to advance to the service field. Next, tell Enterprise Manager the database to which you want to connect. Type “coin” in the service field and click the OK button.
2. This is the opening screen that you will see when you run Schema Manager and connect to your database. From here, click the “Tables” item to see a list of tables in the database, or you can expand the “Tables” item by clicking the “+” next to it. Click the close box in the upper right corner to exit.
3. Clicking Tables results in a list of database tables being displayed on the right-hand side of the form.
   From here, you can expand the “Tables” item by clicking the “+” next to it. You may also click the close box in the upper-right corner to exit this application.
4. Clicking a specific table on the left side of the screen results in information about that table being displayed on the right. The information is divided into four categories: General, Constraints, Storage, and Options. The General category, shown here, contains information about field definitions. Click any of the tabs or click the close box to exit.
5. The Constraints tab shows any constraints that have been defined on the table. Click any of the tabs or click the close box to exit.
6. The Storage tab shows storage properties for the table. These include the tablespace assignment and extent sizes. Click any of the tabs or click the close box to exit.
7. The Options tab contains some optional table settings that pertain to Oracle Parallel Server. Click any of the tabs, click the close box, or click the exit button to end this simulation.