Note that you can still tune individual parameters if you should wish to do so. You can move the entire table, specific partitions or any number of combinations of operations during the redefinition. Other operations might include renaming columns, moving the table to a new tablespace, converting the table to an IOT, or into a partitioned table and so on.
There are a number of rules and limitation with regards to online table redefinition, as you might expect. There are a number of other administrative features that are new to Oracle9i. You can now instruct Oracle to remove database datafiles when dropping a tablespace by using the new including contents and datafiles clause of the drop tablespace command.
Oracle9i now also gives the Remote DBA the option to prohibit any nologging operation on the database with the new force logging clause of the alter database or create database command. This is a great option to have if you are administering a stand-by database environment.
If you have ever had your database crash on you in the middle of a hot backup, you know what a pain it can be to get all of the database datafiles out of hot backup mode. Oracle9i comes to the rescue with the alter database end backup command. Now just one command will take all the database datafiles out of hot backup mode.
This is facilitated through the alter table command. Be careful of the additional disk space requirements that accompany this conversion. You will need about twice the space of the original long to perform the conversion.
Another nice new feature is that sys truly becomes a privileged administrative account. Prior to 9i, the sys account could not grant direct access rights to objects it did not own, unless the owner of the object gave sys the rights to do so. This is no longer the case in Oracle9i. Now sys can grant and revoke access to any object in the database at will. This new feature is closely is associated with the new Oracle9i privilege, grant any object which can be granted to any user to allow them to administer grants throughout the database.
One final thing you might be interested in is that the system tablespace can now be locally managed in Oracle9i Release 2. In fact, if you use the database creation assistant in 9iR2, it will create the system tablespace as a locally managed tablespace by default.
Note that if you make the system tablespace locally managed that no other tablespaces in the database will be able to be dictionary managed. Oracle9i comes with some new partitioning options that you will want to know about. First, Oracle9i introduces a new type of partitioning called list partitioning. With list partitioning you can define a list of values associated with a partition key column, and assign those values to a specific partition.
For example, if you had a retail operation that operated in all fifty states and you often did lookups on your customers by state, you might want to use list partitioning to partition your customer information by state.
Oracle9i Release two builds on the list partitioning feature by allowing you to build range partitioned tables that are sub-partitioned using the list partition method. Also in 9iR2, Oracle has made some changes to split partition operations that make partition split operations more efficient.
In previous versions Oracle tracked block availability with freelists that kept track of all blocks that were available to write to. The method of tracking free space in a segment could be contentious at times, and cause performance problems. While freelist space management is still available and is the default setting , Automatic Segment Space Management ASSM is a new feature in Oracle9i that is designed to simplify free space management of segments and reduce the contention that can accompany the use of freelists.
To create a tablespace that will use ASSM, you use the segment space management auto parameter of the create tablespace command. When you create a segment in a tablespace using ASSM, Oracle will create a series of bitmap blocks known as bitmapped blocks BMBS that will be stored in the segment being created typically at the beginning, but other BMBS can be added anywhere in the segment as needed. ASSM leads to better performance in many cases, and particularly if your segments contain rows that vary in size.
Should you use ASSM? Oracle9i has changed the rules when it comes to indexes. Oracle9i now can perform an index skip scan operation. The skip scan operation allows the Oracle optimizer to consider any column within an index for an index scan operation, even if that column is not on the leading edge of the index. This new feature has several impacts that you will want to consider. First, it means that when you are migrating that you might find your SQL queries getting different execution plans and hopefully running faster because of this.
This also means that the execution plan of some of your hand crafted and hinted SQL queries might change as well. So, test carefully and make sure that this feature will not have a negative impact on your database. Oracle9i offers some new indexing options that can help you to improve performance of your database. A bitmap join index creates an index that is, for all practical purposes, a pre-join of columns from two or more different tables represented in the form of a bitmap. If you have table joins that involve columns with a relatively small number of distinct values, then a bitmap join index might work for you.
Oracle9i now allows you to create bitmap indexes on index-organized tables IOT. To create a bitmap index on an IOT you must first create a mapping table on the table. You create a mapping table when you create the IOT by using the mapping table clause of the create table command. Note that you cannot add a mapping table to an existing IOT.
One of the main purposes of mapping tables is to support another Oracle9i new feature. You can create multiple bitmap indexes on a single IOT in 9i, all of which will be supported by the single mapping table. Oracle9i has added additional index functionality features. These new features include:. The monitoring of an index is binary in nature.
That is to say, you only know if the index have been used, but not how many times it has been used or how recently. Oracle9i now allows you to access external files from within the database directly through the use of the new external table feature.
An external table is defined within the database, and points to a physical datafile that is present on the server where the database operates. You create an external table with the create table command, using the new organization external clause. Note that you cannot currently create any indexes on external tables. If you wish to remove an existing external table, just drop it with the drop table command. Query rewrite depends on the definition of constraints between related tables to work properly.
This has been a problem in the past because you could not create constraints on views, thus if a view were built on a dimension or fact table, and that view was used in a SQL statement, Oracle could not take advantage of query rewrite and thus, perhaps, take advantage of a materialized view. To solve this problem, Oracle9i introduces view constraints.
You can now define primary key, unique key and foreign keys on a view when you issue the create view command. Alternatively you can add constraints to the view through the alter view command. Often source data is destined to more than one table.
This results in additional unnecessary IO to the source table in order to populate the different tables. Oracle9i introduces multi-table insert statements. Multi-table inserts come in 3 different forms:. Oracle9i now allows you to index on a varachr data type using the index by varchar2 n option.
Case statements do not return a value whereas case expressions do return a value. Both types of the case commands are available in two flavors: simple or searched. A simple case command evaluates only a single value whereas a searched case value can evaluate multiple values. The new merge statement is designed for just such a situation.
These new operators are:. Sometimes explain plan output can be wrong. Not often, but it can happen. Every Remote DBA has his own script to format the results of the plan table. Streams - Based on Advanced Queuing and LogMiner, Oracle Streams form a distributed messaging technology that can be used for a variety of tasks including messaging, replication and ETL processes.
Oracle9i 9. Try these methods. Java Server Pages - Use Java as a scripting language to interact with the database from web pages.
Stateless Locking Methods - Learn how to avoid data loss in stateless environments. Complete Data Audit - A simple and generic solution for auditing before and after snapshots of data. Compressed Tables - Compress whole tables or individual table partitions to reduce disk space requirements.
Index Key Compression - Reduce the size of indexes by using index key compression, including advanced index compression in Oracle 12c. Java Native Compilation - Improve the performance of Java procedural code by compiling it to native shared libraries. Mutating Table Exceptions - A simple method to prevent triggers producing mutating table exceptions.
Oracle Label Security - Configure row-level security with this out-of-the-box solution. Oracle 9i. Upcoming SlideShare. Like this presentation? Why not share! Embed Size px. Start on. Show related SlideShares at end. WordPress Shortcode.
Next SlideShares. Oracle 9i Dba Fundamentals. Sql oracle. Oracle 10g Introduction 1. Oracle Architecture. Project Undertaken In college. Related Books Free with a 30 day trial from Scribd.
Related Audiobooks Free with a 30 day trial from Scribd. Mohammed mazin , IT Specialist at al-mayaar at al-mayaar. Chhavi Arya at Student.
0コメント