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
ADVERTISEMENT
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
Dec 13, 2010
Does truncating partition require rebuilding local index partition? (Oracle 10g2) When to use update indexes and when update global indexes?
View 4 Replies
View Related
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
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
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
View Related
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
Jan 30, 2010
How to find indexes which are eligible to rebuild in the database environment? Is there any query to find it?
View 7 Replies
View Related
May 18, 2011
I need to move my indexes (around 300) from data tablespace to index tablespace. What's the best way to do it?
View 9 Replies
View Related
Sep 1, 2011
How to find non-indexed queries and columns that require indexes?
View 3 Replies
View Related
Jan 12, 2011
So our situation is pretty simple. We have 3 tables.
A, B and C
the model is A->>B->>C
Currently A, B and C are range partitioned on a key created_date however it's typical that only C is every qualfied with created date. There is a foreign key from B -> A and C -> Bhave many queries where the data is identified by state that is indexed currently non partitioned on columns in A ... there are also indexes on the foreign keys that get from C -> B -> A. Again these are non partitioned indexes at this time.
It is typical that we qualifier A on either account or user or both. There are indexes (non partitioned on these) We have a problem with now because many of the queries use leading wildcards ie. account like '%ACCOUNT' etc. This often results in large full table scans. Our solution has been to remove the leading wildcard.
We are wondering how we can benefit from partitioning and or sub partitioning table A. since it's partitioned on created_date but rarely qualified by that. We are also wondering where and how we can benefit from either global partitioned index or local partitioned indexes on tables A. We suspect that the index on the foreign key from C to B could be a local partitioned index.
View 3 Replies
View Related
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
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
Oct 17, 2011
How can i check whether a partition tbale have default partition?
View 9 Replies
View Related
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
Feb 28, 2012
How to move a partition of a table to db_keep_cache ?
View 8 Replies
View Related
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
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
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
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
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
Aug 15, 2011
If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?
View 1 Replies
View Related
Oct 27, 2011
Is there a script that it create partitions by auto? who can share it?
View 1 Replies
View Related
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
View Related
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
Sep 1, 2011
The table tb_hxl_hash have two partitions,when i exchange the second partition,it raise error,why?
SQL> Alter Table tb_hxl_hash
2 Exchange Partition P_HS1 With Table tb_hxl_test;
Table altered.
SQL> Alter Table tb_hxl_hash
2 Exchange Partition P_HS2 With Table tb_hxl_test;
Exchange Partition P_HS2 With Table tb_hxl_test
*
ERROR at line 2:ORA-14099: all rows in table do not qualify for specified partition
View 12 Replies
View Related
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
Aug 24, 2011
How can i make my update sql(update tb_test set a='test') appear in v$session_longops? now i execute my sql,there is no any rows in v$session_longops. note:the update run for a long time.
View 3 Replies
View Related
Sep 12, 2011
I installed oracle 11.2.0.2 RAC and want to apply the PSU patch. I would like to know whether its better to apply the April 2011 PSU or July release.
View 1 Replies
View Related
Sep 27, 2011
I am trying to refresh tablespace containing indexes only. Can you let me know whether i am on the right track. Below is my query :
expdp directory=expdp DUMPFILE=user_index.dmp LOGFILE=user_index.log TABLESPACES=user_index
impdp directory=expdp dumpfile=user_index.dmp logfile=user_index_impdp.log tablespaces=user_index
View 4 Replies
View Related