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


ADVERTISEMENT

Increasing Data Size

Oct 3, 2013

How to increase ASM DATA free size? I tried deleting expired backups and old archive logs which are backed up more than one time. Still no success ..

View 1 Replies View Related

Increasing Archive-log File Size?

Jul 30, 2013

1.2.0.2 on RHL.. 3 Log Groups with 1 member each. db_recovery_file_dest string /oracle/oraarch For the purpose of increasing log file size, if i use ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 300M; but it creates Log Group with 2 member. one is at /oracle/oraarch location and other at /oracle/oradata (db_create_file_dest).

We are using ORACLE MANAGED FILE SYSEM . I want only 1 member at /oracle/oraarch (to keep the previous setting intact ...just increasing the size from 100 to 300M). If I manually give the path where to create the logfile member, I get this error: 
ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M;
ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oracle/oraarch/DB/onlinelog/' - file cannot be created
ORA-27038: created file already exists
Additional information: 1

View 8 Replies View Related

Server Administration :: Increasing Tablespace Size?

Mar 7, 2010

I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.

View 8 Replies View Related

Server Administration :: Listener.trc Size Increasing?

Dec 5, 2012

My listener.trc file has grown to a size of 56G and is contineously growing with the following message:

9:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:670] naeshow: [05-DEC-2012

[code]...

View 1 Replies View Related

Server Administration :: Increasing Size Of Redolog Member

Feb 27, 2008

The size of redolog member is 12m . Can I increase the size of that member dynamically, without adding a new member to that group and dropping the old one.

View 8 Replies View Related

Application Express :: Increasing Schema Size Without Raising Service Request?

Jul 13, 2012

How can in increase the allocated space for a schema in Apex Admin section?

I know you can set this when creating a schema alongsite a workspace and looks like the only way to do it is via raising a service request for more space and loggin in as ADMIN and approving it (in increments of 500MB).

View 4 Replies View Related

Server Administration :: Start Using Data And Index Compression In 11g Database

Jun 27, 2012

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 Related

Performance Tuning :: How Does Standard Versus Advanced Handle Index Compression

Aug 18, 2010

How 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 Related

Server Administration :: Index Rebuild Grows Database Size

Oct 29, 2010

Our database size is 100GB and i removed few records from a table and rebuild the index.The size of Index reduced considerably after Index rebuild but now i see our database size increased to 115GB.I know online rebuild creates second index which is also removed after the build is finished then why the increase in database size?Is there a way so it shows up more space ?

select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
8GB

ALTER INDEX abc_index_1 REBUILD ONLINE ;

select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
2GB

View 6 Replies View Related

SQL & PL/SQL :: Disadvantages Of Increasing INTRANS?

Nov 8, 2011

Due to an ORA-08177: can't serialize access for this transaction in our Java application using serializable transactions and after running some tests, we decided to increase INITRANS from 3 to 5 for our tables.

what would be the disadvantages from this measure? Space? I have done some tests with large tables with INITRANS 3 and 5, populated with large amounts of data and the space occupied is the same in bytes and blocks. Performance? Something else?

View 9 Replies View Related

Server Utilities :: Estimate Size Of FlatFile Based On Table Size?

May 8, 2013

We are planning to export the table data to a file pipedelimited. How do i estimate the size of the FlatFile based on the table size? or avg rowlength

View 3 Replies View Related

Server Administration :: How To Reduce Size Of TEMP DBF File Size

Apr 13, 2011

I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .

View 13 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 :: 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

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

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

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

RAC & Failsafe :: Memory Usage Increasing On Oracle

Jan 10, 2012

After the reboots each system was using around 30 GB of memory... now that it's been up for a week memory is up to 98GB used on each system. None of the systems are swapping.

Mem: 98999084k total, 97937116k used, 1061968k free, 774900k buffers
Swap: 16779884k total, 0k used, 16779884k free, 89510312k cached

View 2 Replies View Related

Performance Tuning :: Increasing Buffer Cache Value

Nov 30, 2012

Considering the below factors, I am planning to increase the buffer cache value from 256Mb to 512Mb.

1. Buffer cache hit ratio value is around 35% even in the normal period.
2. free buffer requested value is below during peak & normal hours below.

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
free buffer requested 54,694,995 15,226.9 2,523.7
free buffer requested 23,412,674 6,501.7 2,585.9

3. most of the top 5 physical reads & logical reads queries are well tuned and some of queries are doing FTS on small tables (table count min 1500 max 35000). SO indexing option is not required for these queires. But these queries getting executed frequently.

SQL> show sga

Total System Global Area 2148534928 bytes
Fixed Size 731792 bytes
Variable Size 1879048192 bytes
Database Buffers 268435456 bytes
Redo Buffers 319488 bytes

5.top 5 waitevents during db slow performance & high cpu utilization (>80%) issue.

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 1,848,898 153,793 52.00
buffer busy waits 395,280 87,201 29.49
db file scattered read 3,488,648 34,199 11.56
enqueue 4,052 10,897 3.68
CPU time 5,567 1.88

6. Top 5 waitvents during normal activities and CPU utilization is around 40%.

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,860 45.32
db file scattered read 1,133,669 985 23.99
imm op 776 605 14.73
sbtinfo2 208 139 3.40
sbtbackup 2 123 3.00

View 11 Replies View Related

SQL & PL/SQL :: Generating Increasing Number In Insert Statement

Mar 6, 2012

we are inserting a data in table by selecting data from another table.This is part of a very big procedure and the code is like

Loop
SELECTMAX(biz_trn_no) + 1
INTOv_biz_tran_no
FROMnew_business_transactions
WHEREnb_srno = nb_sr_no;
[code]....

Is there any way I can get this functionality without using a sequence? I assume corelated query would be slow and analytic.

View 6 Replies View Related

SQL & PL/SQL :: Ranking Of Consecutive Non-increasing Values In A Column?

Apr 14, 2012

I have a table with numbers like this in a column

sr no. value
1 17
2 0
3 0
4 38
5 32
6 24
7 12
8 51
9 42
10 1
11 1
12 0
13 0
14 1
15 1
.
.
.
so on....

I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.

For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.

my desired output is:

sr no. value Rank
1 17 1
2 0 1
3 0 1
4 38 2
5 32 2
6 24 2
7 12 2
8 51 3
9 42 3
10 1 3
11 1 3
12 0 3
13 0 3
14 1 4
15 1 4
.
.
.

View 3 Replies View Related

Server Utilities :: Data Compression In Oracle 10g

Oct 18, 2010

Is 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 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

Server Administration :: How To See Whether Table Data Using Compression

Mar 13, 2013

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

View 3 Replies View Related

Partition Drop Not Increasing Free Space In Dba_free_spaces?

Jan 16, 2013

I am Using Oracle 11.2.0.3. I had a script that droped partitions for a table which should have dropped nearly 30 gb of data. We have data and index tablespace.

Query of dba_free_space showed no change on data tablespace. However, index tablespace showed increase in free space.

select  sum(bytes / (1024 * 1024 * 1024)) "Size (GB)"  from dba_free_space where tablespace_name ='&tbs_name'

I am using following command to drop partition

ALTER TABLE table_name
DROP PARTITION "partition_name"
UPDATE GLOBAL INDEXES;

What should be done to increase free space shown for tablespace after partition drop?

View 2 Replies View Related

Listener File Rapidly Increasing In Diag Directory Of 11g

Jul 6, 2012

I noticed that listener file in diag/trace is rapidly increasing. I am using 11g Rel 1 on window 2008 server Its 1 GB in couple of days. Is there any way if I can turn it off ?

View 5 Replies View Related







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