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


ADVERTISEMENT

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

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.

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

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

How To Partition And Index The Table

Oct 24, 2011

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

2. how to partition this table?

View 2 Replies View Related

SQL & PL/SQL :: Finding The Corresponding Index Partition?

Aug 1, 2012

I have a partitioned tables and local partitioned index on the same.

I want to know that a particular index partition belong to which table partition.

View 1 Replies View Related

SQL & PL/SQL :: Use Of Local And Global Partition Index

Aug 17, 2010

What is the use of Local and Global Partition Index?

View 1 Replies View Related

How To Disable Index In Partition Table

Aug 11, 2010

I am trying to disable the index i am getting the error like

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

the table have partition. how to disable the index in partition table.

View 1 Replies View Related

Index Rebuild Partition Not Completing?

Nov 20, 2012

Index with following PARTITIONS. Index rebuild is extremely slow. Below 2 Alter index ..rebuild... took 10 hours to complete. Because of this queries which based on this index are really slow.

SYS@DB AS SYSDBA> select partition_name,tablespace_name,bytes/1024 KB from dba_segments where segment_name='KSTN';
PARTITION_NAME    TABLESPACE_NAME           KB
------------------------------ ------------------------------ ----------
REB_IDX_1            TS_REB                     64
REB_IDX_2            TS_REB                     64
REB_IDX_3            INDEX                        64
REB_IDX_4            TS_REB                     64

[code]....

View 7 Replies View Related

SQL & PL/SQL :: ORA-01502 / Partition Of Index Is In Unusable State

Aug 3, 2011

after merging two partitions into single partition (partition is by list) of a table ,when i analyzed the table it is giving this error : ORA-01502 INDEX TEST.PK_ID or partition of such index is in unusable state.

View 7 Replies View Related

SQL & PL/SQL :: ORA 20000 Index Partition In Unusable State?

Jan 3, 2012

ora 20000 index partition in an unusable state. what can i do

View 6 Replies View Related

Split Partition - Global / Local Index

Aug 23, 2012

I'm trying to split a table partition.

whether the below syntax are correct.

If local index used

ALTER TABLE SNYT.PART_ESTD
SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22')
INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22)
update indexes;

(per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1401247200346349807)
=================================================================
If Global index used

ALTER TABLE SNYT.PART_ESTD
SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22')
INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22)
UPDATE GLOBAL INDEXES;

View 1 Replies View Related

Performance Tuning :: Two UNION ALL - Modify Index Or Partition

Jan 27, 2012

We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read

SELECT V_IDENTIFIER_LOOKUP.UID_V_IDENTIFIER_LOOKUP AS "UID",
V_IDENTIFIER_LOOKUP.ABA, V_IDENTIFIER_LOOKUP.ADDRESS1,
V_IDENTIFIER_LOOKUP.ADDRESS2, V_IDENTIFIER_LOOKUP.ADDRESS3,
V_IDENTIFIER_LOOKUP.ADDRESS4, V_IDENTIFIER_LOOKUP.ALIAS,
V_IDENTIFIER_LOOKUP.CITY, V_IDENTIFIER_LOOKUP.COUNTRYCODE,
V_IDENTIFIER_LOOKUP.CUST_CODE, V_IDENTIFIER_LOOKUP.CUST_NAME,
V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN,
V_IDENTIFIER_LOOKUP.IDENTIFIER,
V_IDENTIFIER_LOOKUP.IDENTIFIER_TYPE,
[code]...

View 1 Replies View Related

Performance Tuning :: Possible To Build Index Partition In Parallel

Jul 8, 2010

Is it possible to build index partition in parallel?I tried following command

alter index <index_name> rebuild partition <partition name> online parallel 5;

It executed without complaining, but want to know if index partitions can be build in parallel?

View 3 Replies View Related

Rebuild Local Index Partition Is Using Full Table Scan

Apr 23, 2012

I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.

The DDL of the partition table/indexes:
=========================
CREATE TABLE TESTME
( INST_NO CHAR(3) NOT NULL,
ACCT_NO CHAR(16) NOT NULL,
REC_NO CHAR(9) NOT NULL,
TRAN_TYPE CHAR(2) DEFAULT ' ',
STAT CHAR(2) DEFAULT ' ',
[code]...

View 2 Replies View Related

Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?

Apr 8, 2013

splitting a table partition without making its primary key index ar any other indexes unusable.

I think it is possible to do so 10g onwards.

DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP

View 2 Replies View Related

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

Configuring Udev Rule For Raw Disks?

Jan 1, 2013

I am using oel 5.8, oracle 11.2.0.3. The raw disk permissions are getting changed when the node gets rebooted.I am trying to use udev rule to change the permission permanently but its not working.

CODE[root@VM-PAGRAC3 rules.d]# more 99-asm.rules
KERNEL=="sd", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p",*
RESULT=="SATA_VBOX_HARDDISK_VB7a29b7ae-de673b56_",
NAME+="oracleasm/disks/DSK1#" OWNER="grid"
GROUP="asmadmin" MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p",
RESULT=="SATA_VBOX_HARDDISK_VB36f5d134-4e0e4c43_",

[code].....

View 8 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 :: How To Manage Rule Link With Utl_mail Package

Aug 3, 2011

I have 2 questions :

1. How many oracle mechanism can link with oracle send mail mechanism ??
2. Any samples which the oracle send mail mechanism to run with some oracle alert mechanism.

View 3 Replies View Related

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

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

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

Workflow :: When Set Vocation Rule - It Stuck All The Users Notifications

Sep 3, 2012

I am working on Workflow and the issue is When a person leaves for vacation he can set his vacation rules so that someone has to attend / approve his workflow notifications. But when we set vocation rules all notification goes into errors state and this error can resolve by refreshing/Retry this notification. And the error detail is mentioned below An Error occurred in the following Workflow.

Item Type = CONCSPL
Item Key = 086937-15990
User Key =

Error Name = 100
Error Message = ORA-01403: no data found
ORA-01403: no data found
Error Stack =
Wf_Engine_Util.Function_Call(cust_f_test.f_find_approver, CONCSPL, 086937-15990, 180198, RUN)

[code]....

Bottom line is all notifition should be successfully process and send to dedicated person.

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







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