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.

------------------------------ ------------------------------ -------- ------------

------------------------------ ------------------------------ -------- ------------

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;
select max(rowid) into rid from NOVAR.PAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid);
2 3 4 5 6 7 8 9 1

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.



(ii) 2nd table

SQL> set serveroutput on
SQL> declare
rid rowid;
n number;
select max(rowid) into rid from NOVAR.PREPAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid);
2 3 4 5 6 7 8 9
10 /

PL/SQL procedure successfully completed.



View 3 Replies


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

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

Any Data Compression Wizards That Automatically Suggest Level At Which Data Should Be Compressed

Jun 29, 2011

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 Related

Server Administration :: Size Of Data Of Table

Oct 20, 2011

ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /

---------- ------------ ---------- -------------------
19 35 2810 3

Ok, the above shows us:

- we have 55 blocks allocated to the table (still)
- 35 blocks are totally empty (above the HWM)
- 19 blocks contains data (the other block is used by the system)
- we have an average of about 2.8k free on each block used.

Therefore, our table

- consumes 19 blocks of storage in total.
- of which 19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

not too sure this calculation is accurate for getting the size (data)of the table.

View 32 Replies View Related

Server Administration :: Data File / Table Relation

Feb 25, 2011

I have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace.

View 2 Replies View Related

Server Administration :: Location Of Table In Tablespace / Data File

Jul 6, 2010

I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it.

View 9 Replies View Related

Server Administration :: Cyrillic Character Set - Import A Table Of Data To Another Database

Aug 31, 2011

I'm try to import a table of data (character set: CL8ISO8859P5) to another database (character set: AL32UTF8) using exp/imp utility.After the import, all Cyrillic text was corrupted!

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

How To Enable Oracle Advance Compression For EXIST Partitioned Table

Feb 8, 2013

I have to enable oracle advance compression for existing table which PARTITION BY RANGE then SUBPARTITION BY HASH.

ORacle version:

View 5 Replies View Related

Server Administration :: Moving 10 GB Data To 200 GB Database Server

Jun 22, 2011

We have an Oracle Server database of Size 50 GB having 10 GB Data. And Planning to have a new Database Server of 200GB . So my question is after moving all the 10 GB data to 200 GB Database Server, will the performance of the system come down? Will it reduce the speed?

View 9 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

Server Administration :: Data Refresh Between 8i And 11g?

Mar 19, 2012

The developer team wants to test their application. For the same they need some data which resides at oracle 8i instance. However they are not able to create a dblink between 11g and 8i as this is not supported by oracle.

Now they want the data to be refreshed from 8i to 11g by exp/imp.

Apart from exp and imp is there is any other alternatives?

View 2 Replies View Related

Server Administration :: RAW Data-type?

Apr 3, 2013

RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.

View 5 Replies View Related

Server Administration :: Synchronize Data Between Two Database

Jul 3, 2013

Now i have one problem. I have two database oracle 10g and install on two server (call is A and B). I already create database link between two database. Each database has over 200 tables.

Basically structure two database is same, but database A have data real time, database B is stand by server. Now i want to synchronize all data of database A to database B. What should i do?

View 4 Replies View Related

Server Administration :: Flashback Data Archive

Aug 23, 2011

I need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);

So my prerequisite was to create tablespace and flash back archive, and alter table to be archived.

create tablespace audit_archive datafile 'd:oradata etaaudit_archive.ora' size 100M;

create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;

alter table teta_admin.t_prac flashback archive audit_flash_archive;

and everything works fine but on sys user.
i can query this table using "as of timestamp" clause

select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')

but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user. and this is point of my failure. this don't work on new user.

interface user have such sys privs:

SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
------------------------------ ---------------------------------------- ---

and table privs:

SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';

------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------

what i need to do in order to query this flashback table from interface user. when i try to do this from this user oracle says ORA-00942.

View 9 Replies View Related

Server Administration :: No Data To Read From Socket

