Query For Blob Space

Oct 26, 2011

explain the difference in numbers between the queries? I am acutally more concerned about the ETM_XML clob since the descrepancy appears to be bigger.

SELECT table_name, column_name, segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name = 'ETM_RAW_XML';

ETM_RAW_XML
IFD_XML
SYS_LOB0007260522C00012$$ 87870668800

ETM_RAW_XML
ETM_XML
SYS_LOB0007260522C00011$$ 125199974400

SQL> SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
83848300852 ~ 78.0898154266179 GB

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
61907953222 ~ 57.6562743838876 GB

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Store Xml In Blob From A Query

Jul 13, 2012

i want to store xml in database. i have following questions,

1) in which col should i keep xml .
2) right now i am keeping it in blob columns, how can i insert update a record in blob col from query, which can be run from worksheet.

I am using oracle 11g express edition.

View 2 Replies View Related

SQL & PL/SQL :: Space In Every Character Of A String With Single Query

Mar 19, 2012

How can we get 'space in every character of a string with Single select query'

for example:-
string 'INDIA'
result should be 'I N D I A'

View 1 Replies View Related

Query To Check Disk Space Usage For Oracle

Apr 21, 2010

What is the query to check disk space usage for oracle 10g ? What is the query to check percent of disk space usage for oracle 10g ?

View 1 Replies View Related

PL/SQL :: How To Give Space Delimited Input Values In A Query

Dec 21, 2012

Example:

select * from emp
where empno in (123 234 345 124)

View 6 Replies View Related

SQL & PL/SQL :: Replace Double Space With Single Space And Also Remove Junk Characters

Jul 1, 2013

I want to replace double space with single space and also remove junk characters from the data. How can I do that?

CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"

View 8 Replies View Related

Any Way To Free Space From Sysaux Table Space?

Oct 3, 2012

Just now sysaux resized to 600m from 250m >>

Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.

CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024)  from dba_data_files where tablespace_name = 'SYSAUX';

TABLESPACE_NAME       FILE_NAME                                             BYTES/(1024*1024)     AUT         MAXBYTES/(1024*1024)
SYSAUX                  /u01/app/oracle/oradata/test/sysaux01.dbf           600                  YES                 32767.9844
CODEsql> @SCRIPT.SQ

TABLESPACE   TOTAL_SPACE(MB)    USED_SPACE(MB)   FREE_SPACE(MB)     % Used    % Free
SYSAUX          600                  248            352               41.33        58.67

1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?

View 9 Replies View Related

Long Raw To BLOB

Sep 17, 2013

I need to migrate data which is present in long raw into BLOB.Can i just use TO_LOB(FIELD_NAME) or i've to something else?..

View 1 Replies View Related

ORA-01704 By Using BLOB With LOB

May 11, 2011

We have our Tables in Tablespace USERS. For Raw_Data we have Tablespace RAW_DATA. We use version 10g express (oraclexe) to develope.

We generated our Table with:

CREATE TABLE "MES_CON_OS"."T_TPM_Interface_SendBuffer"
(
"ID" NUMBER(15,0) NOT NULL ENABLE,
"InterfaceName" NVARCHAR2(50) NOT NULL ENABLE,
"TELEGRAMBYTES" BLOB,
"TelegramString" VARCHAR2(4000 BYTE),
"RxTx" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
[Code] ......

When we try to insert mor than 4000 bytes we get the error: ORA-01704: Zeichenfolge zu lang

View 2 Replies View Related

CTX-DDL And Blob Field?

Sep 28, 2012

Using Oracle 10g2 I'm trying to create a ctx index, using CTX_DLL atributtes.

begin
CTX_DDL.CREATE_PREFERENCE ('LEXER_SINTILDES', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('LEXER_SINTILDES', 'BASE_LETTER', 'YES');
end;

drop index se.INDEX_PRONUMJNE_CTX;

CREATE INDEX INDEX_PRONUMJNE_CTX on TBL_PRONUM (MyBlobColumn) INDEXTYPE IS CTXSYS.CONTEXT parameters('sync (on commit) LEXER LEXER_SINTILDES');However, I got errors:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: LEXER_SINTILDES
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Is it possible to create this kind of indexes for blob fields? Or just for varchar field

View 1 Replies View Related

PL/SQL :: How To Retrieve Xml From Blob Col

Dec 10, 2012

I have kept xml in blob and i am retriving it as following.

select utl_raw.cast_to_varchar2(dbms_lob.substr(colblobforxml,2000,1)) from t1

the problem is, it only gets 2000 chars and i have more than 2000 chars.

View 2 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 :: BLOB Bulk Upload

May 11, 2011

Is it possible to Bulk Upload file into Oracle table's BLOB column using Pl/Sql code.

The process I know of dose 1 file @ a time as of now.

I have more than 10000+ files to upload and this will be one off process.

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

SQL & PL/SQL :: Fetching BLOB Data

Jun 20, 2012

I have two different database servers where I need to migrate table data from one schema to another schema in batch wise for eg say 100 rows. I used BULK COLLECT with LIMIT. But to access BLOB data from table I have facing errors. What could be other approache to do the same.

here pc_work is a table containing BLOB data in sourse schema. I am fetch data from this table to table t1_test_work using dblink but not working

[
declare
type array is table of test_work%ROWTYPE;
L_DATA array;
cursor C is select * from pc_work@prpctrg;
begin
open C;
LOOP

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Blob To Long Raw Conversion

Dec 19, 2010

I have a problem i need to convert a blob column contains pic file to long row

i had many tries but no one succeeded

-----------------------------------
Source table | destination table
id number | id number
img blob | img long raw
------------------------------------

1 - INSERT INTO destination table SELECT id , img FROM Source table WHERE ROWNUM < 2

i have this error ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 146092, maximum: 2000)

2 - INSERT INTO destination table SELECT id , dbms_lob.SUBSTR(img,0,2000) FROM Source table WHERE ROWNUM < 2

now errors but the lengh is 0 (i have no file )

What can i Do

View 7 Replies View Related

SQL & PL/SQL :: Insert MP3 File Into BLOB?

Feb 17, 2008

How can insert mp3 file into BLOB column into tab1 (by PLSQL insert)?

View 29 Replies View Related

SQL & PL/SQL :: Modify BLOB Data

Dec 24, 2010

I have a table containing BLOB column which stores scanned images. Due to an application error, few extra data was padded with BLOB data and now we want to remove it.

The table count will be near to 10 million rows.

We need to remove data from 161byte to 167byte of the blob data. I tried to do with DBMS_LOB.ERASE.But it will create blank spaces for the removed data. Here we need to reduce the size of BLOB data by 6 bytes by removing data from 161bytes to 167 bytes.

View 26 Replies View Related

SQL & PL/SQL :: Parsing BLOB Contents

Mar 28, 2013

I have some BLOB contents in the format of...

DK99F17,AA,032820130840,Other
ABCD,AA,032820130840,OV
AAZ123,BC,032820130932,DWL
CBA12345,ZA,032820130939,Other
Each BLOB is associated to a file name in the format...
03282013100002_thisfile.txt

The blob for each file may be zero rows to n rows in size, but typically there are 2 to 5 rows (four rows were shown in the rows above).The following kind of gets me there, but not quite as it splits up the BLOB rows at the comma and not the line break (HEX=0D0A / CRLF).

with rec as
(select fs.file_name, utl_raw.cast_to_varchar2(fs.file_data) file_data
from tada.files_store fs
where fs.file_name like '%citations.txt'
and trunc(fs.date_created) = to_date('26-MAR-2013','DD-MON-YYYY'))
[code].....

View 4 Replies View Related

SQL & PL/SQL :: Clob To BLOB Updation

Dec 11, 2012

I have a issue in updation of data from CLOB to BLOB.

create or replace function CLOB_TO_BLOB (p_clob CLOB) return BLOB
as
l_blob blob;
l_dest_offset integer := 1;
l_source_offset integer := 1;
l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX;
[code]........

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 696
ORA-06512: at "SYSADM.CLOB_TO_BLOB", line 11
ORA-06512: at line 6

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

Upload Image Onto Table Using BLOB

Mar 1, 2006

I am attempting to Insert an image "C:/hi.jpg" into oracle10g with BLOB using SQLPlus.

I have created a table IMAGES...

CREATE TABLE IMAGES (
ID NUMBER(4) PRIMARY KEY,
IMAGE BLOB
);

Is that a correct way to start? How would you upload the image onto the table?

View 12 Replies View Related

Convert Content Of Blob Ansi To Utf-8?

Nov 25, 2011

I wondered the possibility of converting a content file in the format ansi to utf-8 directly from Oracle.

View 1 Replies View Related

Load Document / Image As Blob?

Feb 24, 2007

I have a table with a blob type field. We need to read scanned documents (doc, pdf, tif, jpg, etc.) from a specified directory and insert relevant data into a table, including the document as a blob.

View 3 Replies View Related

Forms :: BLOB Image Is Not Saving?

Nov 12, 2011

I am failed to saved image in the table via forms. (Although i have successfully updated the table with a procedure at PL/SQL level).

Here is what i have done.

1) Table created

create table prod
(pcode number,
image blob);

2) Made a form with guidence present at orafaq

3) It reads and saves image BUT enter/execute query does not show the image

4) even the following code gives me 0

select dbms_lob.getlength(image) from prod

View 1 Replies View Related

SQL & PL/SQL :: Convert From LONG To BLOB Datatype?

Feb 1, 2012

I have a source data in a column with LONG datatype. I want to insert that data into a separate table in a column which has BLOB datatype.

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







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