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


ADVERTISEMENT

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

Server Administration :: How To Add A Subpartition

Aug 16, 2011

I have created a partition table using template,how can i add a subpartition named p_default which store default values for all pattitions ?

create table tb_p_test
(
RECDATE NUMBER(8) not null,
USERNUMBER VARCHAR2(32) not null,
PROVCODE NUMBER(5) default 0 not null,
count number
[code]....

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

Server Administration :: Subpartition Template View

Oct 12, 2011

Are there some oracle views about subpartition template?

View 3 Replies View Related

Server Administration :: How To Split Default Subpartition

Oct 12, 2011

I create a range-list partition table using the subpartition template,and there is a default subpartition p_default,when i insert a row which provcode values is 6 into the table,and it belong to the default subpartition,how can i spilt it into a new subpartition such as p_6, and make the row belong to the subpartition p_6?

create table tb_hxl_user_split
(
statedate date not null,
provcode number not null,
usernumber varchar2(13) not null,
rem1 varchar2(1024),
create_date date,
create_by number,
last_update_date date,
last_update_by number
[code]....

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

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 :: 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 :: Partition Of Existing Table

Nov 30, 2011

We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.

View 5 Replies View Related

Server Administration :: Unable To Use Existing OraInventory Location During Installation?

Jan 5, 2010

i am installing oracle 11g rel2 ( o/s : hp ux 11.31 ). already we have 11g rel1 installed in the machine where oracle inventory location is /oracle/oraInventory where /oracle is ORACLE_BASE directory..

cd /oracle
ls -ltr
drwxrwxr-x 4 ora11gr1 dba 8192 Aug 30 00:19 oraInventory

now when i install oracle 11g rel2 on the same machine, it should use the existing oraInventory..but it is prompting us to enter the new location for oraInventory during installation....but as per the naming convention, we need to keep inventory directory under /oracle/oraInventory only..

when i select oraInventory location as /oracle/oraInventory from wizard, iam getting below error:

[INS-32055] The Central Inventory is located in the Oracle base.

[INS-32035] Unable to create a new central inventory at the specified location.

should i uninstall the exisitng oracle 11g rel1 and then should i install rel2 (i.e what iam trying to install now ) ?if so, after instlaling rel2, then should i install rel1 ?

View 4 Replies View Related

Server Administration :: Split Existing Schema Into 3 Logical Divisions

Mar 2, 2011

Presently have one instance, one Schema on my DB Server. Want to split the existing schema into 3 logical divisions. Have created the 3 Schemas. Through Oracle Export from the old Schema am able to Export (using wild cards) the tables and indices for each grouping and am able to Import into the new specific schema. The problem i'm having is that i cannot seem to Export the specific sequences due for each of the new schemas. Is there an easy way to accomplish this?

View 6 Replies View Related

SQL & PL/SQL :: How To Add Sub-partition To Existing Partition

Jul 17, 2010

I have a partitioned table in which at the first level it is been partitioned based on date column(C1) and within in each partition it is again sub-partitioned based on a column(C1) which is a numeric value now at first level(C1 column level) there are 6 partitions and in the last partition i want to add another partition how can i do that.

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

Partitioning - Switch Partition Key On Existing Table?

Jan 23, 2011

I recently started working with legacy code and noticed that some huge tables (5 years worth of data, don't have more details on me right now but can post later if needed) are partitioned based on time sequence number column while majority of queries are done based on time (different column). Queries performance is degrading and I'd like to try to modify partitioning and run some tests to evaluate performance improvement.

My only concern is with so much live data I have to come up with solution on how to switch partitioning with the least impact on applications running 24 x 7. Something you have done in the same situation and it worked?

View 1 Replies View Related

Server Administration :: Cannot Use Function In Partition-key

Aug 18, 2011

It cannot use function in partition-key, right?

create table tb_hxl_user_rh
(
statedate number(8),
provcode number not null,
usernumber varchar2(13) not Null
)
partition by range (statedate)
(
partition HXL_USER_20110516 values less than
(to_number(to_char(TO_DATE('2011-05-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),'YYYYMMDD')))
);

ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE

View 8 Replies View Related

SQL & PL/SQL :: Partition A Existing Table Based On Varchar2 Field?

Oct 13, 2011

I need to partition a existing table based on varchar2 field (which is actaully date value but storing as character in the table). Using below statement for creating table, but getting error.

create table TST_CUST_ARC
(
interact_id NUMBER(10),
extrn_id VARCHAR2(38),
src_cd VARCHAR2(25),
full_nm VARCHAR2(45),
run_control_date VARCHAR2(13)

[code]....

Getting error : ORA-00907: missing right parenthesis

View 5 Replies View Related

SQL & PL/SQL :: Partition Keys Has To Be Changed To Dept From The Existing Version

Aug 9, 2011

table was defined as below & indexes are also created on name /dept columns, data is also available :

create table test (
name varchar2(10),
version NUMBER(12),
dept varchar2(10)
[code].....

Now the requirement is that the parition keys has to be changed to 'dept' from the existing 'version' . How to accomplish this without any implication on the indexes and other constraints.

View 1 Replies View Related

Server Administration :: Creating Tablespace In Raw Partition?

Aug 17, 2012

i want to create tablespace in raw partion on windows.I have added a hard disk and added extended partition to it. Then created 4 logical partitions of 256 MB with diskpart.exe.I have assigned a drive letter to one of them as X:

However, I am having trouble in creating tablespace on it. I referred to [URL]... I get following error,

C:UsersAdministrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 17 15:32:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: SYSTEM
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing opti

SQL> create tablespace exampletb datafile '\.X:accounting_1' size 100;
create tablespace exampletb datafile '\.X:accounting_1' size 100

ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
SQL> create tablespace exampletb datafile '\.X:accounting_1' size 502;
create tablespace exampletb datafile '\.X:accounting_1' size 502

[code]...

View 1 Replies View Related

Server Administration :: Default Partition In Table?

Oct 17, 2011

How can i check whether a partition tbale have default partition?

View 9 Replies View Related

Server Administration :: Difference Between Partition Compress?

Nov 11, 2010

I am using compress partition for a large table.During the process I have used the below command for compress---

1. ALTER TABLE <TABLE_NAME> MODIFY PARTITION P1 COMPRESS;

However I found there are different command as below:-

2. ALTER TABLE <TABLE_NAME> MOVE PARTITION P1 COMPRESS;

What is the basic difference between these two as the compression is happening for both command.

View 3 Replies View Related

Server Administration :: Move Partition To Cache

Feb 28, 2012

How to move a partition of a table to db_keep_cache ?

View 8 Replies View Related

Server Administration :: How To Check A Partition Have Been Compressed

Oct 20, 2011

How can i check a partition whether it has been compressed? just as flowing test,i can not get the information about partition P_L1 whether been compressed.

SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';

COMPRESS PARTITION_NAME
-------- ------------------------------
DISABLED P_L1
DISABLED P_L2
DISABLED P_L3
DISABLED P_L4

SQL> Alter Table tb_hxl_list compress;

Table altered.

SQL> Alter Table TB_HXL_LIST
2 Move Partition P_L1 compress;

Table altered.

SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';

COMPRESS PARTITION_NAME
-------- ------------------------------
ENABLED P_L1
ENABLED P_L2
ENABLED P_L3
ENABLED P_L4

View 5 Replies View Related

Server Administration :: How Partition Can Be Brought Offline

Jan 4, 2012

I have a partitioned table - 128 partitions.

If I am not using data of many partitions in any way, will it affect my performance if I am firing select query that uses other/active partitions data.

How can I check when that partitioned was last accessed, also can I brought those inactive partitions offlie? If we can, what will be the advantages or disadvantages of that?

View 23 Replies View Related

Server Administration :: Error While Exchanging Partition?

Mar 15, 2012

I am not able to exchange partition by following below steps:

drop table manu_tst_hashed_tbl_org;
drop table manu_tst_hashed_tbl_bkp;
drop table manu_tst_smpl_tbl;
CREATE TABLE manu_tst_hashed_tbl_org

[code]...

and getting the below erro:

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 - "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action: Ensure that the two tables have the same number of columns with the same type and size.

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

Server Administration :: Privileges For DROP Partition

Jun 21, 2011

I have one user CD_APP. I have one partition table CD.T_FCDR_DT. User has got ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table..

Now when I try to drop a partition, I get error as below:
-------------------------------------
SQL> show user
USER is "CD_APP"
SQL> ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES;
ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------
Do I have to grant some other privileges for this user.

View 12 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 :: Impact On Table Of Partition Truncate / Delete?

Oct 21, 2012

Any impact is there if is do the following:

ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;

will there be any lock on the table during this operation? DML operations will work without any issue or not?

View 10 Replies View Related







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