1.Different Methods for storing User Data
.Regular tables
Regular table is the most commonly used form of storing user data.
.Partitioned tables
.Index-organized tables

An index-organized table is like a heap table with a prmary key index on one or more if its columns.
However,insted of maintaining two separate storage spaces for the table and a B-tree index,an index-organized
table maintains a single B-tree containing the primary key of the table and other column values.An
overflow segment may exist due to the PCTTHRESHOLD value being set and the result of longer row lengths requiring
the overflow area.
Index-organized tables provide fast key-based access to table data for queries involving exact matches and range
searchs.
Also,storage requirements are reduced because key columns are not duplicated in the table and index.The remaining
non-kye columns are stored in the index unless the index entry becomes very large, in that case,the Oracle server
provides an OVERFLOW caluse to handel the proble.
.Clustered tables
A cluster is made up of a table or group tables that share the same data blocks,which are grouped together because
they share common columns and are often used together.

Note:Partitioned tables,index-organized tables,and clustered tables are covered in other courses

2.Oracle data types
Oracle Built-in Data Types
-Scalar Data Types
–Character Data
—-Fixed-length(CAHR ,NCHAR:with an upper limit of 2000 bytes)
These data types are stored with padded blanks.
—-Variable-length(VARCHAR2,NVARCHAR2:with an upper limit of 4000 bytes)

–Numeric Data
Numbers in an Oracle database are always stored as variable-length data.They can store up to 38 significant digits.Numeric data types requres:
- One byte for the exponent(指数)
- One byte for every tow significant digits(有效数) in the mantissa(位数)
- One byte for negative numbers if the number of signification digits is less than 38 bytes

–DATE Date Type
The Oracle server stores dates in fixed-length fields of seven bytes. An Oracle DATE always includes the time.

–TIMESTAMP Data Type
This data type stores the date and time including fractional seconds up to 9 decimal places.

–RAW Data Type

–Long,Long Raw and Large Object(LOBs) Data Types
Oracle provides six data types for storing LOBs:
- CLOB and LONG for large fixed-width character data
- NCLOB for large fixed-width national character set data
- BLOB and LONG RAW for storing unstructured data
- BFILE for storing unstructured data in operating system files

–ROWID and UROWID Data Type
ROWID is a data type that can be queried along with other columns in a table.It has the following characteristics:
.ROWID is unique identifier for each row in the database
.ROWID is not stored explicitly as a column value
.Although the ROWID dose not directlygive the physical address of a row,it can be used to locate the row
.ROWID provides he fastest means of accessing a row in a table
.ROWIDs are stored in indexes to specify rows with a given set of key values

With release 8.1, the Oracle server provides a single datatype called the universal rowid or
UROWID. It supports rowids of foreign tables (non-Oracle tables) and can store all kinds of
rowids. For example: A UROWID datatype is required to store a ROWID for rows stored in
an IOT. The value of the parameter COMPATIBLE must be set to 8.1 or higher to use
UROWID.

-Collections Data Types
–Varying Arrays(VARRAY)

–Nested Tables

–Relationship Data Types(REFs)

–Oracle User-Defined Data Types

3.ROWID Format

Extented ROWID Format

   32 bits         10 bits        22 bits       16 bits
|  O O O O O O |    F F F     |  B B B B B B  |   R R R   |
  Data object   Relative file   Block number   Row number
   number        number

An Extented ROWID needs 10 bytes fo storage on disk and is desplayed using 18 characters.
It is displayed using a base-64 encoding schema,The base-64 encoding scheme uses characters “A-Z(0-25)”,”a-z(26-51)”,”0-9(52-61)”,”+(62)”,”/(63)”
a total of 64 characters.
It consists of the following components:
.Data object number is assigned to each data object, such as table or index when it is created,and it is unique within the database.
.Relative file number is unique to eache file within a tablespace.
.Block number represcents the position of the block,containing the row, within the file.
.Row number identifies the position of the row directory slot in the block header.
Restricted ROWID Format

|   B B B B B B B B   |   R R R R   |    F F F F    |
     Block number       Row number     File number

Prior to Oracle8 used the restricted ROWID format.A restricted ROWID used only six bytes internally and did not contain the data object number.
This format was acceptable in Oracle7 or an earlier release because the file numbers were unique whitin a database.
Thus, earlier releases did not permit more than 1022 data files.Now it is the limit for a tablespace.
Even though Oracle8 removed this restriction by using tablespace-relative file numbers, the restricted ROWID is still used in objetslike nonpartitioned
indexes on nonpartitioned tables where all the index entries refer to rows within the same segment.

Note:The ROWID will change when you reorganize or export/import a table.

Some example for ROWID:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT t.rowid,t.* FROM hr.t_dump t;
 
ROWID                       A B
------------------ ---------- ----------
AAAM/E AAE AAAEsu AAA         20 hello
 
SELECT  object_name ,object_type 
  FROM  dba_objects 
 WHERE object_id=(SELECT 12*64*64+63*64+4 FROM dual);
 
SELECT object_name ,object_type 
  FROM dba_objects 
 WHERE object_id=dbms_rowid.rowid_object('AAAM/EAAEAAAEsuAAA');

4.Create Table

5.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
.DMLs do not generate redo logs
.Can create indexes,views, and triggers on temporary tables

Temporary tables can be created to hold session-privite data that exists only for the duration of a transaction or session.
The clauses that control the duration of the rows are:
. ON COMMIT DELETE ROWS to specify that rows are only visible within the transaction
eg:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SQL> INSERT INTO hr.temporary_employees SELECT * FROM hr.employees;
 
已创建107行。
 
SQL> SELECT count(*) FROM hr.temporary_employees;
 
  COUNT(*)
