PL/SQL :: Oracle Exchange Partition Feature Not Working As Expected?

Aug 17, 2012

I used the Exchange Partition feature to swap segments between 2 tables- one Partitioned, and one Non-Partitioned. The exchange went well. However, all the data in the partitioned table has gone to the partition which stores the maxbound values.

/** actual table names changed due to client confidentiality issues */

-- Drop the 2 intermediate tables if they already exist

drop table ordered_inv_bkp cascade constraints ;
drop table ordered_inv_t cascade constraints ;
/**

1st create a Non-Partitioned Table from ORDERED_INV and then add the primary key and unique index(s):

*/
create table ordered_inv_bkp as select * from ordered_inv ;
alter table ordered_inv_bkp add constraint ordinvb_pk primary key (ordinv_id) ;
--
create unique index ordinv_scinv_uix on ordered_inv_bkp(
SCP_ID ASC,

[code]....

-- Next, we have to create a partitioned table ORDERED_INV_T with a similar

-- structure as ORDERED_INV.

-- This is a bit tricky, and involves a pl/sql code

declare
l_dt_start DATE;
l_ptn VARCHAR2(50);
cnt PLS_INTEGER;
l_cnt_initial PLS_INTEGER;
ts_name VARCHAR2(50);
l_sql VARCHAR2(10000);
ts_indx VARCHAR2(100);

[code]....

-- Add section to set default values for the intermediate table OL_ORDERED_INV_T

FOR crec_cols IN (
SELECT u.column_name ,u.nullable, u.data_default,u.table_name
FROM USER_TAB_COLUMNS u WHERE
u.table_name ='ORDERED_INV' AND
u.data_default IS NOT NULL )
LOOP

[code]....

-- Next, use exchange partition for actual swipe

-- Between ordered_inv_t and ordered_inv_bkp

-- Analyze both tables : ordered_inv_t and ordered_inv_bkp

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HENRY220', TABNAME => 'ORDERED_INV_T');
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HENRY220', TABNAME =>'ORDERED_INV_BKP');
END;
/
SET TIMING ON;

[code]....

View 2 Replies


ADVERTISEMENT

Bad Plan After Exchange Partition

Jul 30, 2012

I ran exchange partition from non-partitioned table to a partitioned table with the following params: WITHOUT VALIDATION UPDATE GLOBAL INDEXES since we have a GLOBAL index( the GLOBAL is a must). After the exchange , if I'm running a simple query on the first column of the PK the plan is very bad and the EM Adviser advices me to build an index based on that column. I'm using 11.2.0.2

View 1 Replies View Related

SQL & PL/SQL :: Exchange A Partition - ORA-14096?

May 9, 2013

I am trying to exchange a partition and I am seeing an ORA-14096. I've done this several time before with other tables without problem. I am pretty sure my columns and index really does match.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

SQL> ALTER TABLE se_keywords EXCHANGE PARTITION p_user_1068 WITH TABLE se_keywords_1068 INCLUDING INDEXES WITHOUT VALIDATION;
ALTER TABLE se_keywords EXCHANGE PARTITION p_user_1068 WITH TABLE se_keywords_1068 INCLUDING INDEXES WITHOUT VALIDATION

ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns

SQL> SELECT COUNT(*) cnt
2 FROM all_tab_columns a
3 FULL OUTER JOIN all_tab_columns b ON a.column_name = b.column_name
4 AND b.owner = USER

[code]...

View 3 Replies View Related

Server Administration :: Exchange Partition With Update Indexes

Nov 7, 2011

I exchange a partition with into a normal table with UPDATE INDEXES,but i found the index of both table are marked UNUSABLE?

SQL> Create Table tb_hxl_list_part
2 (
3 statedate Number,
4 provcode Number
5 )
6 Partition By List(provcode)
7 (
8 Partition p_1 Values(1)
9 );

Table created.

SQL> Create Unique Index idx_tb_hxl_list_part On tb_hxl_list_part(provcode) Local;

Index created.

SQL> Insert Into tb_hxl_list_part Values(20111101,1);

1 row created.

SQL> commit;

Commit complete.

SQL> Select status From dba_ind_partitions aa
2 Where aa.index_name = 'IDX_TB_HXL_LIST_PART';

STATUS
--------
USABLE

SQL> Create Table tb_hxl_list_part_bak
2 (
3 statedate Number,
4 provcode Number
5 );

Table created.

SQL> Create Unique Index idx_hxl_list_part_bak On tb_hxl_list_part_bak(provcode);

Index created.

SQL> Select status From dba_indexes bb
2 Where bb.index_name = 'IDX_HXL_LIST_PART_BAK';

STATUS
--------
VALID

SQL> Alter Table tb_hxl_list_part
2 Exchange Partition p_1
3 With Table TB_HXL_LIST_PART_bak UPDATE Indexes;

Table altered.

SQL> Select status From dba_ind_partitions aa
2 Where aa.index_name = 'IDX_TB_HXL_LIST_PART';

STATUS
--------
UNUSABLE

SQL> Select status From dba_indexes bb
2 Where bb.index_name = 'IDX_HXL_LIST_PART_BAK';

STATUS
--------
UNUSABLE

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

Server Administration :: Disable Partition Feature

Feb 2, 2012

I am using Oracle 10g enterprise edition .

Currently, Partition is enabled in my database.I want to disable it.

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Partitioning';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Partitioning
TRUE

the steps to disable the partition feature.

View 3 Replies View Related

SQL & PL/SQL :: Rebuild And Move Not Working As Expected

Nov 11, 2011

I ran below query and got the indexes to be rebuild:

SELECT (CASE
WHEN b.partitioned = 'NO'
THEN 'alter index '
|| b.owner
|| '.'
|| b.index_name
|| ' rebuild online; '
--|| b.initial_extent
[code]....

Why the fragmented size is not reducing.

View 14 Replies View Related

Windows / .NET :: Microsoft Exchange To Retrieve Data From Oracle DB

Aug 8, 2012

My company already work with Oracle 10g, I developed an application using this database and asp.NET. It allows the management of BD and exploitation of these data by clients via ASP pages.

Now I must use Microsoft Exchange (especially the calendar) to retrieve data from the Oracle database and embed links to setup my ASP pages to the calendar.

Being new to Exchange, my questions are:

- It can be done?
- Should I use a particular driver?
- Some idea ?

View 0 Replies View Related

Oracle 11g Table Compression Feature

Nov 16, 2012

Using Oracle 11g's compression feature in production? I haven't read anything negative yet, that doesn't meant that there isn't anything that could have an adverse affect. I wanted to check to see if there are any affects on the performance or any disadvantages of using this compression feature. I have tested this on one my major tablespace and I did see a big difference in the reduce size on the tablespace but I am still hesitated to put this into production.

View 1 Replies View Related

PL/SQL :: Oracle RDBMS 10g R2 - SQL Performs As Expected When Trace Enabled

Aug 7, 2012

Environment:
Oracle RDBMS 10g R2.
DB OS: HP Itanium

We use Oracle EBS R12.1.2 in our company and one of the analyst reported performance with saving the configuration in Pricing module. The common fix is to gather stats on BOM_EXPLOSIONS table. Recently, when the issue occurred I collected statistics on the table. The performance didnt improve. I went ahead and decided to trace the Oracle form session using the profile 'Initialization SQL Statement - Custom" at user level.

I also monitored the session in OEM 10g grid. The analyst performed the same set of steps and the performance was normal and acceptable. Analyst tried again and performance was matching with the expectation. I cleared the trace profile and analyst tried again. This time analyst had worse performance as the original issue. The issue got fixed later part of the day on its own. This has made me curious and thought to discuss it here.

I have had similar experience with 10g and 11g, when I enable the trace on the issue cannot be reproduced and when trace is off the issue pops back up.

View 2 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 :: ORA-27369 - Job Of Type EXECUTABLE Failed With Exit Code - Exchange Full

Jul 8, 2011

I am trying to xcom the file from Linux server location to Windows server location through shell script. When i execute my DBMS_SCHEDULER to trigger the shell script i am getting the below mentioned error.

Error report:
ORA-27369: job of type EXECUTABLE failed with exit Exchange full
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at "CUSTDOMAIN.TSC_041_REPORT_PRC", line 50
[code]....

View 2 Replies View Related

How To Use Data Pump Feature In Windows Xp

May 19, 2011

My operating system is Windows Xp Professional and the Oracle database version is 10.2.0.

My task is to export datas using export utility. Following are the steps I did in my sqlplus connecting to a database db2.

SQL> CONN / AS SYSDBA

Connected.

SQL> ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

User altered.

SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Grant succeeded.

SQL> expdp scott/tiger@db2 tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

I was not able to export the tables, resulting in the following error.

QUOTE SP2-0734: unknown command beginning "expdp scot..." - rest of line ignored.

What should I do to rectify the error?

View 1 Replies View Related

XE :: ORA-00439 / Feature Not Enabled / Partitioning In 10g

Dec 3, 2012

ORA-00439: feature not enabled: Partitioning...

how to enable partitioning in database....

my version is......

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production.

View 3 Replies View Related

ORA-00439 Feature Not Enabled - Bitmap Index

Jul 11, 2012

I am using Oracle 10g XE (express edition). If I try to create a bitmap index, I get an error

ORA-00439 feature not enabled: Bit mapped Indexes

How do I solve this problem and create a bitmap index?

View 1 Replies View Related

Server Administration :: ORA-03001 - Unimplemented Feature?

May 15, 2012

for error: ORA-03001: unimplemented feature.

SQL> SELECT /*+ PARALLEL (a 8) PARALLEL (b 8) */
SUM (space_used_delta) / 1024 / 1024 "USED_MB",
2 3 SUM (c.bytes) / 1024 / 1024 "TOTAL_MB",
4 ROUND (SUM (space_used_delta) / SUM (c.bytes) * 100, 2) || '%'"PERCENT"

[code]...

ERROR at line 5:
ORA-03001: unimplemented feature

SQL>

All the tables are present, still..??

View 29 Replies View Related

SQL & PL/SQL :: Implement Word Wrap Feature In Code

Jan 24, 2012

I want to implement word wrap feature in code.

Requirement is format given multi line text in the specified lines and each line be of specified character. Here words should not be broken, instead they must come to new line if lines are available.

Function could of the sort:
CREATE OR REPLACE FUNCTION wrap_text(p_text VARCHAR2
,p_len NUMBER-- no of lines
,p_chr NUMBER--no of chr/line
)
RETURN VARCHAR2;

View 4 Replies View Related

Server Administration :: Undo Tablespace Which Has Autoextend On Feature

May 18, 2011

you have an undo tablespace which has autoextend on feature.after a timegap your undo tablespace presently is 100GB

here as a DBA what you will do?

View 3 Replies View Related

Server Administration :: New Feature Of 11g Called-Pending Statistics

Mar 31, 2011

What is the scenario, we should use the new feature of 11g called-Pending Statistics.

Eg:-
EXEC DBMS_STATS.set_table_prefs ('SCOTT', 'EMP', 'PUBLISH', 'false');

View 3 Replies View Related

SQL & PL/SQL :: Exclude Single Schema From Autostats Gathering Feature In 11g

Jul 5, 2011

I need to exclude a single schema from the autostats gathering feature in 11g. The tables in this schema are analyzed at the appropriate time via the application code. The autostats gathering job sometimes kicks in at a time in which the tables are getting updated or loaded which can skew explain plans during the updates/inserts.

I've searched through the oracle documentation and cannot find a way to simply "exclude" the schema without locking it. I see it is possible to disable the autostats at the entire db level but not at the schema level.

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

Performance Tuning :: ORA-00439 / Feature Not Enabled / Bit-mapped Indexes

Mar 29, 2004

I am having Oracle 9i relaese 2 on my db server. I am getting the following error every time I try to create a bitmap index:-

ORA-00439: feature not enabled: Bit-mapped indexes

I have queried the v$option table .Here the value of parameter Bit-mapped indexes is FALSE.

The result of v$version is :-

Oracle9i Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Actually when we created the database our installation was halted . so we manually created the database using Create database command.

How can we enable the BITMAP Index Feature now.

View 10 Replies View Related

Active Data Guard Feature Of 11g To Run Live Reports On Standby

Sep 26, 2012

We have 10g physical standby set in our environment and we are migrating to 11g now. We want to use the active data guard feature of 11g to run the live reports on standby rather production. Questions I have is:

1) On our current 10g standby environment, we use db_name=cusms which is exactly matching with the production database name. I don't see we are using database_unique name on our standby. But I have read several blogs where everyone talks about using db_unique name on standby and db_name can be exactly matching with production on 11g. I wanted to know, is db_unique name a new requirement to have on 11g? can I go ahead and not use db_unique name and just have db_name exactly matching with production? What are the implications of doing so? The reason we want to stick to this is in-case of failover we want the database name to be the same. But I want to hear your thoughts on this:

2)While building standby, I did noticed few things and want your clarifications:

a.On standby database, should I mount instance using pfile or spfile or it doesn't matter?
b. Lets say if I use either spfile or pfile, can I just have db_unique name in that file and just start the instance in no mount and do the duplicate from rman?
c.As soon as my duplicate target database for standby from active database got finished, I usually exit the rman session and go to sqlplus and shutdown the standby instance. (Is this ok to do)
d.Then I start the standby instance with startup (mount and open the database) this should open the standby database in read only mode. Following I issue alter database recover managed standby database using current logfile disconnect to put the database in recovery mode. (any steps missing here)
e.Then go to primary and do few log switches and come back to standby to see if the primary changes moved to secondary or not.

But what I have observed is:

a. When I do the duplicate it runs successful. But during the course of duplicate, primary system generates few archives which are not shipped or applied on standby. When I go to standby to recover the database, it says media recovery needed and ask for archives files. I need to manually move this files from primary to standby to apply. Isn/t this automatically taken care?
b. I also noticed after I can not open the standby database in read only mode after the duplicate command. While trying to open, it says database media recovery needed. What's the best procedure to open the database in read only mode immediately?
c. On my standby init.ora lets say if I use db_unique name, where would my control file be place? Will oracle create controlfile from primary and put it on my standby database and put an update an entry into my pfile or spfile?

