Exadata :: Compress Sub Partition With Pctfree

Nov 12, 2012

How to compress sub partition on exadata, using 'for query high' and pctfree 10 options? I used this statement, but I get only ORA-14160:this physical attribute may not be specified for a table subpartition.

alter table table_name move subpartition subpartition_name PCTFREE 10 compress for query high;

View 9 Replies


ADVERTISEMENT

Exadata :: Uncompress Sub-partition Table After Restoring Exadata To Non-exadata Storage?

Dec 24, 2012

One of my databases which is running on exadata x2-2 , has been restored to non-exadata machine in order to test few things. I had a sub-partition table in the exadata , compressed for query high. test machine (NOT EXADATA) , after uncompressing this subpartiton tables i am getting the following err message :

ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type I have executed the following commands :
alter table crm.cm_ncd modify partition P01_CM_NCD nocompress;
alter table crm.cm_ncd modify partition P02_CM_NCD nocompress;
alter table crm.cm_ncd modify partition P03_CM_NCD nocompress;

[code]...

ERROR at line 1:
ORA-12801: error signaled in parallel query server P005
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type If all the partitions are uncompressed why i am getting this error message ?

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

Exadata :: Compression And Partition Row Movement?

Mar 6, 2013

If I have a Partitioned Table set as COMPRESS FOR QUERY/ARCHIVE HIGH what would be the impact when a row in Partition X has to move to Partition Y?

View 3 Replies View Related

Exadata :: Move Partition From HP Disks To HC Disks

Jun 27, 2012

We have Exadata -V2 quarter rack with High Performance Disks. We applied EHCC's various compression methods on some of the table's partitions.

Now we are setting up Exadata Expansion Rack - High Capacity Disks. Post implementation, we would be moving older partitions to new Expansion Rack wherein compressed partitions are also included.

In this case, would there be any impact on the compression ratio as the expansion rack is having high capacity disks.

And, moving partition method would be same as it is for non-exadata database i.e. "alter table <table_name> move partition

<partition_name> tablespace <tablespace_name>"

View 2 Replies View Related

Exadata :: How To Find Exadata RACK Mode

Oct 17, 2013

This is the below method which I have used to figure out exadata model. 

ex:  grep -i MACHINETYPES /opt/oracle.SupportTools/onecommand/databasemachine.xml <MACHINETYPES>X3-2 Half Rack HP</MACHINETYPES> 

But for one environment, though it is a full rack but still I am getting result as half rack.

View 3 Replies View Related

Exadata :: Import Data To Exadata

May 6, 2013

I am having an Oracle 11g R2 RAC setup. My database size is 22TB in normal servers. I have checked my database in Exadata X2-2 and found out that the HCC was good and as per my assumption the 22TB can come down to 10TB .

My challenge is i need to transfer the 22TB data to the exadata quater rack which is having space constraint. Is there any way other than export & Import as data export and import will use entire 22TB. Is there any way to transfer the data to Exadata with compressed form from the source server.

View 6 Replies View Related

Exadata :: NO_INDEX Hint In Exadata

Jul 19, 2013

I am having doubt on the no_index concept in oracle. I am using oracle exadata server.It is basically data warehouse project.I am in the situation to join some tables and get the result set for reporting purpose.

Among the tables, 2 tables having huge count.1st table has more than  - 1,000,000,000 rows2nd table has more than  - 200,000,000 rows when i join these 2 tables with some small set of tables, it is taking long time (around 20 MIN) to retrieve the result set. The final result set is around 100 rows only.

But, when i force NO_INDEX hint in the same query, is giving the same result in very fast manner(around 5 MIN). Because it is working based on cell smart scan. So, can i force the NO_INDEX hint to all tables? I forced the NO_INDEX hint only the table which is contain 200,000,000 rows not for others.

Query Plan : Normal Query using the the range scan based on the key. No_INDEX Query going full table scan.

View 5 Replies View Related

Exadata :: Exadata With Multi User

Jun 14, 2013

I would like to know if I can create one OS user by ORACLE_HOME database ?

View 3 Replies View Related

Exadata :: Task On Exadata

May 12, 2013

Tasks need to performs are

1) Post Installation check list ie: How can I see every things are installed correctly.
2) upgrade the exadata OS version .
3) Upgrade the Database.
4) Test Migration from Oracle database to Exadata.
5) Right now there RAC servers are installed without domain, I need to add domain name to existing RAC.

View 7 Replies View Related

SQL & PL/SQL :: How To Alter PCTFREE

Oct 1, 2010

I have a table Employee which gets updated and Inserted very frequently. Wanted to increase the PCTFREE value for this table

select table_name,pct_free,chain_cnt from user_tables
where table_name='EMPLOYEE'

EMPLOYEE NULL 0

is the output. I altered the pctfree like below

ALTER TABLE EMPLOYEE PCTFREE 30

and I can see Table altered but the value is not reflecting in above command again. Where else I should alter the value and cross check that value is changed.

View 3 Replies View Related

What Influence PCTFREE On Shrink

Oct 2, 2013

What Influence PCTFREE on Shrink?.

View 1 Replies View Related

SQL & PL/SQL :: How To Change Pctfree Of Partitioned Index

Oct 3, 2011

I have a partitioned index with a pctfree of 10 .I want to change pctfree to 20.How to change?

I tried this

alter index fnsonli.IG01PK rebuild partition SYS_P245 pctfree 20 ;
alter index fnsonli.IG01PK rebuild partition SYS_P245 pctfree 20
*
ERROR at line 1:ORA-14185: incorrect physical attribute specified for this index partition

