Partition Non-partitioned Table To Partitioned Table Date Wise?

Nov 1, 2012

what is the best way to partitioned a non-partitioned table to partitioned table date wise? I have data from last four year?

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: To Add A Partition Into A Ranged Partitioned Table

Mar 15, 2010

I want to add a partition into a ranged partioned table but I receive error:

ALTER TABLE t_log
ADD
PARTITION p897_fft
VALUES
LESS THAN (898);

ORA-14074: partition bound must collate higher than that of the last partition

i check the high value like this:

SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'T_LOG'
AND table_owner = 'PLAT'
[code]........

now, what to do? i dont want to remove Partition P899_MCDW. How to Split?

View 5 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

Server Administration :: Partition Exchange In Hash Partitioned Table

Mar 13, 2012

While trying partition exchange feature of Oracle with 2 hash partitioned tables, I come to know that I can't directly exchange partitions between 2 partitioned tables

I have two hash partitioned tables , so to move partition data from one table to another will include-

1) Exchange from partitioned table to non-partitioned table.
2) exchange from non-partitioned table to new partitioned table.

But I am not sure in which hash partition my data will go in new partitioned table (data need to be moved has single key value on basis of which tables are partitioned),

View 2 Replies View Related

Performance Tuning :: Converted Non-partitioned Table (1 Million Data) Into Range-partition

Mar 28, 2013

As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .

View 9 Replies View Related

Convert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?

Sep 10, 2012

RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?

If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?

View 7 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

SQL & PL/SQL :: Partitioned Table Information

Jan 8, 2013

How to know weather table is partitioned or not ? Is there any data dictionary view to know the information about the Partitioned type,keys on table ?

View 4 Replies View Related

SQL & PL/SQL :: Insert Into Partitioned Table

Mar 14, 2013

I have a table like this:

CREATE TABLE CARGA_P_RECARGA_NEW1
(
TELE_NUM VARCHAR2(10) NOT NULL,
FECHA DATE NOT NULL,

[Code].....

Then I tried to insert some rows in that table, every insert statement is like this:

INSERT INTO CARGA_P_RECARGA_NEW1
VALUES
('3134769595','20/01/2013 07:22:50','1107','CONFB_20121121_20121122175002 60000000000000000090.TXT',0,16,'8327--7991284',1);

Every insert I executed had the month 01 because I expected to query results only from partittion p_0113 but nevermind how query I execute, the result is always the same. I mean if I excute this statement:

SELECT * FROM CARGA_P_RECARGA_NEW1 P_0113;

I get the same result when I execute any other like this:

SELECT * FROM CARGA_P_RECARGA_NEW1 P_0213

What is the error here ?

View 4 Replies View Related

PL/SQL :: Counts On A Partitioned Table?

Apr 26, 2013

We have a partitioned transaction table in our Datawarehouse environment which has the following partition strategy

SCHEME=DATE-HASH
GRAIN=DAILY
SUBGRAIN=NONE
HASH=8
FROM=31/12/2011

We recently had to delete data from the table. This was a simple delete statement with a where clause and without taking into consideration any partition/subpartition clauses. Post committing the delete we have a count mismatch problem with two queries in particular

select count(0) count_without_parallel FROM TRANSACTION_TABLE t;

--THIS RETRIEVES *15774811* ROWS

select /* parallel(t,default) */count(0) count_with_parallel FROM TRANSACTION_TABLE t+

--THIS RETRIEVES *15777617* ROWS WHICH IS THE ACTUAL EXPECTED COUNT.

I also ran the following just to summarize

select (select count_with_parallel from (
select /* parallel(t,default) */count(0) count_with_parallel FROM TRANSACTION_TABLE t))+
-
+(select count_without_parallel from (+
select count(0) count_without_parallel FROM TRANSACTION_TABLE t)) as false_difference
from dual;

The difference in *2806* rows as expected.To re-affirm my counts I ran

select /*+ parallel(t,default) */
'count_on_t',count(*) from TRANSACTION_TABLE t
group by 'count_on_t'
order by 1;

--THIS RETRIEVES *15777617* ROWS

Removing the parallel hint reverts back to the lesser count. Not sure what is wrong but something prevents the query from parsing the whole table and/or partitions and subpartitions.

View 0 Replies View Related

Partitioned Table Based On Field

Nov 27, 2012

I have partitioned the table based on field.But when I am selecting by Partition or by the field I am getting Explain plan as Table Access full.I am pasting the sql and Explain Plan here. The table has two partition by BOOKING_DT_WID. One less than 20100801 and other less than 99991231.

CODESELECT * FROM WC_BOOKING_SALESREP_F WHERE BOOKING_DT_WID >= 20100801;
SELECT * FROM WC_BOOKING_SALESREP_F  PARTITION(SALESREP_LESS1_99991231);
Here is the Explain Plan for the same.
CODESELECT STATEMENT  ALL_ROWSCost: 1,501  Bytes: 293,923,641  Cardinality: 809,707                  
    4 PX COORDINATOR              
[code]....

How do I know if the sql is doing partition prune.

View 1 Replies View Related

Analyzing Partitioned Table On Oracle

Sep 6, 2010

A question regarding analyzing behavior of partition table on Oracle -

Is analyzing different sub partitions within a partition is same as analyzing that partition and what about vice-versa?

Attached File(s)

untitled.JPG ( 9.2K )
Number of downloads: 3

View 2 Replies View Related

Partitioned Table Number Of Tablespaces

Jan 27, 2011

I created table that is range partitioned:

CODEPARTITION t1p1 VALUES LESS THAN (TO_DATE('2011-11-01', 'YYYY-MM-DD'))
PARTITION t1p2 VALUES LESS THAN (TO_DATE('2011-11-02', 'YYYY-MM-DD'))
....
PARTITION t1p4 VALUES LESS THAN (MAXVALUE)

Every year partitions will be added for next 12 month. The table partition will be dropped every month (I have to have data from last six month so in July I could drop partition t1p1, in August - t1p2....). How many tablespaces should I create for this table and how place partitions in them to have data for last six month and use minimum space on disk?

I was thinking about one tablespace for whole table because space of each dropped partition will be reused, what do you think about that?

View 2 Replies View Related

SQL & PL/SQL :: Create Partitioned Table With Tablespace

May 17, 2012

I got just confused while looking at the below two create table statements:

CREATE TABLE Test (
TestID integer not null,
Name varchar2(20) not null )
PARTITION BY LIST (TestID)
(
PARTITION testPart1 VALUES (1) TABLESPACE tbspc1,
PARTITION testPart2 VALUES (2) TABLESPACE tbspc2@RemoteServer);

and

CREATE TABLE Test (
TestID integer not null,
Name varchar2(20) not null )
tablespace tbspc1
PARTITION BY LIST (TestID)
(
PARTITION testPart1 VALUES (1) TABLESPACE tbspc1,
PARTITION testPart2 VALUES (2) TABLESPACE tbspc2@RemoteServer);

View 11 Replies View Related

How To Archive Data On Partitioned Table

Jul 7, 2011

I have a partitioned table that is streamed to another database. I need to archive data on that table. That is I need to add a partition and remove a partition.

If I make those changes to the source table, will it stream over to the destination table?

If not, can I ...

pause streaming make changes to source table make same changes to destination table sreenable streaming. I know making data changes to the destination table can screw up streams but not sure if that holds for ddl.

View 1 Replies View Related

Partitioned Table - What Extent Size To Be Set

Sep 28, 2012

I have a partitioned table (one partition per month). Every month there are added about 1GB data. What extent size should I set? 1GB will be ok?

What if data will be greater than 1GB, adding new 1GB extent takes probably a lot of time and clients may see delays while they're inserting in this time? (it's OLTP system)