View 6 Replies View Related

Precompilers, OCI & OCCI :: Insert Multiple Rows Using Array Bind Feature?

Aug 18, 2010

I am trying to insert multiple rows using array bind feature.

The following is working fine & inserting rows as expected.

char values[3][2] = { "1", "2", "3" };
ub2 lenp[3];
lenp[0] = lenp[1] = lenp[2] = 2;

[Code]...

OCIStmtExecute() is failing with the following error

Error - ORA-01480: trailing null missing from STR bind value

View 1 Replies View Related

Server Administration :: ORA-00439 / Feature Not Enabled / Real Application Clusters

Jan 30, 2012

i installed my oracle 10g R2 in my pc but while startup my database database manually i got error msg cmd> startup nomount

ORA - 00439 : Feature not enabled: Real Application Clusters

View 19 Replies View Related

ORA-00932 - Sample Code Working Fine In 10g And Not Working Now In 11g

Apr 1, 2013

Below is the sample code working fine in 10g and not working now in 11g.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....

we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class

Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 3 Replies View Related

SQL & PL/SQL :: Oracle Partition - 11g?

Dec 18, 2012

I have a question related to partitions and dividing into subpartitions on the existing table.Situation is as follows:

1. we have an inventory table with a list partition on one column sales_desk_id.
2. This table contains millions of records. Due to concurrency and due to high amount of data inserts, now there is a need to make sub partitions based on sale_date.

Question: is there any way to make the subpartitions without dropping the tables?

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

View 12 Replies View Related

SQL & PL/SQL :: Exchange Partitions Between Actual Table From It's Corresponding Staging Table

Nov 26, 2012

We have a table with interval partition. This table is accessed very frequently. We are suppose to exchange partitions between this actual table from it's corresponding staging table.

In order to keep the newly created partitions empty, is there a way to restrict other applications from using it before we push data from staging table to it's actual table.

View 4 Replies View Related







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