Database Reference
In-Depth Information
So, just like with the nested table, there is a lot going on here. A pseudo primary key of 16 bytes was added, there
are virtual columns, and an index created for us. We can change the default behavior with regard to the value of the
object identifier assigned to an object, as we'll see in a moment. First, let's look at the full verbose SQL that would
generate our table for us. This was generated using Data Pump, since I wanted to easily see the dependent objects,
including all of the SQL needed to re-create this particular object instance. This was achieved via the following:
$ expdp eoda directory=tk tables='PEOPLE' dumpfile=p.dmp logfile=p.log
Export: Release 12.1.0.1.0 - Production on Sat Jan 18 17:10:11 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
...
$ impdp eoda directory=tk dumpfile=p.dmp logfile=pi.log sqlfile=people.sql
Import: Release 12.1.0.1.0 - Production on Sat Jan 18 17:11:54 2014
...
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "EODA"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "EODA"."SYS_SQL_FILE_FULL_01":
eoda/******** directory=tk dumpfile=p.dmp logfile=pi.log sqlfile=people.sql
Review of the
people.sql
file that results would show this:
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "EODA"."PEOPLE" OF "EODA"."PERSON_TYPE"
OID 'F0484A73A93A7093E043B7D04F0A821B'
OIDINDEX ( PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
This gives us a little more insight into what is actually taking place here. We see the
OIDINDEX
clause clearly now,
and we see a reference to the OID column followed by a hex number.
The
OID '<big hex number>'
syntax is not documented in the Oracle documentation. All this is doing is
ensuring that during an
expdp
and subsequent
impdp
, the underlying type
PERSON_TYPE
is, in fact, the
same
type.
This will prevent an error that would occur if we performed the following steps:
Create the
PEOPLE
table.
1.
2.
Export the table.
Drop the table and the underlying
PERSON_TYPE
.
3.
Create a new
PERSON_TYPE
with different attributes.
4.
Import the old
PEOPLE
data.
5.
