Partition Table Is Not Getting Dropped?

May 2, 2012

I am trying to delete the partition, but I am getting error "ORA-01426: numeric overflow"

The original partitioning was:

TABLE_NAME PARTITION_NAME NUM_ROWS
F_TFP_CP_MONTH P_201108 0
F_TFP_CP_MONTH P_201201 (NULL)
F_TFP_CP_MONTH P_201202 (NULL)
F_TFP_CP_MONTH P_99999999 (NULL)
F_TFP_CP_MONTH P_201106 1159130358
F_TFP_CP_MONTH P_201105 0
F_TFP_CP_MONTH P_201104 1212566971
F_TFP_CP_MONTH P_201103 1002557990
F_TFP_CP_MONTH P_201102 0
F_TFP_CP_MONTH P_201101 0
F_TFP_CP_MONTH P_201012 0
F_TFP_CP_MONTH P_201011 0
F_TFP_CP_MONTH P_201010 0
F_TFP_CP_MONTH P_201009 0
F_TFP_CP_MONTH P_201112 (NULL)
F_TFP_CP_MONTH P_201111 (NULL)
F_TFP_CP_MONTH P_201110 (NULL)
F_TFP_CP_MONTH P_201109 (NULL)
F_TFP_CP_MONTH P_201107 1627218307

All partitions were dropped, but three; these three returned the same error when trying to drop them:

SQL> alter table dw.F_TFP_CP_MONTH drop partition P_201112;
alter table dw.F_TFP_CP_MONTH drop partition P_201112
*
ERROR at line 1:
ORA-01426: numeric overflow

SQL> alter table dw.F_TFP_CP_MONTH drop partition P_201111;
alter table dw.F_TFP_CP_MONTH drop partition P_201111
*
ERROR at line 1:
ORA-01426: numeric overflow

SQL> alter table dw.F_TFP_CP_MONTH drop partition P_201110;
alter table dw.F_TFP_CP_MONTH drop partition P_201110
*
ERROR at line 1:
ORA-01426: numeric overflow

So, the situation now is that the table only has these three partitions, and we are not able to empty the table, so that we can later purge it and recover the

space.

free that space and empty the contents of the table?

View 1 Replies


ADVERTISEMENT

How To Recover A Dropped Table

Apr 10, 2013

OS: AIX
DB: 11G

I need to recover a dropped table, I have a RMAN backup of midnight and the table was dropped at 7:00 PM today. I do not have flash back enabled on the database. I am thinking of restoring midnight backup and applying the archives just before the table was dropped and opening the database in resetlogs method.

View 4 Replies View Related

SQL & PL/SQL :: Undo Dropped Column From Table

Jul 13, 2013

Flashback query is working fine for dropped table,but throwing error when trying to get back the previous date by dropping a column from a table.

FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2013-07-13 05:00:00', 'YYYY-MM-DD HH24:MI:SS')

SQL Error: ORA-01466: unable to read data - table definition has changed

Is that a limitation of Oracle by not having query to flashback a column but a table.

View 4 Replies View Related

Backup & Recovery :: Recover A Dropped Table From Database As Well As Recyclebin?

Sep 23, 2011

can we recover a dropped table from database as well as recyclebin.

View 13 Replies View Related

Performance Tuning :: How To Assure Partition Table Is Better Than Non-partition Table

Mar 25, 2011

I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?

I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?

View 11 Replies View Related

Performance Tuning :: Select Partition Table With Non-partition Key Condition?

Jun 26, 2010

I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk

when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)

View 3 Replies View Related

SQL & PL/SQL :: Alter Table To Create Partition On Non-partition Table?

Jun 19, 2012

Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.

View 1 Replies View Related

SQL & PL/SQL :: Add Range Sub-Partition To Hash Partition Table

Jan 2, 2013

Can I add range sub partition to a hash partition table. Example like this.

CREATE TABLE test
(
test_id VARCHAR2(10 ) ,
test_TYPE VARCHAR2(5) ,
CREATE_DATE date
)
partition by hash (test_id, test_type)
Partitions 3
SUBPARTITION BY RANGE (CREATE_DATE);

When Tried, I am getting syntax error as invalid option.

View 8 Replies View Related

SQL & PL/SQL :: Adding A New Partition To Existing Partition Table

Jun 14, 2011

i want to create a new partition for version 2

existing table is as below

create table test
(
name varchar2(100),
version NUMBER(12)
)

[Code]....

View 15 Replies View Related

SQL & PL/SQL :: Partitioned Table - Inserted Partition Key Does Not Map To Any Partition

Jul 10, 2012

