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).
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]....
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.
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.
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 ?
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?
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.
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?
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
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.
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.
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
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';
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?
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.
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.
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),