Books > Les11_rev2
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Books > Les11_rev2 as PDF for free.
More details
- Words: 1,001
- Pages: 40
11 Managing Tables
Copyright © Oracle Corporation, 2002. All rights reserved.
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, and drop a table • Drop a column within a table 11-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Storing User Data
11-3
Regular table
Partitioned table
Index-organized table
Cluster
Copyright © Oracle Corporation, 2002. All rights reserved.
11-4
Copyright © Oracle Corporation, 2002. All rights reserved.
11-5
Copyright © Oracle Corporation, 2002. All rights reserved.
Oracle Built-in Data Types Data type User-defined
Built-in
Scalar Collection CHAR(N), NCHAR(N) VARCHAR2(N), VARRAY NVARCHAR2(N) NUMBER(P,S) TABLE DATE TIMESTAMP RAW(N) BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID, UROWID
11-6
Relationship
Copyright © Oracle Corporation, 2002. All rights reserved.
REF
11-7
Copyright © Oracle Corporation, 2002. All rights reserved.
11-8
Copyright © Oracle Corporation, 2002. All rights reserved.
11-9
Copyright © Oracle Corporation, 2002. All rights reserved.
ROWID Format • Extended ROWID Format OOOOOO
FFF
BBBBBB
RRR
Data object number
Relative file number
Block number
Row number
• Restricted ROWID Format BBBBBBBB Block number
11-10
.
RRRR Row number
.
FFFF File number
Copyright © Oracle Corporation, 2002. All rights reserved.
11-11
Copyright © Oracle Corporation, 2002. All rights reserved.
Structure of a Row
Row header Database block
Column length Column value
11-12
Copyright © Oracle Corporation, 2002. All rights reserved.
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)) TABLESPACE USERS;
11-13
Copyright © Oracle Corporation, 2002. All rights reserved.
11-14
Copyright © Oracle Corporation, 2002. All rights reserved.
11-15
Copyright © Oracle Corporation, 2002. All rights reserved.
11-16
Copyright © Oracle Corporation, 2002. All rights reserved.
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.
11-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Creating Temporary Tables • Created using the GLOBAL TEMPORARY clause: CREATE GLOBAL TEMPORARY TABLE hr.employees_temp AS SELECT * FROM hr.employees;
• Tables retain data only for the duration of a transaction or session. • DML locks are not acquired on the data. • You can create indexes, views, and triggers on temporary tables.
11-18
Copyright © Oracle Corporation, 2002. All rights reserved.
Setting PCTFREE and PCTUSED • Compute PCTFREE (Average Row Size - Initial Row Size) * 100 Average Row Size
• Compute PCTUSED Average Row Size * 100 100 - PCTFREE -
11-19
Available Data Space
Copyright © Oracle Corporation, 2002. All rights reserved.
Row Migration and Chaining Before update
After update
Pointer
11-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Changing Storage and Block Utilization Parameters ALTER TABLE hr.employees PCTFREE 30 PCTUSED 50 STORAGE(NEXT 500K MINEXTENTS 2 MAXEXTENTS 100);
11-21
Copyright © Oracle Corporation, 2002. All rights reserved.
11-22
Copyright © Oracle Corporation, 2002. All rights reserved.
11-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Manually Allocating Extents
ALTER TABLE hr.employees ALLOCATE EXTENT(SIZE 500K DATAFILE ‘/DISK3/DATA01.DBF’);
11-24
Copyright © Oracle Corporation, 2002. All rights reserved.
Nonpartitioned Table Reorganization
ALTER TABLE hr.employees MOVE TABLESPACE data1;
• When a nonpartitioned table is reorganized, its structure is kept, but not its contents. • It is used to move a table to a different tablespace or reorganize extents.
11-25
Copyright © Oracle Corporation, 2002. All rights reserved.
Truncating a Table
TRUNCATE TABLE hr.employees;
• Truncating a table deletes all rows in a table and releases used space. • Corresponding indexes are truncated.
11-26
Copyright © Oracle Corporation, 2002. All rights reserved.
Dropping a Table
DROP TABLE hr.departments CASCADE CONSTRAINTS;
11-27
Copyright © Oracle Corporation, 2002. All rights reserved.
11-28
Copyright © Oracle Corporation, 2002. All rights reserved.
Dropping a Column Removing a column from a table: ALTER TABLE hr.employees DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOINT 1000;
• 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.
11-29
Copyright © Oracle Corporation, 2002. All rights reserved.
11-30
Copyright © Oracle Corporation, 2002. All rights reserved.
Renaming a Column Renaming a column from a table: ALTER TABLE hr.employees RENAME COLUMN hire_date TO start_date;
11-31
Copyright © Oracle Corporation, 2002. All rights reserved.
11-32
Copyright © Oracle Corporation, 2002. All rights reserved.
Using the UNUSED Option • Mark a column as unused: ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
• Drop unused columns: ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;
• Continue to drop column operation: ALTER TABLE hr.employees DROP COLUMNS CONTINUE CHECKPOINT 1000;
11-33
Copyright © Oracle Corporation, 2002. All rights reserved.
11-34
Copyright © Oracle Corporation, 2002. All rights reserved.
Obtaining Table Information Information about tables can be obtained by querying the following views: • DBA_TABLES • DBA_OBJECTS
11-35
Copyright © Oracle Corporation, 2002. All rights reserved.
11-36
Copyright © Oracle Corporation, 2002. All rights reserved.
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 • Obtaining table information
11-37
Copyright © Oracle Corporation, 2002. All rights reserved.
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
11-38
Copyright © Oracle Corporation, 2002. All rights reserved.
11-39
Copyright © Oracle Corporation, 2002. All rights reserved.
11-40
Copyright © Oracle Corporation, 2002. All rights reserved.
Copyright © Oracle Corporation, 2002. All rights reserved.
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, and drop a table • Drop a column within a table 11-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Storing User Data
11-3
Regular table
Partitioned table
Index-organized table
Cluster
Copyright © Oracle Corporation, 2002. All rights reserved.
11-4
Copyright © Oracle Corporation, 2002. All rights reserved.
11-5
Copyright © Oracle Corporation, 2002. All rights reserved.
Oracle Built-in Data Types Data type User-defined
Built-in
Scalar Collection CHAR(N), NCHAR(N) VARCHAR2(N), VARRAY NVARCHAR2(N) NUMBER(P,S) TABLE DATE TIMESTAMP RAW(N) BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID, UROWID
11-6
Relationship
Copyright © Oracle Corporation, 2002. All rights reserved.
REF
11-7
Copyright © Oracle Corporation, 2002. All rights reserved.
11-8
Copyright © Oracle Corporation, 2002. All rights reserved.
11-9
Copyright © Oracle Corporation, 2002. All rights reserved.
ROWID Format • Extended ROWID Format OOOOOO
FFF
BBBBBB
RRR
Data object number
Relative file number
Block number
Row number
• Restricted ROWID Format BBBBBBBB Block number
11-10
.
RRRR Row number
.
FFFF File number
Copyright © Oracle Corporation, 2002. All rights reserved.
11-11
Copyright © Oracle Corporation, 2002. All rights reserved.
Structure of a Row
Row header Database block
Column length Column value
11-12
Copyright © Oracle Corporation, 2002. All rights reserved.
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)) TABLESPACE USERS;
11-13
Copyright © Oracle Corporation, 2002. All rights reserved.
11-14
Copyright © Oracle Corporation, 2002. All rights reserved.
11-15
Copyright © Oracle Corporation, 2002. All rights reserved.
11-16
Copyright © Oracle Corporation, 2002. All rights reserved.
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.
11-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Creating Temporary Tables • Created using the GLOBAL TEMPORARY clause: CREATE GLOBAL TEMPORARY TABLE hr.employees_temp AS SELECT * FROM hr.employees;
• Tables retain data only for the duration of a transaction or session. • DML locks are not acquired on the data. • You can create indexes, views, and triggers on temporary tables.
11-18
Copyright © Oracle Corporation, 2002. All rights reserved.
Setting PCTFREE and PCTUSED • Compute PCTFREE (Average Row Size - Initial Row Size) * 100 Average Row Size
• Compute PCTUSED Average Row Size * 100 100 - PCTFREE -
11-19
Available Data Space
Copyright © Oracle Corporation, 2002. All rights reserved.
Row Migration and Chaining Before update
After update
Pointer
11-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Changing Storage and Block Utilization Parameters ALTER TABLE hr.employees PCTFREE 30 PCTUSED 50 STORAGE(NEXT 500K MINEXTENTS 2 MAXEXTENTS 100);
11-21
Copyright © Oracle Corporation, 2002. All rights reserved.
11-22
Copyright © Oracle Corporation, 2002. All rights reserved.
11-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Manually Allocating Extents
ALTER TABLE hr.employees ALLOCATE EXTENT(SIZE 500K DATAFILE ‘/DISK3/DATA01.DBF’);
11-24
Copyright © Oracle Corporation, 2002. All rights reserved.
Nonpartitioned Table Reorganization
ALTER TABLE hr.employees MOVE TABLESPACE data1;
• When a nonpartitioned table is reorganized, its structure is kept, but not its contents. • It is used to move a table to a different tablespace or reorganize extents.
11-25
Copyright © Oracle Corporation, 2002. All rights reserved.
Truncating a Table
TRUNCATE TABLE hr.employees;
• Truncating a table deletes all rows in a table and releases used space. • Corresponding indexes are truncated.
11-26
Copyright © Oracle Corporation, 2002. All rights reserved.
Dropping a Table
DROP TABLE hr.departments CASCADE CONSTRAINTS;
11-27
Copyright © Oracle Corporation, 2002. All rights reserved.
11-28
Copyright © Oracle Corporation, 2002. All rights reserved.
Dropping a Column Removing a column from a table: ALTER TABLE hr.employees DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOINT 1000;
• 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.
11-29
Copyright © Oracle Corporation, 2002. All rights reserved.
11-30
Copyright © Oracle Corporation, 2002. All rights reserved.
Renaming a Column Renaming a column from a table: ALTER TABLE hr.employees RENAME COLUMN hire_date TO start_date;
11-31
Copyright © Oracle Corporation, 2002. All rights reserved.
11-32
Copyright © Oracle Corporation, 2002. All rights reserved.
Using the UNUSED Option • Mark a column as unused: ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
• Drop unused columns: ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;
• Continue to drop column operation: ALTER TABLE hr.employees DROP COLUMNS CONTINUE CHECKPOINT 1000;
11-33
Copyright © Oracle Corporation, 2002. All rights reserved.
11-34
Copyright © Oracle Corporation, 2002. All rights reserved.
Obtaining Table Information Information about tables can be obtained by querying the following views: • DBA_TABLES • DBA_OBJECTS
11-35
Copyright © Oracle Corporation, 2002. All rights reserved.
11-36
Copyright © Oracle Corporation, 2002. All rights reserved.
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 • Obtaining table information
11-37
Copyright © Oracle Corporation, 2002. All rights reserved.
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
11-38
Copyright © Oracle Corporation, 2002. All rights reserved.
11-39
Copyright © Oracle Corporation, 2002. All rights reserved.
11-40
Copyright © Oracle Corporation, 2002. All rights reserved.
Related Documents
Books > Les06_rev2
November 2019 0
Books > Les16_rev2
November 2019 1
Books > Les04_rev2
November 2019 1
Books > Les10_rev2
November 2019 1
Books > Les11_rev2
November 2019 1