I have two tables in which one is partitioned table with the following details.

CREATE TABLE "SCOTT"."TBL_MITTAL"
("ACCOUNT_NAME" VARCHAR2(50 BYTE),
"BILL_NO" VARCHAR2(50 BYTE),
"BILL_DATE" VARCHAR2(50 BYTE),
"CLI" VARCHAR2(50 BYTE),
"ANI" VARCHAR2(50 BYTE),

[code].....

When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error

SQL> insert into tbl_temp select * from tbl_mittal;
insert into tbl_temp select * from tbl_mittal
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.

ACCOUNT_NAMEBILL_NOBILL_DATECLIANICHARGE_START_DATEDURFROM_LOCATIONTO_LOCATIONINVOICE_IDCIRCLE
10000010357423128271095119301-Feb-111723006000931488182328-JAN-11 11.30.54.000000000 AM59.04CHANDIGARHJAIPUR271095119
10000011844187128348720198715-Jun-121409470011825531896615-MAY-12 09.10.36.000000000 AM28.03CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531927115-MAY-12 09.10.41.000000000 AM38.32CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531933015-MAY-12 09.10.46.000000000 AM28.81CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531930215-MAY-12 09.10.53.000000000 AM28.96CHANDIGARHBANTWAL348720198

[code].....

I also tried to upload the same data using sqlldr.

sqlldr log file contents is as follows:

Total logical records skipped: 0
Total logical records read: 1857532
Total logical records rejected: 801092
Total logical records discarded: 37

[code].....

So some sqlldr bad file contents is as follows.

100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,

[code].....

View 2 Replies View Related

Backup & Recovery :: Restore Dropped Or Wrongly Updated Table Using RMAN Backup?

Feb 9, 2011

Is it possible to restore dropped table OR wrongly updated table using RMAN backup.

View 18 Replies View Related

Partition Size In A Partition Table

Jun 8, 2011

How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')

View 3 Replies View Related

How To Assure Partition Table Is Better Than Non-partition Table

Mar 25, 2011

I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?

I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?

View 2 Replies View Related

SQL & PL/SQL :: Add Partition To Table Without Partition?

May 5, 2011

I am trying to add partition to table without partition

with following code

ALTER TABLE ACC_LOC1_TAB
ADD PARTITION testpart BY RANGE (ALT_AUTHDT)
(PARTITION UPTO_2010 values less than (31-mar-2010),
PARTITION APR_JUN_10 VALUES less than (30-JUN-2010),
PARTITION JUL_SEP_10 VALUES less than (30-SEP-2010),
PARTITION OCT_DEC_10 VALUES less than (31-DEC-2010),
PARTITION JAN_MAR_11 VALUES less than (31-MAR-2011))

it will raise error ora-14020

View 4 Replies View Related

Partition A Non-Partition Table In 11.2.0.1

Feb 21, 2013

I am using Oracle 11.2.0.1 Oracle Database.I have a table with 10 Million records and it's a Non Partitioned Table.

1) I would like to partition the table (with partition by range ) without creating new table . I should do it in the existing table itself (not sure DBMS_ REDEFINITION is the only option ) (or) can i use alter table ...?

2) Add one partition which will have data for the unspecified range.

View 5 Replies View Related

SQL & PL/SQL :: Dropping Sub-partition / Range-partition And List Sub-partition

May 28, 2010

i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.

View 6 Replies View Related

View All User That Was Dropped?

Aug 12, 2010

is it possible to view all user that was dropped? how can i know that one user was create by who>? i'm running on multi dba user in my db. are there any history record when we create user?

View 4 Replies View Related

SQL & PL/SQL :: Triggers Dropped And Stored In Recycle Bin

Dec 5, 2012

Some triggers are dropped and stored in recycle bin. when i am trying to restore it by the command

1* alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
SQL> /
alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
*

ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

View 13 Replies View Related

Backup & Recovery :: Retrieve Dropped Tables?

Jun 20, 2012

One of the developer dropped 18 tables by mistake on USER@db schema. Developer asked whether I can restore those tables, but unfortunately they are not in the recycle bin. Developer is re-creating the table now though. But I am just curious whether we need to change these parameters (db_recycle_cache_size) in order support any similar requests in the future.

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recycle_cache_size big integer 0
recyclebin string on

View 10 Replies View Related

How To Audit When A Constraint Is Disabled / Enabled Or Dropped

Sep 3, 2013

Is it possible to audit when a constraint is disabled, enabled or dropped? Sometimes I wonder why some constraints are missing. To make sure someone is dropping I would like to audit that action. Further, I would need to compare schemas to realize if some constraints are missing. As it names are generated automatically by oracle, how could I easily run a select to compare the constraints that does not match between schemas?

