When should one upgrade to 9iDB?
According to Oracle Corporation one can start upgrading to 9i immediately. Oracle 9i, the company's new software that basically collapses 75 of the company's previous products into just two: the application server and the database server. So a wiser approach would be to install Oracle 9i, work with it and hold back until you feel comfortable and convinced to migrate.
Can one upgrade from Oracle7 directly to 9iDB?
No, Oracle does not support upgrading from non-supported releases of the database. Users running Oracle7 will have to upgrade to Oracle8i, and then perform a second upgrade to 9iDB.
How does one upgrade to 9iDB?
Upgrade using the Oracle Upgrade Assistant GUI. For more information, read the Oracle Upgrade Guide (Migrations Guide for Oracle 9i).
Can I redefine tables online? What is online table redefinition?
Oracle9i contains new online reorganization and redefinition capabilities. Any physical attribute of the table can be changed online. The table can be moved to a new location. The table can be partitioned. The table can be converted from one organization (e.g. heap) to another (e.g.index organized).Additionally, many logical attributes can be changed. Column names, types, and sizes can be changed. Columns can be added, deleted, or merged. The major restriction is that the primary key of the table cannot be modified.Oracle 9i provides a new DBMS_REDEFINITION PL/SQL package to redefine tables online. When a table is redefined online, it is accessible to DML during much of the redefinition process.
Can I create indexes on index organized tables online?
Oracle9i supports online create, rebuild, etc. of secondary indexes on index organized tables and can be analyzed at the same time. Secondary indexes support usage of block hints.
Can I analyze objects online?
Oracle9i can validate the structure of an object (Analyze Validate) while the object is online and accessed by users.
Can I control switching of my archivelog based on time?
Oracle 9i provides a time-based means of switching the current online redo log group which can limit the number of redo records, as measured in time, that will not be applied in the standby database.
What is Quiescing the database?
Oracle9i database can be placed into a quiesced state. In this state, only DBA transactions, queries, or executes PL/SQL statements. The ALTER SYSTEM QUIESCE RESTRICTED statement places the database in quiesced state.
What is suspending a database?
Suspending a database halts all input and output (I/O) operations to datafiles and control files. You can use ALTER SYSTEM SUSPEND/ALTER SYSTEM RESUME statement to suspend the database. All ongoing I/O operations are allowed to complete and all new incoming db accesses are placed in a queued state.
What is the difference between Quiescing and suspending a database?
In suspending a database you’re stopping I/O to data and control file and restricting access. You can’t transact in this mode; whereas quiescing allows the DBA to transact.
What is precision Database repair?
Oracle9i contains more precise recovery capabilities. For instance, when a block corruption needs to be repaired via media recovery, a new block media recovery feature allows recovery of only the corrupt blocks while the remainder of the table is online. Trial recovery allows recovery to proceed after a corrupted database block is detected; and if more corruption is detected, the recovery can be backed out.Oracle9i can also recover from crashes more quickly using a new two-pass recovery algorithm that ensures that only the blocks that need be processed are read from and written to the datafiles.
What is Resumable Space Allocation?
Oracle9i allows temporarily suspending a large operation, such as a batch update or data load in the event of space allocation failures. You can then fix the problem, and then resume automatically from the point of interruption - all without disrupting normal database operation. This capability is called resumable space allocation.
How many archive log destinations does Oracle 9i support?
You can archive the online redo log onto 10 destinations with Oracle 9i.
What is automatic segment space management?
Oracle9i can take care of free and used space (extents) within segments stored in locally managed tablespaces to be managed automatically. Using the SEGMENT SPACE MANAGEMENT clause of CREATE TABLESPACE you specify AUTO or MANUAL to specify the type of segment space management Oracle will use.
Can I update global indexes when performing partition related maintenance?
Yes. In your ALTER TABLE statement for the maintenance operation, specify the UPDATE GLOBAL INDEX clause.
Can a database have multiple block sizes?
Oracle 9i supports multiple block sizes. It has a standard block size, as set by the DB_BLOCK_SIZE initialization parameter, and additionally up to 4 nonstandard block sizes. Nonstandard block sizes are specified when creating tablespaces. The standard block size is used for the SYSTEM tablespace and most other tablespaces. Multiple block size support allows for the transporting of tablespaces with unlike block sizes between databases.
Can a database have multiple block sizes?
Oracle 9i supports multiple block sizes. It has a standard block size, as set by the DB_BLOCK_SIZE initialization parameter, and additionally up to 4 nonstandard block sizes. Nonstandard block sizes are specified when creating tablespaces. The standard block size is used for the SYSTEM tablespace and most other tablespaces. Multiple block size support allows for the transporting of tablespaces with unlike block sizes between databases.
Then what happens to my Buffer Cache Size? Can I have multiple DB_BLOCK_BUFFERS parameters? What is Dynamic Buffer Cache?
Buffer cache is now dynamic. The DB_BLOCK_BUFFERS initialization parameter has been replaced by a new dynamic parameter, DB_CACHE_SIZE. The buffer cache now consists of subcaches when multiple block sizes are specified for the database. Up to four DB_ nK_CACHE_SIZE initialization parameters allow you to specify the sizes of buffer subcaches for the additional block sizes.
Okay, What about the SGA then? Can I have Dynamic SGA? What is Dynamic SGA?
The initialization parameters affecting the size of SGA have been made dynamic. It is possible to alter the size of SGA dynamically through an ALTER SYSTEM SET statement. SGA components (viz. Buffer cache and shared pool) can be resized dynamically without starting the instance. Oracle9i also provides advisories to help DBA’s size the SGA for optimal database performance.
What is automatic undo management? Can rollback segments be managed automatically?
Previously, Oracle has used rollback segments to store undo. Oracle now enables you to create an undo tablespace to store undo. Oracle9i also allows administrators to allocate their undo space in a single undo tablespace with the database taking care of issues such as undo block contention, consistent read retention and space utilization. It enables you to exert control over how long undo is retained before being overwritten.
What are Oracle managed files? If Oracle can manage rollback segments automatically, what about other database files?
Oracle9i directly manages the files comprising an Oracle database and can create and delete files as needed. The DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_ n initialization parameters allow you to specify the file system directory to be used for a particular type of file comprising a tablespace, online redo log file, or control file. Oracle then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.
What is automatic deletion of datafiles?
Oracle9i provides an option to automatically remove a tablespaces’s operating system files (datafiles) when the tablespace is dropped using the DROP TABLESPACE statement. A similar option for the ALTER DATABASE TEMPFILE..DROP statement, causes deletion the operating system files associated with a temporary file.
Who should use this oracle managed files feature?
Everybody can use this feature, but it would be a value addition more to low end users or those who don't want to manage them otherwise.
What is Metadata API? What is DBMS_METADATA.GET_DDL package?
A new PL/SQL package, DBMS_METADATA.GET_DDL, allows you to obtain metadata (in the form of DDL used to create the object) about a schema object.
What are external tables? How to fetch flat file data from db without loading it?
Oracle9i allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. The CREATE TABLE ...ORGANIZATION EXTERNAL statement specifies metadata describing the external table.
What is a Server Parameter File?
Yes. In your CREATE TABLE or ALTER TABLE statement, USING INDEX clause allows you to specify this. Additionally, you can prevent the dropping of the index enforcing a unique or primary key constraint when the constraint is dropped or disabled.
What is a Server Parameter File?
9i introduces the persistence of INIT.ora across multiple shutdowns. This allows remote activities like startup without having a local copy of the INIT.ora. This is of great help in database performance tuning as parameter changes made by performance management tools (Oracle Enterprise Manager) and internal self-tuning now persist across shutdowns.
Can I assign a temporary table space to a database?
The new DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement allows you to create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace. The SYSTEM tablespace is no longer used as the default storage location for temporary data in such cases.
Can I set a time zone while creating a database? What is TIME_ZONE parameter in CREATE DATABASE command?
The CREATE DATABASE statement now has a SET TIME_ZONE clause that allows you to set the time zone of the database as a displacement from UTC (Coordinated Universal Time -- formerly Greenwich Mean Time). Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. Additionally, a new session parameter TIME_ZONE has been added to the SET clause of ALTER SESSION.
Can I name a transaction?
Oracle now allows you to assign a name to a transaction. The transaction name is helpful in resolving in-doubt distributed transactions, and replaces a COMMIT COMMENT.
Can I create templates for database creation from an existing database?
Yes, you can. The Oracle Database Configuration Assistant provides templates, which are saved definitions of databases, from which you can generate your database. Oracle also supports creation of templates by capturing the definition of an existing database which was not available until Oracle 9i.
How to monitor Index usage?
A MONITORING USAGE clause has been added for the ALTER INDEX statement. It allows you to monitor an index to determine if it is actively being used.
What is list partitioning? How is list partitioning useful? We had range and hash partitioning, right?
Oracle introduces list partitioning, which enables you to specify a list of discrete values for the partitioning column in the description for each partition. The list partitioning method is specifically designed for modeling data distributions that follow discrete values. This cannot be easily done by range or hash partitioning.
Can I partition an IOT using hash method?
Oracle 9i now supports partitioning of index-organized tables by the hash method.
What are dynamic job queue processes?
The job queue process creation has been made dynamic so that only the required numbers of processes are created to execute the jobs that are ready for execution. A job queue coordinator background process (CJQ) dynamically spawns Jnnn processes to execute jobs.
What is new in Database Resource Manager?
The following new functionality has been added to the Database Resource Manager:- Ability to create an active session pool. This pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will abort.
- Automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.
- Ability to prevent the execution of operations that are estimated to run for a longer time than a predefined limit
- Ability to create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.
Can I authenticate and enable a role to a user through middle tier? Can I enable application roles on the fly?
Oracle9i enables you to authorize a middle-tier server to act on behalf of a client. The GRANT CONNECT THROUGH clause of the ALTER USER statement specifies this functionality. You can also specify roles that the middle tier is permitted to activate when connecting as the client.Oracle provides a mechanism by which roles granted to application users are enabled using a designated PL/SQL package. This feature introduces the IDENTIFIED USING package clause for the CREATE ROLE statement.
What is fine grained auditing?
In Oracle’s traditional auditing methods, a fixed set of facts is recorded in the audit trail. Audit options can only be set to monitor access of objects or privileges. A new PL/SQL package, DBMS_FGA, allows applications to implement fine-grained auditing of data access based on content.
What is new in Oracle LogMiner?
LogMiner in Oracle9i provides comprehensive SQL based Log Analysis. LogMiner now supports index organized and clustered tables, chained rows, direct loads, scalar object types, LOB, LONG data types and DDLs.LogMiner also displays the primary key, and supports queries on the logs based on content of change (for example, show all changes to employee ‘John’).
Using LogMiner, the change history of a database can be queried from the logs. LogMiner now allows content based data filtering which enables change history to be queried at the row level.
What is a Flashback query? Can I scroll back in a result set based on time?
The flashback query feature of Oracle9i allows data to be queried from a point in the past. Users set the date and time that they would like to view, and then any SQL query that they execute will operate on data, as it existed at that point in time. This capability is available at the SQL level so that applications can invoke them like any other normal SQL operation, without needing the intervention of an administrator.
What is Oracle UltraSearch?
Oracle9i Ultra Search unifies search capabilities to search all kinds of content, including text and multimedia across heterogeneous sources. Ultra Search includes a web interface, web crawling and search administration facilities, as well as a programmable Java API, to provide a unified interface for enterprise and vertical portal search applications
What are Real Application Clusters? What is Cache Fusion?
Oracle9i Real Application Clusters enables scale the database tier horizontally as usage and demand continues to grow, without changes to the application and yet appear as a single system.Furthermore, Oracle9i Real Application Clusters dynamically shifts database resources across the cluster servers for optimal performance using a technology called Cache Fusion, which utilizes the collective caches of all the nodes in the cluster to satisfy database requests. Oracle9i Cache Fusion directly ships data blocks from one node’s cache to another node’s cache in read/read, read/write, and write/write contention situations which eliminates the latencies associated with disk based cache coordination.
What is Data Guard? What is zero data loss mode?
The existing (physical) standby product has been renamed as Data Guard in Oracle9i. Many of the tasks associated with managing a standby database are automated, including initial instantiation, failover, and graceful primary-to-secondary switch-over and switch back. Administrators can also optionally specify the log apply delay by which each standby site lags the production environment (for increased protection from human errors or corruption), and choose a zero data loss mode in which online redo log data is synchronously sent to the standby site.
What is new XML support in Oracle 9i?
Oracle9i features a number of enhanced database operations to store XML in the databases via SQL and render traditional database data as XML. Oracle9i has a built in XML Developer Kits (XDKs) and now supports native XML type.With the Java XML Developer Kit (XDK) pre-loaded, and the C XDK linked into Oracle9i, developers are able to generate, manipulate, render and store XML-formatted data.
In addition, for developers who have requirements to store and retrieve large amounts of complex XML through their content management applications, XML type support in Oracle9i provides XPATH navigation capabilities to optimize performance.
Does cost based optimizer consider CPU and memory as resources?
Cost based optimizer now considers the addition of memory and CPU costs in cost calculation algorithm.
Does Oracle 9i support VI protocol support? What is VI protocol?
VI is an emerging communication protocol for clustered server environments. Unlike TCP/IP, VI is a "thin" protocol specially designed for cluster environment, it places most of the messaging burden upon high-speed network hardware and frees the CPU for more important tasks.
Can I compile my PL/SQL programs into native binaries?
Yes. You can compile PL/SQL programs into native binaries.
What are new SQL & PLSQL features?
SQL Features include:- Inheritance, type evolution and dynamic method dispatch are supported in Oracle 9i.
- CASE WHEN statement, ANSI compliant joins, and reserved name versioning is supported. (New ANSI requirements).
- Scrolling cursor support to migrate from non-Oracle databases.
PL/SQL Features include:
- Native compilation.
- An integrated front-end for SQL compilation that supports all SQL syntax changes for SQL embedded in PL/SQL.
- Stored procedures written in 3GL languages (PL/SQL, Java, as well as external routines) can utilize SQL parallel query mechanism and can now pipeline (incrementally return) data to the calling SQL statement.
What is new in OLAP features?
Oracle9i introduces Oracle OLAP (based on Oracle Express Server), a scalable, high-performance OLAP calculation engine with fully integrated management and administration.Oracle OLAP offers:
- Complete OLAP reporting, analytical, and planning applications via the Oracle9i OLAP API.
- SQL OLAP functions.
- Oracle9i provides analysis capabilities directly against the relational database, thus eliminating the need to manage separate analytical database.
What is new in data warehousing/data mining features?
Oracle9i also includes new data mining engine (based on Oracle Darwin), which enables to incorporate accurate, real-time recommendations and personalization functionality into to your online operations. Oracle9i includes in-database scoring along with the ability to manage data mining operations.
Does Oracle 9i support pipelining in ETL operations?
Yes. Oracle 9i data load can include multi table insert and upsert semantics. Oracle9i provides support for external tables, that quickly load the data into your database and a new data capture facility will allow incremental changes from target sources to be captured and applied to the data warehouse automatically.
What new Java features?
For Java, improved garbage collection (using a new algorithm), better native compilation, increased object sharing, and session pinning have all improved the performance of applications built in Java executing inside the database. JDBC and SQLJ performance improvements have also improved the performance of Java in the middle tier or on the client.New JDBC features include NCHAR support for storing Unicode data, exposure of object type inheritance to Java developers, multi-level collections, etc. New SQLJ features include support for dynamic SQL statements in SQLJ, fetches from an untyped ResultSetIterator, optional direct generation of Oracle JDBC code. Java Stored procedures include support for returning multiple rows (REFCURSORS).
Can I resume backups?
Yes, Recovery Manager in Oracle9i enables one time backup configuration, automatic management of backups and archived logs based on a user specified recovery window, restartable backups and restores, and test restore/recovery. Recovery Manager implements a recovery window, a new policy to control when backups expire. The new controlfile autobackup feature also allows for restoring/recovering a database even when a Recovery Manager repository is not available.
What multilingual support does Oracle 9i have?
Oracle9i supports multilingual applications through Unicode 3.0 on database and middle-tier. Unicode 3.0 support includes the UTF-8 and UTF-16 encoding forms and surrogate support which means an additional 1 million characters can be supported.You can set up or migrate your entire database character set to UTF8 or UTF-16 or with the new Unicode data type, UTF-8 or UTF-16 can be used to define columns that support one or more new languages incrementally, for an existing monolingual database. All access programming interfaces to Oracle9i are enabled for both UTF-16 and UTF-8. Oracle Locale Builder, a new easy-to-use GUI tool, allows you to customize your own linguistic sorts provided by Oracle 9i.
Where can one get more information about 9iDB?
About the Authors
Harshvardhan Vyas is a database consultant. He is an Oracle Certified DBA and has interests in data modeling, database design, and performance tuning. He can be contacted at harshvardhan_vyas@yahoo.com. He is based in India.Frank Naudé is the pricipal editor of the Oracle FAQ (www.orafaq.net), and has written much of the content. He has 13 years of experience using Oracle, and is based in South Africa.
 |
 |