Miscellaneous Manageability Features

Miscellaneous Manageability Features

New Database Limits
Maximum database size: 512 petabytes (petabyte is 2 to the 50th power of bytes)
Maximum number of tablespaces: ~2 billion
Maximum number of datafiles per tablespace: 1022
Maximum number of partitions per table or index: 64,000
Maximum number of columns per table: 1000
Maximum columns per index: 32
New maximum size for CHAR: 2000 bytes
New maximum size for VARCHAR2: 4000 bytes
NCHAR and NVARCHAR2 are new multibyte national language support character equivalents of CHAR and VARCHAR2 and store the same amount of data respectively.
Size of Oracle 8 extended ROWID: 10 bytes
Size of Oracle 7 restricted ROWID: 6 bytes
Size of BLOB (binary large object type): 4GB
Size of CLOB (character large object type): 4GB
Size of NCLOB (NLS character large object type): 4GB

Relocate and Reorg tables
You can use the ALTER TABLE statement to reorganize extents or move a table to a different tablespace.
Can move (relocate) a table from one tablespace to another using the following command:
ALTER TABLE table_name MOVE TABLESPACE target_tablespace_name;
Documentation says that any associated indexes, constraints and grants in the original tablespace should also be moved. May require up to twice the amount of space currently being consumed by the table and its related objects.
You can not perform any DML (insert, update, or delete) operations on a table that is in the process of being moved
Can not relocate a partitioned table.
Use the following command to “rebuild” an Index-Organized Table:
ALTER TABLE iot_table_name MOVE INITRANS 10;
Can be performed online for non-partitioned IOT’s if there are no overflow data segments with following command:
ALTER TABLE docindex MOVE ONLINE INITRANS 10;
With an overflow segment use:
ALTER TABLE iot_table_name MOVE TABLESPACE iot_index_tbs OVERFLOW TABLESPACE iot_over-flow_tbs;

Remove Unused Columns From a Table
Use the ALTER TABLE ...DROP COLUMN command to remove a column from a table. ALTER TABLE table_name DROP COLUMN column_name;
dropping multiple columns at once:
ALTER TABLE table_name DROP (col1, col2, ... coln);
If the columns to be dropped are part of unique constraints or primary keys use the CASCADE CONSTRAINTS option on the ALTER TABLE command. This will drop any referential integrity constraints referring to a primary or foreign key defined on the column.
Use the DROP COLUMN INVALIDATE command to remove the column and any views, triggers, or stored programs that are dependent on the column.
Can’t drop a pseudo-column, nested table type of column, or a partition key column.
Can add a column to a table with the same name as the dropped column name.
The DROP COLUMN option actually removes the column from the table description (DESCRIPTORS), removes the column data and its length usage from each row of the table and frees up space in the data block. Can be slow on large tables.
Can use the ALTER TABLE...SET UNUSED option to mark a column as unused. Data becomes unavailable, but still exists in the rows of the table. Much faster!
Example:
ALTER TABLE table_name SET UNUSED COLUMN column_name;
To drop an “unused” column at a later time use the following command:
ALTER TABLE table_name DROP UNUSED COLUMNS;
During an IMPORT, any columns marked as UNUSED are removed and not included in the imported table. During an EXPORT, fields marked as unused are ignored.
Related data dictionary tables:
ALL_, DBA_, and USER_UNUSED_COL_TABS - Shows tables with columns that have been marked as unused.

Define Temporary Tables
Temp tables hold data only for the length of a transaction or session.
No re-do is generated for temp tables so they are not protected by the redo log.
Views, Indexes and Triggers can be created based upon temp tables.
You can export a temp table, but it will contain only the table structure...NO ROWS.
Temp tables store their data in the session’s sort space. If more space is required, the temporary tablespace of the USER is used.
You can query the DBA_TABLES view to determine if a table is a temporary table. It contains a DURATION column that reflects the length of temp table existence.
Examples:
Create temp table to last duration of session:
CREATE GLOBAL TEMPORARY TABLE table_name....ON COMMIT PRESERVE ROWS;
Create temp table to last duration of transaction:
CREATE GLOBAL TEMPORARY TABLE table_name....ON COMMIT DELETE ROWS;
Create temp table as a clone of another table:
CREATE GLOBAL TEMPORARY TABLE table_name AS SELECT ...

SQL*Loader Enhancements
Can load objects, collections and LOB’s.
Removal of the 64K physical record limit.
New FILLER keyword for filler fields.
New VARCHARC, VARRAW, LONG VARRAW and VARRAWC datatypes that function similar to VARCHAR.
Can specify a customer record separator.
Can AND together DEFAULTIF and NULLIF predicates.
Can specify a field delimiter that is longer than one character.

Export and Import Utility Enhancements
Can use a query in EXPORT to unload tables.
Can specify multiple dump files and work around the previous 2GB export file limit.
Can export tables with objects and LOB’s, even in direct mode.
Can export and import precalculated optimizer stats without the need to recompute stats during import, for selected exports and tables.
More control over the validation of type object identifiers during import.

Monitor Long-running Operations
Reference V$FILESTAT and correlate I/O activity to the EXPLAIN plan is one way to follow progress.