Oracle Tables

卫子平

2023-12-01

General

Table Related Data Dictionary Objects

tab$
col$

DBA	ALL	USER
dba_col_comments	all_col_comments	user_col_comments
dba_external_tables	all_external_tables	user_external_tables
dba_external _locations	all_external _locations	user_external _locations
dba_partial_drop _tabs	all_partial_drop _tabs	user_partial_drop _tabs
dba_tables	all_tables	user_tables
dba_tab_cols	all_tab_cols	user_tab_cols
dba_tab_columns	all_tab_columns	user_tab_columns
dba_tab_col_ statistics	all_tab_col _statistics	user_tab_col _statistics
dba_tab_comments	all_tab_comments	user_tab_comments
dba_tab_histograms	all_tab_histograms	user_tab_histograms
dba_tab _modifications	all_tab _modifications	user_tab _modifications
dba_tab_privs	all_tab_privs	user_tab_privs
dba_tab_statistics	all_tab_statistics	user_tab_statistics
dba_tab_stats _history	all_tab_stats _history	user_tab_stats _history
dba_unused_col_tabs	all_unused_col_tabs	user_unused_col_tabs

Table Related System Privileges

alter any table	flashback any table
backup any table	insert any table
create any table	lock any table
comment any table	select any table
create table	under any table
delete any table	update any table
drop any table

Types Of Tables

Table Type	Description
Heap	Default Oracle table type
Global Temporary	Two different types depending
Clusters	One or more tables in a single database block
External	External files readable as tables
Index Organized	Merger between a table and an index
Partitions	Partition/Subpartitioned by hash, range, or list
XML	XML Tables

How Rows Are Stored

The format of a row is: row header, column length - value; column_length - value; column_length - value.

The length of a field is stored as an attribute in the row.

If the column name is "LAST_NAME" and the column is defined as CHAR(20) it is be stored as :20:Morgan--------------:

If the column name is "LAST_NAME" and the column is defined as
VARCHAR2(20) it is stored as :6:Morgan:

Definitions

BUFFER POOL

Defines a default buffer pool (cache) for a schema object.

CREATE TABLE buffer_test (
testcol VARCHAR2(20))
STORAGE (buffer_pool DEFAULT);

SELECT table_name, buffer_pool
FROM user_tables;

ALTER TABLE buffer_test STORAGE (buffer_pool RECYCLE);

SELECT table_name, buffer_pool
FROM user_tables;

ALTER TABLE buffer_test STORAGE (buffer_pool KEEP);

SELECT table_name, buffer_pool
FROM user_tables;

CACHE

Use the CACHE clauses to indicate how Oracle Database should store blocks in the buffer cache. If you specify neither CACHE nor NOCACHE, then:

In a CREATE TABLE statement, NOCACHE is the default
In an ALTER TABLE statement, the existing value is not changed.

CACHE
For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.

You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.

NOCACHE
For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage.

As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB value either is not brought into the buffer cache or is brought into the buffer cache and placed at the least recently used end of the LRU list. The latter is the default behavior.

CREATE TABLE heap_cache (
testcol VARCHAR2(20))
CACHE;

SELECT table_name, cache
FROM user_tables
WHERE table_name = 'HEAP_CACHE';

ALTER TABLE heap_cache NOCACHE;

SELECT table_name, cache
FROM user_tables
WHERE table_name = 'HEAP_CACHE';

CACHE READS

CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations but not during write operations.

< CACHE READS | NOCACHE>

CREATE TABLE cache_test (
testlob BLOB)
LOB (testlob) STORE AS (CACHE READS);

SELECT table_name, cache
FROM user_lobs;

ALTER TABLE cache_test MODIFY LOB (testlob) (NOCACHE);

COMPRESSED

The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.

When you enable table compression, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format. You can specify table compression for the following portions of a heap-organized table.

For an entire table, in the physical_properties clause of relational_table or object_table
For a range partition, in the table_partition_description of the range_partitioning clause
For a list partition, in the table_partition_description of the list_partitioning clause
For the storage table of a nested table, in the nested_table_col_properties clause

conn / as sysdba

CREATE TABLESPACE compressed
DATAFILE 'compressed.dbf' SIZE 5M
DEFAULT COMPRESS;

SELECT tablespace_name, def_tab_compression
FROM dba_tablespaces;

ALTER USER uwclass
QUOTA unlimited ON compressed;

conn uwclass/uwclass

CREATE TABLE regtab
TABLESPACE data_sml AS
SELECT * FROM all_objects;

