Setting New Sub Partition Template Has No Effect

Oct 19, 2010

Not sure if its an issue or just an Oracle "feature" of some sort. So I create a table with a specified partition/ sub partition template as follows:

CODEcreate table sr_part_test
(
part_key NUMBER,
subpart_key VARCHAR(10)
)
tablespace NORKOM_DATA
partition by range ( part_key )
subpartition by list ( subpart_key )
[code].......

Then when I split the MAX VALUE partition, all is hunky dorey and as expected:

CODEalter table sr_part_test split partition sr_part_max at (4) into
(
partition sr_part_4,
partition sr_part_max
);
select * from user_tab_subpartitions where table_name = 'SR_PART_TEST' and partition_name = 'SR_PART_4';
[code]......

But - what if I want to have new generated names and possibly even new subpartition keys? Won't work:

CODEalter table sr_part_test
set subpartition template
(
subpartition sp_1_NEWNAME values ('foo'),
subpartition sp_2_NEWNAME values ('bar'),
subpartition sp_3_NEWNAME values ('baz')
);
[code]........

Notice that the sub partition names are still using the old names (which I do NOT want), and still using the old keys! C .

View 3 Replies


ADVERTISEMENT

Forms :: Migrating 6i With 11i Template To R12 Template

Apr 20, 2010

We have many customized forms built on 6i (11i Template) and deployed to EBS 11i, We want to Upgrade to R12, what shall i do with the customized form which already applied to 11i Template ?I know i have to migrate 6i to 10g then deploy, but my problem in the Template, shall i remove 11i Template and apply R12 Template from the beggining?

View 4 Replies View Related

SQL & PL/SQL :: Effect Of Truncate On Associated Indexes Of Tables?

Jul 21, 2012

I know that truncate is a ddl operation that removes all the data from the table and set the HWM to very beginning. I am curious to know, is anyhow indexes will be affected on the issuance of truncate command or we need to rebuild the indexes after issuing truncate.

Also is there any way to know that how many rows/blocks a select statement is scanning because of the effect of HWM even the table has 0 rows.

View 1 Replies View Related

Forms :: Marquee Effect In Text

Mar 15, 2011

Is it Possible to get Marquee Effect in Form Text ?

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

SQL & PL/SQL :: Index Effect While Inserting Records In A Table

Dec 17, 2011

I have one indexed column in a table.for performance improval purpose i created index to that column .

my doubt is..it may decrease the performance while inserting data into that column?

View 5 Replies View Related

Auto Accounting Rule Is Not Taking Effect In PROD

Aug 10, 2013

we are in the migration of oracle apps 11.5.9 [version] , while uploading AR open invoices we used auto accounting rule for revenue account , but we did this changes its not taking an effect , same revenue a/c we uploaded is uploaded , but the auto accounting is not taking an effect ,

for a work around we migrated the data ! but we would be the root cause and possibilities why it hasn't worked ?

But this auto accounting rule worked in CRP & UAT as well ...!!!

View 2 Replies View Related

Backup & Recovery :: Effect Of Insert On Export (expdp)

Apr 19, 2012

What will be the effect of insert statement on expdp if I am doing full table export?

View 4 Replies View Related

Server Administration :: Initialization Parameter File - Currently In Effect For Session

Jan 31, 2012

I have a question about spfile.

SQL> SELECT COUNT(*) FROM v$parameter WHERE value IS NOT NULL;
COUNT(*)
----------
246
SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
COUNT(*)
----------
20

v$parameter displays information about the initialization parameters that are currently in effect for the session,but v$spparameter displays information about the contents of the spfile.I want to know why the count of parameters whose values are not null in v$parameter is greater.where are they from? My initialization parameter file is spfile.

View 3 Replies View Related

Data Guard :: Truncate Table Effect On Physical Standby

Nov 20, 2009

Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.

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

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

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 :: Tables Activity - Inserted Partition Key Does Not Map To Any Partition

Sep 22, 2011

I have two tables Activity and Activity1.

Activity Structure
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),
CHILD_REFERENCE_NO VARCHAR2 (19),
USER_ID VARCHAR2 (30) DEFAULT user NOT NULL,
TERMINAL VARCHAR2 (30) DEFAULT userenv ('TERMINAL') NOT NULL )

Activity1 Structure Which I have Done Partitioning When I insert data from Activity to Activity1 it gives that error ORA-14400: inserted partition key does not map to any partition what I am doing wrong

CREATE TABLE ACTIVITY1(
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),

[code]....

Insert Statement

insert into ACTIVITY1(ACTIVITY_TYPE,
ACTIVITY_DATE,
ACTIVITY_ON,
REFERENCE_NO,
CHILD_REFERENCE_NO,

[code]....

View 2 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 :: Can Drop Partition Based Indexes From A Particular Partition

Mar 3, 2011

I have partition based table one the basis of year month. And we have 8 local indexes on this table. Every month we have to create a new partition and load data into this partition and the volume of the data is around 14million and the load process is taking long time due to indexes. Is it possible to drop the indexes from particular partition?

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

SQL & PL/SQL :: Updated PartitionID With Partition Number For Each Partition Set

Apr 12, 2010

needed in stored procedure to achieve this...how to get this..(stored procedure)

table:
studentid,sname,partitonid
901,x,null
902,y,null
903,z,null
904,p,null
905,q,null
906,a,null
907,b,null
908,d,null
909,f,null
910,m,null

For the above data set i need to divide into 5 partittions and need to updated the partitonid with the partition number for each partition set,like the below result set

studentid,sname,partitonid
901,x,p1
902,y,p1
903,z,p2
904,p,p2
905,q,p3
906,a,p3
907,b,p4
908,d,p4
909,f,p5
910,m,p5

View 3 Replies View Related

PL/SQL :: Alter Script For Adding Partition And Sub-partition

Mar 28, 2013

write ALTER STATEMENT for adding new partition P1 and SUBPARTITION P1_201001 and P1_201002.

TABLE
=======
CREATE TABLE TEST
(
"REPORT_ID"    NUMBER,
"MONTH_ID"      NUMBER,
"GROUP_ID"      NUMBER,
"AGE_GROUP_ID"  NUMBER,
[code]...........  

View 4 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 :: How To Get Insert Template For Any Given Table

Jun 21, 2010

I would like to know how to get an insert template for any given table.

For example
SQL> @insertTemplate
Enter Table Name: emp
INSERT INTO emp VALUES
(NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- EMP_NAME NUMBER(15,0)
,NOT NULL -- SALARY NUMBER(15,0)
,NULL -- HIRE_DATE DATE
) ;

For any given table the script should create an insert template like above?

View 13 Replies View Related

Template File Used In Database Creation?

May 15, 2012

I am having a template file (only .dbt file) to create the database instance.I Kept it in $ORACLE_HOME/assistant/dbca/template/,after running DBCA it was loaded with details mentioned in it ,but at the second last step when i try to see initialization parameters control file ,datafile and tablespace detail was not mentioned.when it started database creation ,got hanged on 5% completion showing failure in processing system parameters. Is it the right way to create database using DBCA?what should I do to create database using template?

View 1 Replies View Related

JDeveloper, Java & XML :: Syntax In Rtf Template?

Mar 17, 2011

i am using this syntax but its going in error, <?if:number(DAY_1)>=1 and number(DAY_1)<=25?><?attribute@incontext:background-color;'LightGreen'?><?end if?>

View 1 Replies View Related

Specify Existing Template For Database Creation

Nov 7, 2013

During the database installation the two nodes after all the prerequisite check and copy of files i am getting the below error message 

"Template4 General_Purpose.dbc does not exist. Please specify an existing template for database creation". 

How can i overcome this error ? I am installing Oracle 11g R2 11.2.0 On Oracle Enterprise Linux (5.1.19) running on [Oracle Virtual Box 4.1.12 ]. Also what are the steps to uninstall the previously installed db_home1 which failed due to this error. 

View 1 Replies View Related

PL/SQL :: EXCEL TEMPLATE Error Message?

Oct 31, 2013

i have a xml sheet which contain 30 attributes,and only because of one attribute i got this error when i preview my xls sheet and the rest 29 attribute is working correctly.

   Start Excel Previewonly open: falsemTemplate: C:Documents and Settingsesys36Local SettingsApplication DataOracleBIPublisherTemplateBuilderforExcel mp mp mp.xlsmTmpTemplate: C:Documents and Settingsesys36Local SettingsApplication DataOracleBIPublisherTemplateBuilderforExcel mp/tmp.xlsjava.lang.NumberFormatException: multiple points    at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source)    at java.lang.Double.valueOf(Unknown Source)    at oracle.xdo.template.excel.render.BookDataWriter.copyCell(BookDataWriter.java:766)    at

[code]....

View 1 Replies View Related

Server Administration :: Subpartition Template View

Oct 12, 2011

Are there some oracle views about subpartition template?

View 3 Replies View Related

How To Create Instance Using Database Template File

May 8, 2012

how to create database instance using database template file(.dbt) while running DBCA?

View 1 Replies View Related

JDeveloper, Java & XML :: Adding Boilerplate Into XML Template

Jul 3, 2007

I am in the process of customising the Oracle Sourcing Notifications template/stylesheet - PON_SOURCING_XSLFO.xsl - to add a logo (done) and change the text (problems!?).

I would like to remove some of the dynamic text and replace it with some boiler plate by either hard coding it in the template or feeding modified data to the placeholders.

Where I can access the Sourcing Notifications text definitions or alternatively let me know how, if it is possible, to hard code text into the XML template.

View 1 Replies View Related

Insert Formatted Template In A Table Column

Jan 17, 2011

I want to insert a formatted template into a table column. Is there any function for this requirement.

want is this format

Best Regards,
XYZ

View 1 Replies View Related







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