Feb 5, 2010

I am getting an error through by application when i am trying to access a screen.

Error:No data to read from socket.

What could be the reasons for this error.

View 1 Replies View Related

Server Administration :: Data Migration Between Two Different Application?

Mar 9, 2011

We are planning to migrate data from an application called clintrace to another application called argus safety. Both the applications are related to pharmacovigilence safety operation. Both the applications functionality are similar. So both the database are having the same data though the table structures might be different. Both the database are oracle clintrace db is 9i and argus db is 11g.

View 8 Replies View Related

Server Administration :: Fresh Installation With Old ORA Data

Jan 6, 2013

we had a db working fine..we just take a proper shutdown of db and then try restart os...os never comes up.we have db installed in separate drive tahn C:(where os reside)

now the os is instaaled is gone? i have left with app folder and all the database file in other drive

i do the fresh oracle installation/ what things from the old app folder should i replace in new app folder created to get all the data back??

View 8 Replies View Related

Server Administration :: Contention On Data Blocks?

Oct 22, 2010

We are suffering from very bad application response for last few days, when i try to check and drill down, where the actual contention is? I came to know that there may be contention on data blocks, which may be a prime reason for degraded performance. Herewith i'm pasting my actual stats of gathered from v$waitstat. I gone through some of asktom docs and find that there may be a problem with freelist or segment space management. My data tablespace is segment space management = Manual.

My main question is

1) Should i need to increase freelist value (Right now my value is 1)

2) Or i have to move on segment space management = auto

SQL> select * from v$waitstat;

------------------ ---------- ----------
data block 2022 4052
sort block 0 0
save undo block 0 0
segment header 1 1
save undo header 0 0
free list 0 0
extent map 0 0
1st level bmb 0 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 6 0
undo block 0 0
18 rows selected.

View 1 Replies View Related

Server Administration :: Data Block Corruption

May 12, 2011

1) What is PHYSICAL/LOGICAL Corruption.
2) How it occurs.
3) Will RMAN works on both the types of corruption or only Physical (My senior told it works on both).

View 3 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

** 9005.nc1 --WO_NO
1239401A - STRUCT
9005 -MARK
9005 --POS
2 --QTY

View 24 Replies View Related

Server Administration :: Long Data Type Illegal?

Dec 26, 2012

when I try comnd create table a as select * from b where 1=2; it says illegal datatype long..i m bemused what sin has the long datatype done?

View 4 Replies View Related

Server Administration :: What Can Be Maximum Size Of Data File

Nov 24, 2011

I am having I/O issues if i create 20 GB DATAFILES on SMALL TABLE SPACE. guide me with the maximum size limit of data file that I can create in Windows 2003 32 bit server.

View 3 Replies View Related

Server Administration :: Pooling Meta Data Into New Schema?

May 22, 2012

I have created different scripts to monitor growth planning of the database my idea was generate a simple output file to a directory and create a report and send it as mail thru mail server. but the client requested me to create new schema and population all the stats i have collected into the tables in this schema .

How to pool all the stats from different databases into this new schema efficiently.

View 3 Replies View Related

Server Administration :: Continuously Changing Data Blocks?

Jan 10, 2011

If the data blocks in the buffer continuously get updated such that they never reach the Least used list of LRU,then when will they be written to disk?

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

Server Administration :: Table Cannot Shrink?

Sep 26, 2011

My table can not shrink, why?

SQL> Alter Table tb_hxl_user Shrink Space Cascade;
Alter Table tb_hxl_user Shrink Space Cascade
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

SQL> desc tb_hxl_user;
Name Null? Type
----------------------------------------- -------- ----------------------------


View 9 Replies View Related

Server Administration :: XE Limitations - How To Calculate Size Of User Data

Apr 15, 2011

I have read following statement from a link [URL]...

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.

calculation of this 4GB size. how can we calculate this size?

by simply going to DBF file and seeing their size?
by exporting dump and seeing the size of that dump?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved