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.
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 ?
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 RelatedIs there any way to compress data while export in ORACLE 10g or if there is any other way I can reduce the space consumed by the datafiles.
View 10 Replies View RelatedI have noticed that Oracle text related objects, particularily the $I tables are some of the largest objects in our database. I have been actively pursuing utilizing Oracle advanced compression in our databases for OLTP table compression and LOB object compression. I have been unable to find any documentation or notes on if it is advisable to implement either table OLTP or LOB compression for Oracle text objects.
View 1 Replies View RelatedI have to enable oracle advance compression for existing table which PARTITION BY RANGE then SUBPARTITION BY HASH.
ORacle version: 11.2.0.2.0
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.
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?
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,
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.
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 RelatedIf 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 RelatedI 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?
1)i have 2 SWP TABLES. while dropping a column, i am getting error -
ORA-39726: unsupported add/drop column operation on compressed tables.
2) when i checked compression status, those were not compressed. But as per our code standard, SWP tables should not be in compress mode.
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PAYMENT_SWP DISABLED
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PREPAYMENT_SWP DISABLED
3) as a workaround, i compressed these 2 SWP tables with OLTP option, and then i was able to drop the column from these 2 SWP tables.
4) Below statement is correct or not ?
IF A TABLE USING BLOCK LEVEL COMPRESSION, THEN this error will come - ORA-39726: unsupported add/drop column operation on compressed tables.
if above statement is correct, then how to find out whether table data is using block level compression ?
5) we have DBMS_COMPRESSION.GET_COMPRESSION_TYPE. using this i just tried to find out, but i am getting "1" as output. I am not getting the exact meaning of it.
confirm what is the conclusion on this ?
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
/
2 3 4 5 6 7 8 9 1
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> /
1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.PAYMENT_SWP;
MAX(ROWID)
------------------
AAsz4fAHSAAAD3IABs
(ii) 2nd table
SQL> set serveroutput on
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PREPAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
2 3 4 5 6 7 8 9
10 /
1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.INVOICELINE_SWP;
MAX(ROWID)
------------------
AAsz4ZAEkAAAp8XAAA
I am using oracle forms 10g and basically we have a system that takes over 300 photos on a daily basis, this all works fine and with no issues except for say maybe 2-3 photos a month. Occasionally we will get a 'corrupt photo' (it not actually corrupt, it displays in everything as it should except forms) . When we encounter these photos forms just crashes out and the user is unable to query the record with the associated photo until it is deleted and a new one is taken (or alternatively if we take the photo from the database open it in paint.net and just hit save it will then work). There is no difference that we can see in the photo which doesnt work and those that do work. I have tried using WRITE_IMAGE_FILE to save the photo to disk and Read_Image_File to read from the disk to see if that makes a difference. If i save the file as jpeg and no compression it still crashes, if i save it with low compression it works fine but we lose quality which we dont want to lose. Bitmap wont work at all. Saving as JFIF and GIF works fine without any compression but we still lose quality.
The photo will display fine if we use a javabean to display it but in this instance a javabean is not an option.
One weird thing we noticed is that when we are on the form that crashes with these photos and query back a working photo first, and THEN query the 'corrupt photo' the corrupt photo displays fine, but if we go into the form and query a corrupt photo first forms crashes as explained.
We have a requirement from the customer to start using data and index compression in our 11g database.. Is this something available in Oracle 10g,11g without any additional costs? We are not sure if this will work with our application so we will have to test it in-house, is it possible to compress the existing table data/index to test it out?
View 3 Replies View RelatedIs there a way to estimate the storage savings for Hybrid Columnar Compression (HCC) in Oracle Exadata x3-2 machine ?
View 5 Replies View RelatedHow does standard vs advanced handle index compression? Is there an advantage to having one vs the other besides the incurred cost?
View 3 Replies View RelatedHow to import dump into specific tablespace instead of default tablespace users.
I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .
i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
My database version is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
My os version is
Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux
My database is OLP system.
My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?
Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.
I have a basic question : After the transfer of a database on an other server, the tablespace contains 5 files, but 3 of the 5 files are full. The data in the files may change, they are not static. What is the risk of poor performance, do you think that the files should have free space.
View 2 Replies View RelatedI am trying to import the dump of DB into a new DB on ORACLE 10G XE. But while importing i am getting error saying "Table Space has reached it's Max Limit of 4Gb".
I tried to add more file in Tablespace, Turn on AutoExtend Option but I am not able to get Tablespace of more than 4Gb. Is it just not possible to extend TableSpace beyond 4Gb in oracle 10G XE
I require Table space of around 15-20Gb. If it cannot be achieved in ORACLE 10G XE then which ORACLE version should i use to get the required TABLE SPACE.( i want to install it on Window's Vista Home Basic)
How we can create dictionary managed temporary tablespace in Oracle 10g.
SQL> create temporary tablespace temp
2 tempfile '+GWDAAS04_TEMP_DG01/pimsb_gw/tempfile/temp01.dbf' size 500m
3 extent management dictionary;
create temporary tablespace temp
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
I have a serious doubt in oracle architecture functionality, when a user issues a update statement the data blocks are carried to db buffer cache and where does the changes to the data blocks are made???? Does a copy of the data block is kept in db buffer cache and the changes are made to the block in buffer cache?? or the a copy of the data block is kept in undo tablespace and changes are made to the blocks in the undo tablespace???
In simple the changes to the data blocks are made at db buffer cache or undo tablespace?
whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?
View 3 Replies View RelatedOn Oracle 11gr2 DB Prod DB client error log gives error "Txxnx_AxSxNERGY service terminated unexpectedly" and it has done this 25 time(s) but from DB size in alert.log i can find only "ORA-1652: unable to extend temp segment by 128 in tablespace TIVOLIORTEMPTS" error.
After increasing table space size it starts working fine but i am not sure whether Table space was a reason for service termination or network issue i can not find any error in listener.log file .
I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.
The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.
This database is a mission critical database and very less downtime can be allowed.
I have issue of TABLESPACES on test instance. Tablespace files are TEMP1,TEMP2 and APPS_UNDOTS1. Initially TEMP1 and TEMP2 were of 4 GB but now the have grown to 32 GB each respectively. Resulting an occupied space of 64 GB on test server. I want to reuse that space on test instance as those tablespaces never crosses used space of 1%. Reason of this problem was my DBA set AUTOEXTEND as ON for tablespaces.
Attached File(s)
1.jpeg ( 1.8MB )
Number of downloads: 1
2.jpeg ( 1.6MB )
Number of downloads: 1
3.jpeg ( 1.52MB )
Number of downloads: 0
One of my friend is facing a problem that while creating the database he has chosen UNDOTBS size as 4 GB and now he wants to resize it.
View 1 Replies View Related