Page 161
Instance Manager provides a variety of functions related to the management of database availability. The database administrator can shut down and start up database instances, mount and open databases, and toggle archive logging on and off. Automatic archival can be temporarily turned on (until the instance is cycled) or several types of manual archival can be triggered. Information on the status of a database, its logging status, and its SGA configuration is available by selecting the database object in the navigation window.
TIP |
If the database management functions found on the menu bar by selecting Database are grayed out and inaccessible, it is because the connected user is not authorized to perform these functions. If you want to perform these tasks, reconnect to the database as SYSOPER or SYSDBA. |
Selecting the Sessions folder in the navigation window provides session information. In addition, access to the instance to new sessions is restricted or allowed using the Sessions selection from the menu bar. Sessions can be disconnected either immediately or after the current transaction is complete. Selecting In-Doubt Transactions provides information about these transactions, which can be forced to commit or rollback.
Instance configuration information is also managed by the Instance Manager. Parameters that are dynamically updateable in the database take effect immediately. Other changes take effect the next time the database is cycled. Multiple configurations are stored in the Windows Registry for access and review. Any configuration can be exported to a file or automatically propagated to remote systems.
TIP |
Stored configurations help track changes to each database. If all configuration changes are made and stored in Instance Manager, you can always see your change history online. The date of implementation and the person making the change should be recorded as part of the configuration name and description. Hopefully, future releases will store configurations in the repository instead of the Windows Registry of the local workstation. |
Schema Manager is perhaps the most comprehensive and widely used of the Database Administration Tools. Schema Manager administers clusters, constraints, database links, functions, indexes, packages, partitions, privileges, procedures, queues, refresh groups, sequences, snapshot logs, snapshots, synonyms, tables, triggers, views (see Figure 9.8), and the relationships these objects have to each other.
Schema Manager provides a variety of capabilities related to managing schema objects. The database administrator can create any managed object and can even use existing objects as templates. When an object is selected in the navigation tree, Schema Manager creates or assigns object privileges, synonyms, and indexes related to the selected object. Partitions can be removed from a selected table.
Page 162
As powerful as Schema Manager is, it is one of the simplest Database Administration Tools to use. Listing 9.1 shows the result of a simple operation to clone the SYS.OBJ$ table and its indexes as recorded by Schema Manager.
Listing 9.1O BJ$_TBL.SQLDDL to Create OBJ$ and Its Indexes
REM REM Schema Manager recorded SQL to create a duplicate copy of SYS.OBJ$ REM and its indexes and constraints.REM CREATE TABLE "JGENTRY".TEST (OBJ# NUMBER NOT NULL, DATAOBJ# NUMBER NULL, OWNER# NUMBER NOT NULL, NAME VARCHAR2(30) NOT NULL, NAMESPACE NUMBER NOT NULL, SUBNAME VARCHAR2(30) NULL, TYPE# NUMBER NOT NULL, CTIME DATE NOT NULL, MTIME DATE NOT NULL, STIME DATE NOT NULL, STATUS NUMBER NOT NULL, REMOTEOWNER VARCHAR2(30) NULL, LINKNAME VARCHAR2(128) NULL, FLAGS NUMBER NULL, OID$ RAW(16) NULL, SPARE1 NUMBER NULL, SPARE2 NUMBER NULL, SPARE3 NUMBER NULL, SPARE4 VARCHAR2(1000) NULL, SPARE5 VARCHAR2(1000) NULL, SPARE6 DATE NULL, CHECK (OBJ# IS NOT NULL), CHECK (OWNER# IS NOT NULL), CHECK (NAME IS NOT NULL), CHECK (NAMESPACE IS NOT NULL), CHECK (TYPE# IS NOT NULL), CHECK (CTIME IS NOT NULL), CHECK (MTIME IS NOT NULL), CHECK (STIME IS NOT NULL), CHECK (STATUS IS NOT NULL)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 10K NEXT 100K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) TABLESPACE "USER_DATA" CREATE UNIQUE INDEX "JGENTRY"."I_OBJ1" ON "JGENTRY"."TEST" ("OBJ#") TABLESPACE "USER_DATA" PCTFREE 10 INITRANS 2 MAXTRANS 255
Page 163
STORAGE (INITIAL 10K NEXT 24K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1) CREATE UNIQUE INDEX "JGENTRY"."I_OBJ2" ON "JGENTRY"."TEST" ("OWNER#", "NAME", "NAMESPACE", "REMOTEOWNER", "LINKNAME", "SUBNAME") TABLESPACE "USER_DATA" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 10K NEXT 100K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1) CREATE INDEX "JGENTRY"."I_OBJ3" ON "JGENTRY"."TEST" ("OID$") TABLESPACE "USER_DATA" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1) EXIT;
The entire content of OBJ$_TBL.SQL was generated using the "create like" operation on four existing objects, changing the schema names and tablespaces, and reassigning the column order to the indexes (which is not retained by the "create like" operation on an index).
TIP |
Capture your own SQL using the record and playback feature in Schema Manager. |
Security Manager provides database administrators with a means to manage access privileges and object privileges within Oracle8 (see Figure 9.9). The database administrator can easily create, alter, and drop security objects and associate privileges, roles, quotas, and profiles to users and other roles.
Security Manager is so easy to use that it could be provided to end-user security administrators to administer application security. These end-user security administrators have ADMIN OPTION granted for the roles and object privileges within their spheres of responsibility. Custom application components to perform this function are avoidable by the proper use of roles, application user training, and Security Manager. Application users enjoy direct and timely management of data they own, and database and security administrators can spend time on more productive pursuits.
Storage Manager provides database administrators with a means to manage and monitor tablespaces, data files, and rollback segments within Oracle8. The database administrator can easily create, alter, and drop storage (see Figure 9.10). The interdependencies of storage objects are shown from the perspective of any of the three storage objects. Storage Manager adds rollback segments and data files to tablespaces, takes tablespaces online and offline, and takes them into and out of read-only mode. Individual data files and rollback segments can also be taken online and offline. Storage Manager can also shrink rollback segments.