I have a table with structure as:
CREATE TABLE XML_TABLE_1
ID NUMBER NOT NULL,
SOURCE VARCHAR2(255 CHAR) NOT NULL,
CREATION_DATE TIMESTAMP(6) NOT NULL
XMLTYPE XML_TEXT STORE AS BINARY XML (
DISABLE STORAGE IN ROW
- 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
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?
Regarding your first question, have a look at the comment section (for scripts) here: http://www.liberidu.com/blog/2008/09/05/xmldb-performance-xml-binary-xml-storage-models/ It probably should still work, besides the fact that they are old SQL scripts.
I think foll query calculates it right while including the LOB storage as:
SELECT SUM(bytes)/1024/1024/1024 gb
WHERE (owner = 'SCHEMA_NAME' and
segment_name = 'TABLE_NAME')
OR (owner, segment_name) IN (
SELECT owner, segment_name
WHERE owner = 'SCHEMA_NAME'
AND table_name = 'TABLE_NAME')
It's 80 GB for our Table with XMLType Column.
But for the second point:
Do we need 80GB of UNDO/ROLLBACK Segment for performing:
Insert /*+ append */ into TableName1 Select * from TableName;