Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

creating partition in existing tables also getting errors and solutions

partition errors:-
1)
SQL> Create table t1_part as select * from all_users;
Table created.
SQL> ALTER TABLE T1_PART ADD CONSTRAINT T1_PK PRIMARY KEY(USER_ID);
Table altered.

SQL> DESC T1_PART;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 CREATED                                   NOT NULL DATE


SQL> Create table t2_part(User_name Varchar2(30) NOT NULL,user_id   Number NOT NULL,Created   DATE NOT NULL )
    partition by hash(user_id) partitions 4;
Table created.

SQL> DESC T2_PART;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_NAME                           NOT NULL VARCHAR2(30)
 USER_ID                                  NOT NULL NUMBER
 CREATED                                 NOT NULL DATE

SQL> ALTER TABLE T2_PART ADD CONSTRAINT T2_PK PRIMARY KEY(USER_ID);
Table altered.

SQL> BEGIN
    dbms_redefinition.abort_redef_table(uname=>'SCOTT',orig_table=>'T1_PART',int_table=>'T2_PART');
  END;
    /

PL/SQL procedure successfully completed.

SQL> begin
     dbms_redefinition.can_redef_table(uname=>'SCOTT',tname=>'T1_PART');
     dbms_redefinition.start_redef_table(uname=>'SCOTT',orig_table=>'T1_PART',int_table=>'T2_PART');
     dbms_redefinition.finish_redef_table(uname=>'SCOTT',orig_table=>'T1_PART',int_table=>'T2_PART');
    end;
    /
begin
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 3

solution:-

you changed column names "user_name" - if you do that, you'd have to do a bit more work (column mapping)
if you use the same names, simple:

9IR2> Create table t2_part(Username Varchar2(30) NOT NULL,user_id   Number NOT NULL,Created   DATE NOT NULL)
    partition by hash(user_id) partitions 4;

Table created.

9IR2> ALTER TABLE T2_PART ADD CONSTRAINT T2_PK PRIMARY KEY(USER_ID);

Table altered.

9IR2> exec dbms_redefinition.can_redef_table(uname=>user, tname=>'T1_PART');

PL/SQL procedure successfully completed.

9IR2> exec dbms_redefinition.start_redef_table(uname=>user, orig_table=>'T1_PART',int_table=>'T2_PART' );

PL/SQL procedure successfully completed.

9IR2> exec dbms_redefinition.finish_redef_table(uname=>user, orig_table=>'T1_PART',int_table=>'T2_PART');

PL/SQL procedure successfully completed.





2)

CREATE TABLE HE0_DT_INF_INTERFAZ_MES
  (
    COD_PAIS NUMBER,
    FEC_DATA NUMBER,
    INTERFAZ VARCHAR2(100)
  )
  partition BY RANGE(COD_PAIS, FEC_DATA)
  (
    PARTITION PDIA_98_20091023 VALUES LESS THAN (98,20091024)
  );

SQL> INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
                                  VALUES(98, 20091024, 'CTA');  2
INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


solution:-

But, we I attempt (97,20091024), it goes through

SQL> INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)

  2                                    VALUES(97, 20091024, 'CTA');




3)

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools