Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

More and More migrated and chained rows

I have created a locally managed tablespace and then created a table.
Its have more than 5 millions chained rows.It is going on increasing every day.
Please give me some suggestion.
I think I the table is not correctly created with good space.
I am attaching the script how I have created the tablespace and the table.


CREATE TABLESPACE COMM DATAFILE
  '/oradata/pecom/db4/pecom01.dbf' SIZE 2100M AUTOEXTEND OFF,
  '/oradata/pecom/db1/pecom02.dbf' SIZE 1500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M,
  '/oradata2/pecom/db6/pecom03.dbf' SIZE 1500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M,
  '/oradata2/pecom/db7/pecom04.dbf' SIZE 1500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M,
  '/oradata/pecom/db4/pecom05.dbf' SIZE 1500M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

-----------------------------------------------------------------------

CREATE TABLE VOTE_TODAY
(
  AGENDA_NBR             CHAR(9 BYTE)           NOT NULL,
  PROPOSAL_NBR           NUMBER(3)              NOT NULL,
  VOTE_GROUP_ID          NUMBER(10)             NOT NULL,
  SHARES_FOR             NUMBER(17,5),
  SHARES_AGAINST         NUMBER(17,5),
  SHARES_ABSTAIN         NUMBER(17,5),
  SHARES_TNA             NUMBER(17,5),
  SHARES_MEETING_ATTEND  NUMBER(17,5),
  CREATED_ON             TIMESTAMP(6)           NOT NULL,
  CREATED_BY             CHAR(18 BYTE)          NOT NULL,
  MODLAST_ON             TIMESTAMP(6),
  MODLAST_BY             CHAR(18 BYTE)
)
TABLESPACE PECOM
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCACHE
NOPARALLEL;
Please suggest.



0
ballioballi
Asked:
ballioballi
1 Solution
 
jrb1Commented:
I'd recommend:

1) analyze table vote_today compute statistics;

2)

select least(round(100-((2*avg_row_len)/(8096/10))),95) pctused
,greatest(round((2*avg_row_len)/(8096/10)),5) pctfree
from dba_tables
where table_name = 'VOTE_TODAY';


3) Use the first value for your pctused, the second for pctfree.

alter table vote_today pctused #newnum1 pctfree #newnum2;

4) Probably want to do something to reorg the table.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now