Oracle tablespace management and optimization

1. TOM's table information viewing process and authorization

Create or replace procedure SHOW_SPACE (P_SEGNAME IN VARCHAR2, P_OWNER IN VARCHAR2 default user, P_TYPE IN VARCHAR2 DEFAULT 'table', P_PARTITION IN VARCHAR2 default null) -- this procedure uses authid current user so it can query dba _ * -- views using privileges from a role and so it can be installed -- once per database, instead of once per user who wanted to use it. AUTHID CURRENT_USER AS L_FREE_BLKS NUMBER; L_TOTAL_BLOCKS NUMBER; L_TOTAL_BYTES NUMBER; L_UNUSED_BLOCKS NUMBER; L_UNUSED_BYTES NUMBER; Comment VARCHAR2 (255); Comment NUMBER; L_FS1_BLOCKS NUMBER; rochelle fs1_bytes NUMBER; Rochelle fs2_blocks NUMBER; Rochelle fs2_bytes NUMBER; Rochelle fs3_blocks NUMBER; Rochelle fs3_bytes NUMBER; Rochelle fs4_blocks NUMBER; Rochelle fs4_bytes NUMBER; Rochelle full_blocks NUMBER; Rochelle full_bytes NUMBER; -- inline procedure to print out numbers nicely formatted -- with a simple label. procedure p (P_LABEL IN VARCHAR2, P_NUM in number) is begin DBMS_OUTPUT.PUT_LINE (RPAD (P_LABEL, 40 ,'. ') | TO_CHAR (P_NUM, '000000'); END; BEGIN -- this query is executed dynamically in order to allow this procedure -- TO BE CR Eated by a user who has access to DBA_SEGMENTS/TABLESPACES -- via a role as is customary. -- NOTE: at runtime, the invoker must have access to these two -- VIEWS! -- This query determines if the object is an assm object or not. begin execute immediate 'select TS. SEGMENT_SPACE_MANAGEMENTFROM DBA_SEGMENTS SEG, DBA_TABLESPACES tswhere seg. SEGMENT_NAME =: P_SEGNAMEAND (: P_PARTITION is null orseg. PARTITION_NAME =: P_PARTITION) and seg. OWNER =: P_OWNERAND SEG. TABLESPACE_NAME = TS. TABLESPACE_NAME 'into L_SEGMENT_SPACE_MGMT USING P_SEGNAME, P_PARTITION, P_PARTITION, P_OWNER; exception when partition THEN partition ('This must be a partitioned table, USE P_PARTITION => '); RETURN; END; -- if the object is in an assm tablespace, we must use this api -- call to get space information; else we use the FREE_BLOCKS -- api for the user managed segments. IF feature = 'auto' THEN blocks (P_OWNER, P_SEGNAME, P_TYPE, expiration, L_FS2_BLOCKS, blocks, L_FS3_BLOCKS, blocks, L_FS4_BLOCKS, blocks, L_FULL_BLOCKS, L_FULL_BYTES, p_PARTITION); P ('unformatted BLOCKS ', L_UNFORMATTED_BLOCKS); P ('fs1 BLOCKS (0-25)', L_FS1_BLOCKS); P ('fs2 BLOCKS (25-50 )', l_FS2_BLOCKS); P ('fs3 BLOCKS (50-75) ', L_FS3_BLOCKS); P ('fs4 BLOCKS (75-100)', L_FS4_BLOCKS); P ('full BLOCKS ', rochelle full_blocks); ELSE partition (partition => P_OWNER, SEGMENT_NAME => P_SEGNAME, SEGMENT_TYPE => P_TYPE, FREELIST_GROUP_ID => 0, FREE_BLKS => L_FREE_BLKS); P ', rochelle free_blks); end if; -- and then the unused space api call to get the rest of the -- INFORMATION. partition (partition => P_OWNER, SEGMENT_NAME => P_SEGNAME, SEGMENT_TYPE => P_TYPE, PARTITION_NAME => P_PARTITION, TOTAL_BLOCKS => partition, TOTAL_BYTES => partition, UNUSED_BLOCKS => blocks, UNUSED_BYTES => L_UNUSED_BYTES, BYTES => BYTES, BYTES => BLOCKS, LAST_USED_BLOCK => L_LAST_USED_BLOCK); P ('total BLOCKS ', L_TOTAL_BLOCKS); P ('total BYTES ', l_TOTAL_BYTES); P ('total MBYTES ', TRUNC (L_TOTAL_BYTES/1024/1024); P ('unused BLOCKS', L_UNUSED_BLOCKS); P ('unused BYTES ', L_UNUSED_BYTES ); P ('Last used ext fileid', L_LASTUSEDEXTFILEID); P ('Last used ext blockid', L_LASTUSEDEXTBLOCKID); P ('Last USED Block', L_LAST_USED_BLOCK); END; allows common users to execute SYS. SHOW_SPACESYS @ zcs11G> drop user zcs1 CASCADE; create user zcs identified by zcs; grant connect, resource, dba to zcs; grant execute on SYS. SHOW_SPACE TO zcs; connect zcs/zcsdrop table t1 purge; create table t1 (id int, name varchar2 (19) segment creation IMMEDIATE tablespace users; set serverout on; exec sys. show_space ('t1 ');

2. HVW viewing Method

. 01-view HWM = 2 1. Use user_tables to view -- HWM = 0 + 1 header block + 1 = 2ZCS @ 11g> analyze table t3 compute statistics; select '--', t. blocks, t. empty_blocks, s. blocks from user_tables t, dba_segments s where t. table_name = s. segment_name and t. table_name = 't3 'and owner = 'sys '; -- 0 = blocks used under hwm 7 = blocks allocated to unused tables outside of hwm 8 = segments of the large two, use show_space to view -- HWM = Total_Blocks8-Unused_Blocks7 + 1 = 2ZCS @ 11g> set serverout on; exec sys. show_space ('t3 ');/* free blocks ............................. 0 total blocks ............................ 8 unused blocks ........................... 7 */

3. Shrink shrinks high water level

1. shrink operation 1. the rowid of the row is changed. Therefore, you must enable row movementSYS @ zcs11G> alter table t4 enable row movement; 2. shrink space cascadecascade can be omitted) SYS @ zcs11G> alter table t4 shrink space cascade; 4. shrink space can be divided into two steps: 1. When shrink space compact is busy: Only record rows in the table are reorganized, HWM and index remain unchanged. 2. shrink space cascade is idle: All other actions

4. MOVE to contract HVW

Alter table move step: 1. desc username. table_name ---- check whether the table contains LOB2. if the table does not have the LOB field, directly alter table move; then rebuild index -- if the table contains the LOB field alter table owner. table_name move tablespace tablespace_name lob (lob_column) store as lobsegment tablespace tablespace_name; -- you can also move the lob separately, but the index on the table will also be invalid, which is not recommended as the alter table owner. table_name move lob (lob_column) store as lobsegment tablespace tablespace_name; 3. rebuild Index first, use the following SQL statement to view the index on the table: select. owner,. index_name,. index_type,. partitioned,. status, B. status p_status, B. composite from dba_indexesa left join dba_ind_partitions B on. owner = B. index_owner and. index_name = B. index_name where. owner = '& owner' and. table_name = '& table_name'; for common indexes, directly rebuild index index_name online nologging parallel. For partition indexes, you must rebuild each partition separately. For combined partition indexes, you must rebuild each subpartition separately. 4. limits on collecting statistics on tables: Although shrink can be used in 10 Gb, there are also some restrictions: 1 ). it does not work for cluster, cluster table, or objects with Long or lob columns. 2). Tables with function-based indexes or bitmap join indexes are not supported. 3) mapping tables or index-organized tables are not supported. 4). compressed tables are not supported.

Note:

1. alter table move omitting tablespace XXX, indicating that the user moves to the default tablespace. Therefore, the current tablespace must be at least twice the size of the table.

2. The alter table move process will cause index failure. You must consider re-indexing.

3. A lock will be generated during the alter table move process and should be avoided during peak business hours!

5. migrate a table to a tablespace

I. move a common table, index 1. Basic Syntax: a. alter table table_name t move tablespace xxx; B. alter index index_name rebuild tablespace xxx; move a common table, in the statement that does not use an invalid index, the statement runs normally, but if the statement uses the index primary key as the unique index), the index used in the report becomes invalid, statement execution fails. Other statements, such as foreign keys, non-null constraints, and default values, are not invalid. 2. re-create a primary key or index. The basic syntax is: a, alter index index_name rebuild; B, alter index pk_name rebuild; 3. Use the rebuild syntax to move the index: a. alter index index_name rebuild tablespace tbs_name; B. alter index pk_name rebuild tablespace tbs_name; 2. move a partitioned table and an index is the same as that of a common table. The index of a partitioned table is invalid. The difference is just syntax. 1. Basic PARTITION syntax Note: if it is a single-level PARTITION, use the keyword PARTITION. If it is a multi-level PARTITION, use SUBPARTITION instead of PARTITION. If the partition or partition index is large, you can use PARALLEL move or rebuild, PARALLEL (DEGREE 2); for example: alter table PART_ALARM move SUBPARTITION p_01 TABLESPACE usersPARALLEL (DEGREE 2 ); -- global index alter index GX1 _ PART_ALARM REBUILD tablespace usersPARALLEL (DEGREE 2); -- partition index alter index LX1 _ PART_ALARM rebuild subpartition p_01 TABLESPACE users1PARALLEL (DEGREE 2 ); alter index LX1 _ PART_ALARM rebuild subpartition p_02 TABLESPACE users2PARALLEL (DEGREE 2) ;.................. Alter index LX1 _ PART_ALARM rebuild subpartition p_0n TABLESPACE usersnPARALLEL (DEGREE 2); 2. move a PARTITION of the table alter table tab_name move PARTITION partition_name TABLESPACE tbs_name; 3. re-create the global index alter index global_index REBUILD; or alter index global_index REBUILD tablespace tbs_name; 4. re-create the local index alter table tab_name modify partition partition_name rebuild unusable local indexes; or alter index local_index_name rebuild partition partition_name TABLESPACE tbs_name; 8. table space usage is very fond of Internal Control

8. Table space usage monitoring

6-Comprehensive Monitoring dba_data_files, dba_free_space) SYS @ 11g> select '-- Disk_MB =' | disk, 'free _ MB = '| Free, 'used _ MB = '| (disk-free) from (select tablespace_name, sum (bytes)/1024/1024 disk from dba_data_files group by tablespace_name) a, (select tablespace_name, sum (bytes)/1024/1024 free from dba_free_space group by tablespace_name) B where. tablespace_name = B. tablespace_name and. tablespace_name = 'system'; -- Disk_MB = 680 Free_MB = 4.375 Used_MB = 675.62502.7-Integrated Monitoring sm $ ts_avail, sm $ ts_used, sm $ ts_free) SYS @ 11g> select '-- Disk_use_MB =' | disk. BYTES/1024/1024, 'used _ MB = '| trunc (Used. BYTES/1024/1024), 'free _ MB = '| trunc (Free. BYTES/1024/1024) fromsm $ ts_avail disk, sm $ ts_used used, sm $ ts_free freewhere disk. tablespace_name = used. tablespace_nameand disk. tablespace_name = free. tablespace_nameand disk. tablespace_name = 'system'; -- Disk_use_MB = 680 Used_MB = 674 Free_MB = 4 3. View percentage SYS @ 11g> select '-- Disk_use_MB =' |. bytes/1024/1024, 'used _ MB = '| trunc (B. bytes/1024/1024), 'free _ MB = '| trunc (C. bytes/1024/1024), '% USED =' | trunc (B. bytes * 100)/. bytes), '% FREE =' | trunc (C. bytes * 100)/. bytes) FROMSYS. sm $ ts_avail A, SYS. sm $ ts_used B, SYS. sm $ ts_free cwhere. tablespace_name = B. tablespace_nameAND. tablespace_name = C. tablespace_nameand. tablespace_name = 'system'; -- Disk_use_MB = 680 Used_MB = 674 Free_MB = 4% USED = 99% FREE = 0

This article from the "No dual city" blog, please be sure to keep this source http://929044991.blog.51cto.com/1758347/1303503