1 / 22

Mastering Table Management in Oracle Database: Methods, Types, and Structures

Learn the fundamentals of managing tables in Oracle, including data storage methods, data types, row IDs, table creation, storage structure management, reorganization, and more.

mshead
Download Presentation

Mastering Table Management in Oracle Database: Methods, Types, and Structures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing Tables

  2. 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

  3. Storing User Data Regular table Partitionedtable Index-organizedtable Cluster

  4. Oracle Built-in Data Types Data type User-defined Built-in Scalar Collection Relationship 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

  5. . . BBBBBBBB RRRR FFFF Block number Row number File number ROWID Format • Extended ROWID Format • Restricted ROWID Format OOOOOO FFF BBBBBB RRR Data object number Relative file number Block number Row number

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

  7. 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) );

  8. 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.

  9. 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;

  10. Average Row Size * 100100 -PCTFREE- Available Data Space Setting PCTFREE and PCTUSED • Compute PCTFREE • Compute PCTUSED (Average Row Size - Initial Row Size) * 100 Average Row Size

  11. Row Migration and Chaining Before update After update Pointer

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

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

  14. 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;

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

  16. Dropping a Table DROP TABLE hr.department CASCADE CONSTRAINTS;

  17. 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;

  18. 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;

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

  20. 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

  21. 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

More Related