Calculate Size Of Table With BLOB Column

Feb 5, 2007

when trying to calculate the occupied space for a table, I'm using DBA_SEGMENTS, which works fine as long as the table does not have a BLOB column.

As far as I can tell, the size of the BLOB data is stored with the SEGMENT_TYPE = 'LOBSEGMENT', but I cannot find a view that tells me which DBA_SEGMENT row belongs to the BLOB column in the table I'm checking.

To give you an example:

select sum(BYTES)
from DBA_SEGMENTS
where owner = user
and segment_name = 'MY_TABLE'
group by SEGMENT_NAME

returns 262144

running:

SELECT sum(length(blob_column))
FROM my_table

returns 821333

There are entries in DBA_SEGMENTS for my user with the type LOBSEGMENT, but I cannot find a way to map the correct DBA_SEGMENTS row to the table I am checking.

I'm using Oracle 10.2.0.3.0 on Redhat

View 2 Replies


ADVERTISEMENT

Windows :: How To Retrieve The Blob Column Data From Table

Mar 7, 2011

I use the oracle 10g database.I am trying to insert and retrive the image.Inserting an image is done.but while retrieving an image iam getting an run time exception in java "java.sql.sqlexception:general error".i am not able to understand this.

The code to insert the image is
FileInputStream fi=new FileInputStream(f);
int size = fi.available();
System.out.println("j"+size);
byte b1[] = new byte[size];
i=fi.read(b1,0,size);
System.out.println("i"+i);
st.executeUpdate("insert into image1 values('b1',"+k+")");
when i am retrieving the image i tried like this
ResultSet rs=st.executeQuery("select imagecolumn from tablename");
here iam getting an exception as i named above.

View 4 Replies View Related

PL/SQL :: Calculate Size Of Clob Datatype

Nov 14, 2012

i am trying to calculate the avg size of a row, in which i have one column which has clob datatype,

for rest of the datatypes i am using (avg(vsize(col_name,varchar2(10)))), i want a simple function that can calculate the clob datatype.

View 12 Replies View Related

Server Administration :: Unable To Calculate Datafile Shrink Possible Size

Jul 28, 2011

I need to resize my datafile as i have allocated more space and need to reduce ( i.e.data load completed now). my tablespace is having 11.74 gb free space now. it has 3 datafile.

TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992

TABLESPACE_NAME FILE_ID FILE_NAME Size(MB)
------------------ ---------- ------------------------------------------------------- ----------
CFC_DATA 71 +DATA/dedw/datafile/cfc_data.4074.731085435 65535.9688
CFC_DATA 334 +DATA/dedw/datafile/cfc_data.4473.757566557 20480
CFC_DATA 1710 +DATA/dedw/datafile/cfc_data.2012.728095695 64512I used below script to find out HWM in order to resize the datafile.
db_block_size is 16KB.
[code]....

in TOAD, we have an option, that is "Minimum size" button against each datafile.. Need the SQL which is running behind when we press this button from TOAD ?

View 1 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?
or
by exporting dump and seeing the size of that dump?

View 3 Replies View Related

XML DB :: Find Size Of Table With XML Type Column Store As Binary XML

Dec 21, 2012

I have a table with structure as:

CREATE TABLE XML_TABLE_1
(
ID NUMBER NOT NULL,
SOURCE VARCHAR2(255 CHAR) NOT NULL,
XML_TEXT SYS.XMLTYPE,
CREATION_DATE TIMESTAMP(6) NOT NULL
[code].....

- So HOW do I find the total size occupied by this table. Does BINARY storage work as LOB storage. i.e. I need to consider USER_LOBS as well for this. OR foll. will work

select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024 )) as tablesize_in_GB
From dba_segments
where segment_name = 'XML_TABLE_1'
and OWNER = 'SCHEMANAME'
group by segment_name ;

- Also if I am copying it to another table of same structure as:

Insert /*+ append */ into XML_TABLE_2 Select * from XML_TABLE_1.

