SQL & PL/SQL :: Create Duplicate Partition Table

Feb 1, 2012

I plan to create a copy of original partition table as below in source database DB1.

create table tblname as select * from sourcetab;

The purpose i am creating copy of original tables is i want to have same partition tables with data in target database DB2 (where the partition feature is diabled).

So i am creating copy of original partition tables ,will then create dump of duplicate tables using export method and then load the dump into target database using import.

Both source and target database are in different servers. Partition is disabled in target database. Partition table size is 2GB and it has 5 partitions P1,P2,P3,P4,P5 .Each partition is of size 100 MB. Count of partition table is 15805412

1.My question is would there be any problem while loading dump into target database (where the partition feature is not enabled)?
2.Is it feasible to create a copy of partition tables using below statements in same database ?

create table tblname as select * from sourcetab;

View 39 Replies


ADVERTISEMENT

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

Create Duplicate Table With Constraints

Oct 15, 2010

When we create a duplicate table, we use the below query:

create table table2 as select * from table1;

But table2 is created without any constraints inherited from table1.Can I know how can i create the table with all the constraints existing in parent table.

View 5 Replies View Related

SQL & PL/SQL :: Create Partition On Existing Table?

Aug 13, 2010

I need to create partition on the non partition table without dropping a table.

Range partition on stage tables and hash partition on fact tables.

View 4 Replies View Related

SQL & PL/SQL :: How To Create Materialized View On Partition Table

May 16, 2012

how to create materialized view on partition table?

View 1 Replies View Related

SQL & PL/SQL :: Create Normal Partition Range On Date And Sub Partition List On Batch ID

Mar 17, 2011

I Know we can create dynamic partitions on table in oracle 11g. Is it possible to create normal partition and sub partition both dynamically.I have to create Normal partition range on date and sub partition list on Batch ID (varchar).

View 3 Replies View Related

SQL & PL/SQL :: Apply Redefinition And Create Range Partition And Hash Sub-partition?

Apr 3, 2013

At present we have a non partitioned table.

Can we apply redefinition and create range partition and hash sub partition on it?

View 2 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 :: 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

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

SQL & PL/SQL :: Create Range Partition?

May 3, 2012

I have to create partition on 1 table where i have date field and id.

i want to create range parttion for both the column.

View 2 Replies View Related

SQL & PL/SQL :: To Create A Bitmap Index On Partition Key

Dec 3, 2012

I have an IOT table with partitioned on list. I have p1,p2 and p3 partitions. Now I would like to create a bitmap index on partition key.

I gave ALTER TABLE .. MOVE MAPPING TABLE;

But getting the below error,

ORA-28660: Partitioned Index-Organized table may not be Moved as a whole.

View 1 Replies View Related

Can Create Range Partition Of Like 10 Minutes?

Oct 18, 2012

I want to ask whether I can create range partition of like 10 minutes? Like I have data of 1 hour and I want to make 10 partition?

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

Any Rule In Documentation When Create Partition Index?

Feb 20, 2012

Is there any rule in documentation, when create partition index? For tables, it is said to partition when table is greater than 2GB, but what about index? WHat size it should have to partition?

View 1 Replies View Related

SQL & PL/SQL :: Proc Create Partition With Variable Name / Value / Tablespace

Aug 18, 2010

developing a procedure to create a partition every month passing in parms for the name and values, and also need to determine the appropriate tablespace that has adequate space available. Working within the constraints of the environment. I would like to pass in the table name as well. This code gives the general idea..

CREATE OR REPLACE PROCEDURE FS.CREATE_PART_test
declare
v_monyy varchar2;
v_yyyymm varchar2;
v_tblspc dba_data_files.tablespace_name%type;
[code]...

View 2 Replies View Related

Windows :: Create ASM Disk Did Not Recognize Unallocated Partition

Jan 21, 2012

This is the first time I've got a job in Windows platform, I've worked as DBA with Unix/Linux still now.

I received one command: Install Oracle 11gR2 on Windows 7 Enterprise, so that, I plan to create one Partition (really, not mounted, this is Unallocated partition by Partition Magic 3rd software) which will contain datafile in it.

And, when I install 11gR2 Grid, the step ASM creation did not recognize the Unallocated Partition I created.

View 3 Replies View Related

Server Administration :: Get Script That Create Partition By Auto?

Oct 27, 2011

Is there a script that it create partitions by auto? who can share it?

View 1 Replies View Related

Performance Tuning :: Create Hash Partition On Fact Tables?

Aug 5, 2010

I have to create a hash partition on fact tables.. we can use temp tablespace or permanent tablespace.

View 10 Replies View Related

Server Administration :: Create Subpartition For Existing List Partition

Dec 14, 2012

We have a table which is already partitioned by list and now we would like to add a subpartition.

Create table Item_mst
(
ccn varchar2(10),
Flag varchar2(1),
Item varchar2(10),
status varchar2(1)
)

[Code]..

Now I could like to alter the above table for adding a new subpartition on each partition on status.

ALTER TABLE Item_mst
MODIFY PARTITION Item_mst_Test
ADD SUBPARTITION Item_mst_Test_A VALUES ('A');

Above alter gives ORA-14253: table is not partitioned by composite range method.

However dropping and recreating the table with subpartitions is working fine.

But Dropping and recreating the table in production is very cumbersome as it has huge data and many indices.

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

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

SQL & PL/SQL :: Duplicate Value In Table

Dec 15, 2010

I have a following table,

create table ashok_temp11(col1 varchar2(10),col2 varchar2(10));

and have the following values,

insert into ashok_temp11 values('A1','1001');
insert into ashok_temp11 values('A2','1001');
insert into ashok_temp11 values('A3','1002');
insert into ashok_temp11 values('A4','1003');

[Code]...

I need the duplicate values in the table. That means

col1 col2
a1 1001
a2 1001
a6 1001
a3 1002
a7 1002

I tried the following query and its not fetching me any records.

SELECT col1,
col2
FROM ashok_temp11
GROUP BY col1,
col2
HAVING COUNT(col2) > 1
ORDER BY col2

View 11 Replies View Related

SQL & PL/SQL :: Find Out Duplicate Name From Table?

Oct 28, 2010

i have a employee table .i want to find out duplicate name from this table.so i write following query

select empname from employee
having count(empname) >1
group by empname

i find the result but problem is how i can find out the same name if it have any space like ramkumar ram kumar both are same but above query did not.give this data. how i can solve it

View 16 Replies View Related

SQL & PL/SQL :: How To Get Duplicate Record In Table

Aug 7, 2012

I looking for a cursor where i can find duplicate rows in a table. Like i have emp table in which i have deptno column. I have four with same deptno e.g 10. Now my requirement is after i get the 1 record with deptno 10 , i need a message that shows remaining 3 record as duplicate entry except the very first entry.

View 1 Replies View Related







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