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


ADVERTISEMENT

SQL & PL/SQL :: Convert Binary To Oracle Data Type?

Jun 28, 2011

I have a data synch procedure where I am extracting data from an Oracle table and inserting it into a SQL Server table. The Oracle column is VARCHAR2 and the receiving SQL Server column is Binary. I am receiving a conversion error. I have tried HEXTORAW in the Oracle procedure but it does not work. What data type can I use.

View 5 Replies View Related

How To Get Binary Data From Geometry Column

Mar 21, 2011

How to get binary data from Geometry column?

I want to read GEOMETRY COLUMN data as binary, using GET_WKB() method. I'm able to read character data by using GET_WKT().
But our requirement is to read data in binary format and display it.

View 1 Replies View Related

SQL & PL/SQL :: How To Store Images In Table Column

Dec 21, 2011

How to store images in table column. I have table called emp with datatype empno,name,photo_id(long raw)
.
i want to store image in table for each record.

View 3 Replies View Related

SQL & PL/SQL :: How To Find Size Of View Or Table In Database

Jul 10, 2010

How can we find the size of a view or synonym or table in a database. What is the code.

View 4 Replies View Related

Find Tablespace Size (used And Free) For Partitioned Table?

Aug 9, 2013

I need to find the tablespace size (used and free) for a partitioned table

View 3 Replies View Related

PL/SQL :: ORA-02267 / Column Type Incompatible With Referenced Column Type

Oct 23, 2012

i what to update a column data type am geting this error

ORA-02267: column type incompatible with referenced column type

my query is

when i run

SELECT       b.table_name||
' joins to the '||
a.table_name||
' table on '||
c.column_name||
' = '||
d.column_name          Table_relationships

[code]....

View 9 Replies View Related

SQL & PL/SQL :: Store The Date In String Type Format

Nov 23, 2010

We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)

The Query is :

select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX
from
(
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX
from
(
select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,

[code].....

View 4 Replies View Related

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

SQL & PL/SQL :: Table Column Datatype With %type Attribute

Jun 23, 2011

Is it possible to replace datatype of column of one table with another table's column datatype using %type like below

SQL>create table test1 (v1 varchar2(10));

SQL>create table test2 (v1 test1.v1%type);

View 3 Replies View Related

SQL & PL/SQL :: Table With VARCAHAR2 Data Type Column

Jul 28, 2010

I have a table with VARCAHAR2 data type column, I am unable to update that column with the following data.

UPDATE we set sname='(([Emp].Job.Catageory
IN 'SALES,COMPUTER,SCIENCE,TELESERVICE')
AND ([Employee].IDtype IN 'ABC'))'

I am getting the error "ORA-00933: SQL command not properly ended"

View 9 Replies View Related

SQL & PL/SQL :: Can Change Existing Table Column Data-type

May 18, 2011

i have one table emp in this table i have the column eno,ename,hiredate and i have data also in this table.

my eno colum data type is number so now i have to change this colum data type from number to varchar2.

if yes how can when i am trying to change this colum data type i got this error

Failed to commit: ORA-01439: column to be modified must be empty to change datatype.

View 1 Replies View Related

SQL & PL/SQL :: Table With Modifieddate Column With DATE Data Type

Jun 4, 2012

I have a table with modifieddate column with 'DATE' data type.I am facing date format exception and tried with to_char, to_date but its throwing invalid number exception. how to format date accordingly.

SELECT * FROM EMP WHERE modifieddate > '31-Dec-2011 18:30';

ORA-01722: invalid number
01722. 00000 - "invalid number"

View 9 Replies View Related

SQL & PL/SQL :: Column Of Table With Data Type Varchar2 - Corresponding Index

Jan 10, 2013

I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column

like '%abc%'
like 'abc%'
like '&abc'

Will be the corresponding index will be for those cases?

View 3 Replies View Related

SQL & PL/SQL :: How To Find Table That Contains Certain Column

Nov 9, 2011

To find out column in which table it is located.

View 1 Replies View Related

SQL & PL/SQL :: How To Find Table And Column Name

May 14, 2013

With Data in a table, is it possible to find out the table name and column name? Means i know the account number (data) of the table and with this data i want to find out column name and table name.

View 3 Replies View Related

SQL & PL/SQL :: Find Out Column Value In All Table In Whole Database

May 19, 2010

I need the column name and table name which is having the particular value in the whole database.

E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.

View 14 Replies View Related

SQL & PL/SQL :: Find Out Table Name Based On Column Name

Jul 28, 2011

i need an sql query or procedure to find out table name based on the column name..

View 4 Replies View Related

SQL & PL/SQL :: How To Find Column In Which Table It Is Located

Dec 1, 2011

How to find out the required column from user tables using query ?

View 6 Replies View Related

PL/SQL :: Query To Find Table And Column Name By Using Value

Sep 3, 2013

I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are  

where owner NOT IN ('SYS','SYSTEM') and      data_type IN ('CHAR','VARCHAR2','NUMBER') 

My query as follows 

select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner

View 3 Replies View Related

SQL & PL/SQL :: Find Difference Between Two Table With Respect To Column?

Jul 20, 2013

I have two tables tab1 and tab2 with below data Now requirement is to find difference between two table with respect to column "serial_no" which exist only in table tab1 so as per below at serial_no 2 CC4 in tab1 but CC2 in tab2 so this is different how to dump out this data

TAB1
SQL> select * from tab1;

PARENT CHILD ROW_ID SERIAL_NO
-------------------- -------------------- ---------- ----------
P1 CC1 101 1
P1 CC4 104 2
P1 CC5 105 3
P1 CC2 102 4
P1 CC3 103 5

TAB2
SQL> select * from tab2;
PARENT CHILD PREVIOUS_ROWID ROW_ID NEXT_ROWID
-------------------- -------------------- -------------- ---------- ----------
P1 CC1 0 101 102
P1 CC2 101 102 103
P1 CC3 102 103 104
P1 CC4 103 104 105
P1 CC5 104 105 0

create table tab1(parent varchar2(20), child varchar2(20),row_id integer,serial_no integer);

insert into tab1 values('P1','CC1',101,1);
insert into tab1 values('P1','CC4',104,2);
insert into tab1 values('P1','CC5',105,3);
insert into tab1 values('P1','CC2',102,4);
insert into tab1 values('P1','CC3',103,5);

create table tab2(parent varchar2(20), child varchar2(20),previous_rowid integer,row_id integer,next_rowid integer);

insert into tab2 values('P1','CC1',0,101,102);
insert into tab2 values('P1','CC2',101,102,103);
insert into tab2 values('P1','CC3',102,103,104);
insert into tab2 values('P1','CC4',103,104,105);
insert into tab2 values('P1','CC5',104,105,0);

View 8 Replies View Related

SQL & PL/SQL :: How To Find Date And Time Of A Column In Table

Jun 20, 2013

How to find date and time of a column in table ?

say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".

View 10 Replies View Related

PL/SQL :: Find Out Column / Combination Of Columns From Given Table

Sep 23, 2013

Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.

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

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

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

Application Express :: Change Font Size For Item Of Type Display

Jul 13, 2012

how to change the font size for an item of type display only ?

View 4 Replies View Related

SQL & PL/SQL :: Query To Find Application Table Column Details Associated With Descriptive Flex Field

Feb 5, 2010

I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.

I tried the below.

/* Get descriptive_flexfield_name for the application table name*/
SELECT *
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');

/* Get the application column name and end user column name*/
SELECT *
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';

There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained. I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?

I want to build a single query to fetch the application column name and flex field name for a specific table .

View 6 Replies View Related

SQL & PL/SQL :: How To Find OS Block Size

Oct 25, 2002

I find posted and written in many places that the DB block size should be a multiple of the OS block size. I can't find any information, however, on how to find what the OS block size is for an OS. How to find the OS block size for Windows and UNIX systems (Solaris, Linux, and HP-UX)?

View 4 Replies View Related

PL/SQL :: Column Wise Data Store

Feb 18, 2013

how to tune qurey for coulumn wise data saved.because we have to join same table n number of times.for reference go through the following scnarios.
     
Suppose one table T1 is there and it has two column KEY and VALUE.if we are writing qurey for retriving desire result in row manner we have to join samae table no of times.

KEY Value
     agreesWith true
id 1
     assessment False
     basisForDateOfProgression 1
     bestOverallResponse 2
     bestOverallUnknownComments data is ok

Qurey:

select * from (
select t.agreesWith from t1 t
)a

[Code]...

In this manner we can join upto bestOverallUnknownComments .so which method we follow to reduce the execution time and performance should be good.

View 1 Replies View Related







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