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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform