0

I have Oracle database 12 with tablespace with bigfile Yes

I have limited to 120Gb file data so I need change my tablespace to bigfile no and create multi datafile for my tablespace.

I need to export or copy data from old to new table but my problem is I have over 1 billion records and I don't know what is the fastest way to copy this table to another table.

CREATE TABLE C##USER.READ 
(
  DEVICEID NUMBER(38, 0) 
, THETIME VARCHAR2(20 BYTE) 
, THEH VARCHAR2(10 BYTE) 
, ENERGYEXPORT NUMBER(10, 0) 
, LOADPROFILE NUMBER(10, 0) 
, DEMANDREGISTER NUMBER(10, 0) 
, AREA NUMBER(38, 0) 
, STATUSM NUMBER(2, 0) 
) 
TABLESPACE TABLE_SPACE 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NOPARALLEL 
PARTITION BY LIST (AREA) 
SUBPARTITION BY RANGE (THETIME) 
(
  PARTITION AREAPART VALUES (DEFAULT) 
  TABLESPACE TABLE_SPACE 
  PCTFREE 10 
  INITRANS 1 
  STORAGE 
  ( 
    BUFFER_POOL DEFAULT 
  ) 
  NOCOMPRESS NO INMEMORY 
  (
    SUBPARTITION AREAPART_THETIME VALUES LESS THAN (MAXVALUE) 
    NOCOMPRESS NO INMEMORY  
  )  
);

CREATE INDEX C##USER.READ_INDEX1 ON C##USER.READ (DEVICEID ASC) 
LOGGING 
TABLESPACE TABLE_SPACE 
PCTFREE 10 
INITRANS 2 
STORAGE 
( 
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
NOPARALLEL;

The tablespace Sql :

CREATE BIGFILE TABLESPACE TABLE_SPACE 
    DATAFILE 
        '~\TABLE_SPACE.DBF' SIZE 127301910528 AUTOEXTEND ON NEXT 8192 MAXSIZE 35184372064256 
    BLOCKSIZE 8192 
    DEFAULT NOCOMPRESS NO INMEMORY 
    ONLINE 
    SEGMENT SPACE MANAGEMENT AUTO 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
0
1

Move the original table from the old tablespace to the new tablespace instead. There is no need to create a new table and copy the records.

For a partitioned object, move each partition individually. See:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.