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
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
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
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
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
Aug 9, 2013
I need to find the tablespace size (used and free) for a partitioned table
View 3 Replies
View Related
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
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
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
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
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
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
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
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
Nov 9, 2011
To find out column in which table it is located.
View 1 Replies
View Related
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
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
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
Dec 1, 2011
How to find out the required column from user tables using query ?
View 6 Replies
View Related
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
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
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
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
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
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
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
Jul 13, 2012
how to change the font size for an item of type display only ?
View 4 Replies
View Related
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
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
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