View 8 Replies View Related

Syntax For Setting Pctfree And Pct Used For A Specific Table?

Apr 25, 2013

tell the syntax for setting pctfree and pct used for a specific table.

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

Server Administration :: How To Compress Index

Oct 23, 2011

How to compress a index?

SQL> Create Table tb_compress_test
2 As
3 Select * From dba_objects;

Table created.

SQL> Create Index idx_object_id On tb_compress_test(object_id);

Index created.

SQL> Alter Index idx_object_id Move Compress;
Alter Index idx_object_id Move Compress
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

View 9 Replies View Related

Server Administration :: How To Compress Table

Sep 8, 2011

There is a huge table,i want to compress it,how can i do? alter table tb_my_table compress After executed the sql,the size of table have any change.

View 2 Replies View Related

SQL & PL/SQL :: Compress Already Existing - Partitioned Index

Jul 5, 2012

Today I found myself in a situation where I needed to compress an already existing, partitioned index. First I issued an alter table to rebuild and compress the whole index:

SQL> ALTER INDEX MY_INDEX REBUILD COMPRESS;

ERROR at line 1:ORA-14086: a partitioned index may not be rebuilt as a wholeSo next I tried to rebuild compress one of the partitions:

SQL> ALTER INDEX MY_INDEX REBUILD PARTITION PART1 COMPRESS;

ERROR at line 1:ORA-28659: COMPRESS must be specified at object level first

Turns out that there is no way you can do a rebuild compress on partitioned indexes. I did not realize that until I stumbled across document 312843.1 on Metalink. It says the only way to compress a partitioned index is to drop it and recreate it (through 11.2).

My question is do we have any new feature in Oracle 11gR2 to rebuild partition indexes?

View 2 Replies View Related

COMPRESS Option Along With CREATE TABLE

Sep 26, 2013

I am trying out the COMPRESS option along with CREATE TABLE. just wanted to understand if we need the "Advanced Compression" option enabled. Does this require extra license?

View 2 Replies View Related

Compress Existing Table Which Is Not Partitioned

Aug 1, 2013

Im having table which is of 45M rows table [Not partitioned], Now I want to compress the old data other than last 3Months data, I should not go for partition compress. Rarely some select queries will be fired on that Old data. Now how can I compress that table without affecting the Indexes , Dependencies proc, pkgs, Functions.   

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"

View 3 Replies View Related

PL/SQL :: How To Compress Multiple Files And Store In Table

Apr 9, 2013

I am having many different files and i want to use pure plsql to get these files stored as blob from a table then compress them into 1 files and store that into another table. I did some search and its like possible but didnt get conclusive solution

View 4 Replies View Related

Possible To Compress Existing Table In Oracle Database

Nov 29, 2012

it's possible to compress the existing table in oracle database directly.

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

Exadata :: How To Find Disks Are HC Or HP

Oct 21, 2013

Is there any command to identify the disks are HC OR HP ? Indirectly i could find with the following exercise -

 $ grep -i MACHINETYPES /opt/oracle.SupportTools/onecommand/databasemachine.xml  <MACHINETYPES>X2-2 Half rack</MACHINETYPES> ASMCMD> lsdgState    Type    Rebal  Sector  Block       AU  Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  NameMOUNTED  NORMAL  N         512   4096  4194304  18235392  10597264          2605056         3996104              0             N  DATA/MOUNTED  NORMAL  N         512   4096  4194304   2087680   2085808           298240          893784              0             Y  DBFS_DG/MOUNTED  NORMAL  N         512   4096  4194304  27240192  18871860          3891456         7490202              0             N  RECO/ = 45TB  >>

[URL] .......  >> Page 5 >> HP SAS disks

View 2 Replies View Related

Exadata :: Resize ASM Diskgroup

Oct 9, 2012

My exadata quarter rack machine has two asm diskgroups, DATA1 with 5TB and RECO with 3TB. I'd like to resize RECO to 1TB and DATA1 to 7TB.

I know ALTER DISKGROUP RESIZE command, but my question is about resize RECO volume from 3 to 1 TB: is it supported by Oracle?? Let me know risks /issues with this resize?

View 2 Replies View Related

SQL & PL/SQL :: Add Range Sub-Partition To Hash Partition Table

Jan 2, 2013

Can I add range sub partition to a hash partition table. Example like this.

CREATE TABLE test
(
test_id VARCHAR2(10 ) ,
test_TYPE VARCHAR2(5) ,
CREATE_DATE date
)
partition by hash (test_id, test_type)
Partitions 3
SUBPARTITION BY RANGE (CREATE_DATE);

When Tried, I am getting syntax error as invalid option.

View 8 Replies View Related

SQL & PL/SQL :: Tables Activity - Inserted Partition Key Does Not Map To Any Partition

Sep 22, 2011

I have two tables Activity and Activity1.

Activity Structure
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),
CHILD_REFERENCE_NO VARCHAR2 (19),
USER_ID VARCHAR2 (30) DEFAULT user NOT NULL,
TERMINAL VARCHAR2 (30) DEFAULT userenv ('TERMINAL') NOT NULL )

Activity1 Structure Which I have Done Partitioning When I insert data from Activity to Activity1 it gives that error ORA-14400: inserted partition key does not map to any partition what I am doing wrong

CREATE TABLE ACTIVITY1(
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),

[code]....

Insert Statement

insert into ACTIVITY1(ACTIVITY_TYPE,
ACTIVITY_DATE,
ACTIVITY_ON,
REFERENCE_NO,
CHILD_REFERENCE_NO,

[code]....

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







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