| Oracle® Database XBRL Extension Developer's Guide 11g Release 2 (11.2) Part Number E17070-04 |
|
|
PDF · Mobi · ePub |
| Oracle® Database XBRL Extension Developer's Guide 11g Release 2 (11.2) Part Number E17070-04 |
|
|
PDF · Mobi · ePub |
This chapter covers database administration of XBRL Extension to Oracle XML DB. It includes these topics:
Note:
Refer to "Placeholders in Oracle Database XBRL Extension Developer's Guide" for explanations of the placeholders used here.An XBRL repository uses a set of base tables and a set of automatically generated XMLIndex indexes.
The base tables include the following XMLType tables with binary XML storage, which store all of your XBRL-related documents. See also Example 4-5.
ORA$XBRLSCHEMA – taxonomy schema documents
ORA$XBRLINSTANCE – instance documents
ORA$XBRLLINKBASE – linkbase documents
There are also other, non-XMLType base tables, including these:
ORA$XBRLPATH – has these columns:
doctype: XBRL document type (SCHEMA, LINKBASE, or INSTANCE)
docpath: location of the document in the XBRL repository
uploaddate: time when the document was loaded into the XBRL repository
ORA$XBRLNWKCACHE – a cache for information used during concept generation
XMLIndex indexes (with structured components) are created automatically for base tables ORA$XBRLSCHEMA and ORA$XBRLINSTANCE. These indexes project the values of the structured parts of an XBRL document. They improve performance for queries and analysis of XBRL documents. These indexes have their own storage tables.
The script that creates an XBRL repository, xbrlcrt.sql, takes two parameters that specify the tablespaces to use for the base tables, on the one hand, and the index storage tables, on the other hand. These parameters are xb_rep_ts and xb_rep_idx_ts, respectively. You can of course use the same value for both parameters if you wish. In that case, the base tables and the index storage tables share the same tablespace.
For an existing XBRL repository, you can use script xbrlrecidxdrv.sql to change the tablespace used by the index storage tables. Again, you use parameter xb_rep_idx_ts to specify the tablespace to use for this:
shell> cd XBRLScripts SQL+> @xbrlrecidxdrv.sql xb_rep xb_rep_pass xb_rep_idx_ts
For a given XBRL repository, you can partition base table ORA$XBRLINSTANCE, which stores your XBRL instance documents. If you do this then the automatically created XMLIndex index on that table, XBRL$INSTANCEIDX, together with all of its index storage tables, are automatically equipartitioned. Equipartitioning means that there is a corresponding index-table partition for each partition of the base table.
If you want to partition base table ORA$XBRLINSTANCE for a given repository then you must edit script xbrlddl.sql before you use script xbrlcrt.sql to create that repository. In script xbrlddl.sql, change the default CREATE TABLE statement for table ORA$XBRLINSTANCE to one that partitions the table.
Example 4-1 illustrates this. It partitions XMLType table ORA$XBRLINSTANCE using virtual column entity_identifier_text, targeting XML element identifier (a child of element entity and a grandchild of the first context element in an XBRL document).
Example 4-1 Edited CREATE TABLE Statement with Partitioning
CREATE TABLE ORA$XBRLINSTANCE of XMLType STORE AS BINARY XML
VIRTUAL COLUMNS
(entity_identifier_text AS
(XMLCast
(XMLQuery('declare namespace
xbrli="http://www.xbrl.org/2003/instance";
/xbrli:/xbrl/xbrli:context[1]/xbrli:entity/xbrli:identifier/text()'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS VARCHAR2(50))))
PARTITION BY RANGE (entity_identifier_text)
(PARTITION p1 VALUES LESS THAN ('0000066741'),
PARTITION p2 VALUES LESS THAN ('0000789020')
PARTITION p3 VALUES LESS THAN (MAXVALUE));
You can define such partitioning when you create your XBRL repository after installing XBRL Extension to Oracle XML DB. If you already have an existing repository that you want to partition (or partition differently), you must create a new repository partitioned as needed, and then load it using the data from the previously existing repository. In other words, there is no way to partition an existing repository; you must create a new, partitioned one to replace the existing one.
See Also:
"Creating an XBRL Repository", steps 4 and 5 for information about checking the result of executing your updated CREATE TABLE statement
Oracle XML DB Developer's Guide for information about partitioning XMLType data using virtual columns
When you create an XBRL repository, any partitions of base table ORA$XBRLINSTANCE are in the same tablespace, xb_rep_ts, and the index storage table partitions are all in the same tablespace, xb_rep_idx_ts. (See "Creating a Tablespace for the XBRL Repository Indexes".)
You can use different tablespaces for different partitions by altering the base table (ORA$XBRLINSTANCE) for XBRL instance documents and for the storage tables of the corresponding XMLIndex index (XBRL$INSTANCEIDX). Example 4-2 and Example 4-3 illustrate this, respectively.
This section provides some queries you can use to obtain information about your XBRL data or about the product, XBRL Extension to Oracle XML DB.
Example 4-4 shows how to obtain the version number of the product.
Example 4-4 Version Number for XBRL Extension to Oracle XML DB
SELECT value FROM xbrlsys.ora$xbrlrepprop WHERE name='XBRLVERSION';
Example 4-5 just lists the XMLType tables for the current XBRL repository, that is, ORA$XBRLSCHEMA, ORA$XBRLLINKBASE, and ORA$XBRLINSTANCE.
Example 4-6 lists the repository XMLType tables and their XMLIndex indexes.
Example 4-6 XMLType Tables and Their XMLIndex Indexes
SELECT table_name, index_name FROM USER_XML_INDEXES;
Example 4-7 lists all of the repository XMLIndex indexes, together with their corresponding index storage tables.
Example 4-7 XMLIndex Indexes and Their Index Storage Tables
SELECT DISTINCT p.index_name, t.table_name
FROM ALL_XML_INDEXES p,
XMLTable('//xmltab'
PASSING p.parameters
COLUMNS table_name VARCHAR2(200) PATH '@name') t
Example 4-8 lists the table and tablespace names of the nonpartitioned XMLType tables.
Example 4-8 Nonpartitioned XMLType Tables and Their Tablespaces
SELECT table_name, tablespace_name FROM USER_OBJECT_TABLES;
Example 4-9 lists the names of all nonpartitioned index storage tables and their tablespaces, for a given XMLIndex index — in this case, index XBRL$INSTANCEIX.
Example 4-9 Nonpartitioned XMLIndex Index Storage Tables and Their Tablespaces
SELECT u.table_name, u.tablespace_name FROM USER_TABLES u WHERE u.table_name
IN (SELECT DISTINCT t.table_name
FROM ALL_XML_INDEXES p,
XMLTable('//xmltab' PASSING p.parameters
COLUMNS table_name VARCHAR2(200) PATH '@name') t
WHERE index_name = 'XBRL$INSTANCEIDX') ORDER BY 1;
Example 4-10 obtains the tablespace name for a given nonpartitioned index storage table — in this case, table ORAXBRL_INSTANCE_ITEM.
Example 4-10 Tablespace of a Given Nonpartitioned Index Storage Table
SELECT tablespace_name FROM USER_TABLES WHERE table_name = 'ORAXBRL_INSTANCE_ITEM';
Example 4-11 lists the table names, the index names, and the tablespace name for the nonpartitioned secondary indexes on the index storage tables for a given XMLIndex index — in this case, index XBRL$INSTANCEIX.
Example 4-11 Tables and Tablespace of Secondary Indexes on Index Storage Tables
SELECT i.table_name, i.index_name, i.tablespace_name FROM USER_INDEXES i
WHERE i.table_name
IN (SELECT DISTINCT t.table_name
FROM ALL_XML_INDEXES p,
XMLTable('//xmltab' PASSING p.parameters
COLUMNS table_name VARCHAR2(200) PATH '@name') t
WHERE index_name = 'XBRL$INSTANCEIDX') AND i.index_name NOT LIKE 'SYS%'
ORDER BY 1, 2;
Example 4-12 lists the partitions and their tablespaces for partitioned XMLType table ORA$XBRLINSTANCE.
Example 4-12 Partitions and Tablespaces for XMLType Table ORA$XBRLINSTANCE
SELECT partition_name, tablespace_name FROM USER_TAB_PARTITIONS WHERE table_name = 'ORA$XBRLINSTANCE';
Example 4-13 lists the partitions and tablespaces for the index storage tables of partitioned XMLIndex index XBRL$INSTANCEIX.
Example 4-13 Partitions and Tablespaces for Storage Tables of XBRL$INSTANCEIX
SELECT y.table_name, y.partition_name, y.tablespace_name
FROM USER_TAB_PARTITIONS y
WHERE y.table_name
IN (SELECT DISTINCT t.table_name
FROM ALL_XML_INDEXES p,
XMLTable('//xmltab'
PASSING p.parameters
COLUMNS table_name VARCHAR2(200) PATH '@name') t
WHERE index_name ='XBRL$INSTANCEIDX')
ORDER BY 1,2;
Example 4-14 lists the partitions and their tablespaces for a given partitioned index storage table — in this case, ORAXBRL_INSTANCE_ITEM.
Example 4-14 Partitions and Tablespaces of a Given Partitioned Index Storage Table
SELECT partition_name, tablespace_name FROM USER_TAB_PARTITIONS WHERE table_name = 'ORAXBRL_INSTANCE_ITEM';
Example 4-15 lists the index storage tables for partitioned XMLIndex index XBRL$INSTANCEIX, along with their secondary indexes, their partitions, and their tablespaces.
Example 4-15 Detailed Information About Partitioned Index XBRL$INSTANCEIX
SELECT u.table_name, i.index_name, i.partition_name, i.tablespace_name
FROM user_ind_partitions i, user_indexes u
WHERE i.index_name = u.index_name
AND u.table_name
IN (SELECT DISTINCT t.table_name
FROM ALL_XML_INDEXES p,
XMLTable('//xmltab'
PASSING p.parameters
COLUMNS table_name VARCHAR2(200) PATH '@name') t
WHERE index_name ='XBRL$INSTANCEIDX')
AND i.index_name NOT LIKE 'SYS%'
ORDER BY 1, 2, 3;
|
 Copyright © 2010, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|