CREATE INDEX ix_regtab_object_type
ON regtab (object_type)
TABLESPACE data_sml;

CREATE TABLE comptab
TABLESPACE compressed AS
SELECT * from all_objects;

CREATE INDEX ix_comptab_object_type
ON comptab (object_type)
TABLESPACE data_sml;

SELECT table_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');

exec dbms_stats.gather_schema_stats(OWNNAME=>'UWCLASS', CASCADE=>TRUE);

SELECT table_name, blocks
FROM user_tables;

SELECT index_name, leaf_blocks
FROM user_indexes
WHERE index_name IN ('IX_REGTAB_OBJECT_TYPE', 'IX_COMPTAB_OBJECT_TYPE');

DEFAULT

The value inserted into the column if the insert or update would leave the column value NULL.

| NULL>

CREATE TABLE default_test (
active VARCHAR2(1) DEFAULT 'Y',
created_by VARCHAR2(30) DEFAULT USER,
created_on TIMESTAMP DEFAULT SYSTIMESTAMP);

set long 100000
set linesize 121
col data_default format a50

SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'DEFAULT_TEST';

ALTER TABLE default_test MODIFY (active DEFAULT 'N');

SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'DEFAULT_TEST';

ALTER TABLE default_test MODIFY (active DEFAULT NULL);

SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'DEFAULT_TEST';

ENCRYPTION

The

TBD

FREELISTS

The number of lists maintained on a table that can be used to identify a block available for insert. Set this to 1 on all tables except those receiving very large numbers of simultaneous inserts. When a process requests a free list, it uses a 'hashing' function to select which free list based on the process id. Using a prime number with such mechanisms usually reduces the number of collisions that occur if the input is randomly distributed. Therefore, if you need more than one free list make the number of lists a prime number (for example 1, 2, 3, 5, 7, 11, 13, 17, 19, 23, .... for optimal performance).

Oracle ignores a setting of FREELISTS if the tablespace in which the object resides is in automatic segment-space management mode.

FREELISTS

SELECT table_name, freelists
FROM user_tables;

FREELIST GROUPS

The number of groups of free lists for the database object you are creating. The database uses the instance number of Oracle Real Application Cluster instances to map each instance to one free list group.

This parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO.

FREELIST GROUPS

SELECT table_name, freelist_groups
FROM user_tables;

INITIAL

In a tablespace that is specified as EXTENT MANAGEMENT LOCAL. The database uses the value of INITIAL in conjunction with the extent size for the tablespace to determine the initial amount of space to reserve for the object. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then the database must allocate one 5M extent, because that is the uniform size of extents for the tablespace. If the extent size of the tablespace is smaller than the value of INITIAL, then the initial amount of space allocated will in fact be more than one extent.
INITIAL

SELECT tablespace_name, initial_extent
FROM user_tablespaces

SELECT table_name, tablespace_name, initial_extent
FROM user_tables;

INITRANS

Specifies the number of DML transaction entries for which space is initially reserved in the data block header. Space is reserved in the headers of all data blocks in the associated segment.

Oracle uses control information stored in the data block to indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE.

Under some circumstances, Oracle can have insufficient history information to determine whether a row has been updated by a "too recent" transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.

INITRANS

SELECT table_name, ini_trans
FROM user_tables;

LOGGING

Specify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING).The logging attribute of the table is independent of that of its indexes.

This attribute also specifies whether subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged (LOGGING) or not logged (NOLOGGING).

CREATE TABLE logging_test (
testcol VARCHAR2(20))
LOGGING;

SELECT table_name, logging
FROM user_tables;

ALTER TABLE logging_test NOLOGGING;

SELECT table_name, logging
FROM user_tables;

MAXTRANS

Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of the free space in a block, if available. Once allocated, this space effectively becomes a permanent part of the block header. The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block. Therefore, you can limit the amount of free space that can be allocated for transaction entries in a data block using MAXTRANS.

MAXTRANS

SELECT table_name, max_trans
FROM user_tables;

MINEXTENTS

The total number of extents to be allocated when the segment is created. This allows for a large allocation of space at creation time, even if contiguous space is not available.

In a tablespace that is specified as EXTENT MANAGEMENT LOCAL, MINEXTENTS is used only to compute the initial amount of space that is allocated. The initial amount of space that is allocated and is equal to INITIAL * MINEXTENTS. Thereafter it is set to 1 for these tablespaces. (as seen in the DBA_SEGMENTS view).

