You will have to examine how the question is worded. By default 11g will create all three tablespaces. You can however create a database with only SYSTEM and SYSAUX. Technically speaking you can upgrade pre-10g database and not add SYSAUX, or remove the SYSAUX tablespace after the database is created and therefore have a database with only a SYSTEM tablespace, but you would be ill advised to do so and I really don't think they would look for that in an answer.
From the 11.2 Administration Guide:
The SYSAUX tablespace is always created at database creation.
From another section of the Administration Guide:
An auto-extending undo tablespace named UNDOTBS1 is automatically
created when you create the database with Database Configuration
Assistant (DBCA)...
If no undo tablespace is available, then the
instance starts without an undo tablespace, and undo data is written
to the SYSTEM tablespace. You should avoid running in this mode.
From the 10.2 Concepts Guide:
...the SYSAUX tablespace is always created during database creation or
database upgrade...
During normal database operation, the Oracle database server does not
allow the SYSAUX tablespace to be dropped or renamed. Transportable
tablespaces for SYSAUX is not supported.
Note: If the SYSAUX tablespace is unavailable, such as due to a media
failure, then some database features might fail.
From the 10.1 SQL Reference:
You cannot drop the SYSTEM tablespace. You can drop the SYSAUX
tablespace only if you have the SYSDBA system privilege and you have
started the database in MIGRATE mode.
From the 10.1 Administrators Guide:
If the SYSAUX tablespace becomes unavailable, core database
functionality will remain operational. The database features that use
the SYSAUX tablespace could fail, or function with limited capability.
http://oradbpedia.com/wiki/The_SYSAUX_Tablespace has this take:
The SYSAUX tablespace is a new tablespace that is required in Oracle
Database 10g...
When you migrate to Oracle Database 10g, the SYSAUX tablespace needs
to be created as a part of that migration. This is done after mounting
the database under the new Oracle Database 10g database software. Once
you have mounted it, open the database in migrate mode with the
startup migrate command. After the database is open, you can create
the SYSAUX tablespace.
Note: The loss of the SYSAUX tablespace is not fatal to your database.
In our testing it appears that the only real impact is that certain
functionality related to the occupants of the SYSAUX tablespace is
lost.
Earlier versions did not have a SYSAUX tablespace:
SYSAUX is the name of the compulsory tablespace, introduced in Oracle
10g.
A few more days of reading and experimentation and I was able to (mostly) answer a lot of these:
I found this buried in the ODP.NET documentation (ironically not in the OracleBulkCopy
docs):
The ODP.NET Bulk Copy feature uses a direct path load approach, which is similar to,
but not the same as Oracle SQL*Loader. Using direct path load is faster than conventional loading (using conventional SQL INSERT
statements).
So it appears that it does use direct path.
This I was able to verify by doing a large bulk copy operation and getting the index properties from SQL Developer. The index did appear as UNUSABLE
while the bulk copy was in progress. However, I've also discovered that OracleBulkCopy.WriteToServer
will refuse to run if the index starts in an UNUSABLE
state, so clearly there's more going on here, because if it were as simple as disabling and rebuilding the index then it shouldn't care about initial state.
It does make a difference specifically if the index is also a constraint. Found this little gem in the documentation linked above:
Enabled Constraints
During an Oracle bulk copy, the following constraints are automatically enabled by
default:
NOT NULL
UNIQUE
PRIMARY KEY
(unique-constraints on not-null columns)
NOT NULL
constraints are checked at column array build time. Any row that violates the NOT NULL
constraint is rejected.
UNIQUE
constraints are verified when indexes are rebuilt at the end of the load. The index is left in an Index Unusable state if it violates a UNIQUE
constraint.
The documentation is a little hazy on what happens during the load, especially with primary keys, but one thing is absolutely certain - it behaves differently with a primary key vs. without one. Since the OracleBulkCopy
will happily allow you violate index constraints (and punt the index into UNUSABLE
state when it's done), my hunch is that it's building the PK index during the bulk copy but simply not validating it until afterward.
I'm not sure whether the difference observed is within Oracle itself or just a quirk of the OracleBulkCopy
. The jury's still out on this one.
OracleBulkCopy
will throw an exception if an index is initially in the UNUSABLE
state, so it's really a moot point.
If there are other factors, indexes (and especially PK indexes) are still the most important, as I found out by:
Creating a global temporary table with the same schema (using CREATE AS
), then bulk copying into the temporary table, and finally doing a plain old INSERT
from the temp table into the real table. Since the temp table has no index, the bulk copy happens very fast, and the final INSERT
is also fast because the data is already in a table (I haven't tried the append hint yet, since a 5M row table-to-table copy already takes less than 1 minute).
I'm not yet sure of the potential ramifications of (ab)using the temporary table space this way, but so far it hasn't given me any trouble, and it's much safer than the alternative by way of preventing corruption of either the rows or indexes.
The success of this also pretty clearly demonstrates that the PK index is the problem, as that is the only practical difference between the temp table and the permanent table - both started with zero rows during the performance tests.
Conclusion: Don't bother trying to bulk copy more than around 100k rows into an indexed Oracle table using ODP.NET. Either drop the index (if you don't actually need it) or "preload" the data into a different (non-indexed) table.
Best Answer
The file is: