티스토리 뷰
출처 : http://www.psoug.org/reference/tables.html
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
General | |||||||||||||||||||||||||||||||||||
Table Related Data Dictionary Objects |
|
||||||||||||||||||||||||||||||||||
Table Related System Privileges |
|
||||||||||||||||||||||||||||||||||
Types Of 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: Oracle starts adding rows from the end of the block towards the block header. In this way, the block header can grow if required. To see the actual block and row as stored by Oracle use the DBMS_ROWID built-in package. |
||||||||||||||||||||||||||||||||||
Heap Table | |||||||||||||||||||||||||||||||||||
Create Table With a Single Columns |
CREATE TABLE <table_name> ( <column_name> <column_data_type>); |
||||||||||||||||||||||||||||||||||
CREATE TABLE
one_col ( last_name VARCHAR2(25)); desc one_col desc user_tables SELECT table_name, tablespace_name FROM user_tables; desc user_tab_columns desc user_tab_cols SELECT column_id, column_name FROM user_tab_columns WHERE table_name = 'ONE_COL' ORDER BY column_id; |
|||||||||||||||||||||||||||||||||||
Create Table With Multiple Columns |
CREATE TABLE <table_name> ( <column_name> <column_data_type>, <column_name> <column_data_type>); |
||||||||||||||||||||||||||||||||||
CREATE TABLE
multi_col ( pid NUMBER(5), fname VARCHAR2(20), lname VARCHAR2(25)); desc multi_col SELECT column_id, column_name FROM user_tab_columns WHERE table_name = 'MULTI_COL' ORDER BY column_id; |
|||||||||||||||||||||||||||||||||||
Create Table Using Select Statement With Data |
CREATE TABLE <table_name> TABLESPACE <tablespace_name> AS <select statement>; |
||||||||||||||||||||||||||||||||||
CREATE TABLE
ctas AS SELECT table_name, tablespace_name FROM all_tables; SELECT * FROM ctas; |
|||||||||||||||||||||||||||||||||||
Create Table Using Select Statement With No Data Using Tablespace Defaults |
CREATE TABLE <table_name> TABLESPACE <tablespace_name> AS <select statement> WHERE <Boolean False>; |
||||||||||||||||||||||||||||||||||
CREATE TABLE
ctas_nodata AS SELECT table_name, tablespace_name FROM all_tables WHERE 1=2; SELECT * FROM ctas_nodata; |
|||||||||||||||||||||||||||||||||||
Create Table Options | |||||||||||||||||||||||||||||||||||
BUFFER POOL |
Defines a default buffer pool (cache) for a schema object. <KEEP | RECYCLE | DEFAULT> |
||||||||||||||||||||||||||||||||||
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; Note: Additional information can be found here. |
|||||||||||||||||||||||||||||||||||
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:
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. <CACHE | NOCACHE> |
||||||||||||||||||||||||||||||||||
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); |
|||||||||||||||||||||||||||||||||||
DEFAULT |
The value inserted into the column if the insert or
update would leave the column value NULL. <DEFAULT <value> | 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 Specification | Valid algorithms are 3DES168, AES128,
AES192, and AES256 USING '<encryption_algorithm>' IDENTIFIED BY <password> [NO] [SALT] |
||||||||||||||||||||||||||||||||||
See Transparent Data Encryption | |||||||||||||||||||||||||||||||||||
Flashback Archive |
FLASHBACK [ARCHIVE <flashback_archive>] or NO FLASHBACK ARCHIVE |
||||||||||||||||||||||||||||||||||
See Flashback Archive Page | |||||||||||||||||||||||||||||||||||
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 <integer> |
||||||||||||||||||||||||||||||||||
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 <integer> |
||||||||||||||||||||||||||||||||||
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 <integer> <K | M | G> |
||||||||||||||||||||||||||||||||||
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 <integer> |
||||||||||||||||||||||||||||||||||
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 <integer> |
||||||||||||||||||||||||||||||||||
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 <integer> |
||||||||||||||||||||||||||||||||||
SELECT table_name,
min_extents FROM user_tables; |
|||||||||||||||||||||||||||||||||||
MONITOR | Deprecated in 10g. DBMS_STATS now does this by default. | ||||||||||||||||||||||||||||||||||
NEXT |
Not
meaningful for objects created in a tablespace that is specified as
EXTENT MANAGEMENT LOCAL because the database automatically manages
extents. INITIAL <integer> <K | M | G> |
||||||||||||||||||||||||||||||||||
SELECT tablespace_name, next_extent FROM user_tablespaces SELECT table_name, tablespace_name, next_extent FROM user_tables; |
|||||||||||||||||||||||||||||||||||
Create Table With NOT NULL Constraints |
CREATE TABLE <table_name> <column_name> <column_data_type> NOT NULL, <column_name> <column_data_type>); |
||||||||||||||||||||||||||||||||||
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; ALTER TABLE nn_test MODIFY (pid NOT NULL); SELECT column_id, nullable FROM user_tab_columns WHERE table_name = 'NN_TEST' ORDER BY column_id; |
|||||||||||||||||||||||||||||||||||
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, the default, for serial execution. <PARALLEL | NOPARALLEL> For this to be optimally effective the table should be distributed among multiple datafiles. |
||||||||||||||||||||||||||||||||||
CREATE TABLE parallel_test ( testcol VARCHAR2(20)); SELECT table_name, degree FROM user_tables; set autotrace traceonly explain SELECT * FROM parallel_test; set autotrace off ALTER TABLE parallel_test PARALLEL 2; SELECT table_name, degree FROM user_tables; set autotrace traceonly explain SELECT * FROM parallel_test; |
|||||||||||||||||||||||||||||||||||
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 <integer> |
||||||||||||||||||||||||||||||||||
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 <integer> |
||||||||||||||||||||||||||||||||||
SELECT table_name, pct_used FROM user_tables; |
|||||||||||||||||||||||||||||||||||
READ ONLY Clause New in 11g |
READ ONLY | ||||||||||||||||||||||||||||||||||
CREATE TABLE
readonly ( testcol VARCHAR2(20)) READ ONLY; -- why did this fail? CREATE TABLE readonly ( testcol VARCHAR2(20)); INSERT INTO readonly (testcol) VALUES ('Morgan'); ALTER TABLE readonly READ ONLY; SELECT table_name, read_only FROM user_tables ORDER BY 2; INSERT INTO readonly (testcol) VALUES ('Morgan');
|
|||||||||||||||||||||||||||||||||||
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. <ROWDEPENDENCIES | NOROWDEPENDENCIES> |
||||||||||||||||||||||||||||||||||
CREATE TABLE rowdep_test ( testcol VARCHAR2(20)) ROWDEPENDENCIES; SELECT table_name, dependencies FROM user_tables; -- Not alterable after table creation -- See ORA_ROWSCN under pseudocolumns for more information about this |
|||||||||||||||||||||||||||||||||||
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. <ENABLE | DISABLE> 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; |
|||||||||||||||||||||||||||||||||||
Supplemental ID Key Logging Clause |
DATA (<ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY>) COLUMNS | ||||||||||||||||||||||||||||||||||
CREATE TABLE
sup_log1 ( pid NUMBER(5), fname VARCHAR2(20), lname VARCHAR2(25), SUPPLEMENTAL LOG DATA (ALL) COLUMNS); conn / as sysdba SELECT object_id FROM dba_objects WHERE owner = 'UWCLASS' AND object_name = 'SUP_LOG1'; SELECT type# FROM cdef$ WHERE obj# = 74097;
|
|||||||||||||||||||||||||||||||||||
Supplemental PL/SQL Clause |
DATA FOR PROCEDURAL REPLICATION | ||||||||||||||||||||||||||||||||||
CREATE TABLE
sup_log2 ( pid NUMBER(5), fname VARCHAR2(20), lname VARCHAR2(25), SUPPLEMENTAL LOG DATA FOR PROCEDURAL REPLICATION); -- Note: While valid this syntax may not be functional |
|||||||||||||||||||||||||||||||||||
TABLE LOCK Prevent and Re-enable DDL On A Table |
ALTER TABLE <table_name> DISABLE TABLE LOCK; | ||||||||||||||||||||||||||||||||||
CREATE TABLE tl_test ( col1 VARCHAR2(20)); desc tl_test ALTER TABLE tl_test DISABLE TABLE LOCK; ALTER TABLE tl_test ADD (col2 VARCHAR2(20)); TRUNCATE TABLE tl_test; DROP TABLE tl_test PURGE; ALTER TABLE tl_test ENABLE TABLE LOCK; TRUNCATE TABLE tl_test; DROP TABLE tl_test PURGE; |
|||||||||||||||||||||||||||||||||||
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 <tablespace_name> or TABLESPACE <tablespace_group_name> |
||||||||||||||||||||||||||||||||||
SELECT tablespace_name,
max_bytes, max_blocks FROM user_ts_quotas; CREATE TABLE tbsp_test ( testcol VARCHAR2(20)) TABLESPACE users; SELECT table_name, tablespace_name FROM user_tables ORDER BY 1; ALTER TABLE tbsp_test MOVE TABLESPACE uwdata; SELECT table_name, tablespace_name FROM user_tables ORDER BY 1; CREATE TABLE ctas2 TABLESPACE uwdata AS SELECT * FROM all_tables; |
|||||||||||||||||||||||||||||||||||
VIRTUAL COLUMN New in 11g |
<column_name> [<data_type>]
[GENERATED ALWAYS] AS (column_expression) VIRTUAL (inline_constraint) Note: "GENERATED ALWAYS" and "VIRTUAL" are optional keywords |
||||||||||||||||||||||||||||||||||
CREATE TABLE vcol ( salary NUMBER(8), bonus NUMBER(3), total_comp NUMBER(10) GENERATED ALWAYS AS (salary+bonus) VIRTUAL); desc vcol col data_type format a30 col data_default format a30 set long 100000 SELECT column_name, data_type, virtual_column, data_default FROM user_tab_cols WHERE table_name = 'VCOL'; INSERT into vcol (salary, bonus, total_comp) VALUES (100, 10, 110); INSERT into vcol (salary, bonus) VALUES (100, 10); SELECT * FROM vcol; DROP TABLE vcol PURGE; CREATE TABLE vcol ( salary NUMBER(8), bonus NUMBER(3), total_comp NUMBER(10) GENERATED ALWAYS AS (salary+bonus)); DROP TABLE vcol PURGE; CREATE TABLE vcol ( salary NUMBER(8), bonus NUMBER(3), total_comp NUMBER(10) AS (salary+bonus)); ALTER TABLE vcol ADD CONSTRAINT cc_vcol_total_comp CHECK (total_comp < 50001); INSERT into vcol (salary, bonus) VALUES (100, 10); INSERT into vcol (salary, bonus) VALUES (200, 12); COMMIT; INSERT into vcol (salary, bonus) VALUES (50000, 1); SELECT * FROM vcol; EXPLAIN PLAN FOR SELECT * FROM vcol; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT /*+ RESULT_CACHE */ * FROM vcol; SELECT * FROM TABLE(dbms_xplan.display); ALTER TABLE vcol ADD CONSTRAINT pk_vcol PRIMARY KEY (salary) USING INDEX; CREATE INDEX ix_virtual_column ON vcol(total_comp); SELECT index_name, index_type FROM user_indexes WHERE table_name = 'VCOL'; col column_name format a30 SELECT column_name, column_position, column_length FROM user_ind_columns WHERE table_name = 'VCOL'; BEGIN FOR i IN 1 .. 20000 LOOP BEGIN INSERT INTO vcol (salary, bonus) VALUES (i, TRUNC(MOD(i, 5))); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; COMMIT; END; / exec dbms_stats.gather_index_stats(USER, 'PK_VCOL'); exec dbms_stats.gather_index_stats(USER, 'IX_VIRTUAL_COLUMN'); desc user_ind_statistics SELECT index_name, object_type, blevel, leaf_blocks, distinct_keys, clustering_factor FROM user_ind_statistics WHERE table_name = 'VCOL'; |
|||||||||||||||||||||||||||||||||||
Compressed Table | |||||||||||||||||||||||||||||||||||
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.
Table compression saves disk space and reduces memory use in the buffer cache, and is completely transparent to applications. Compression ratios as high as 3.5 : 1 can be achieved. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for DML. |
||||||||||||||||||||||||||||||||||
Compressed Tablespace |
CREATE TABLESPACE ... DEFAULT COMPRESS; |
||||||||||||||||||||||||||||||||||
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 uwdata AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; CREATE TABLE comptab TABLESPACE compressed AS SELECT * from all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; SELECT table_name, tablespace_name, compression FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); exec dbms_stats.gather_table_stats(USER, 'REGTAB'); exec dbms_stats.gather_table_stats(USER, 'COMPTAB'); SELECT table_name, blocks FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); conn / as sysdba DROP TABLESPACE compressed INCLUDING CONTENTS AND DATAFILES; |
|||||||||||||||||||||||||||||||||||
Compressed Table New in 11g |
CREATE TABLE ... COMPRESS; |
||||||||||||||||||||||||||||||||||
conn uwclass/uwclass CREATE TABLE regtab TABLESPACE uwdata AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; CREATE TABLE comptab TABLESPACE uwdata COMPRESS AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; SELECT table_name, tablespace_name, compression FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); exec dbms_stats.gather_table_stats(USER, 'REGTAB'); exec dbms_stats.gather_table_stats(USER, 'COMPTAB'); SELECT table_name, blocks FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); |
|||||||||||||||||||||||||||||||||||
Compressed Direct Load New in 11g |
CREATE TABLE ... COMPRESS FOR DIRECT_LOAD OPERATIONS; |
||||||||||||||||||||||||||||||||||
CREATE TABLE regtab TABLESPACE uwdata AS SELECT /*+ APPEND */ * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; CREATE TABLE comptab TABLESPACE uwdata COMPRESS FOR DIRECT_LOAD OPERATIONS AS SELECT /*+ APPEND */ * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; SELECT table_name, tablespace_name, compression FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); exec dbms_stats.gather_table_stats(USER, 'REGTAB'); exec dbms_stats.gather_table_stats(USER, 'COMPTAB'); SELECT table_name, blocks FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); |
|||||||||||||||||||||||||||||||||||
Commenting | |||||||||||||||||||||||||||||||||||
Comment a table | COMMENT ON TABLE <table_name> IS '<comment>'; | ||||||||||||||||||||||||||||||||||
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 <table_name.column_name> IS '<comment>'; | ||||||||||||||||||||||||||||||||||
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 <table_name> MODIFY (<field_name data_type>); |
||||||||||||||||||||||||||||||||||
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 <table_name> MODIFY (<field_name data_type>, <field_name data type>); |
||||||||||||||||||||||||||||||||||
ALTER TABLE test
ADD (third_col DATE, fourth_col VARCHAR2(3)); desc test |
|||||||||||||||||||||||||||||||||||
Rename A Column |
ALTER TABLE <table_name> RENAME COLUMN <current_name> TO <new_name>; |
||||||||||||||||||||||||||||||||||
ALTER TABLE test
RENAME COLUMN third_col TO date_col; desc test |
|||||||||||||||||||||||||||||||||||
Drop A Column On A Small To Medium Sized Table |
ALTER TABLE <table_name> DROP COLUMN <column_name>; |
||||||||||||||||||||||||||||||||||
ALTER TABLE test
DROP COLUMN fourth_col; desc test |
|||||||||||||||||||||||||||||||||||
Drop A Column On A Very Large Table | ALTER TABLE <table_name> DROP COLUMN <column_name> CHECKPOINT <integer>; |
||||||||||||||||||||||||||||||||||
ALTER TABLE test DROP COLUMN fourth_col CHECKPOINT 1000; | |||||||||||||||||||||||||||||||||||
Set A Column Unused |
ALTER TABLE <table_name> SET UNUSED COLUMN <column_name>; |
||||||||||||||||||||||||||||||||||
ALTER TABLE test
SET UNUSED COLUMN second_col; desc test SELECT * FROM user_unused_col_tabs; |
|||||||||||||||||||||||||||||||||||
Drop Unused Columns |
ALTER TABLE <table_name> DROP UNUSED COLUMNS; |
||||||||||||||||||||||||||||||||||
ALTER TABLE test
DROP UNUSED COLUMNS; desc test |
|||||||||||||||||||||||||||||||||||
Drop Unused Columns On A Very Large Table |
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <integer>; |
||||||||||||||||||||||||||||||||||
ALTER TABLE test
DROP UNUSED COLUMNS CHECKPOINT 250; desc test |
|||||||||||||||||||||||||||||||||||
Alter Table Change Data Type |
ALTER TABLE <table_name> MODIFY (<column_name new_data_type); |
||||||||||||||||||||||||||||||||||
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 <table_name> MODIFY (<column_name> <data_type>, <column_name> <data_type>); |
||||||||||||||||||||||||||||||||||
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 <table_name> ALLOCATE EXTENT; ALLOCATE EXTENT [( { SIZE size_clause | DATAFILE 'filename' | INSTANCE integer } [ SIZE size_clause | DATAFILE 'filename' | INSTANCE integer ]...)] |
||||||||||||||||||||||||||||||||||
CREATE TABLE allo_test ( testcol VARCHAR2(20)); col segment_name format a30 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 (SIZE 1M INSTANCE 1); SELECT segment_name, extents FROM user_segments; |
|||||||||||||||||||||||||||||||||||
Deallocate Unused Space |
ALTER TABLE <table_name> 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 <table_name> 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.
ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK; |
||||||||||||||||||||||||||||||||||
ALTER TABLE test MINIMIZE NUMBER_OF_RECORDS_PER_BLOCK; | |||||||||||||||||||||||||||||||||||
Release Control on the Number of Records Per Block | ALTER TABLE <table_name> 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 <table_name> MOVE TABLESPACE <tablespace_name> LOB (<lob_column_name>) STORE AS <lob_segment_name> (TABLESPACE <tablespace_name>); |
||||||||||||||||||||||||||||||||||
CREATE TABLE lobtab ( recid NUMBER(5), lobcol BLOB) LOB (lobcol) STORE AS (TABLESPACE uwdata STORAGE (INITIAL 1M) CHUNK 4000 NOCACHE NOLOGGING) TABLESPACE uwdata; 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 uwdata 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 <table_name>; | ||||||||||||||||||||||||||||||||||
DROP TABLE zip_code; | |||||||||||||||||||||||||||||||||||
10g Drop Table Not Into Recycle Bin | DROP TABLE <table_name> PURGE; | ||||||||||||||||||||||||||||||||||
DROP TABLE zip_code PURGE; | |||||||||||||||||||||||||||||||||||
Drop Table Cascade Constraints | DROP TABLE <table_name> 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 <current_table_name> TO <new_name>; | ||||||||||||||||||||||||||||||||||
CREATE TABLE old_name ( test VARCHAR2(20)); SELECT table_name FROM user_tables ORDER BY 1; RENAME old_name TO new_name; SELECT table_name FROM user_tables ORDER BY 1; |
|||||||||||||||||||||||||||||||||||
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; |
'Dev > Oracle' 카테고리의 다른 글
[펌] oracle procedure 관련 (0) | 2008.02.01 |
---|---|
[펌] oracle import 관련 구문 (0) | 2007.11.22 |
[펌] oracle tablespace 관련 구문 (0) | 2007.10.11 |
[펌] oracle update 구문 (1) | 2007.10.01 |
[펌] oracle export 관련 구문 (0) | 2007.09.02 |
댓글
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
TAG
- 김용
- 출근길
- Git
- 정부과천청사
- 일하는 곳
- 파워콤해지
- 액땜
- GB-P100
- iBATIS
- 충동구매
- 무료 프로그램
- Eclipse
- connect by
- 헌혈
- 금연
- VMware
- 안드로이드 앱
- EditPlus
- 프로젝트
- oracle
- 낭패
- 짜증
- 출근
- ubuntu
- 윈도우
- 법무부
- 오라클
- 바이크
- Java
- 최대 메모리
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
글 보관함