Oracle Interview Preparation Notes Part 2

Tablespace and Data Files:

Locally Managed Tables
· Use bitmaps versus data dictionary => Less contention on dictionary .
· No rollback is generated (no dictionary table updates).
· No coalescing is needed (automatic tracking of adjacent free space).

CREATE TABLESPACE … EXTENT MANAGEMENT LOCAL
{ AUTOALLOCATE | UNIFORM SIZE 1 M }

Autoallocate – tablespace extents are system managed
Uniform Size – all extents are same size, should be equal to a multiple of SORT_AREA_SIZE

Temporary Tablespaces

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘file’ SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
Tempfile = Not recorded in the control file, always NOLOGGING, not recovered, cannot be renamed.

ALTER TABLESPACE tsname OFFLINE NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVER
Temporary – checkpoint for online data files only (offline files may need recovery)
For Recover – for tablespace point in time recovery.

ALTER TABLESPACE tsname READ ONLY can now be done while transactions are running

DBA_TABLESPACES, V$TABLESPACE, DBA_DATA_FILES, V$DATAFILE
DBA_TEMP_FILES, V$TEMPFILE

Storage Structures:

PCTFREE (10) = % of space reserved in data block for row growth (inserts until pctfree reached).
PCTUSED (40) = minimum % of used space before block is put back on free list (reused).

DBA_SEGMENTS (for extent summaries) DBA_EXTENTS (for extent detail)

Rollback Segments:

· Store location of data and the data as it existed before being modified.
· Create 1 rollback segment for every 4 OLTP concurrent transactions.
· Create 1 large rollback for every large batch transaction.
· Increase minextents to reduce overhead of repeated extensions.
· PRIVATE is the default, PUBLIC is used for OPS (parallel server).

Read consistency…
· For SQL statements, read consistency is always provided for SQL
· For transactions, you must SET TRANSACTION READ ONLY or SERIALIZABLE

SHRINK = deallocation of an extent
WRAP = write to next extent
EXTEND = Allocation of new extent (if not already allocated)

ORA-1555 = Snapshot Too Old – before image of a committed transaction is overwritten but needed by a reader. To remedy, Increase minextents or the size of the extents or the optimal setting.

Managing Tables:

Collection = objects that contain objects:
VARRAY = ordered set of data elements of the same type
NESTED TABLE = table as a column in the parent table

CREATE TABLE table (column Datatype, (…)) TABLSPACE tsname [PCTFREE x] [PCTUSED y]
[INITRANS x] [MAXTRANS y] { Storage Clause }
[LOGGING | NOLOGGING] [CACHE|NOCACHE]
NoCache – Full table scans blocks are places at LRU end of the LRU list.
NoLogging – creation of table and certain data loads are not recorded in redo logs.

If minextents > 1 and more than 1 datafile exists, Oracle will spread extents across the datafiles.

Temporary Tables: hold session private data.
CREATE GLOBAL TEMPORARY TABLE x as select *…ON COMMIT [DELETE|PRESERVE] ROWS
Delete rows – keep rows for life of transaction only.
Preserve rows – keep rows for the entire session.

Migrated Row = When an updated row can’t fit into block, it is moved to another block (a pointer remains). Increase PCTFREE to minimize migration.
Chained Row = A row cannot fit into 1 block and is divided into chunks and stored in more than 1 block.
Increase block size or split table.

ALTER TABLE ALLOCATE EXTENT (Size Datafile) – use to preallocate to avoid extensions in a load.
ALTER TABLE emp MOVE TABLESPACE name – moves to new segment preserving indexes and privs.
You still must rebuild the indexes (invalidated).

Highwater Mark = Mark the last used block, Not reset after deletes - table scans always scan to the HWM.
Use the DBMS_SPACE package to determine HWM
ALTER TABLE emp DEALLOCATE UNUSED [KEEP 1M] - deallocate space above HWM except 1M.
If minextents < KEEP value, then Deallocates only to minextents.
TRUNCATE TABLE emp DROP | REUSE STORAGE. Resets the HWM to first block, no rollback.
Reuse does not deallocate extents.

ALTER TABLE DROP COLUMN c CHECKPOINT 1000 Drops column (removes data),
checkpoint every 1000 rows. Table is marked as INVALID until drop is completed.
ALTER TABLE t SET UNUSED COLUMN c Marked as unused to be deleted later by…
ALTER TABLE t DROP UNUSED COLUMNS;