MINEXTENTS

SELECT table_name, min_extents
FROM user_tables;

Not meaningful for objects created in a tablespace that is specified as EXTENT MANAGEMENT LOCAL because the database automatically manages extents.
INITIAL

SELECT tablespace_name, next_extent
FROM user_tablespaces

SELECT table_name, tablespace_name, next_extent
FROM user_tables;

PARALLEL

Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

Specify NOPARALLEL for serial execution. This is the default.

CREATE TABLE parallel_test (
testcol VARCHAR2(20))
PARALLEL(DEGREE 4);

DROP TABLE parallel_test PURGE;

CREATE TABLE parallel_test (
testcol VARCHAR2(20))
PARALLEL 4;

SELECT table_name, degree
FROM user_tables;

ALTER TABLE parallel_test PARALLEL 2;

SELECT table_name, degree
FROM user_tables;

PCTFREE

Determines when a used block is removed from the list of available blocks. When a block is removed from the list ... no more data is written to it so that when records are updated there is room for the data in the block ... thus no chained rows.

Tables on which there are no updates should have PCTFREE set to 0. The default value of 10 leaves 90% of each block empty.

PCTFREE

SELECT table_name, pct_free
FROM user_tables;

PCTUSED

Determines when a used block is re-added to the list of available blocks. When deletes take place and the room available in a block falls below this value ... the block is made available for new inserts to take place.

Tables on which there are no updates should have PCTUSED set to 99. The default value is 40% which means that blocks are available for insertion when they are less than 40% full.

PCTUSED

SELECT table_name, pct_used
FROM user_tables;

ROW CHAINING AND MIGRATION

Source: http://www.tlingua.com

Row chaining occurs when a row can no longer fit into its original block. If the entire row can fit in a new block, the row is moved completely, leaving only a forwarding pointer - this is known as row migration. If the row has grown so large that it may not fit in a single block then the row is split into two or more blocks - row chaining. When Oracle is forced to split a row into pieces, it often splits individual columns into one or more pieces.

See Analyze: See "List Chained Rows"

ROWDEPENDENCIES

This clause lets you specify whether table will use row-level dependency tracking. With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. You cannot change this setting after table is created.

See the ORA_ROWSCN demo

CREATE TABLE rowdep_test (
testcol VARCHAR2(20))
ROWDEPENDENCIES;

SELECT table_name, dependencies
FROM user_tables;

-- Not alterable after table creation

ROW MOVEMENT

The row_movement_clause specifies whether the database can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data. The default is to disable row movement.

ROW MOVEMENT

CREATE TABLE rowmove_test (
testcol VARCHAR2(20))
ENABLE ROW MOVEMENT;

SELECT table_name, row_movement
FROM user_tables;

ALTER TABLE rowmove_test DISABLE ROW MOVEMENT;

SELECT table_name, row_movement
FROM user_tables;

TABLESPACE

the name of the tablespace where the table will be built. The table may exist in one or more the the datafiles mapped to the tablespace.

TABLESPACE
or
TABLESPACE

SELECT tablespace_name
FROM user_tablespaces;

CREATE TABLE tbsp_test (
testcol VARCHAR2(20))
TABLESPACE user_data;

SELECT table_name, tablespace_name
FROM user_tables;

ALTER TABLE tbsp_test MOVE TABLESPACE data_sml;

SELECT table_name, tablespace_name
FROM user_tables;

TABLE LOCK

Prevent and Re-enable DDL On A Table

ALTER TABLE DISABLE TABLE LOCK;

CREATE TABLE tl_test (
col1 VARCHAR2(20));

desc tl_test

ALTER TABLE tl_test ADD (col2 VARCHAR2(20));

desc tl_test

ALTER TABLE tl_test DISABLE TABLE LOCK;

ALTER TABLE tl_test ADD (col3 VARCHAR2(20));

ALTER TABLE tl_test ENABLE TABLE LOCK;

ALTER TABLE tl_test ADD (col3 VARCHAR2(20));

desc tl_test

Create Heap Table

Create Table With Multiple Columns

CREATE TABLE (
,
);

CREATE TABLE mc_test (pid NUMBER(5),
fname VARCHAR2(20),
lname VARCHAR2(25));

desc mc_test

SELECT column_id, column_name
FROM user_tab_columns
WHERE table_name = 'MC_TEST'
ORDER BY column_id;

Create Table With NOT NULL Constraints

CREATE TABLE NOT NULL,
);