View 10 Replies View Related

Server Administration :: Dropped User In Query Result?

Jul 17, 2005

I had droped one user from my oracle database..but when i run this below query i found that user exist

SQL>select username from dba_users;

but when i try to connect that user i get result

SQL> connect sunil/sunil
ERROR:
ORA-01017: invalid username/password; logon denied

Now tell me how to remove this user from result of this query

SQL>select username from dba_users;

View 11 Replies View Related

Backup & Recovery :: Restore Schema And Its Associated Objects Once Dropped?

Sep 11, 2011

Is there a way to restore the schema and its associated objects once it is dropped?

1. The flash back database feature is not ON in the database where schema was dropped.

2. The data files/ table space has not been dropped

3. There was no export taken for the schema.

View 4 Replies View Related

SQL & PL/SQL :: ORA-29279 / SMTP Permanent Error / 550 (Dropped Spammer Pretending To Be Us)

Apr 13, 2011

We have scheduled many email alerts for management, these were running fine but from three days, we are facing problem.

We received following Error during sending email from Database.

ORA-29279: SMTP permanent error: 550 "Dropped spammer pretending to be us"

View 1 Replies View Related

Automatic Storage Management :: Disk Dropped After The Time Has Passed?

Mar 16, 2013

After a power failure, I have now dropped disks in my 2 diskgroups DG_RACDATA1 and OCRVD.

Disks has been renamed and prefixed by DROPPED...

Their state is now "FORCING"
Mount status = missing
header status = unknown
Mode status = Offline

These disks was belonging to a failure group with redundancy so the cluster still works ...but I have lost the redundancy.

How to reuse these offline disk or confirm that what I plan to do is good:

Shall I put them online ? ALTER DISKGROUP DG_RACDATA1 ONLINE DISK DROPPED0000_DG_RACDATA1

Shall I drop them ? ALTER DISKGROUP DG_RACDATA1 DROP DISK DROPPED0000_DG_RACDATA1 FORCE

and then: zeroing them and readd to failgroup ? but how ?

View 4 Replies View Related

How To Partition And Index The Table

Oct 24, 2011

how to partition and index my table for a special problem.

The table:

CREATE TABLE TEST (
ID NUMBER PRIMARY KEY,
U_VALUE NUMBER NOT NULL, -- Ranges from 0 - 30.000.000
O_VALUE NUMBER NOT NULL, -- Ranges from U_VALUE - 30.000.000
CREATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
UPDATE_TS TIMESTAMP NOT NULL,
ITEM_TYPE NUMBER NOT NULL --<< Only 4 different values >>
);

As you can see, U_VALUE is ALWAYS lower than O_VALUE.I need to have the CREATE_TS in either main- or subpartition do drop the partitions after some time so i don,t have to use DELETE statements. The table has 360 millions rows.

The application has only 8 query which will always use a WHERE clause like this:

SELECT * FROM TEST
WHERE U_VALUE <= :1 AND O_VALUE => :2 AND ITEM_TYPE = :3

1. Is there any good technique how to create a good index for the queries (application will execute 120 queries per second)?

2. how to partition this table?

View 2 Replies View Related

Locks On Partition Table?

May 13, 2011

I have the below questions on the locking mechanism in a partition table. Example I execute the below query,

delete from table_name subpartition (subpartition_name);

In that case,

1. If we perform above query, then which level of lock is happened to the table/partition & sub-partitions?

2. If we perform above query on same sub-partition of the same table with different where clause, what will happen?

3. If we perform above query for same table but different sub partition at same time, what will happen?

View 2 Replies View Related

SQL & PL/SQL :: Indexing In Partition Table

Apr 16, 2013

i want to ask about indexing in partition table. i have table that indexed by local index. when i want to select all data. I execute this query

select * from Book_Issue_Part where status='Pinjam';

but it does not select all data, only partly data have selected. is it a wrong query to select all data in indexing partition table? so what query should i execute to get all data.

View 2 Replies View Related

SQL & PL/SQL :: Table Partition Using Joins

Mar 21, 2011

can i able to partition the table based on the column which is in another table ??

For example Table X need to be partitioned based on the column in The Table Y . and Table X and table Y has some relation.

View 2 Replies View Related

SQL & PL/SQL :: Partition Of Existing Table

Nov 30, 2011

We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.

View 5 Replies View Related

SQL & PL/SQL :: Analyze Partition Table

May 22, 2012

Currently we are deleting the partition daily. Table structure is Interval partitioning. After deleting partition how to analyze the partition table.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved