Author: snowhite2000 Date: 02-03-13 20:21
How to manage the space used by Table
1. How to find your table there is fragmentation: (a) analyzed table tablename compute statistics; ---- you may analyze all the tables under that user schema) (b) select table_name, round (avg_row_len * num_rows / 1024/1024, 0) Data_size from user_tables; ---- You can know about Data Size (unit: m) (c) in the table, SELECT Segment_name, Round (Blocks * 8/1024, 0) table_size from user_segments where segment_type = 'Table'; ---- You can know the actual size of your table (unit: m) If you compare the same table (c) Table_size is far greater than (b) Data_Size, your table may have Fragmentation. Perhaps there may be useful extens in High Water Mark, if this is the case, you can directly DELLOCATE UNUSED SPAALLOCATE Keep 0; 2. If you are determined to be Fragmentation, there is a lower method to solve it. (A) create a temp table, copy all the data from fragmented table to temp table, truncate the fragmented table, copy data back, then drop temp table: ---- this way, you really do not need to recrate any indexes , constrains etc. but if there are columns in the table are other tables' reference foreign key, and the table has some procedures / tiggers, you have to disable them all before truncate the table. and, you also need enable those foreign keys, procedures and triggers (b) alter table tablename move tablespace B;. (tablespace B is different than original tablespace, you may move back from tablespace B to original one, run table move commend again) ---- you need rebuild all indexes on The Table (C) Export / Import ---- This method doesn't have to say more. Please have a good way to follow. Thank you.
Author: Ben Ben Bear
Time: 02-03-13 22:16
How to find your table space with fragmentation: Ttitle 'TableSpace Fragmentation Information INFORMATION'SELECT SUBSTR (TS.NAME, 1, 10) Tspace, TF.Blocks Blocks, Sum (F.Length) Free, Count (*) PIECES, MAX (f Biggest, MIN (F.Length) Smallst, Round (Avg (F.Length)) AverageFrom Sys.Fet $ F, SYS.FILE $ TF, SYS.TS $ TSWHERE TS.TS # = f.ts # a Ts.ts # = tf.ts # group by ts.name, tf.blocks; Solving the method I agree with you. Author: ArthurXu
Time: 02-03-13 23:36
Try this one
column table_name format a40column degree heading "DEGREE" column density heading "DATA | DENSITY" column new_free format 99 heading "SUGGEST | PCTFREE" column new_used format 99 heading "SUGGEST | PCTUSED" column reads_wasted format 999999 heading "MBREADS | TO SAVE" select / * Ordered * / u.name || '.' || oname table_name, LPAD (DECODE (T.degree, 32767, 'Default', NVL (T.degree, 1)), 7) Degree, Substr TO_CHAR (100 * T. Rowcnt / (Floor ((P.Value - 66 - T.initrans * 24) / Greatest (T.AVGRLN 2, 11)) * T.BLKCNT), '999.00'), 2) | | '%' Density, 1 New_Free, 99 - CEIL ((100 * (P.Value - 66 - T.Initrans * 24 -Greatest (Flors ((P.Value - 66 - T.initrans * 24) / gretemest (T .avgrln 2, 11)) - 1, 1) * Greatest (T.avgrln 2, 11)) / (P.Value - 66 - t.initrans * 24))))))))))))))))))))))))))))))))))))))))))))))))))) New_USED T.Rowcnt / Floor ((P.Value - 66 - t.initrans * 24) / greatest (t.avgrln 2, 11))) / m.value) Reads_wastedFromsys.tab $ T, (SelectValuefromsys.v_ $ parameterwherename = 'DB_FILE_MULTIBLOCK_READ_COUNT') M, SYS.OBJ $ O, SYS.USER $ U, (Select Value from sys.v_ $ parameter where name = 'db_block_size') PWHERET.TAB # is null andt.blkcnt> m.value andt.chncnt = 0 andt.avgspc> T.avgrln andceil ((t.blkcnt - T.Rowcnt / Floor) (P.Value - 66 - T.initrans * 24) / Greatestest (T.avgrln 2, 11)))) / m.value)> 0 ando.obj # = T.Obj # ando.owner #! = 0 AnDu .user # = o.owner # order by5 desc, 2 / author: Yong Huang
Time: 02-03-14 15:08
RE: How to manage the space used by Table
Comments on the following suggestion.1. It's only available in Oracle8i.2. Tablespace B can be the same as the original.3. In fact, the tablespace clause can be omitted entirely.4. Do remember to rebuild indexes as snowwhite2000 suggested. Indexes become unusable after alter table move, even if the indexes are on columns with no data.Here's my screen: SQL> alter table t move; Table altered.SQL> select tablespace_name from user_tables where table_name = 'T'; tABLESPACE_NAME --- --------------------------- Systemsql> ALTER TABLE TABLE TABLESPACE System; Table Altered.note: Don't create a Table in System Like I do here.yong huangquote:
Originally released by Snowhite2000
(B) alter table tablename move tablespace B; (tablespace B is different than original tablespace, you may move back from tablespace B to original one, run table move commend again) ---- you need rebuild all indexes on the table
Author: Yong Huang
Time: 02-03-14 15:22
This Query May Fail To Find Anything If The TableSpace IS Locally Managed.Yong Huang
Quote:
Originally published by stupid bear
SELECT SUBSTR (TS.NAME, 1, 10) Tspace, Tf.Blocks Blocks, SUM (F.Length) Free, Count (*) Pieces, Max (F.Length) Biggest, Min (F.Length) Smallst, Round AVG (F.Length)) AVGEFROM sys.FET $ f, sys.file $ tf, sys.ts $ tswhere ts.ts # = f.ts # and ts.ts # = tf.ts # group by ts.name, Tf.blocks;
Author: flyingknife
Time: 02-03-14 22:02
Is there any way?
If Oracle is the version below 8i, and the database needs to run in 7 * 24 mode, what is the way to clean the debris?
Author: chao_ping
Time: 02-03-15 03:59
Quest's product Live-REORG can meet your needs. However, it should be very expensive.
Author: oracledba
Time: 02-03-16 06:19
If there is a fragmentation. . . ALTER TABLESPACE TEMP COALESCE; do you do this?
Author: chao_ping
Time: 02-03-16 06:40
This statement is helpless. It is only possible to fusion the adjacent fragments. It turned out that Yong Huang said this problem, that is, the fragment of the table space is divided into two kinds, one is that the debris is adjacent, one is separated by an extent. The second case is true fragments. And your sentence can handle the first second. The first second actually you can do pctincrease = 1. Or automatically merge it when Oracle Allocate New Extent. The debris restructuring of the table space is still more trouble, if you are interested, you can discuss it. Author: snowhite2000
Time: 02-03-17 01:43
How to find existing Table Storage Parameter is not suitable Storage Parameter causes Fragmentation. How to find an inappropriate parameter? This is a way: run this script:
Code:
------------------------------------------------ TTITLE - center 'Segment Storage Summary' skip 2 col ownr format a8 heading 'Owner' justify c col name format a28 heading 'Segment name' justify c col type format a8 heading 'Type' justify c trunc col hfil format 9,990 heading 'Header | File' justify c col hblk format 99,990 heading 'Header | Block' justify c col exts format 9,990 heading 'Extents' justify c col blks format 999,990 heading 'Blocks' justify c break on ownr skip 1 select owner ownr, segment_name name, segment_type type, header_file hfil, header_block hblk, extents exts, blocks blks from dba_segments where owner like upper ( '& ownr') and segment_name like upper ( '& segt') / undef ownr undef segt set verify on ------------ ----------------------------------------
I am in a problematic Database:
Code:
Segment Storage Summary Header Owner Segment Name Type File Block Extens Blocks ------------------------------------------------------------------------------------------------------------------------------------------ ------------ -------------- -------- Clinical Account_info Table 18 2 236 4,720 Address Table 19 2 1,359 27,465 Allergy_INFO TABLE 27 2 1 20 aUDIT_EVENT TABLE 20 2 1 80 cONTACT_INFO TABLE 21 2 164 3,280 CONTACT_TYPE_DESC TABLE 27 22 1 20 DOCTOR_HOSPITAL_ASSOCIAT TABLE 27 42 1 50 DOCTOR_INFO TABLE 27 92 1 35 DOCTOR_PATIENT_RELN TABLE 22 2 158 3,160 DOC_PAT_RELN_CODE TABLE 27 127 1 20 DRUG_INFO TABLE 27 147 1 80 ERROR_PROCEDURES TABLE 22 22 624 12,620 ERROR_TXN TABLE 8 2 1,691 108,480 ERROR_TXN_DESC TABLE 27 227 1 20 GUARANTOR TABLE 22 182 154 3,080 HOSPITAL_INFO TABLE 27 247 1 20 INSURANCE TABLE 23 2 523 10,460 LAB_RAD_ORDER TABLE 24 2 2,087 42,145 lab_rad_order_result Table 25 2 560 11,
200 lab_rad_order_status table 27 267 1 20 lab_rad_result_desc Table 26 2 9,013 169,442 Medical_Record Table 24 22 49 with the following Statement check:
Code:
SQL> Select segment_name, next_extent / 1024 from user_segments; segment_name next_extent / 1024 ----------------------------------- ---------------- ACCOUNT_INFO 128 ADDRESS 128 ALLERGY_INFO 128 aUDIT_EVENT 128 cONTACT_INFO 128 CONTACT_TYPE_DESC 128 DOCTOR_HOSPITAL_ASSOCIAT 128 DOCTOR_INFO 128 DOCTOR_PATIENT_RELN 128 DOC_PAT_RELN_CODE 128 DRUG_INFO 128 ERROR_PROCEDURES 128 ERROR_TXN 512 ERROR_TXN_DESC 128 GUARANTOR 128 HOSPITAL_INFO 128 Insurance 128 LAB_RAD_ORDER 512 LAB_RAD_ORDER_RESULT 128 LAB_RAD_RADER_STATUS 128 lab_RAD_RESULT_DESC 128 Medical_Record
Notice: lab_rad_result_desc Table 26 2 9,013 169,442 The result of this line, 169442 blocks from 9013 Extens, generated OveRextended Segments. So the solution is to increase the size of Next_extent, I will increase the original 128K to 512K. REORGINIZE TABLES is awaiting next time Database Downtime. View the result. Similarly, you can use the TableSpace map tool in OEM Diagnostics Pack. After Analyze TableSpace, Report has a problem with tables / indexes. Just hosted your advice, thank you.
Author: iHero Date: 02-03-18 00:13 using the OEM will make your analysis more convenient: The following is my analysis and examples: use the OEM database management is very convenient: a TABLEASPCE MAP -> choose the table space Analysis 2002-1-22 Table Space Analysis Report Results TABLESPACE Analysis Report Type Section Early Warning Status ----------------------------- -------------------------------------------------- - Table IHERO.IHERO_RESUME_BAS ALERT ALERT: Over-extension segments. The allocated area: 2350. Warning: Excessive chain line. 7.01% of the rows are linked / transplanted. Walking length (bytes): 311. Table IHERO.IHERO_RESUME_EDU ALERT ALERT: Over-extension segments. The allocated area: 1091 Excessive row chaining In both cases, the data in a table or the table partition may not be loaded with a single data block. Therefore, a line of fragments are generated. In the first case, when the line is inserted for the first time, a data block cannot be loaded due to the large line. Oracle Server uses a series of data blocks that remain in this segment store row data. Chain rows often appear in large rows, such as rows that contain a long data type. In this case, if the larger database block size defined by the DB_BLOCK_SIZE initialization parameter is not used, the line continues is inevitable. However, in the second case, it is initially loaded into a row of a data block to be updated, making the length of the entire row increase, and the idle space of the data block is completely filled. In this case, Oracle Server ports the entire row data to a new data block (assuming this destination can be loaded into new blocks). Oracle Server retains the original line of the implantation to reference the new block containing the transplant line. When continuous or transplantation, the input / output performance associated with the row is reduced because Oracle Server must scan more than one data block to retrieve the information of the row. There are two ways to solve the problem of row. Rebuilding tables or table partitions can avoid row fragment as the lines are closely loaded in the reconstruction process. However, if other changes to the table or table partition (just repair the transplant line), the problem is fixed, not fully rebuilt. Note: If the row may increase in the update, consider adding a PCTFREE value. My analysis. My database db_block = 8K is large enough. And the line size is not very large, the main reason for row transplant is data update Among them, the original desire is not much. So re-establish the parameters of the table to increase the PCTFREE value. Import data .1. Creat Table Temp as select * from tablename 2. Drop Table name .3. Re-build table (Change Table Parameters) 4. INSERT INTO TABLENAME SELECT * from Temp. Import data. You can use INSERT / * APPEND * / INTO TALBENAME SELECT * form TEMP. Avoid logging. Direct Load Insert. The allocated area is too much. Analysis cause may be The original parameters of the table space are too small. 1. Adjust the TableSpace next size. ALTER TABLESPACE TABLESPACENAME Minimum Extent 54k2.exp Export Data .3. Use the indexfile parameters when executing the IMP command: IMP userid = scott / tiger file = Emp.dmp.dmp.dmp.dmp.dmp IndexFile = Emp.sql Oracle writes the creation information of the table and the index to the specified file instead of writing the data back.
Editing it, remove information such as "REM", find the initial parameter, change it as needed. 4. Execute Emp.SQL in SQL * Plus. 5.IMP load data: Imp userid = scott / tiger ignore = y file = emp.dmp also can not establish a SQL table files directly IMP ALTER TABLE TABLENAME STORAGE (NEXT 540k) of storage parameters modified in the table...: ligengocp time: 02-03-20 23:44 1 What is Migrated and Chained RowsIf an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block This is called chaining a row Rows can also be chained when they are inserted Dynamic space management, especially migration and chaining, is detrimental to performance:... UPDATE statements that cause migration and CHAINING Perform poorly. Queries That Select Migrated or Chained Rows Must Perform More I / O. 2 How to view migrated and chained rows2.
. 1 Use the ANALYZE statement to collect information about migrated and chained rows For example: ANALYZE TABLE order_hist LIST CHAINED ROWS; Query the output table: SELECT * FROM CHAINED_ROWSWHERE TABLE_NAME = 'ORDER_HIST'; OWNER_NAME TABLE_NAME CLUST ... HEAD_ROWID TIMESTAMP --- --------------------------------------------- --SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96The output lists all rows that are either migrated or chained. If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows with the following steps: Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows: CREATE TABLE int_order_histAS SELECT * FROM order_histWHERE ROWID IN (SELECT Head_rowidfrom chained_rowswhere table_name = 'order_hist'); delete the migrate and chained rows from the existing table: delete from Orde r_histWHERE ROWID IN (SELECT HEAD_ROWIDFROM CHAINED_ROWSWHERE TABLE_NAME = 'ORDER_HIST'); Insert the rows of the intermediate table into the existing table: INSERT INTO order_histSELECT * FROM int_order_hist; Drop the intermediate table: DROP TABLE int_order_history; Delete the information collected in step 1 from The Output Table: Delete from Chained_Rowswhere Table_name = 'Order_hist'; 2.2 View Table Fetch Continued ROW Record in V $ SysStat 3. How to avoid migrated and chained rows3.1 to increase table parameters PctFree, let block have more space Increased recording 3.2 Increase db_block_size, you can accommodate larger words ** chaining is offen unavoidable with tables That Have a long column or long char varcha2 columns. **
Author: ligengocp Date: 02-03-21 00:04 1 What is Migrated and Chained RowsIf an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block This is called chaining a row Rows can also be chained when they are inserted Dynamic space management, especially migration and chaining, is detrimental to performance:... UPDATE statements that cause MIGRATION AND CHAINING Perform Poorly. Queries That SELECT MIGRATED OR CHAINED ROWS MUST Perform More I / O. 2 How to View Migrate and Chained Rows2.
1 using Analyze with LIST CHAINED ROWS, which analyzes Migrated and Chained Rows, the result may be input to the table CHAINED_ROWS, the script /ORACLE_HOME/RDBMS/ADMIN/UTLCHAIN.SQL follows: create table CHAINED_ROWS (owner_name varchar2 (30), table_name varchar2 (30), cluster_name varchar2 (30), partition_name varchar2 (30), subpartition_name varchar2 (30), head_rowid rowid, analyze_timestamp date); Use the ANALYZE statement to collect information about migrated and chained rows For example: ANALYZE TABLE order_hist LIST cHAINED. Rows; Query the Output Table: Select * from chained_rowswhere Table_name = 'Order_hist'; Owner_Name Table_name Clust ... Head_rowid TimeStamp --------------------- ... ------------------ --------- Scott Order_hist ... AAAALUAAAAAAAA1AAA 04-MAR-96SCOTT ORDER_HIST ... AAAALUAAAAAAA1AAB 04-Mar- 96SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96The output lists all rows that are either migrated or chained. If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows with the following st eps: Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows: CREATE TABLE int_order_histAS SELECT * FROM order_histWHERE ROWID IN (SELECT HEAD_ROWIDFROM CHAINED_ROWSWHERE TABLE_NAME = 'ORDER_HIST'); Delete the migrated and chained rows from the existing table: DELETE FROM order_histWHERE ROWID IN (SELECT HEAD_ROWIDFROM CHAINED_ROWSWHERE TABLE_NAME = 'ORDER_HIST'); Insert the rows of the intermediate table into the existing table: INSERT INTO order_histSELECT * FROM int_order_hist; Drop the intermediate table: DROP TABLE int_order_history;
