managing tables n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Managing Tables PowerPoint Presentation
Download Presentation
Managing Tables

Loading in 2 Seconds...

play fullscreen
1 / 22

Managing Tables - PowerPoint PPT Presentation


  • 64 Views
  • Uploaded on

Managing Tables. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Managing Tables' - aria


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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

Partitionedtable

Index-organizedtable

Cluster

oracle built in data types
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

rowid format

.

.

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

structure of a row
Structure of a Row

Row header

Column length

Database block

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 * 100100 -PCTFREE- Available Data Space

Setting PCTFREE and PCTUSED
  • Compute PCTFREE
  • Compute PCTUSED

(Average Row Size - Initial Row Size) * 100

Average Row Size

row migration and chaining
Row Migration and Chaining

Before update

After update

Pointer

changing storage and block utilization parameters
ALTER TABLE hr.employees

PCTFREE 30

PCTUSED 50

STORAGE(NEXT 500K

MINEXTENTS 2

MAXEXTENTS 100);

Changing Storage and Block Utilization Parameters
manually allocating extents
ALTER TABLE hr.employees

ALLOCATE EXTENT(SIZE 500K

DATAFILE ‘/DISK3/DATA01.DBF’);

Manually Allocating Extents
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