Then how much space in ROllbackSegment do I need. Is it equal to the size of the table XML_TABLE_1?

View 2 Replies View Related

Server Administration :: Estimated DB Size If BLOB Data Type Is Included

Jul 7, 2010

We will create a new instance in our production server, but this time, part of it's table structure has a BLOB data type (re: <column name> blob(3000)). It's our first time to handle this kind of Oracle data type. What would be my estimate size for it's default tablespace?

View 5 Replies View Related

Data Guard :: Move BLOB Images In DB To BFILE To Reduce Time And Size Of Backup?

Jan 22, 2012

We are planning to move BLOB images in our DB to BFILE to reduce the time and size of backup.

So when we have a physical dataguard setup, can we configure data guard to copy the OS level BFILE's to the DR site?

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

PL/SQL :: How To Calculate Value Column

Dec 21, 2012

We have 2 tables.

Example :
Table Name1: Sales_orders - Columns(Cust_Id,Customer,Quantity,Unit_Price,........etc)
Table name2: Sales_taxes - Columns(Central_sales_Tax,Service_Tax,ValueAdd_Tax,Entry_Tax,Professional_Tax)

My requirement is;
I what create new table/view which contains all value of both table and new column called Total Amount.
Total amount=SUM(Quantity*Unit_Price+Central_sales_Tax+Service_Tax+ValueAdd_Tax+Entry_Tax+Professional_Tax)

View 9 Replies View Related

SQL & PL/SQL :: Reuse Calculate Column In Same View?

Sep 15, 2011

Correct example

select
pp.IVA
, pp.CNR
, pp.POLO
,(pp.CNR-pp.POLO) diff
, ((pp.CNR-pp.POLO) * pp.IVA / 100) + (pp.CNR-pp.POLO) diva
from AB_PERIODI_PREZZI pp

Invalid example

select
pp.IVA
, pp.CNR
, pp.POLO
,(pp.CNR-pp.POLO) diff
, ((diff * pp.IVA / 100) + diff) diva
from AB_PERIODI_PREZZI pp

In my real view, the diff calculate column is very long (with case etc...) I must reuse many time the diff calculate column to calculate other columns.I do not want use subquery because there is many join to do in the same query. How can I reuse a calculate column in the same view?

View 1 Replies View Related

PL/SQL :: How To Calculate Values And Display To Virtual Column

Mar 12, 2013

How I can build a query with conditions and calculations? E.g. I've got this table

Start          | End     |     Working Place     |     Mandatory
------------------------------------------------------------------------------------
01-JAN-13 | 11-JAN-13 |     Office           |          1
14-JAN-13 | 25-JAN-13 |     Home Office      |     0
04-MRZ-13| 15-MRZ-13 |     Office           |          0
11-FEB-13 | 22-FEB-13 |     Office           |          1

Now if column working place=Office and column mandatory=0
the new column "price" has to calculate: (End-Start)* $25.00
and if working place=Office and column mandatory=1
the "price" column has to calculate: (End-Start)* $20.60
else $0.00

I tried it with the case statement but I didn't know how to calculate my values and display it to the virtual column "price".

Something like
case
when Working_Place = 'Office' and Mandatory=1
then ...
else '0.00'
end as PRICE
?????

Or is it not possible?

View 5 Replies View Related

How To Insert A BLOB Column

Nov 9, 2008

how to insert into a BLOB column. I have stored procedure with an input parameter of type BLOB now how do I insert this BLOB into a table.

View 2 Replies View Related

SQL & PL/SQL :: Insert Into BLOB Column

Jul 21, 2011

I have a table with a column of type blob. Now i want to create a procedure which will insert into that table. But I don't like to create a directory. How can i solve this.

I want to insert a row like this:

insert into table x(image)
values('d:photo est.jpg');

View 2 Replies View Related

PL/SQL :: Delete Blob Column

Jun 7, 2013

I have these tables:

Products

CREATE TABLE "COMPDB"."PRODUCTS"
   (     "PRO_ID" NUMBER NOT NULL ENABLE,
     "PRO_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE,
     "COMPETITOR" NUMBER NOT NULL ENABLE,
     "CATEGORY" NUMBER NOT NULL ENABLE,

[Code]..

Now my problem:

In want to delete the blob column(The Content) in Products and the columns, which belong to this column(Filename,Mimetype,Last_update_date).
EMPTY_BLOB() doesn't work

View 4 Replies View Related

PL/SQL :: Get Particular Value From Column Which Is BLOB Datatype?

Jul 31, 2013

on this query. I need to get a particular value from a column which is a BLOB datatype. Here is the sample

data ID                           TESTDATA1                            Best Buy
00001234 12222 30 00 2                           
Lowes 00001234 12222 100 00 3                           
Walmart 00001234 12222 129 00 4                           
abc 00001234 12222 5000 00 5                           
Toshiba 00001234 12222 21 00 6                           
abcdefghij 00001234 12222 49 00  

Where '00001234' is the Invoice , '12222' is Netamount field and highlighted in red is the $ amount for that invoice.

The only data i need query to return 12222 (netamount)= $amount. I tried using substr  select substr((TESTDATA),19,26)test from TABLEA; But this gives me the only the first row but not other amount which have different positions.

Here is the desired output

OutputID                            TESTDATA 1                          
12222 30 2                            12222 100 3                           
12222 129 4                            12222 5000 5                           
12222 21 6                            12222 49 

View 11 Replies View Related

SQL & PL/SQL :: Unzipping The File In BLOB Column?

Apr 9, 2012

I am trying to extract the BLOB column in Oracle Apex and show the contents of it as a popup message.

The files which are stored in the blob column are being zipped using windows zip function in Perl and being inserted in the dataabse using some perl program as follows,

my $zip = Archive::Zip->new();
my $zipfile = $zip->addFile( @$_->[1] ,basename(@$_->[0]));

While extracting the blob column we are using the utl_compress.lz_uncompress_extract() to extract the uncompressed version of file, but we are getting the following error as

"ORA-29294: A data error occurred during compression or uncompression".

I even tried the solution as mentioned in oracle forum

View 6 Replies View Related

Forms :: Updating BLOB Column

Mar 11, 2012

I am using Forms 6i,From my Form, user selects "sno" and upload BLOB image through OAF Upload utility. It stores the rowid of "SNO" and image...into Table "t1"...Now, i want to update the BLOB image into the original table"t2" using rowid..For this , i wrote the rowlevel trigger on Table "t1", in the trigger, i called a Procedure...In that procedure .. i am trying to update the BLOB column..It is not updating the BLOB column..But it is updating the Other values.. except BLOB column.

View 1 Replies View Related

SQL & PL/SQL :: Insert PDF File Into BLOB Column?

Jan 20, 2011

I want to store a pdf file into a database column of BLOB type. The pdf file on the client system not on the database server. Is there any way i can achieve this?

View 1 Replies View Related

SQL & PL/SQL :: BLOB Datatype - Copy From One Column To Another

Mar 11, 2011

I have two tables with BLOB datatype. I am trying to copy from one column to another column , i am getting errors

CREATE OR REPLACE PROCEDURE update_blob_lob_copy(
v_id in integer,
v_string in varchar
)
as
auditlob lobdemoaudit.theblob%type;
sourcelob lobdemo.THEBLOB%type;
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Retrieve Data From Blob Column?

Sep 30, 2011

example for retrieve data from blob column using pl sql procedure in oracle?

View 2 Replies View Related

SQL & PL/SQL :: Column Length Versus Column Size

Oct 6, 2011

if one of the columns is given as

ABC varchar2(10)

can we tell anything about the size of the data in bytes that this column going to hold.

View 3 Replies View Related

SQL & PL/SQL :: Create One BLOB Column Including One PDF File?

Dec 24, 2010

I use Database 11g .

I have 2 BLOB columns include pdf files.

How can I create one BLOB column include one one pdf file? merge 2 pdf files into one file.

View 4 Replies View Related

SQL & PL/SQL :: Insert Image Files Into Blob Column?

Mar 3, 2011

I am trying to insert an image files into a blob column using the following code.

CREATE TABLE MY_IMAGE_TABLE (
ID NUMBER,
NAME VARCHAR2(20),
IMAGE BLOB);
CREATE OR REPLACE DIRECTORY MY_FILES AS '\dppdb-dev est';
GRANT ALL ON DIRECTORY MY_FILES TO PUBLIC;

[code]....

However, I am getting the this error

ORA-22288: file or LOB operation FILEOPEN failed

I've done some tracing on toad and it seems that the file am trying to insert exists yet am unable to open it.

View 4 Replies View Related

SQL & PL/SQL :: Update Blob Column With Images From A Directory

Jan 17, 2012

i have to update the blob column in a table with new image.

we have a table with 10 columns in which one column is 'image' as BLOB data type, and one more column with "image_name " and has more than 1000 rows.

I have a 300 images which I need to update in "image" column .

I need to match the given images name with "image_name" column, if row is present in the table than update the "image" column with the given image.

I search and found almost same code to insert the image in new table with a procedure, I mean code is to call the procedure, ( by passing image name and image directory)

Is there any way that we will check all the image given in the directory, if that image_name is present in the table then update the image provide me a sample code for this

View 2 Replies View Related

PL/SQL :: How To Store PDF In BLOB Column Then Select / Search It Using SQL

Jul 19, 2012

(1.) We have an Oracle 10g DB.

(2.) We want to store PDFs/MS Word docs inside BLOB column in a table.

(3.) We need to show PDF/MS Word content to the user, without opening the PDFs/MS World like we normally do (i.e. Using PDV reader/ MS Word).

(4.) i.e. We need to use SQL statements and

(a.) Get all contents of the PDF/MS WORD doc.

(b.) Search for specific strings inside these docs in the BLOB columns.

View 6 Replies View Related

Application Express :: How To Calculate Total Column Dynamically In Tabular Form

May 27, 2013

I am trying to calculating the total column based on two columns, Total=unitprice*no.of items

I wrote this Javascript in page properties>javascrpt>Execute when Page Loads

"$('input[name=f05],input[name=f06]').change( function(){
parent_row = $(this).parents('tr:first');
sal = ( parent_row.find('input[name=f05]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f05]').val() );
comm = ( parent_row.find('input[name=f06]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f06]').val() );
total = sal * comm;
parent_row.find('input[name=f07]').val(total);
});