CREATE TABLE nn_test (
pid NUMBER(5) NOT NULL,
fname VARCHAR2(20),
lname VARCHAR2(25));

desc nn_test

SELECT column_id, nullable
FROM user_tab_columns
WHERE table_name = 'NN_TEST'
ORDER BY column_id;

ALTER TABLE nn_test MODIFY (pid NULL);

desc nn_test

SELECT column_id, nullable
FROM user_tab_columns
WHERE table_name = 'NN_TEST'
ORDER BY column_id;

Create Table Using Select Statement With Data

CREATE TABLE
PCTFREE
TABLESPACE AS
;

CREATE TABLE my_tables
PCTFREE 0
TABLESPACE user_data AS
SELECT table_name, tablespace_name
FROM all_tables;

SELECT *
FROM my_tables;

Create Table Using Select Statement With No Data Using Tablespace Defaults

CREATE TABLE
TABLESPACE AS

WHERE ;

CREATE TABLE my_tables_empty
TABLESPACE user_data AS
SELECT table_name, tablespace_name
FROM all_tables
WHERE 1=2;

SELECT *
FROM my_tables;

Create Global Temporary Table

Global temporary tables have two major benefits:

1. Non-interference between private sets of data.

2. Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do
the same thing).

3. Decreased redo generation as, by definition, they are non-logging.

However:

Mixing temporary tables (GTTs) with permanent tables usually causes some grief to the CBO. It has no information
about the number of rows in the GTT, and therefore guesses (badly).

Even if you analyze table .. or dbms_stats.gather_table_stats() you don't get stats on the temporary table.

Set the init parameter dynamic_sampling to at least 2 for GTTs to be sampled at run-time.

Create Global Temporary Table That Empties On
Commit

You can not specify a tablespace with global temporary tables. GTT's are built in the TEMP tablespace.

CREATE GLOBAL TEMPORARY TABLE (
);

-- or explicitly specifying the ON COMMIT action

CREATE GLOBAL TEMPORARY TABLE (
,
,
)
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE);

-- or explicitly specifying the ON COMMIT action

CREATE GLOBAL TEMPORARY TABLE gtt_zip2 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE)
ON COMMIT DELETE ROWS;

INSERT INTO gtt_zip1
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);

SELECT *
FROM gtt_zip1;

COMMIT;

SELECT *
FROM gtt_zip1;

Create Global Temporary Table That Empties At End Of Session

You can not specify a tablespace with global temporary tables. GTT's are built in the TEMP tablespace.

CREATE GLOBAL TEMPORARY TABLE (
,
,
)
ON COMMIT PRESERVE ROWS;

CREATE GLOBAL TEMPORARY TABLE gtt_zip3 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE)
ON COMMIT PRESERVE ROWS;

INSERT INTO gtt_zip3
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);

SELECT *
FROM gtt_zip3;

COMMIT;

SELECT *
FROM gtt_zip3;
-- log on as a different user
-- log back on as original user

SELECT *
FROM gtt_zip3;

GTT Demo

CREATE OR REPLACE PACKAGE redo_diff IS
PROCEDURE diff_it;
END redo_diff;
/

CREATE OR REPLACE PACKAGE BODY redo_diff IS
s NUMBER;
--=========================================
FUNCTION get_size RETURN NUMBER IS
s_ NUMBER;
BEGIN
SELECT value
INTO s_
FROM sys.v_$sysstat
WHERE name = 'redo size';

RETURN s_;
END get_size;
--=========================================
PROCEDURE diff_it IS
s_new NUMBER;
BEGIN
s_new := get_size;

dbms_output.put_line('redo diff: ' || to_char(s_new - s));
s := s_new;
END diff_it;

BEGIN
s := get_size;
END redo_diff;
/

CREATE OR REPLACE TYPE subst_ AS OBJECT (
rn number,
ob varchar2(128));
/

CREATE OR REPLACE TYPE subst_t_ AS TABLE OF subst_;
/

set serveroutput on

DECLARE
t subst_t_;
time1_ number;
time2_ number;
sz1_ number;
sz2_ number;
BEGIN
redo_diff.diff_it;
time1_ := dbms_utility.get_time;

-- Filling 500000 records
SELECT CAST(MULTISET(SELECT rownum,a.object_name
FROM all_objects a,all_objects b,all_objects c,all_objects d
WHERE rownum <= 500000) AS subst_t_)
INTO t
FROM dual;

