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?
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.
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?
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)
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.
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.
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.
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')
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?
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))
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.
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.
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?
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
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
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?
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)?
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.
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.