TheOracleDatabaseGatewayforSQLServertranslatesaquerythatreferstoanOracledatabasedatadictionarytableintoaquerythatretrievesthedatafromSQLServersystemtables.YouperformqueriesondatadictionarytablesoverthedatabaselinkinthesamewayyouquerydatadictionarytablesintheOracledatabase.ThegatewaydatadictionaryissimilartotheOracledatabasedatadictionaryinappearanceanduse.
Thisappendixcontainsthefollowingsections:
ThefollowingparagraphsdescribetheOracleDatabaseGatewayforSQLServerdatadictionarysupport.
SQLServerdatadictionaryinformationisstoredintheSQLServerdatabaseasSQLServersystemtables.AllSQLServersystemtableshavenamesprefixedwith"sys".TheSQLServersystemtablesdefinethestructureofadatabase.Whenyouchangedatadefinitions,SQLServerreadsandmodifiestheSQLServersystemtablestoaddinformationabouttheusertables.
SQL>SELECT*FROM"ALL_CATALOG"@MSQL;Whenadatadictionaryaccessqueryisissued,thegateway:
QueriesissueddirectlytoindividualSQLServersystemtablesareallowedbuttheyreturndifferentresultsbecausetheSQLServersystemtablecolumnnamesdifferfromthoseofthedatadictionaryview.Also,certaincolumnsinanSQLServersystemtablecannotbeusedindatadictionaryprocessing.
NootherOracledatabasedatadictionarytablesorviewsaresupported.Ifyouuseaviewnotonthelist,youwillreceivetheOracledatabaseerrorcodefornomorerowsavailable.
QueriesthroughthegatewayofanydatadictionarytableorviewbeginningwithALL_canreturnrowsfromtheSQLServerdatabaseevenwhenaccessprivilegesforthoseSQLServerobjectshavenotbeengranted.WhenqueryinganOracledatabasewiththeOracledatadictionary,rowsarereturnedonlyforthoseobjectsyouarepermittedtoaccess.
ThetablesinthissectionlistOracledatadictionaryviewnamesandtheequivalentSQLServersystemtablesused.Aplussign(+)indicatesthatajoinoperationisinvolved.
ALL_CATALOG
sysusers+sysobjects
ALL_COL_COMMENTS
sysusers+sysobjects+syscolumns
ALL_CONS_COLUMNS
sp_pkeys+sp_fkeys
ALL_CONSTRAINTS
sysusers+sysobjects+sysindexes+sysconstraints+sysreferences
ALL_IND_COLUMNS
sysusers+sysindexes+syscolumns
ALL_INDEXES
sysusers+sysindexes+sysobjects
ALL_OBJECTS
sysusers+sysobjects+sysindexes
ALL_TAB_COLUMNS
ALL_TAB_COMMENTS
ALL_TABLES
ALL_USERS
sysusers
ALL_VIEWS
sysusers+sysobjects+syscomments
DBA_CATALOG
DBA_COL_COMMENTS
DBA_OBJECTS
DBA_TABLES
DBA_TAB_COLUMNS
DBA_TAB_COMMENTS
DICT_COLUMNS
sysobjects+syscolumns
DICTIONARY
sysobjects
DUAL
TABLE_PRIVILEGES
sysprotects+sysusers+sysobjects
USER_CATALOG
USER_COL_COMMENTS
USER_CONS_COLUMNS
USER_CONSTRAINTS
USER_IND_COLUMNS
USER_INDEXES
USER_OBJECTS
USER_TAB_COLUMNS
USER_TAB_COMMENTS
USER_TABLES
USER_USERS
USER_VIEWS
ThereisaminordifferencebetweenthegatewaydatadictionaryandatypicalOracledatabasedatadictionary.TheOracledatabasecolumnsthataremissinginanSQLServersystemtablearefilledwithzeros,spaces,nullvalues,not-applicablevalues(N.A.),ordefaultvalues,dependingonthecolumntype.
Thegatewaydatadictionarytablesandviewsprovidethefollowinginformation:
TheyaredescribedherewithinformationretrievedbyanOracleSQL*PlusDESCRIBEcommand.ThevaluesintheNullcolumnmightdifferfromtheOracledatabasedatadictionarytablesandviews.Anydefaultvalueisshowntotherightofanitem,butthisisnotinformationreturnedbyDESCRIBE.
Note:
OWNER
VARCHAR2(256)
-
TABLE_NAME
TABLE_TYPE
VARCHAR2(5)
"TABLE"or"VIEW"
COLUMN_NAME
COMMENTS
VARCHAR2(1)
CONSTRAINT_NAME
POSITION
NUMBER(5)
CONSTRAINT_TYPE
"C"or"P"or"R"or"U"
SEARCH_CONDITION
NULL
R_OWNER
R_CONSTRAINT_NAME
DELETE_RULE
STATUS
DEFERRABLE
DEFERRED
VALIDATED
GENERATED
BAD
RELY
LAST_CHANGE
DATE
INDEX_OWNER
INDEX_NAME
TABLE_OWNER
COLUMN_POSITION
NUMBER(3)
COLUMN_LENGTH
NUMBER
Char_LENGTH
DESCEND
VARCHAR2(4)
INDEX_TYPE
VARCHAR(7)
"TABLE"or"CLUSTER"
UNIQUENESS
COMPRESSION
PREFIX_LENGTH
0
TABLESPACE_NAME
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
PCT_THRESHOLD
INCLUDE_COLUMN
FREELISTS
FREELIST_GROUPS
PCT_FREE
LOGGING
BLEVEL
LEAF_BLOCKS
DISTINCT_KEYS
AVG_LEAF_BLOCKS_PER_KEY
AVG_DATA_BLOCKS_PER_KEY
CLUSTERING_FACTOR
NUM_ROWS
SAMPLE_SIZE
LAST_ANALYZED
DEGREE
INSTANCES
PARTITIONED
TEMPORARY
SECONDARY
BUFFER_POOL
USER_STATS
DURATION
PCT_DIRECT_ACCESS
ITYP_OWNER
ITYP_NAME
PARAMETERS
GLOBAL_STATS
DOMIDX_STATUS
DOMIDX_OPSTATUS
FUNCIDX_STATUS
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
VARCHAR2(9)
"TABLE"or"VIEW"or"INDEX"or"PROCEDURE"
CREATED
LAST_DDL_TIME
TIMESTAMP
"VALID"
DATA_TYPE
DATA_TYPE_MOD
DATA_TYPE_OWNER
DATA_LENGTH
DATA_PRECISION
DATA_SCALE
NULLABLE
"Y"or"N"
COLUMN_ID
DEFAULT_LENGTH
DATA_DEFAULT
NUM_DISTINCT
LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS
NUM_BUCKETS
CHARACTER_SET_NAME
CHAR_COL_DEC_LENGTH
AVG_COL_LEN
CHAR_LENGTH
CHAR_USED
V80_FMT_IMAGE
DATA_UPGRADED
HISTOGRAM
CLUSTER_NAME
IOT_NAME
PCT_USED
BACKED_UP
BLOCKS
EMPTY_BLOCKS
AVG_SPACE
CHAIN_CNT
AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS
CACHE
TABLE_LOCK
IOT_TYPE
VARHCAR2(1)
NESTED
ROW_MOVEMENT
SKIP_CORRUPT
MONITORING
USERNAME
USER_ID
NUMBER(10)
VIEW_NAME
TEXT_LENGTH
TEXT
TYPE_TEXT_LENGTH
TYPE_TEXT
OID_TEXT_LENGTH
OID_TEXT
VIEW_TYPE_OWNER
VIEW_TYPE
DUMMY
"X"
GRANTEE
GRANTOR
SELECT_PRIV
"Y"
INSERT_PRIV
"A"
DELETE_PRIV
UPDATE_PRIV
REFERENCES_PRIV
ALTER_PRIV
INDEX_PRIV
VARCHAR2(30)
"R"or"P"or"U"or"C"
VARCHAR2(7)
CHAR_COL_DECL_LENGTH
ACCOUNT_STATUS
"OPEN"
LOCK_DATE
EXPIRY_DATE
DEFAULT_TABLESPACE
TEMPORARY_TABLESPACE
INITIAL_RSRC_CONSUMER_GROUP
EXTERNAL_NAME
Scriptingonthispageenhancescontentnavigation,butdoesnotchangethecontentinanyway.