sz1_ := t.count;
time2_ := dbms_utility.get_time;

dbms_output.put_line('filled ' || sz1_ || ' records, time used: '
|| TO_CHAR((time2_ - time1_)/100, '99999.00') || ' secs');

redo_diff.diff_it;

-- deleting approx 50%

SELECT CAST(MULTISET(SELECT rn, ob
FROM TABLE(CAST(t AS subst_t_))
WHERE SUBSTR(ob,1,2) > 'DB') AS subst_t_)
INTO t
FROM dual;

sz2_ :=t.count;
time1_ := dbms_utility.get_time;

dbms_output.put_line('deleted ' || to_char(sz1_ - sz2_) ||
' records, time used: ' || TO_CHAR((time1_-time2_)/100, '99999.00')
|| ' secs');

redo_diff.diff_it;
END;
/

drop type subst_t_;
drop type subst_;

Commenting

Comment a table

COMMENT ON TABLE IS '';

COMMENT ON TABLE zip_code IS 'US Postal Service Zip Codes';

SELECT table_name, comments
FROM user_tab_comments;

Comment a column

COMMENT ON COLUMN IS '';

COMMENT ON COLUMN zip_code.zip_code IS '5 Digit Zip Code';

SELECT table_name, column_name, comments
FROM user_col_comments;

Alter Table Column Clauses

Add a new column

ALTER TABLE
MODIFY ();

CREATE TABLE test (
first_col VARCHAR2(20));

desc test

ALTER TABLE test ADD (second_col NUMBER(20));

desc test

Add More Than One New Column

ALTER TABLE
MODIFY (, );

ALTER TABLE test ADD (third_col DATE, fourth_col VARCHAR2(3));

desc test

Rename A Column

ALTER TABLE
RENAME COLUMN TO ;

ALTER TABLE test RENAME COLUMN third_col TO date_col;

desc test

Drop A Column On A Small To Medium Sized Table

ALTER TABLE
DROP COLUMN ;

ALTER TABLE test DROP COLUMN fourth_col;

desc test

Drop A Column On A Very Large Table

ALTER TABLE
DROP COLUMN
CHECKPOINT ;

ALTER TABLE test DROP COLUMN fourth_col CHECKPOINT 1000;

Set A Column Unused

ALTER TABLE
SET UNUSED COLUMN ;

ALTER TABLE test SET UNUSED COLUMN second_col;

desc test

SELECT *
FROM user_unused_col_tabs;

Drop Unused Columns

ALTER TABLE
DROP UNUSED COLUMNS;

ALTER TABLE test DROP UNUSED COLUMNS;

desc test

Drop Unused Columns On A Very Large Table

ALTER TABLE
DROP UNUSED COLUMNS
CHECKPOINT ;

ALTER TABLE test DROP UNUSED COLUMNS CHECKPOINT 250;

desc test

Alter Table Change Data Type

