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


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

Exadata :: Enable OLTP Compression On Tables

Sep 2, 2012

I am trying to enable OLTP compression on tables and at tablespace level for the tables

Steps I am following are:

1. Move indexes to its own tablespace
2. enable OLTP compression at table level:
alter table table_name move compress for OLTP
3. Rebuild indexes
4. Issue I have is what to do with tables with LOB columns
ALTER TABLE lob_table MOVE LOB (LOB_COL) STORE AS (TABLESPACE index_tbsp); -- Is this correct?
5. alter tablespace data_tablespace default compress for OLTP;

I have a question, is the sequence of steps correct. For tables with LOB columns do we needto move lobindex to index tablespace. Beacuse lobsegment and lobindex are created in data tablespace?

View 2 Replies View Related

Exadata :: Estimate Storage Savings For Hybrid Columnar Compression?

Mar 27, 2013

Is there a way to estimate the storage savings for Hybrid Columnar Compression (HCC) in Oracle Exadata x3-2 machine ?

View 5 Replies View Related

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 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 :: Enable Row Movement On IOT?

Nov 11, 2011

I have the table is partitioned.

Can I set the table table to "alter table x enable row movement" even when it is index organized table?

How do I know if the table is INDEX ORGANIZED TABLE.

View 3 Replies View Related

SQL & PL/SQL :: Row-movement And Fragmentation?

May 29, 2012

find out whether the rowmovement option in a partitioned table will cause fragmentation or not?

View 3 Replies View Related

Server Administration :: Enable Row Movement?

Jul 3, 2012

I have a job that runs the following commands for each table in a schema.. (I just inherited it ugggg)

alter table ODS.ODS_MONTHLY_MF_AUM enable row movement;
alter table ODS.ODS_MONTHLY_MF_AUM shrink space;
alter table ODS.ODS_MONTHLY_MF_AUM disable row movement;

On occassion I get this error when enabling row movement

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Other than dealing with apps that depend on ROWID's is there any negative impact to always keeping row movement enabled for tables.

Secondly, does any body have a script that can determine if the space really needs to be shrunk for a table.

View 13 Replies View Related

Server Administration :: How To Test Row Movement

Sep 6, 2011

How to test row movement? I want to a row data reside in two different blocks. how to do the test?

View 13 Replies View Related

PL/SQL :: Creation Of Stock Movement Report

Nov 8, 2012

I want to create a Stock Movement Report of a Health Management System in Oracle Report Builder 6i, as per following columns.

Sr. No Medicine Name Entry Date Opening Stock Stock In Stock Out Stock on-Hand
1 Mefnac DS 14-Aug-2012 50 450 50 450
15-Aug-2012 450 0 100 350
16-Aug-2012 350 50 400 500

Stock-In would be an aggregate sum of purchased quantity against particular medicine in a specific date.
Stock-Out would be an aggregate sum of issued quantity against particular medicne in a specific date.

View 1 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 :: Enable / Disable Row Movement

Oct 20, 2011

for defragment of a table,

after enable it:

alter table test enable row movement;

do i need to:

alter table test disable row movement; ??

View 3 Replies View Related

Reports & Discoverer :: Oracle Report Dynamic Text Movement

Jan 11, 2011

I have one report requirement like below diagrams, My report having one repeating frame and one dynamic LOGO field. My repeating frame have 2 field say item_no, and item_name. This repeating frame will grow based on the items say min 1 to max 8 items.

My dynamic logo has some min and max movement position, Say in the below diagram my dynamic logo min movement position is line no 6 and max movement position is 9.

Suppose i have only 1 or 2 or 3 or 4 or 5 items then LOGO should fit in the line no 6, if i have 6 items then logo should come in the 7th line, if i have 7 items then logo should come in the 8th line ect.,like that max 8 item will come in the report, if items are 8 then logo should fit in the 9th line number.
----------------
1|*****ITEM1****|
2|**************|
3|**************|
4|**************|
5|**************|
6|*****LOGO*****|-- MIN LOGO POSITION
7|**************|
[code]......

View 1 Replies View Related

Oracle Tablespace Compression

Mar 25, 2013

We have started developing a new application to compress tablespaces based on the business specification.

This is a Data Warehouse.

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

And there is a requirement to find the uncompressed segments,to find whether the tablespace is already compressed or not.

