DBAI_les11_Rev1_1

Managing Tables : 

Managing Tables

Objectives : 

Objectives After completing this lesson, you should be able to do the following: Identify the various methods of storing data Outline Oracle data types Distinguish between an extended versus a restricted ROWID Outline the structure of a row Create regular and temporary tables Manage storage structures within a table Reorganize, truncate, drop a table Drop a column within a table

Storing User Data : 

Storing User Data Regular table Cluster Partitionedtable Index-organizedtable

Oracle Built-in Data Types : 

Oracle Built-in Data Types CHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N) NUMBER(P,S) DATETIMESTAMP RAW(N)BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID, UROWID VARRAYTABLE REF Data type Built-in User-defined Scalar Relationship Collection

ROWID Format : 

ROWID Format Extended ROWID Format Restricted ROWID Format OOOOOO BBBBBB FFF RRR Data object number Relative file number Row number Block number

Structure of a Row : 

Structure of a Row Database block Row header Column length Column value

Creating a Table : 

Creating a Table CREATE TABLE hr.employees( employee_id NUMBER(6), first_name VARCHAR2(20), last_name VARCHAR2(25), email VARCHAR2(25), phone_number VARCHAR2(20), hire_date DATE DEFAULT SYSDATE, job_id VARCHAR2(10), salary NUMBER(8,2), commission_pct NUMBER (2,2), manager_id NUMBER(6), department_id NUMBER(4) );

Creating a Table: Guidelines : 

Creating a Table: Guidelines Place tables in separate tablespaces. Use locally-managed tablespaces to avoid fragmentation. Use few standard extent sizes for tables to reduce tablespace fragmentation.

Creating Temporary Tables : 

Creating Temporary Tables Created using the GLOBAL TEMPORARY clause Tables retain data only for the duration of a transaction or session DML locks are not acquired on the data Can create indexes, views, and triggers on temporary tables CREATE GLOBAL TEMPORARY TABLE hr.employees_temp AS SELECT * FROM hr.employees;

Setting PCTFREE and PCTUSED : 

(Average Row Size - Initial Row Size) * 100 Average Row Size Setting PCTFREE and PCTUSED Compute PCTFREE Compute PCTUSED

Row Migration and Chaining : 

Before update After update Pointer Row Migration and Chaining

Changing Storage and Block Utilization Parameters : 

Changing Storage and Block Utilization Parameters ALTER TABLE hr.employees PCTFREE 30 PCTUSED 50 STORAGE(NEXT 500K MINEXTENTS 2 MAXEXTENTS 100);

Manually Allocating Extents : 

Manually Allocating Extents ALTER TABLE hr.employees ALLOCATE EXTENT(SIZE 500K DATAFILE ‘/DISK3/DATA01.DBF’);

Nonpartitioned Table Reorganization : 

Nonpartitioned Table Reorganization When a nonpartitioned table is reorganized, its structure is kept, but not its contents. Used to move a table to a different tablespace or reorganize extents. ALTER TABLE hr.employees MOVE TABLESPACE data1;

Truncating a Table : 

Truncating a Table Truncating a table deletes all rows in a table and releases used space. Corresponding indexes are truncated. TRUNCATE TABLE hr.employees;

Dropping a Table : 

Dropping a Table DROP TABLE hr.department CASCADE CONSTRAINTS;

Dropping a Column : 

Dropping a Column Removing a column from a table: Removes the column length and data from each row, freeing space in the data block. Dropping a column in a large table takes a considerable amount of time. ALTER TABLE hr.employees DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOINT 1000;

Using the UNUSED Option : 

Using the UNUSED Option Mark a column as unused: Drop unused columns: Continue to drop column operation: ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS; ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000; ALTER TABLE hr.employees DROP COLUMNS CONTINUE CHECKPOINT 1000;

Obtaining Table Information : 

Obtaining Table Information Information about tables can be obtained by querying the following views: DBA_TABLES DBA_OBJECTS

Summary : 

Summary In this lesson, you should have learned how to: Distinguish between an extended versus a restricted ROWID Outline the structure of a row Create regular and temporary tables Manage storage structures within a table Reorganize, truncate, and drop a table Drop a column within a table

Practice 11 Overview : 

Practice 11 Overview This practice covers the following topics: Creating a table Viewing, marking as unused, and dropping columns within a table Allocating extents manually Truncating a table Obtaining table information