When new extent is allocated? Exact in time of lacking space in existing extent or before? Partitions are dropped after one year so free space isn't a problem.

View 6 Replies View Related

Index Creation In Partitioned Table

Jul 20, 2012

Facts:
Oracle 10.1.0.5
AIX 5.3

My table, HMTX have 10 partitions each of one have 6 millions of rows (average). We have 7 partitioned LOCAL indexes in that table.
Every month we load data into a new partition (6 million of rows aprox) and drop the oldest partition in table HMTX.

In order to do that we have a script that contain the next statements:

drop of all indexes
drop index n1;
drop index n...;
drop index n7;

[Code]...

create indexes again with tha same storage and degree parameters
CREATE INDEX hmtx_TST_N1 ON hmtx (campo1, campo2, campo3 .... campo8)
TABLESPACE xxxx
PCTFREE 0
INITRANS 2

[Code]....

My problem is in rhe index creation section, despite use parallel with degree 8 and nologging the index was created in :

Elapsed: 02:43:50.85.

In past months that index was created in :
Elapsed: 01:43:36.94
Elapsed: 04:48:31.24
Elapsed: 00:57:16.28

there are another way in order speed the index creation ?? o another way to disable ths index ??

View 4 Replies View Related

Compress Existing Table Which Is Not Partitioned

Aug 1, 2013

Im having table which is of 45M rows table [Not partitioned], Now I want to compress the old data other than last 3Months data, I should not go for partition compress. Rarely some select queries will be fired on that Old data. Now how can I compress that table without affecting the Indexes , Dependencies proc, pkgs, Functions.   

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"

View 3 Replies View Related

How Does Local Index Work In Partitioned Table

Nov 28, 2012

I have table with 4 partition by range partition. I am loading the table in bulk mode to latest partition. Before I load , I dropped the index and after Load I will be creating index. So when I am dropping index, it is dropping index from all the partitions and when creating the index, I am creating the index for all partitions. When I am creating index using local, it is telling you have to create local index for all partitions at the same time. because of that I have to drop and recreate all indexes again. Again I have to gather stats for whole table .

I was thinking we can build index for one partition and index should remain as is for old partitions If this is not the case, how do I plan my load for a partitioned table using bulk mode to latest partition.

View 4 Replies View Related

Performance Tuning :: Table Partitioned With Primary Key

Aug 4, 2010

I have normal tables with hugh Data and would like to increase the performace by following means:

1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ).
2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace.
3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.