But its not Giving any result in total column?

View 3 Replies View Related

Cannot Create BLOB Data Type For A Column In Tavle

Jul 6, 2010

user@tank> create table filetable(id number(6), file_content BLOB);

0 rows affected (0.02 seconds)

vlanke@tank_db> desc filetable;
ID NULL NUMBER(6,0)
FILE_CONTENT NULL

I am new to Database field.

- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB.
- My question is under desc filetable; query why does it not show me a data type as BLOB.
- create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.

View 1 Replies View Related

SQL & PL/SQL :: Read Only The Input String From BLOB Datatype Column?

Mar 28, 2013

I have a table that has column with BLOB datatype. I am trying read only the inputted String from the BLOB datatype column. I have used the below query,

SELECT utl_raw.cast_to_varchar2(column1) FROM TAB1
and it gives the result as
<H2><FONT color=#cc0000><EM><U>test</U></EM></FONT></H2>

However I would like to extract only the string "test" which is inputted. Is there any build-in function in oracle which will satisfy this requirement?

View 7 Replies View Related

Forms :: Display Blob Column In A Form (not Image)

Oct 12, 2011

I have a blob column in one table. I need to show it in my form (6i). If i put an image file in the column, it works (because i show it in a Image item type) but if i put a text file in the blob column, it does n´t work.

View 4 Replies View Related







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