Oracle Table Segment Attribute Clauses Version 18c
General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Specifies those aspects of a table definition that affect the table's extents and blocks. Oracle considers compression, in-memory, ilm part of the Physical Attributes but for purposes of clarity in the Library we treat them as separate entities.
Specify to enable tracking for the table. You can specify flashback_archive to designate a particular flashback data archive for this table.
The flashback data archive you specify much already exist.
Specify if you want logging of all tables, indexes, and partitions within the tablespace.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels. It is always prefereable to do FORCE LOGGING at the database level.
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.
Partitioning is the method of choice for improving performance of I/O and maintenance activities by breaking up large tables into multiple independently accessed segments.
Specifies those aspects of a table definition that affect the table's extents and blocks.
Oracle considers compression, in-memory,ilm part of the Physical Attributes but for purposes of clarity in the Library we treat them as separate entities.
Enables In-Database Archiving, which allows designation of table rows as active or archived. Queries can then only b performed on the active rows within the table.
The SHARING clause can be used with a table built in an Application Root to share the METADATA or DATA, by pointer, with the Application Root's PDB children.
Specify this clause to migrate data to tablespace when the condition specified in the AFTER clause is met or when the PL/SQL function specified in the ON clause returns TRUE.
If you specify READ ONLY, then tablespace is made read only after the data is migrated.
This clause lets you specify that the virtual column expression is unusable for evaluating queries in one or more editions.
The remaining editions form a range of editions in which it is safe for the optimizer to use the virtual column expression to evaluate queries.
SELECT table_name, tablespace_name, segment_created
FROM user_tables
WHERE table_name IN ('LATER', 'NOW');
SELECT segment_name, tablespace_name
FROM user_segments
WHERE segment_name IN ('LATER', 'NOW');
INSERT INTO now (col1, col2) VALUES (1, 'A');
INSERT INTO later (col1, col2) VALUES (1, 'B');
SELECT segment_name, tablespace_name
FROM user_segments
WHERE segment_name IN ('LATER', 'NOW');
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 <1 | integer>
SELECT table_name, ini_trans
FROM user_tables;
MAXTRANS
In earlier releases, the MAXTRANS parameter determined the maximum number of concurrent update transactions allowed for each data block in the segment. This parameter has been deprecated.
Oracle now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.
Existing objects for which a value of MAXTRANS has already been set retain that setting. However, if you attempt to change the value for MAXTRANS, Oracle ignores the new specification and substitutes the value 255 without returning an error.
MAXTRANS <255 | integer>
Deprecated and ignored by the database
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 <10 | integer>
SELECT table_name, pct_free
FROM user_all_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.
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 <1 | integer>
SELECT table_name, freelists
FROM user_all_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 <1 | integer>
SELECT table_name, freelist_groups
FROM user_all_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 | T | P | E>
SELECT tablespace_name, initial_extent
FROM user_tablespaces
SELECT table_name, tablespace_name, initial_extent
FROM user_tables;
NEXT
Not meaningful for objects created in a tablespace that is specified as EXTENT MANAGEMENT LOCAL because the database automatically manages extents.
NEXT <integer> <K | M | G | T | P | E>
SELECT tablespace_name, next_extent
FROM user_tablespaces
SELECT table_name, tablespace_name, next_extent
FROM user_all_tables;
MAXEXTENTS
The maximum number of extents that can be allocated to the segment.
MAXEXTENTS <integer>
SELECT table_name, max_extents
FROM user_all_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_all_tables;
MAXSIZE
Defines the maximum size of a table in bytes.
MAXSIZE <integer <K | M | G | T | P | E> | UNLIMITED>
The OPTIMAL keyword is relevant only to rollback segments. It specifies an optimal size in bytes for a rollback segment.
Oracle tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions.
Oracle deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.
The value of OPTIMAL cannot be less than the space initially allocated by the MINEXTENTS, INITIAL, NEXT, and PCTINCREASE parameters. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size.
PCTINCREASE
The default is zero and it should be left that way. Using alternate values back in Oracle 8.0 and before was always a bad idea but unfortunately back then that didn't stop a lot of people from setting other values.
CLUSTERING BY <linear | interleaved> ORDER <clustering_columns>;
CREATE TABLE sales_ac (sale_id NUMBER(10), customer_id NUMBER(10))
CLUSTERING BY LINEAR ORDER (customer_id);
col last_load_clst format a9
col last_datamove_clst format a13
SELECT *
FROM dba_clustering_tables;
OWNER TABLE_NAME CLUSTERING_ ON_ ON_ VAL WIT LAST_LOAD LAST_DATAMOVE
-------- ----------- ----------- --- --- --- --- --------- -------------
UWCLASS SALES_AC LINEAR YES YES YES NO
Attribute Clustering with Clustering Join
CLUSTERING <schema_name>.<table_name> [JOIN <schema_name>.<table_name> ON (<equijoin_condition>)[,...])
BY <linear | interleaved> ORDER <clustering_columns>;
ALTER TABLE sales_header
ADD CONSTRAINT pk_sales_header
PRIMARY KEY (sale_id);
CREATE TABLE sales_detail (sale_id NUMBER(10), customer_id NUMBER(10))
CLUSTERING sales_header JOIN
uwclass.sales_header ON (sales_detail.sale_id = sales_header.sale_id)
BY LINEAR ORDER (customer_id);
SELECT *
FROM dba_clustering_tables;
OWNER TABLE_NAME CLUSTERING_ ON_ ON_ VAL WIT LAST_LOAD LAST_DATAMOVE
-------- ------------- ----------- --- --- --- --- --------- -------------
UWCLASS SALES_DETAIL LINEAR YES YES YES NO
col tab1_owner format a10
col tab1_name format a12
col tab1_column format a11
col tab2_owner format a10
col tab2_name format a12
col tab2_column format a11
-- to use this clause the schema owner must be editions enabled and the function used to specify the virtual column editioned.
EVALUATE USING <CURRENT EDITION | EDITION <edition_name> | NULL EDITION>;
conn uwclass/uwclass@pdbdev
show edition
CREATE OR REPLACE FUNCTION evalValue(rid IN NUMBER) RETURN PLS_INTEGER AUTHID DEFINER DETERMINISTIC IS
BEGIN
RETURN TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),rid,1));
END evalValue;
/
SELECT evalValue FROM dual;
CREATE TABLE eec (
rid NUMBER,
virtcol NUMBER AS (evalValue(rid))
EVALUATE USING CURRENT EDITION);
col evaluation_edition format a20
SELECT table_name, column_name, evaluation_edition
FROM user_tab_cols
WHERE table_name = 'EEC';
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).
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.
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.
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
ALTER TABLE allo_test ALLOCATE EXTENT;
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
ALTER TABLE allo_test ALLOCATE EXTENT
(SIZE 1M INSTANCE 1);
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
Deallocate Unused Space
ALTER TABLE <table_name> DEALLOCATE UNUSED [KEEP <integer>];
ALTER TABLE allo_test DEALLOCATE UNUSED;
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
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];
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 for bitmap indexes
This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.
It cannot be specify MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table or if the table is empty.
ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK;
ALTER TABLE test MINIMIZE RECORDS_PER_BLOCK;
Release Control on the Number of Records Per Block
ALTER TABLE <table_name> NOMINIMIZE RECORDS_PER_BLOCK;
ALTER TABLE test NOMINIMIZE RECORDS_PER_BLOCK;
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;
Another space usage query
SELECT owner, table_name, NVL(num_rows*avg_row_len,0)/1024000 MB
FROM dba_tables
ORDER BY owner, table_name;