I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.Is there any use of having Local index on IS_LIVE.Please note that Primary Key doesnot have this new column in it.

View 10 Replies View Related

Server Administration :: Rename A Partitioned Table?

Mar 21, 2007

i want to rename a table that has partitions.

alter table
testora.oldtablename
rename to
testora.newtablename;

ORA-14048: a partition maintenance operation may not be combined with other operations

View 2 Replies View Related

Server Administration :: DDL Used To Create A Table That Is Partitioned By Day

Nov 4, 2010

DDL used to create a table that is partitioned by day, then rolled up to a month using the interval partitioning technique.

View 3 Replies View Related

SQL & PL/SQL :: Create Primary Key Using Local On Partitioned Table?

Mar 2, 2012

I have to create primary key using local on a partitioned table.

Since the table is huge it has to be done parallel. Following stmt is giving error

alter table XYZ add CONSTRAINT PKN_XYZ PRIMARY KEY (ID,LogDtTm)
USING INDEX LOCAL parallel 25 INITRANS 5 TABLESPACE OLTP_IDX_TS ;

View 5 Replies View Related

Performance Tuning :: Stats On A Partitioned Table?

Aug 8, 2013

Is it possible for the DBMS_STATS "LIST STALE" command to show a stale partition but NOT have its table show as stale?

I had a scenario where the table itself AND 1 partition showed as stale. I ran a fnd_stats gather table stats just on that 1 partition. Once it was completed it showed the partition to no longer be stale. it also showed that the table was no longer stale. so I guess I do not need to run stats on the whole table as well?

so if this is the case, when would I need to run stats on the full partitioned table if running it on the partitions themselves removes the staleness of the table?

View 3 Replies View Related

PL/SQL :: Fetch DISTINCT Values From Partitioned Table

May 12, 2013

What is the fastest way to fetch DISTINCT values from partitioned table?

1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)

Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
  CNTRY_KEY     NUMBER,
  STUDY_ID      NUMBER,
  PRD_KEY_YEAR  NUMBER,
  PRD_KEY_WEEK  NUMBER,
  DATE_FROM     DATE,

[Code]...

-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)

** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR

[Code]...

** Explain Plan:
Plan
SELECT STATEMENT  ALL_ROWSCost: 6,235  Bytes: 629  Cardinality: 37                           
      8 HASH UNIQUE  Cost: 6,235  Bytes: 629  Cardinality: 37                      
         7 CONCATENATION                 
              3 PARTITION RANGE ITERATOR  Cost: 1,985  Bytes: 1,031,900  Cardinality: 60,700  Partition #: 3  Partitions accessed #194 - #207          

[Code]...

Partition #: 7  Partitions determined by Key ValuesThe above query is taking around 6-7 minutes to fetch the data.

View 12 Replies View Related

A Datafile Deleted Belongs To Partitioned Table

Oct 11, 2012

A datafile was deleted from a partitioned table which has 31 partitions , that datafile contains data from day 25 to day 31 st I am not having a backups of this database. but I have all the dumps of that table what is the solution to get those 6 partitions data back.

View 9 Replies View Related

Server Utilities :: Exporting / Importing Partitioned Table

Sep 2, 2013

I am trying to export a partition of a table and import it to another database. I get the below error when I try to import.

ORA-14400: inserted partition key does not map to any partition

If I export the table(for that particular partition) and import the table(after dropping the table) in destination, the partitions and sub partitions are created without any problem.

The table is Range Partitioned and Sub partitioned in List. So I had to perform the below operation if I want to retain other data in the Destination table.

1. Drop the existing partition
2. Create the partition and sub partition, same as source
3. Execute imp

In fact I had to perform step#2, as if I split the partition also, the sub partition gets replicated in the new partition, which again throws the same error. Is there better way of managing the partitions and subpartition in destination with exp/imp utility, so that I need not perform step#1 and step#2 manually.

View 11 Replies View Related

Find Tablespace Size (used And Free) For Partitioned Table?

Aug 9, 2013

I need to find the tablespace size (used and free) for a partitioned table

View 3 Replies View Related

How To Enable Oracle Advance Compression For EXIST Partitioned Table

Feb 8, 2013

I have to enable oracle advance compression for existing table which PARTITION BY RANGE then SUBPARTITION BY HASH.

ORacle version: 11.2.0.2.0

View 5 Replies View Related

Export/Import/SQL Loader :: Importing Data In A Partitioned Table

Sep 18, 2012

I have a query regarding importing data in a partitioned table. let me make myself more clear with an example:

I have 1 month table that contains 30 partitions single partition for a single day on one machine say machine A. on another machine say machine B i create the same table with the same script which is on machine A for the same table. i loaded data till 1-15th of a month in Machine A table and rest of 15 -30 Days data into table on machine B at the end i want to import the data on partitioned table on machine B that is from 15th -30th to machine A table. I just want to know whether data is properly imported or not not or i need to specify something

I take export partition wise (15 -30th) 15 partitions dumps and imported into Machine A table. Is it possible that i can import day wise partition from 15th to 30th into a partitioned table which already contains data from 1st -15th partition.

I know this is possible

View 2 Replies View Related







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