ALTER TABLE
MODIFY (

CREATE TABLE dt_ test (
test_col VARCHAR2(20));

desc dt_test

ALTER TABLE dt_ test MODIFY (test_col NUMBER(6));

desc dt_test

NOTE: column must be empty to change data type

Alter Table Change Data Type Multiple Fields

ALTER TABLE
MODIFY (,
);

CREATE TABLE mcdt_test (
col_one NUMBER(10),
col_two VARCHAR2(10),
dat_col DATE);

DESC mcdt_test

ALTER TABLE mcdt_test
MODIFY (col_one NUMBER(12), col_two VARCHAR2(20));

desc mcdt_test

Alter Table Storage Clauses

Force Extent Allocation

ALTER TABLE ALLOCATE EXTENT;

CREATE TABLE allo_test (
testcol VARCHAR2(20));

SELECT segment_name, extents
FROM user_segments;

ALTER TABLE allo_test ALLOCATE EXTENT;

SELECT segment_name, extents
FROM user_segments;

ALTER TABLE allo_test ALLOCATE EXTENT;

SELECT segment_name, extents
FROM user_segments;

Deallocate Unused Space

ALTER TABLE DEALLOCATE UNUSED;

SELECT segment_name, extents
FROM user_segments;

ALTER TABLE allo_test DEALLOCATE UNUSED;

SELECT segment_name, extents
FROM user_segments;

Shrink Space

Valid only for segments in tablespaces with automatic segment management. Row movement must be enabled.

COMPACT defragments the segment space and compacts the table rows for subsequent release. COMPACT does not readjust the high water mark and does not release the space immediately. CASCADE performs the same operations on all dependent objects.

ALTER TABLE SHRINK SPACE [COMPACT] [CASCADE];

CREATE TABLE shrink_test (
rid NUMBER(5),
testcol VARCHAR2(20))
ENABLE ROW MOVEMENT;

SELECT bid, count(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;

BEGIN
FOR i IN 1..40000
LOOP
INSERT INTO shrink_test (rid, testcol)
VALUES (i, 'ABCDEFGHIJKLMNOPQRST');
END LOOP;
COMMIT;
END;
/

SELECT COUNT(*)
FROM shrink_test;

SELECT bid, count(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;

DELETE FROM shrink_test WHERE mod(rid, 2) = 0;
COMMIT;

SELECT COUNT(*)
FROM shrink_test;

SELECT bid, count(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;

ALTER TABLE shrink_test SHRINK SPACE COMPACT CASCADE;

SELECT bid, count(*)
FROM (
SELECT dbms_rowid.rowid_block_number(rowid) BID
FROM shrink_test)
GROUP BY bid;

Control the Number of Records per Block

This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.

Can not be specified MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. The bitmap index must be dropped.

ALTER TABLE MINIMIZE RECORDS_PER_BLOCK;

ALTER TABLE test MINIMIZE NUMBER_OF_RECORDS_PER_BLOCK;

Release Control on the Number of Records Per Block

ALTER TABLE NOMINIMIZE RECORDS_PER_BLOCK;

ALTER TABLE test NOMINIMIZE NUMBER_OF_RECORDS_PER_BLOCK;

Move Table Containing An LOB Segment To A Different Tablespace

ALTER TABLE
MOVE TABLESPACE
LOB () STORE AS
(TABLESPACE );

CREATE TABLE lobtab (
recid NUMBER(5),
lobcol BLOB)
LOB (lobcol) STORE AS (TABLESPACE data_sml
STORAGE (INITIAL 1M)
CHUNK 4000
NOCACHE NOLOGGING)
TABLESPACE data_sml;

desc lobtab

col segment_name format a30

SELECT segment_name, segment_type, tablespace_name
FROM user_segments;

SELECT tablespace_name, bytes
FROM user_ts_quotas;

conn / as sysdba

ALTER USER uwclass
QUOTA 10M ON users;

conn uwclass/uwclass

-- does not refresh in 10.2 / this is an unfixed bug
SELECT tablespace_name, bytes
FROM user_ts_quotas;

ALTER TABLE lobtab
MOVE TABLESPACE data_sml
LOB (lobcol) STORE AS lobseg (TABLESPACE example);

SELECT segment_name, tablespace_name
FROM user_segments
WHERE segment_name IN ('LOBTAB', 'LOBSEG');

SELECT segment_name, segment_type, tablespace_name
FROM user_segments;

Drop Table

Drop Table Into Recycle Bin

DROP TABLE ;

DROP TABLE zip_code;

10g Drop Table Not Into Recycle Bin

DROP TABLE PURGE;

DROP TABLE zip_code PURGE;

Drop Table Cascade Constraints

DROP TABLE CASCADE CONSTRAINTS;

DROP TABLE work_note_header CASCADE CONSTRAINTS;

Miscellaneous

Slow table reads

The init parameter db_file_multiblock_read_count is paramount

Rename a table

Thanks Chris Barr for the second syntax.

RENAME TO ;

CREATE TABLE old_name (
test VARCHAR2(20));
DESC old_name

RENAME TABLE old_name TO new_name;

RENAME new_name TO old_name;

Table Related Queries

How much space is a table taking in the tablespace?

SELECT SUBSTR(s.segment_name,1,20) TABLE_NAME,
SUBSTR(s.tablespace_name,1,20) TABLESPACE_NAME,
ROUND(DECODE(s.extents, 1, s.initial_extent,
(s.initial_extent + (s.extents-1) * s.next_extent))/1024000,2) ALLOCATED_MB,
ROUND((t.num_rows * t.avg_row_len / 1024000),2) REQUIRED_MB
FROM dba_segments s, dba_tables t
WHERE s.owner = t.owner
AND s.segment_name = t.table_name
ORDER BY s.segment_name;

Space again

SELECT owner, table_name, NVL(num_rows*avg_row_len,0)/1024000 MB
FROM dba_tables
ORDER BY owner, table_name;

http://www.psoug.org/reference/tables.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-242291/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-242291/

Oracle Tables

相关阅读

相关文章

相关问答