Oracle Tables

zhouwf0726發表於2019-06-12
Oracle
General
Table Related Data Dictionary Objectstab$
col$
DBAALLUSER
dba_col_commentsall_col_commentsuser_col_comments
dba_external_tablesall_external_tablesuser_external_tables
dba_external
_locations		all_external
_locations		user_external
_locations
dba_partial_drop
_tabs		all_partial_drop
_tabs		user_partial_drop
_tabs
dba_tablesall_tablesuser_tables
dba_tab_colsall_tab_colsuser_tab_cols
dba_tab_columnsall_tab_columnsuser_tab_columns
dba_tab_col_
statistics		all_tab_col
_statistics		user_tab_col
_statistics
dba_tab_commentsall_tab_commentsuser_tab_comments
dba_tab_histogramsall_tab_histogramsuser_tab_histograms
dba_tab
_modifications		all_tab
_modifications		user_tab
_modifications
dba_tab_privsall_tab_privsuser_tab_privs
dba_tab_statisticsall_tab_statisticsuser_tab_statistics
dba_tab_stats
_history		all_tab_stats
_history		user_tab_stats
_history
dba_unused_col_tabsall_unused_col_tabsuser_unused_col_tabs
Table Related System Privileges
alter any tableflashback any table
backup any tableinsert any table
create any tablelock any table
comment any tableselect any table
create tableunder any table
delete any tableupdate any table
drop any table
Types Of Tables

Table Type

Description

HeapDefault Oracle table type
Global TemporaryTwo different types depending
ClustersOne or more tables in a single database block
ExternalExternal files readable as tables
Index OrganizedMerger between a table and an index
PartitionsPartition/Subpartitioned by hash, range, or list
XMLXML Tables
How Rows Are StoredThe 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 POOLDefines 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 READSCACHE 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');
DEFAULTThe 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';
ENCRYPTIONThe
TBD
FREELISTSThe 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 GROUPSThe 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;
INITIALIn 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;
INITRANSSpecifies 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;
LOGGINGSpecify 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;
MAXTRANSOnce 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;
MINEXTENTSThe 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;
NEXTNot 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;
PARALLELSpecify 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;
PCTFREEDetermines 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;
PCTUSEDDetermines 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"
ROWDEPENDENCIESThis 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 MOVEMENTThe 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;
TABLESPACEthe 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 ColumnsCREATE 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 ConstraintsCREATE 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 DataCREATE 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 DefaultsCREATE TABLE
TABLESPACE AS
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 DemoCREATE 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 tableCOMMENT ON TABLE IS '';
COMMENT ON TABLE zip_code IS 'US Postal Service Zip Codes';

SELECT table_name, comments
FROM user_tab_comments;
Comment a columnCOMMENT 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 columnALTER 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 ColumnALTER TABLE
MODIFY (, );
ALTER TABLE test ADD (third_col DATE, fourth_col VARCHAR2(3));

desc test
Rename A ColumnALTER 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 TableALTER TABLE
DROP COLUMN ;
ALTER TABLE test DROP COLUMN fourth_col;

desc test
Drop A Column On A Very Large TableALTER TABLE
DROP COLUMN
CHECKPOINT ;
ALTER TABLE test DROP COLUMN fourth_col CHECKPOINT 1000;
Set A Column UnusedALTER TABLE
SET UNUSED COLUMN ;
ALTER TABLE test SET UNUSED COLUMN second_col;

desc test

SELECT *
FROM user_unused_col_tabs;
Drop Unused ColumnsALTER TABLE
DROP UNUSED COLUMNS;
ALTER TABLE test DROP UNUSED COLUMNS;

desc test
Drop Unused Columns On A Very Large TableALTER TABLE
DROP UNUSED COLUMNS
CHECKPOINT ;
ALTER TABLE test DROP UNUSED COLUMNS CHECKPOINT 250;

desc test
Alter Table Change Data TypeALTER 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 FieldsALTER 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 AllocationALTER 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 SpaceALTER 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 BlockThis 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 BlockALTER 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 BinDROP TABLE ;
DROP TABLE zip_code;
10g Drop Table Not Into Recycle BinDROP 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 readsThe 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 againSELECT 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章