----------
       107
 
SQL> commit;
 
提交完成。
 
SQL> SELECT count(*) FROM hr.temporary_employees;
 
  COUNT(*)
----------
         0

.ON COMMIT PRESERVE ROWS to specify that rows are visible for the entire session

Note:
Export and Import utilities to export and import the definition of a temporary table. However,
no data is exported, even if you use the ROWS option. The definition of a temporary table is
visible to all sessions

6.Changing Storage Parameters
Syntax

ALTER TABLE [schema.]table
{[ storage-clause ]
[ INITRANS integer ]
[ MAXTRANS integer]

The Effects of Changing Storage Parameters
The parameters that can be modified and the implications of the modifications are as follows:
* NEXT: When the Oracle server allocates another extent for the table, the new value
will be used. Subsequent extent sizes will increase by PCTINCREASE}

* PCTINCREASE: A change in PCTINCREASE will be registered in the data
dictionary. It will be used to recalculate NEXT when the next extent is allocated by the
Oracle server. Consider a case where a table with two extents has NEXT=10K and
PCTINCREASE=0. If PCTINCREASE is changed to 100, the third extent to be
allocated will be 10K, the fourth extent will be 20K, the fifth extent will be 40K, and so
on.
* MINEXTENTS: The value of MINEXTENTS can be changed to any value that is less
than or equal to the current number of extents in the table. It will have no immediate
effect on the table, but will be used if the table is truncated.
* MAXEXTENTS: The value of MAXEXTENTS can be set to any value equal to or
greater than the current number of extents for the table. The value can also be set to
UNLIMITED.
Restictions
* The value of INITIAL cannot be modified for a table.
* The value of NEXT specified will be rounded to a value that is a multiple of the block
size greater than or equal to the value specified.

7.Manually Allocating Extents
Extents may need to be allocated manually:
* To control the distribution of extents of a table across files
* Before loading data in bulk to avoid dynamic extension of tables

Syntax

1
2
3

ALTER TABLE [schema.]TABLE
ALLOCATE EXTENT [ ([SIZE integer [K|M]]
[ DATAFILE ‘filename’ ]) ]

If SIZE is omitted, the Oracle server will use the NEXT_EXTENT size from DBA_TABLES
to allocate the extent.
The file specified in the DATAFILE clause must belong to the tablespace that the table
belongs to. Otherwise, the statement will generate an error. If the DATAFILE clause is not
used, the Oracle server will allocate the extent in one of the files in the tablespace containing
the table.
Note: The NEXT_EXTENT value in DBA_TABLES will not be affected by manual extent
allocation. The Oracle server will not recalculate the size of the next extent when this
command is executed.

8.Nonpartitioned Table Reorganization

A nonpartitioned table can be moved without having to run the Export or Import utility. In
addition, it allows the storage parameters to be changed. This is useful when:
* Moving a table from one tablespace to another
* Reorganizing the table to eliminate row migration
After moving a table you will have to rebuild the indexes

9.Truncating a Table

1
2

TRUNCATE TABLE [schema.] TABLE
[{DROP | REUSE} STORAGE]

The effects of using this command are as follows:
* All rows in the table are deleted.
* No undo data is generated and the command commits implicitly because TRUNCATE
TABLE is a DDL command.
* Corresponding indexes are also truncated.
* A table that is being referenced by a foreign key cannot be truncated.
* The delete triggers do not fire when this command is used.

10.Drop a Table

Syntax
Use the following command to drop a table:

1
2

DROP TABLE [schema.] TABLE
[CASCADE CONSTRAINTS]

When a table is dropped, the extents used by the table are released. If they are contiguous,
they may be coalesced either automatically or manually at a later stage.
The CASCADE CONSTRAINTS option is necessary if the table is the parent table in a
foreign key relationship.

11.Drop a Column

1
2
3

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.
*Before Oracle8i, it was not possible to drop a column from a table.

Using a Checkpoint When Dropping a Column
Dropping a column can be time consuming and require a large amount of undo space. While
dropping columns from large tables, checkpoints can be specified to minimize the use of
undo space. In the example in the slide, a checkpoint occurs every 1,000 rows. The table is
marked INVALID until the operation completes. If the instance fails during the operation,
the table remains INVALID on start up, and the operation will have to be completed.
Use the following statement to resume an interrupted drop operation:

1
2

ALTER TABLE hr.employees
DROP COLUMNS CONTINUE;

Use of this will generate an error if the table is in a VALID state.

12.Using the UNUSED Option
* Mark a column as unused
Unused columns act as if they are not part of the table. Queries cannot see data from unused
columns. In addition, the names and data types of those columns are not displayed when a
DESCRIBE command is executed. A user can add a new column with the same name as an
unused column.
When dropping two columns all rows in the table are updated
twice, by setting the columns to unused and then drop the columns the rows will only be
updated once.

1
2

ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS;

*Drop unused columns

1
2

ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;

* Continue to drop column operation

1
2

ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;

Identifying Tables with Unused Columns
To identify tables with unused columns, you can query the view DBA_UNUSED_COL_TABS.

To identify tables that have partially completed DROP COLUMN operations the
DBA_PARTIAL_DROP_TABS view can be queried.

13.Restrictions on Dropping a Column
You cannot do the following:
* Drop a column from an object type table
* Drop columns from nested tables
* Drop all columns in a table
* Drop a partitioning key column
* Drop a column from tables owned by SYS
* Drop a parent key column
* Drop a column from an index-organized table if the column is a primary key
* A LONG or LONG RAW column that is unused but not dropped will prevent an add of a
LONG or LONG RAW column to the table. (Even though a describe of the table appears
to show no LONG or LONG RAW column.

Popularity: 37% [?]