View 1 Replies View Related

RMAN Compression Parameter

Aug 22, 2011

We are seeing volume issue when taking Rman level 0 backup for a database , the database version is 11.2.0.2 and its on RHEL 2.1. As 11g supports compression for RMAN, we have implemented so as to reduce the backup space used.

" CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE "

However during full backup the volume size increases, meaning we have to increase /data volume (currently 500G) to more then a 1T for just rman to go through, else the backup hangs. Once backup is done we again bring down the volume size to less then 1T. The other compression parameters are HIGH and MEDIUM, hoever I am not very sure if changing to high or low will work as I couldn't find any right doc in meta link or may be I didn't searched correctly, I will continue to look for that.

View 2 Replies View Related

Advanced Compression In Oracle 11g R2?

Aug 8, 2012

Hybrid Columnar Compression is dependent on the underlying storage system. See Oracle Database Licensing Information for more information.

The below is from the Oracle® Database PL/SQL Packages and Types Reference
Compression Constant Compression Level Description
COMP_FOR_OLTP 2 OLTP compression
COMP_FOR_QUERY_HIGH 4 High compression level for query operations
COMP_FOR_QUERY_LOW 8 Low compression level for query operations
COMP_FOR_ARCHIVE_HIGH 16 High compression level for archive operations
COMP_FOR_ARCHIVE_LOW 32 Low compression level for archive operations

To use Compression Level 4 or higher do we have to have ZFS or Pillar storage ?

View 4 Replies View Related

Using Archive Compression For Standby?

Jul 13, 2013

Database version: 11.2.0.3(Exadata) 

We want to use archive compression for our standby(standalone non-exadata). We don't have license for Advanced Compression. Is the archive compression possible without Advanced Compression?

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

SQL & PL/SQL :: Unable To Add Columns With Default Value Due To Compression

Mar 22, 2011

We are trying to add 2 columns in a partitioned table.but we are getting below error:

SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables

But, it is not a compressed table.

select table_name, compression from user_tables
where table_name ='SVC_ORDER_CODE_FACT'

TABLE_NAME COMPRESSION
------------------------------ -----------
SVC_ORDER_CODE_FACT

actually we are trying to add 2 columns as below:

ALTER TABLE SVC_ORDER_CODE_FACT
ADD (MKT_FEATURE_KEY NUMBER default '-2', PREV_MKT_FEATURE_KEY NUMBER default '-2');

But, if we add column without default value,

View 2 Replies View Related

SQL & PL/SQL :: Index Compression Increasing The Size?

Jun 21, 2012

I had created a Primary key and wanted to compress as per my senior instructions.Below are my results the size increased after compression.

select compression from dba_indexes where index_name = 'TEST_IDX';
Compression
----------
DISABLED
select sum(blocks) no_of_blocks, (sum(blocks)*8192)/(1024*1024)size_MB

[code]....

We ran a compression on the primary key index TEST_IDX

ALTER INDEX SCOTT.TEST_IDX REBUILD INITRANS 15 TABLESPACE DATA_01 COMPRESS;
ANALYZE INDEX SCOTT.TEST_IDX VALIDATE STRUCTURE;

Now when i ran the below select statement:

select compression from dba_indexes where index_name = 'TEST_IDX';
Compression
----------
ENABLED
select sum(blocks) no_of_blocks, (sum(blocks)*8192)/(1024*1024)size_MB

[code]....

As you can see after compression the blocks and size has been increased, but i ran for many tables and other indexes, we observed the blocks and size was reduced by 50-70%, i am not sure why this happened to the index compression.

View 3 Replies View Related

RMAN :: 10G Compression - Default Features

Oct 18, 2013

What are the default features available along with 10g R2 Enterprise Edition , Especially for RMAN Backup ? What are the features required for licensing in 10g R2 EE version .

View 2 Replies View Related

Oracle 11g Table Compression Feature

Nov 16, 2012

Using Oracle 11g's compression feature in production? I haven't read anything negative yet, that doesn't meant that there isn't anything that could have an adverse affect. I wanted to check to see if there are any affects on the performance or any disadvantages of using this compression feature. I have tested this on one my major tablespace and I did see a big difference in the reduce size on the tablespace but I am still hesitated to put this into production.

View 1 Replies View Related







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