These are my answers to frequently asked technical questions that I have received either in person or via email or have seen on newsgroups.
NOTE:
The text on this page is not intended for publication anywhere other than this page of kevinloney.com. Some of the material here may eventually form the basis of free articles that can be published in your newsletters, but you should consider the material on this page to be rough drafts.
1. I have a table whose name contains lowercase letters. How did that happen, and how to I select from it? Are there any other potential problems with using tables like this?
2. Oracle seems to have ignored my hint. Why did it do that?
6. Should I use the AUTOEXTEND feature for datafiles, and if so when?
7. How do I reset the value of a sequence back to an earlier value?
8. Are there any Year 2000 problems with Oracle products?
11. Is the rule-based optimizer (RBO) going away?
12. How can I generate the autotrace output from PLAN_TABLE?
13. How do I use the "multiple buffer pools" feature of Oracle8?
14. What is the NOLOGGING option of Oracle8?
A: If you use double quotes in your create table commands, you can use almost anything - even reserved words - as column names. For example, you could create a table named DATE. You can also use the double quote syntax to create tables with mixed case names. For example:
create table "EmPlOyEe" ...
Now, just because you can do this doesn't make it a good idea. You'll have a couple of significant problems if you use mixed case names or reserved words in your object names. First, you'll have to use double quotes whenever you refer to the table, and you'll have to get the case exactly right:
select 'x' from "EmPlOyEe";
select 'x' from employee;
The second query won't work because Oracle will change the table name to uppercase when interpreting that table name and look for values in TAB$ that match it, and turn up none. You can see if you have mixed case table names by selecting all your table names from DBA_TABLES and checking their case. If you have a lot of tables, you could automate this by using TRANSLATE to change all lowercase letters to a value (like #) and using INSTR to determine if an INSTR search for # within the TRANSLATEd values returns a value greater than 0.
Why should you care? If you have a table named EmPlOyEe, you can also have a table named EMPLOYEE owned by the same user - and Oracle will always use EMPLOYEE unless you put quotes around the table name - so there is a possibility that you will be querying the wrong table!
select * from EmPlOyEe;
If you have an ad hoc query tool that shows all the tables you can select from, you will be shown both tables. If the tool modifies the table names prior to displaying them (Q+E used to show them all lowercase) then you'll have two identical entries in the list and no idea which is the one you want.
The other problem with using quotes in your DDL is that if you create an object using a reserved word, then Oracle will accept that value:
create table "DATE" (x CHAR);
You can do the same thing with your column names, and it's a bad idea. Any query of reserved word objects that does not use double quotes around the reserved word will fail.
Objects with these names are usually created by third-party tools that are designed to work with multiple RDBMSs. PowerBuilder is one example of this - the DDL it creates puts double quotes around everything. There's no problem with that as long as you make sure the case is consistently uppercase and no reserved words are used.
The short answer is "I don't know." But I can guess pretty well.
Hints are embedded within comments, so if there is anything wrong with the syntax you use, Oracle ignores the hint and treats it as a comment. No error is returned by a disregarded hint. If you use a hint, follow these rules:
This rule helps you all the time, and is required when you execute a query that selects from two identically named tables. For example, you could select
from JOE.Employee, MARY.Employee
but your hint syntax needs to reference only one object without the owner name. So you'll need to give an alias to the tables and use that alias in your hint:
from JOE.Employee JE, MARY.Employee ME
Using simple aliases also helps to minimize the errors due to typos in hints.
Don't add three hints at one time. Add them one at a time and verify the impact of each on the explain plan. Adding hints one at a time will help you to debug any problems you encounter.
Whenever you can, use no more than one hint. The more hints you use, the more likely you are to give the optimizer contradictory hints. The point of hints is not to give the optimizer more choices - it already has lots of choices. Hints allow you to influence the optimizer's decision-making process. Decide what path you want to try and hint accordingly.
After doing all that, the optimizer may still ignore your hint and choose a path it prefers. As Eyal Aronoff says, if they were supposed to work all the time, they'd be called "commands", not "hints".
If you don't have enough disks to properly separate your database files, you will be making performance trade-offs as you try to minimize contention among your database files. Here are the design rules I use when designing a layout for a small database system:
The system is usually an OLTP system. A small system can't hold a data warehouse, so you should approach the system from an OLTP design standpoint. I would assume that transactions tend to be small in size, large in number and variety, and fairly randomly scattered among the available tables. In such a system, I would design the application to be as index-intensive as possible, with a minimum number of full table scans performed.
Try to isolate the SYSTEM tablespace. A simple query of the database performs lots of queries of the data dictionary. For example, consider a query like:
select Code from CODES_TABLE where Description = 'Widget';
For an OLTP system, that's a common style of query - the application may have many different simple queries. Each time the user executes that query, Oracle needs to check:
etc. If you've every traced a query, you've seen the dictionary hits.
Because DDL can happen at any time, Oracle keeps re-checking this information. That leads to an artificially elevated data block buffer cache hit ratio, but it also adds to the number of queries performed against the data dictionary. In an OLTP system, isolate your SYSTEM tablespace as much as possible - it will account for around 40% of your I/O.
Isolate your index tablespace. This step depends on your success in tuning the application. In an OLTP system, your index tablespace accesses should account for at least 35% of the I/O in the system. If they do, then isolate the index tablespace as much as possible. If you have a low hit ratio, or you can't predict the user's access paths, then prioritize the isolation of the data tablespace over that of the index tablespace.
If necessary, separate the rollback segments and data tablespaces. Now we're into trade-off mode, because that makes four tablespaces and only 3 disks. You'll need to monitor the system to determine where to put the rollback segments tablespace. If you have a high volume of transactions, you'll need to keep the rollback segments apart from the data tablespace. If the volume of transactions is low, then you can store the rollback segments on the same disk as the data tablespace with little contention:
Disk 1: SYSTEM tablespace, control file, redo log
Disk 2: INDEX tablespace, control file, redo log, ROLLBACK tablespace
Disk 3: DATA tablespace, control file, redo log
or
Disk 1: SYSTEM tablespace, control file, redo log
Disk 2: INDEX tablespace, control file, redo log
Disk 3: DATA tablespace, control file, redo log, ROLLBACK tablespace
If neither of those is a good fit, you can split the rollbacks across Disk 2 and Disk 3.
In systems of this size, you can usually put TEMP on the same disk as INDEX with little contention, and the TOOLS tablespaces can go on Disk 1. As always, you must monitor your I/O balancing after you've implemented the database to make sure your configuration properly addresses the data usage in the database.
Not in my database you don't. I'm not sure how the idea of storing non-relational data like Word documents in a relational database started, but I wish it would stop. From an architecture standpoint, it makes no sense at all to me. A word processing file should be stored at the operating system level - that's what operating systems are meant for. Use the relational database to store relational data about the file, and point to the external file location. If you're using Oracle8, you can use the BFILE datatype to point to an external binary file.
Think about it. Say you store a small Word file (20KB) with every record in your database. Your database block size is 4KB. There is no way to store that record without chaining 6 blocks together (accounting for block overhead, pctfree, etc.). Chaining is a bad idea because if one of the columns' values is updated so that it no longer fits in the space available, the row has to be migrated somewhere else and Oracle has to manage all the spanning information. You can use LOB storage to manage this in Oracle8, but in Oracle7 you have to use LONGs.
Does that sound like a good idea to anyone? Then you've never seen "column slide" first-hand.
Back in Oracle6, I supported an application that used SQL*TextRetrieval, the ancestor of Oracle ConText. In TextRetrieval (v1), you stored the text in LONG datatypes in the database. For example, you'd store the document number as a NUMBER column, the document date as a DATE column, and the abstract as a LONG. TextRetrieval let you perform basic document indexing and word searching on the text.
Problem was, you had to be able to update records. And when you updated records that spanned blocks with LONG datatypes, you very rarely encountered a condition called "column slide". This would occur about once every six months. Here's what it looked like:
Say you have your table, Document_ID NUMBER, Document_Date DATE, Abstract LONG.
In column slide, one of the column values does not exist for one record, and the other columns' data is moved up. So in one record, you'll have no Document_ID value, you'll have the Document_Date interpreted as the Document_ID value, and the Abstract interpreted as the Document_Date value. All the other rows will have the proper structure.
Are we having fun yet?
Export wouldn't pick this up as an error, but you know Import failed when it tried to put that record in. Queries of that record would fail too. Working around this problem led to a paper I wrote with Matt Reagan, "Editing Corrupted Export Files: When Import Fails" we delivered at IOUG in 1992.
Now, I'm sure Oracle has resolved this sort of problem (it's been six years and many major releases since then, after all, and they're now advertising Oracle8i as a way to store all your files in a database) but after the second time I hit this problem in a calendar year, I chose the following strategy. I strongly encourage people who want to store operating system files in a database to consider this approach:
1. Never store records more than one database block in length in database.
2. To help achieve (1), use a large database block size.
3. Avoid the use of LONGs unless absolutely necessary. (You can't select them across a link, you can's do an insert as select, you can't do functions like LENGTH on them, etc.)
4. Store relational data in relational databases. Store huge attributes outside the database and point to them via file names and identifiers.
Maybe the Oracle8i features will make this strategy obsolete, but it's worked so far. If you choose a different strategy, good luck.
Once you have added a datafile to a database, it is part of the database as long as the tablespace it belongs to is part of the database. You cannot remove a datafile from a database in a simple fashion. Even if the datafile is empty, Oracle will expect it to be available, and you will not be able to shutdown and restart the database unless all of the datafiles exist.
There are several ways to get rid of a datafile, and all involve dropping the tablespace involved.
A. The Easy One.
If the entire tablespace contains no data, the process is fairly simple.
1. Drop the tablespace
2. Delete the tablespace's datafiles.
3. If needed, re-create the tablespace with different (or fewer) datafiles.
This sort of file re-org is commonly performed on tablespaces dedicated to temporary segments.
B. The Almost As Easy One
If the tablespace contains data that can be re-created from existing data (such as indexes or summary tables) then you can:
1. Export the data in the tablespace.
2. Follow steps A1 to A3 above.
3. Re-create the data via Import, or, in the case of summary data, via the SQL used to create the data in the first place.
C. The Harder One
If the tablespace contains data that you can't re-create, you'll need to follow the steps in B, but you'll have to be extremely careful. For example, the tablespace may contain a table, and the table's index may be in a separate tablespace. You'll need to be aware of the space usage for that index and make sure that re-creating the objects does not cause a problem. You may wish to re-build multiple tablespaces at once, in which case you need to first check DBA_SEGMENTS to make sure you know exactly what objects are stored in the tablespaces.
When you use Export, you can change the storage parameters of a table by setting the COMPRESS flag to Y. Oracle will generate the CREATE command for the table such that all of the allocated extents of the table will, when Imported, go into a single extent. If your datafiles are not sized properly (or the free space is fragmented) then Import may not be able to allocate the required extent. To get around that problem, you will need to manually create the table with acceptable extent sizes or use COMPRESS=N.
D. The Even Harder One
If the tablespace contains rollback segments, then you'll have to take the active rollback segments offline and bring online rollback segments in a different tablespace prior to dropping and re-creating the tablespace. After you re-create the tablespace, you'll need to re-execute the CREATE ROLLBACK SEGMENT commands.
E. The Impossible One
Once you've added a datafile to your SYSTEM tablespace, it is there for the life of the database.
Most people try to delete datafiles because they've hit the MAXDATAFILES setting in their control files. That setting defaults to 30; you can override it by issuing a CREATE CONTROLFILE command with the new setting. To generate that command, use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command. The ALTER DATABASE command will generate a trace file in your dump directory that contains the current CREATE CONTROLFILE command. You can edit that script prior to executing it. Prior to executing that command, you should shutdown your database, rename your existing controlfiles (as a backup), and mount the database. When you execute the script, Oracle will then create the new controlfiles with the new MAXDATAFILES setting and you will be able to create new datafiles in your database.
As of Oracle7.2, you can set your datafiles to AUTOEXTEND. For each datafile, you can specify the size of new extents to allocate to the file and maximum size the file can reach. Although this seems like a widely useful feature, I advise against using it (except in the most extraordinary circumstances) for the following reasons:
The latter option allows you to control your file sizes, distribute I/O costs for the table, and manage the table's data more effectively. Sound desirable?
Lastly, AUTOEXTEND is a zero-administration feature. Zero-administration means no DBAs needed. And no DBAs is a Bad Thing.
Seriously, think about how this feature came into Oracle in the first place. Oracle had introduced Oracle7 for Windows, and it was basically a straight port of Oracle to a PC platform. Every other PC database was basically zero-administration. For an Oracle database on the PC, users had to know about tablespaces, quotas, datafiles, etc. If you wanted to create an application for disconnected PC users, then you had to make the database simple to administer. AUTOEXTEND answered some of these problems, since you could eliminate file limitations from the list of database features you needed to administer.
But is zero-administration of a production, shared database a good thing? It certainly has a benefit, but consider the costs listed in #1 and #2 above - it can potentially cause your backups to fail and stop your database. If DBAs can't guarantee that backups are reliable and the database is available, then all the certifications in the world won't help them keep the job.
Update: Talk about timing. The day after I wrote this answer, I got a call saying that a datafile in a database had taken itself offline. The only time I had heard of that happening before occurred when AUTOEXTEND first came out - an early adopter told me that when they tried to use it, the files took themselves offline and therefore required recovery. The DBA having troubles this day said that the datafile in question had been in AUTOEXTEND mode. A call to Oracle Support confirmed that in the particular version in use (7.3.3.1), there was a problem with AUTOEXTEND or any datafile resizes if you have DB_WRITERS >1.
In this case, the DBA was lucky - it was an index tablespace, in a database using archivelog so he was able to re-create the data that had been in the datafile. So much for Zero Administration.
7. How do I reset the value of a sequence back to an earlier value?
Once you select a value from a sequence, the sequence is incremented. If you need to reset the sequence back to an earlier setting for its value, there is a simple work-around.
First, assume that the sequence (MYSEQ) is incremented by 1 each time:
select MYSEQ.Nextval from DUAL;
If you select a value from that sequence, then you cannot retrieve that value again. If, for example, you test your application by selecting from the sequence 100 times, then the sequence will be incremented by 100. What if you then want to reset the sequence value? Rather than dropping and recreating the sequence, you can simply alter it to force a negative increment to be used:
alter sequence MYSEQ increment by -100;
Then select from the sequence once:
select MYSEQ.Nextval from DUAL;
then set the increment back to its prior setting:
alter sequence MYSEQ increment by 1;
Oracle doesn't always document its support for negative numbers within operators. For example, the SUBSTR function supports negative numbers for the starting location of the string - SUBSTR counts back from the end of the string. Thus, selecting SUBSTR(columnname, -2) should return the last two characters of the string, regardless of its length. INSTR has similar support for negative values.
8. Are there any Year 2000 problems with Oracle products?
Let me start by saying that I am not aware of any major Year 2000 problem in any Oracle product that will be supported as of 1/1/2000.
That being said, however, there are some customers who will continue to use unsupported Oracle software after 1/1/2000. Oracle usually gives its customers 18 months from the release of a new major version to migrate to that version. Oracle8 was announced in June 97, so there should be no one on Oracle7 still - right?
Wrong. Oracle8 adoption moved slowly, and with all the money and effort going into Year 2000 compliance and testing efforts, upgrading the backend database version didn't always get done. As a result, there will be databases running on Oracle7 well into the Year 2000. If you support one of them, you may encounter one of the following Year 2000 errors:
Source: Metalink, Doc ID: Note:67423.1
"Some V$ tables have VARCHAR2 columns which include date related information. Eg: V$LOG.FIRST_TIME V$LOG_HISTORY.TIME This does not cause any operational problems but may affect any scripts which use information in these views.
The VARCHAR2 dates in the columns of redo log related views behave thus:
years 80-99 represent years 1980 to 1999 respectively
years 00-79 represent yeats 2000 to 2079 respectively"
I haven't seen anyone write custom scripts around those views. Chances are this change mostly affects third-party resellers.
"Prior to 7.3.4.0 the 'CC' date format gave the wrong century for the year 2000. It should give 20 and only show 21 for years 2001 to 3000 inclusive. All Oracle8 and 8i releases give the correct century"
Upgrade to the supported version and the problem goes away.
"Oracle8 only: The EXPIRY_DATE column of DBA_USERS and USER_USERS may show an incorrect date if PASSWORD_LIFE_TIME makes the expiry date in or beyond the year 2000."
Sounds like a simple work-around. Since 8.1.5 has just been released, I'd expect this bug to be resolved shortly.
"Enterprise Backup Utility (EBU) year 2000 problem. EBU reports errors if run in the year 2000 or later."
The EBU only supports Oracle7 databases. Oracle8 databases use RMAN instead. Upgrade to the supported version and the problem goes away.
"Oracle7 Export only: Cumulative Exports may not include the correct tables for the first cumulative export in the year 2000."
That's not really surprising. As documented at http://www.adelante.u-net.com/frpage4.htm , an Oracle user previously reported that the catexp.sql script for Oracle7 forces the use of only two-digit years. As noted on that page, the problem is resolved in Oracle8.
For all but the expiry date problem, upgrading to the current supported version of Oracle will resolve these issues.
When you create a table, you specify a value for the pctfree parameter. If you don't specify a value for that parameter, then Oracle will use the default pctfree setting for the tablespace. If you use a pctfree setting of 10, then 10 per cent of the space in the block will not be available for new row inserts. Instead, it will be kept free to handle the expansion of existing rows in the block.
For example, suppose you insert a new row in a block. When you later update that row (for example, by updating NULL values to text values), Oracle tries to store the new data with the rest of the row, in the same block. To keep the data in the same block, the update may use the pctfree area in the block. Thus, if you actively update your records, you will need to have a high pctfree setting to avoid forcing Oracle to chain or migrate rows during updates. The more you update your data, the more of the pctfree space will be used.
When you perform an Export, Oracle exports the table definition, and then the table's rows. During an Import, Oracle creates the table and then performs sequential inserts of the rows. So during the Import for the example table, Oracle sets pctfree to 10 for the table and then begins inserting new rows. Since inserts don't use the pctfree area, the Imported table will require as much as 10 per cent more space than the Exported table. At the completion of the Import, the pctfree area in the new table will be empty, while it was not empty in the Exported table. That difference in internal space usage can cause the table to acquire new data blocks and extents, increasing its space requirements.
Sorting the data in your tables can improve the performance of range scans of that data. For example, if you frequently perform queries such as:
select * from EMP
where Name like 'M%';
then your query performance will benefit if all of the rows in the EMP table are stored sorted by their Name values. Since all of the 'M' names would be stored together, Oracle will have fewer data blocks to retrieve from the table during the query.
If you can identify columns commonly used in range scans, and if the data is fairly static, your queries may benefit from pre-sorting your data. If the data in your table is volatile, the performance benefit will degrade as the data values in your table become more dispersed.
The problem is that you cannot create a table with an order by clause. You can't create table as select with an order by, and you can't insert as select with an order by. There are two solutions available. The first (and more difficult) solution is to write all of the table's data to a flat file, sort the flat file, and reload the data. The second method relies on a feature of Oracle's group by operation.
When you perform a group by operation, Oracle sorts the resulting groups. You can use that sorting action to pre-sort the data during a create table as select command. For this example, assume that the EMP table has columns called Name, ID, and HireDate. Create a view that encompasses all of the columns plus the RowNum pseudocolumn:
create view EMPGROUP as
select Name, ID, HireDate, RowNum
from EMP
group by Name, ID, HireDate, RowNum;
Now, create a table selecting all but the RowNum column from that view:
create table SORTED_EMP
as select Name, ID, HireDate from EMPGROUP;
Note: In Oracle7.2 and above, you can use an inline view in place of the EMPGROUP view.
Your data will now be sorted by Name in the SORTED_EMP table. Note that the create table as select command does not create any constraints, indexes, grants, or triggers on the new table.
In the select clause, the RowNum column was specified. If you leave out the RowNum column, then duplicate rows from the EMP table will be eliminated by the group by operation.
For further information on intra-block data management (and the full discussion that this example is based on), see Chapter 5 of ORACLE8 Advanced Tuning and Administration.
11. Is the rule-based optimizer (RBO) going away?
Eventually - and new options in Oracle8i make it likely that the RBO will soon go away.
Rather than asking when the RBO is going away, the better question is why it stayed around so long. Oracle Financials still uses the RBO as does the Designer/2000 repository, and the Oracle data dictionary uses it too. Why haven't those applications been migrated to the cost-based optimizer (CBO)? Because it's difficult to base an application on the CBO unless the database is properly maintained. Using the CBO requires frequent analysis of tables and their data distribution, and that, in turn, requires that the DBA have the time (and temporary segment space!) available to effectively analyze the database on a regular basis. Otherwise, the statistics will be out of sync with the data.
To improve the CBO performance, Oracle has introduced two significant features in Oracle8, the second of which spells the end of the RBO. First, Oracle8 lets you create partitions - and you can analyze a partition of a table instead of the whole table. You can use partitions to significantly reduce the amount of time and resources required to analyze your tables. Second, Oracle8i introduces "stored outlines" that enable you to migrate a fixed set of hints with your queries as you move your data from Development to Production.
Now, if I were a product manager of a tool that previously relied on RBO, I'd be actively migrating my tool to CBO with stored outlines. That way, when I sent out a new version of the tool, I'd also send my customers the execution paths for all of the queries. I wouldn't need to rely on the RBO's rules anymore, and I'd have more flexibility in tuning the application's queries.
So while the RBO is still supported (there's even a RULE hint), I believe that stored outlines will be the death knell for the RBO. If you are not already using the CBO, start working with it as you migrate to Oracle8 and Oracle8i.
12. How can I generate the autotrace output from PLAN_TABLE?
A search of the net found only the traditional query of PLAN_TABLE. If you want the output shown by autotrace, try this query instead:
select ID ID_plus_exp,
Parent_ID parent_id_plus_exp,
LPAD(' ',2*(level-1))|| /* Indent for the level */
Operation|| /* The operation */
DECODE(other_tag,null,'','*')|| /* will display an '*' if parallel */
DECODE(options,null,'',' ('||options||')')|| /* display the options */
DECODE(object_name,null,'',' of '''||object_name||'''')||
DECODE(object_type,null,'',' '||object_type||')')||
DECODE(id,0,decode(optimizer,null,'',' optimizer='||optimizer))||
DECODE(cost,null,'',' (cost='||cost|| /* display cost info. */
DECODE(cardinality,null,'',' card='||cardinality)|| /* cardinality */
DECODE(bytes,null,'',' bytes='||bytes)||')') plan_plus_exp,
object_node object_node_plus_exp /* parallel and remote info */
from PLAN_TABLE
start with ID=0 and Statement_ID='TEST'
connect by prior ID=Parent_ID and Statement_ID='TEST'
order by ID,Position;
Source: ORACLE8 Advanced Tuning and Administration, Oracle Press, Osborne/McGraw-Hill.
13. How do I use the "multiple buffer pools" feature of Oracle8?
You can create multiple buffer pools within your SGA. You can use multiple buffer pools to separate large datasets from the rest of your application, reducing the likelihood they will contend for the same resources within the data block buffer cache. For each buffer pool you create, you need to specify its size and its number of LRU latches. The number of buffers must be at least 50 times greater than the number of LRU latches.
When creating buffer pools, you need to specify the size of the keep area and the size of the recycle area. Like the reserved area of the Shared SQL Pool, the keep area retains entries, while the recycle area is more frequently recycled. You can specify the size of the keep area via the BUFFER_POOL_KEEP parameter, as shown in the following listing:
BUFFER_POOL_KEEP=(buffers:200, lru_latches:3)
BUFFER_POOL_RECYCLE=(buffers:50, lru_latches:1)
The sizes of the keep and recycle buffer pools reduce the available space in the data block buffer cache (set via the DB_BLOCK_BUFFERS parameter). For a table to use one of the new buffer pools, specify the name of the buffer pool via the buffer_pool parameter within the table’s storage clause. For example, if you want a table to be quickly removed from memory, assign it to the RECYCLE pool. The default pool is named DEFAULT, so you can use the alter table command to redirect a table to the DEFAULT pool at a later date.
Source: ORACLE8i DBA Handbook
14. What is the NOLOGGING option of Oracle8?
From ORACLE8i DBA Handbook:
<begin excerpt>
When ORACLE7.2 was released, a new keyword was introduced to the create table as select and create index commands: unrecoverable. The unrecoverable keyword eliminated the writing of online redo log file entries during the execution of the command. As of ORACLE8, the keyword unrecoverable has been replaced with the more widely applicable nologging keyword.
When you create a table using the nologging keyword, the transactions that initially populate the table are not written to the online redo log. Additionally, any subsequent SQL*Loader Direct Path loads and any insert commands using the APPEND hint will not write redo log entries to the online redo log files. Thus, you can target specific tables (such as your large business transaction tables or the aggregation tables) for no logging. Being able to avoid writing these transactions to the online redo log files allows you to keep the full database in ARCHIVELOG state while the largest tables are essentially in NOARCHIVELOG state. You can also specify nologging for the LOB portions of tables that use BLOB or CLOB datatypes.
<end excerpt>
Of course, you need to be careful that you can recover (via other means) the transactions you are not logging. For example, you may need to keep data load files around, or re-execute insert as select commands following a recovery. Also, if you're relying on a standby database or other log-based replication, you'll need to find a way to move the nologged transactions to the replica. If you tell Oracle not to log the transactions, they won't be logged - and thus can't be recovered via traditional archive log recovery.
The material provided in this section may not be used in books or in sold items. No warranty regarding the uses of this material for any purpose is either expressed or implied. The legal disclaimer from the front page of this web site applies to all materials provided here.