C
Partitioning Legacy Point Data

Spatial has undergone an architectural change, beginning with the 7.3.3 release. The emphasis on partitioned tables has been replaced by the improved spatial indexing features.

Spatial provides the essential functions, procedures, and scripts for using and managing both spatially indexed data and partitioned point data. The information in this appendix is relevant only to users who have not yet migrated to the new data model. For all other users, spatial indexing is preferred and recommended.

C.1 Overview

Partitioning is a technique where data is loaded into tables that automatically subdivide when a predefined maximum size is reached. During subdivision, data is moved from the parent partition to the child partitions and the parent partition is deleted. Storage parameters for child partitions are inherited from the root partition and can be changed at any time.

A partitioned table has a partition key that is an HHCODE column created by encoding multidimensional point data using the SDO_ENCODE() function. In the partitioning process, at each subdivision, data is subdivided into 2n partitions where n is the number of dimensions encoded in the HHCODE column. You can encode up to 32 dimensions using Spatial.

C.2 Partitioning Process

This guide does not attempt to provide the information necessary for fully utilizing table partitioning for point data. Existing users who need to use this method should continue to use their Spatial Data Option 7.3.2 documentation. The following is a high-level description of the partitioning process:

1. Start with an Oracle8i table containing multidimensional point data. For example, columns of X and Y coordinate data from a blueprint or map.

2. Create a table or view from the original Oracle8i table containing the columns you want, plus a new HHCODE column.

   An HHCODE column is a new data type used to encode multiple dimensions into a unique orderable value. HHCODE is not a point, but rather a bounded cell representing an object space in as many dimensions as have been defined. An HHCODE data type is defined as RAW(255).

3. Create the HHCODE data type by encoding multiple dimensions into a single value using the SDO_ENCODE() function. The HHCODE data type will be used as the partition key.

4. Register a partitioned table in the Spatial data dictionary using the SDO_ADMIN.REGISTER_PARTITION_INFO() procedure. This procedure takes the name of a table, the name of the partition key column, and the maximum number of records you want stored in a partition before it subdivides.

5. Call the SDO_ADMIN.PARTITION() procedure with the name of the table or view containing the partition key column and the tablespace in which the partitions should be created. In this step, the data is partitioned based on dimensions encoded in the HHCODE column.

6. If the underlying table has constraints, grants, or triggers, the owner needs to use the SDO_ADMIN.PROPAGATE_GRANTS() procedure to set those properties on the partitions.

7. To add more partitioned point data, load the data into a table, and call SDO_ADMIN.PARTITION() again. The dimensions encoded in the HHCODE column must have the same boundaries to be loaded into the existing partitioned table.

8. After you have added data multiple times, or after adding or deleting a large amount of data, there may be partitions that exceed the high-water mark or there may be partitions that can be merged. Call the SDO_ADMIN.REPARTITION() procedure to reorganize the partitioned table. Repartitioning is a computation-intensive task that should be performed only when necessary.

C.3 Scripts for the Deprecated Partitioned Point Data Model

This section describes the following scripts:

• altpart.sql

• drppart.sql

• sdogrant.sql

Although the scripts described in this section are available, the recommended approach is to use Oracle8i partitioning and spatial indexing.

C.3.1 altpart.sql Script

The altpart.sql script file shows how to use dynamic SQL in a PL/SQL procedure to modify all partitions of a Spatial partitioned table.

The Spatial data dictionary view used in this SQL script requires that a registered Spatial partitioned table is specified. If the table is not registered, you can use the USER_TABLES view to select all the partitioned tables from the user's schema. To use the USER_TABLES view, enter the following syntax:

SQL> SELECT TABLENAME FROM user_tables WHERE TABLENAME LIKE 

2> '%tablename_P%';

C.3.2 drppart.sql Script

The drppart.sql script file shows how to use dynamic SQL in a PL/SQL procedure to drop (remove) all partitions of a Spatial partitioned table. After running this procedure, you must run the SDO_ADMIN.DROP_PARTITION_INFO() procedure.

The Spatial data dictionary view used in this SQL script requires that a registered Spatial partitioned table is specified. If the table is not registered, you can use the USER_TABLES view to select all the partitioned tables from the user's schema. To use the USER_TABLES view, use the following syntax:

SQL> SELECT TABLENAME FROM user_tables WHERE TABLENAME LIKE 

2> '%tablename_P%';

C.3.3 sdogrant.sql Script

The sdogrant.sql script file contains an administrative procedure, PROPAGATE_GRANTS(), which is used after calling the SDO_ADMIN.PARTITION() or SDO_ADMIN.REPARTITION() procedures.

This procedure must first be compiled by running the sdogrant.sql file. The PROPAGATE_GRANTS() procedure is callable only by the user who compiled it.

C.4 Administrative Functions for the Deprecated Model

Table C-1 lists the procedures that can be used with partitioned point data. These procedures are neither required nor compatible with the geometry-based data format.

Table C-1 Administrative Procedures for Partitioned Point Data

Procedure	Data Structure	Description
SDO_ADMIN.ALTER_HIGH_WATER_MARK	Partitioned points	Alters the high-water mark of a partitioned table.
SDO_ADMIN.DROP_PARTITION_INFO	Partitioned points	Removes a partitioned table.
SDO_ADMIN.PARTITION	Partitioned points	Places data into partitioned tables.
SDO_ADMIN.PROPAGATE_GRANTS	Partitioned points	Propagates the grants on the registered underlying table to the various partitions.
SDO_ADMIN.REGISTER_PARTITION_ INFO	Partitioned points	Creates a partitioned spatial table.
SDO_ADMIN.REPARTITION	Partitioned points	Reorganizes a table based on the sorted values of the data contained within it.
SDO_ADMIN.VERIFY_PARTITIONS	Partitioned points	Checks for the existence of a table.

---

SDO_ADMIN.ALTER_HIGH_WATER_MARK

Purpose

This procedure alters the high-water mark of a partitioned spatial table. The high-water mark defines how many records can be stored in a partition before it subdivides. The table must exist and be registered in the Spatial data dictionary.

This procedure is for use only with partitioned point data.

Syntax

SDO_ADMIN.ALTER_HIGH_WATER_MARK (tablename, high_water_mark)

Keywords and Parameters

tablename	Specifies the name of the partitioned table. Data type is VARCHAR2.
high_water_mark	Specifies the new high-water mark for the table. Data type is INTEGER.

Usage Notes

None.

The following example changes the high-water mark to 5000 records for the table1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.ALTER_HIGH_WATER_MARK('table1', 5000);

Related Topics

• SDO_ADMIN.REPARTITION() procedure

• altpart.sql sample SQL script file

---

SDO_ADMIN.DROP_PARTITION_INFO

Purpose

This procedure removes a partitioned spatial table from the Spatial data dictionary. The table must exist and must be registered in the Spatial data dictionary.

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.DROP_PARTITION_INFO (tablename)

Keywords and Parameters

tablename

Specifies the name of the partitioned table. Data type is VARCHAR2.

Usage Notes

This procedure does not remove the spatial table and its associated partition tables from the user's schema. For a description of how to remove a partitioned spatial table from the user's schema, see the drppart.sql sample SQL script file described in Section C.3.2.

The following example removes the table1 table from the Spatial data dictionary:

SQL> EXECUTE SDO_ADMIN.DROP_PARTITION_INFO('table1');

Related Topics

• drppart.sql sample SQL script file

---

SDO_ADMIN.PARTITION

Purpose

This procedure places data into partitioned tables based on the sorted order of encoded dimensional values.

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.PARTITION (owner.source_table, tablename, parallel, guess, plummet_flag [,tablespace] )

Keywords and Parameters

owner.source_table	Specifies the Oracle8i table or view of the table containing the partition key column. Data type is VARCHAR2.
tablename	Specifies the name of the table to partition. Data type is VARCHAR2.
parallel	Specifies the degree of parallelism for an operation on a single instance. Data type is INTEGER.
guess	Specifies the estimated largest common level of all the potential partitions to be created from data in the source_table. The common level of a partition is the number of levels of resolution of the common HHCODE for the partition. Data type is INTEGER.
plummet_flag	Specifies if the common HHCODE for all the potential partitions to be created from data in the source_table contains the maximum possible common level. If TRUE, the common HHCODE for each potential partition contains the maximum possible common level. If FALSE, the common HHCODE for each potential partition contains the minimum possible common level. Default value is FALSE. Data type is BOOLEAN.
tablespace	Specifies the tablespace in which the partitions should be created. Default is the tablespace of the underlying table.

Usage Notes

Consider the following when using this procedure:

• The maximum size of the partitioned tables is determined by the high-water mark of the partitioned spatial table.

• To perform this procedure, first load the original data into an Oracle8i table using a utility such as SQL*Loader. After the data is loaded, encode the data using the appropriate combination of Spatial data conversion functions. The encoded data is used as the partition key column. The partition key column is provided as either a column in the Oracle8i table or as a view of that table.

• For more information on specifying the degree of parallelism, see the Oracle8i Tuning manual.

The following example partitions the table1 partitioned spatial table with data contained in the source1 table:

SQL> EXECUTE SDO_ADMIN.PARTITION('source1','table1',1,10,FALSE);

Related Topics

• SDO_ADMIN.REGISTER_PARTITION_INFO( ) procedure

---

SDO_ADMIN.PROPAGATE_GRANTS

Purpose

This procedure is used to propagate the grants on the underlying table to the partitions.

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.PROPAGATE_GRANTS (tablename)

Keywords and Parameters

tablename

Specifies the name of the partitioned table. Data type is VARCHAR2.

Usage Notes

This procedure is used after calls to SDO_ADMIN.PARTITION() or SDO_ADMIN.REPARTITION(). It must be called by the owner of the partition.

This procedure must be compiled prior to use. See Section C.3.3.

The following example propagates grants from the TABLE1 partitioned spatial table:

SQL>  EXECUTE SDO_ADMIN.PROPAGATE_GRANTS('TABLE1'); 

Related Topics

• SDO_ADMIN.PARTITION( ) procedure

• SDO_ADMIN.REPARTITION( ) procedure

---

SDO_ADMIN.REGISTER_PARTITION_INFO

Purpose

This procedure creates a partitioned spatial table entry in the Spatial data dictionary, and defines the partition key column and the high-water mark for the table.

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.REGISTER_PARTITION_INFO (tablename, column, high_water_mark)

Keywords and Parameters

tablename	Specifies the name of the partitioned table. Data type is VARCHAR2.
column	Specifies the name of the partition key column for the table. Data type is VARCHAR2.
high_water_mark	Specifies the number of records to store in a partition before the partition subdivides. Data type is INTEGER.

Usage Notes

The SQL CREATE TABLE statement is used to create the partitioned spatial table, with the partition key column defined as RAW(255), prior to calling this procedure.

The following example registers the TABLE1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.REGISTER_PARTITION_INFO('table1', 

2> 'hhcolumn', 1000);

Related Topics

• SDO_ADMIN.PARTITION( ) procedure

---

SDO_ADMIN.REPARTITION

Purpose

This procedure reorganizes a partitioned spatial table based on the sorted order of encoded dimensional values already contained in it. The table must exist and must be registered in the Spatial data dictionary.

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.REPARTITION (tablename, parallel, [tablespace])

Keywords and Parameters

tablename	Specifies the name of the partitioned table. Data type is VARCHAR2.
parallel	Specifies the degree of parallelism for an operation on a single instance. Data type is INTEGER.
tablespace	Specifies the name of the tablespace in which to create the partition. Data type is VARCHAR2.

Usage Notes

Consider the following when using this procedure:

• The tablespace variable is optional. If you do not supply a tablespace name, the partitions are created in the same tablespace as the registered partition table.

• The maximum size of the reorganized partition tables is determined by the high-water mark of the partitioned spatial table.

The following example repartitions the table1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.REPARTITION('table1', 1);

Related Topics

• SDO_ADMIN.ALTER_HIGH_WATER_MARK( ) procedure

---

SDO_ADMIN.VERIFY_PARTITIONS

Purpose

This procedure checks if the partitioned spatial table exists, if it is registered in the Spatial data dictionary, and if the partition key column exists as defined in the Spatial data dictionary.

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.VERIFY_PARTITIONS (tablename)

Keywords and Parameters

tablename

Specifies the name of the table. Data type is VARCHAR2.

Usage Notes

This procedure can generate the following errors depending on the results of the verification:

• SDO 13113 (Oracle table does not exist)

• SDO 13108 (spatial table not found)

• SDO 13111 (spatial table has no partition key defined)

• SDO 13129 (HHCODE column not found)

The following example verifies the table1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.VERIFY_PARTITIONS('table1');

Related Topics

• SDO_ADMIN.REGISTER_PARTITION_INFO( ) procedure

C.5 Data Functions

The functions described in this section are not required for creating or maintaining a spatial database, however, they are provided for convenience in working with legacy data in partitioned point data tables. They are used with SQL SELECT, INSERT, UPDATE, and DELETE statements to perform the following:

• Generate dimensions from bounded, hierarchical, or date data values

• Encode and decode dimensions

• Retrieve bounded, hierarchical, or date data values from dimensions

When using these functions in basic SQL statements, use the form:
SDO_<function>. When using the functions inside a PL/SQL block, use a period (.) instead of the underscore (_).

This section contains descriptions of the spatial functions listed in Table C-2.

Table C-2 Partitioned Point Data Functions

Function	Purpose
SDO_BVALUETODIM	Creates a dimension from bounded data values.
SDO_COMPARE	Evaluates the relationship between two objects described by HHCODEs.
SDO_DATETODIM	Creates a dimension from an Oracle DATE data type.
SDO_DECODE	Extracts a single dimension from an HHCODE.
SDO_ENCODE	Creates an HHCODE by combining dimensions to describe an area or point.
SDO_TO_BVALUE	Extracts a bounded data value from a dimension.
SDO_TO_DATE	Extracts an Oracle DATE data type from a dimension.

---

SDO_BVALUETODIM

Purpose

This function creates a dimension from a bounded value, which is a value contained in a set of values expressed as a lower boundary and an upper boundary.

Syntax

SDO_BVALUETODIM (value, lower_boundary, upper_boundary, decimal_scale)

Keywords and Parameters

value	Specifies the value for the particular dimension. Data type is NUMBER.
lower_boundary	Specifies the lower boundary of the dimension range. Data type is NUMBER.
upper_boundary	Specifies the upper boundary of the dimension range. Data type is NUMBER.
decimal_scale	Specifies the number of digits to the right of the decimal point. Data type is NUMBER.

Returns

This function returns a dimension. The data type is RAW.

Usage Notes

The following example shows the SDO_BVALUETODIM() function:

SQL> INSERT INTO sourcetable1(SAMPLENAME,DATA_PT)

2> VALUES ('SAMPLE1',SDO_ENCODE(SDO_BVALUETODIM(10,-100,100,7),

3> SDO_BVALUETODIM(20,-100,100,7));

Related Topics

• SDO_ENCODE( ) function

• SDO_TO_BVALUE( ) function

---

SDO_COMPARE

Purpose

This function evaluates the relationship between an area or point described by an HHCODE and another HHCODE, or a range of HHCODEs expressed as an upper bound and lower bound.

Syntax

SDO_COMPARE (hhcode_expression, {hhcode_expression | lower_bound_HHCODE,upper_bound_HHCODE})

Keywords and Parameters

hhcode_expression	Specifies an expression that evaluates to an HHCODE. Data type is RAW.
lower_bound_HHCODE	Specifies the lower bound HHCODE expression. Data type is RAW.
upper_bound_HHCODE	Specifies the upper bound HHCODE expression. Data type is RAW.

Returns

This function returns one of the following keywords:

• ENCLOSES

• EQUAL

• INSIDE

• OUTSIDE

• OVERLAP

The data type is VARCHAR2.

Usage Notes

The following example selects all points that fall within the given multidimensional range:

SQL> SELECT SDO_GID FROM layer1_SDOINDEX WHERE

2> SDO_COMPARE(SDO_MAXCODE,

3> SDO_ENCODE(5,5),

4> SDO_ENCODE(25,25))='INSIDE';

The following example selects GIDs based on interaction between their spatial index tiles:

SQL> SELECT SDO_GID FROM layer1_SDOINDEX A, layer2_SDOINDEX B

2> WHERE SDO_COMPARE(A.SDO_CODE,B.SDO_CODE) != 'OUTSIDE';

Related Topics

• SDO_GEOM.RELATE( ) function

---

SDO_DATETODIM

Purpose

This function creates a dimension from an Oracle DATE data type. The component number determines the level of resolution of the date in the dimension.

Syntax

SDO_DATETODIM (date [, component])

Keywords and Parameters

date	Specifies the calendar date. Data type is DATE.
component	Specifies the level of resolution. The component number values are defined as follows: 1 accurate to year 2 accurate to month 3 accurate to day 4 accurate to hour 5 accurate to minute 6 accurate to second The default value is 6. Data type is INTEGER.

Returns

This function returns a dimension. The data type is RAW.

Usage Notes

You must use a valid Oracle date format string.

The following example shows the SDO_DATETODIM() function:

SQL> INSERT INTO sourcetable1(SAMPLENAME,DATA_PT)

2> VAUES('SAMPLE1',SDO_ENCODE(SDO_DATETODIM(TO_DATE('19-Jul-96'),

3> SDO_BVALUETODIM(100,-1000,1000,7)));

Related Topics

• SDO_ENCODE( ) function

• SDO_TO_DATE( ) function

---

SDO_DECODE

Purpose

This function extracts a single dimension from an HHCODE.

Syntax

SDO_DECODE (hhcode_expression, dimension_number)

Keywords and Parameters

hhcode_expression	Specifies an expression that evaluates to an HHCODE. Data type is RAW.
dimension_number	Specifies the dimension number to extract. Data type is INTEGER.

Returns

This function returns a dimension. The data type is RAW.

Usage Notes

The SDO_DECODE() function is called once for each dimension to be decoded.

The following example shows the SDO_DECODE() function:

SQL> SELECT 

2> SDO_TO_BVALUE(SDO_DECODE(DATA_PT,1),1,6),

3> SDO_TO_BVALUE(SDO_DECODE(DATA_PT,2),-100,100),

4> SDO_TO_DATE(SDO_DECODE(DATA_PT,3))

5> FROM sourcetable1 WHERE SAMPLENAME='SAMPLE1';

Related Topics

• SDO_TO_BVALUE( ) function

• SDO_TO_DATE( ) function

---

SDO_ENCODE

Purpose

This function combines dimensions to create the HHCODE that describes an area or point.

Syntax

SDO_ENCODE (dimension1[,dimension2 ...])

Keywords and Parameters

dimension

Specifies an expression created by the SDO_BVALUETODIM or SDO_DATETODIM functions. Data type is RAW.

Returns

This function returns an HHCODE. The data type is RAW.

Usage Notes

Consider the following when using this function:

• When encoding dimensions, the order of the dimensions in the parameter list must be consistent for all rows within the table.

• This function can encode up to 32 dimensions.

The following example shows the SDO_ENCODE() function:

SQL> INSERT INTO sourcetable1(SAMPLENAME,DATA_PT)

2> VALUES ('SAMPLE1',SDO_ENCODE(SDO_BVALUETODIM(50,-100, 100, 10),

3> SDO_BVALUETODIM(30,-100,100,10),

4> SDO_DATETODIM(TO_DATE('05-Jul-96'),3)));

Related Topics

• SDO_BVALUETODIM( ) function

• SDO_DATETODIM( ) function

---

SDO_TO_BVALUE

Purpose

This function returns the original bounded data value of a dimension.

Syntax

SDO_TO_BVALUE (dimension, lower_boundary, upper_boundary)

Keywords and Parameters

dimension	Specifies the dimension. Data type is RAW.
lower_boundary	Specifies the lower boundary of the dimension range. Data type is NUMBER.
upper_boundary	Specifies the upper boundary of the dimension range. Data type is NUMBER.

Returns

This function returns a bounded data value. The data type is NUMBER.

Usage Notes

This function returns a number that is the value for a dimension within the specified range. This is not necessarily the range for which the dimension was originally created.

The following example shows the SDO_TO_BVALUE() function:

SQL> SELECT (SDO_TO_BVALUE(SDO_DECODE(DATA_PT,2),-100,100)

2> FROM sourcetable1 WHERE SAMPLENAME='SAMPLE1';

Related Topics

• SDO_DECODE( ) function

• SDO_BVALUETODIM( ) function

---

SDO_TO_DATE

Purpose

This function returns the original date value of a dimension.

Syntax

SDO_TO_DATE (dimension)

Keywords and Parameters

dimension

Specifies the dimension. Data type is RAW.

Returns

This function returns an Oracle DATE data type.

Usage Notes

The following example shows the SDO_TO_DATE() function:

SQL> SELECT SDO_TO_DATE(SDO_DECODE(DATA_PT,3))

2> FROM sourcetable1 WHERE SAMPLENAME='SAMPLE1';

Related Topics

• SDO_DATETODIM( ) function

• SDO_DECODE( ) function

C.6 Data Dictionary

The Spatial data dictionary is a set of tables owned by the database user mdsys. An extension to the Oracle8i data dictionary, it automatically maintains information about spatial tables, columns, and partitions. The Spatial data dictionary is created during the installation process. All nonspatial attribute information is maintained in the Oracle8i data dictionary.

The Spatial data dictionary has public views that provide extensive information about spatial tables. This section contains descriptions of the views that are available.

The following views are publicly available:

• ALL_MD_COLUMNS

• ALL_MD_DIMENSIONS

• ALL_MD_EXCEPTIONS

• ALL_MD_LOADER_ERRORS

• ALL_MD_PARTITIONS

• ALL_MD_TABLES

• ALL_MD_TABLESPACES

• DBA_MD_COLUMNS

• DBA_MD_DIMENSIONS

• DBA_MD_EXCEPTIONS

• DBA_MD_LOADER_ERRORS

• DBA_MD_PARTITIONS

• DBA_MD_TABLES

• DBA_MD_TABLESPACES

• USER_MD_COLUMNS

• USER_MD_DIMENSIONS

• USER_MD_EXCEPTIONS

• USER_MD_LOADER_ERRORS

• USER_MD_PARTITIONS

• USER_MD_TABLES

• USER_MD_TABLESPACES

ALL_MD_COLUMNS

Returns a list of all columns that are part of spatial tables.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
COLUMN_NAME	name of the column
DATA_TYPE	data type of the column
DATA_LENGTH	length of the column in bytes
DATA_PRECISION	scale for NUMBER data type, binary precision for FLOAT data type, and NULL for all other data types
DATA_SCALE	digits to right of decimal point in an HHCODE column or a number
NDIM	number of dimensions in the HHCODE column (It is NULL for all other data types.)
MAX_LEVEL	maximum number of levels in the column
NULLABLE	indicates if column allows NULL values
PARTITION_KEY	indicates if column is the partition key column; only one is allowed per partitioned table
COLUMN_ID	sequence number of the column as created
DEFAULT_LENGTH	length of the default value for the column
NUM_DISTINCT	number of distinct values in each column of the table
LOW_VALUE	lowest value for tables with three or fewer rows (It is the second-lowest value in the column for tables with more than three rows.)
HIGH_VALUE	highest value for tables with three or fewer rows (It is the second-highest value in the column for tables with more than three rows.)

ALL_MD_DIMENSIONS

Returns a list of all dimensions that are part of HHCODE columns.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
COLUMN_NAME	name of the column
DIMENSION_NAME	name of the dimension
DIMENSION_NUMBER	dimension number
LOWER_BOUND	lower boundary of the dimension range
UPPER_BOUND	upper boundary of the dimension range
SCALE	scale of the dimension
RECURSION_LEVEL	number of levels encoded in the HHCODE column

ALL_MD_EXCEPTIONS

Contains information about spatial tables that should be removed (dropped) as a result of some failed operation, such as a failed load.

Column	Description
OWNER	owner of the object
NAME	object name
OPERATION	operation during which the failure occurred
CCHH	common code HHCODE

ALL_MD_LOADER_ERRORS

Contains the current status of a file that was loaded into a table using SD*Loader.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	spatial table name
FILENAME	SLF file name
ROWS_LOADED	number of rows loaded before failure

ALL_MD_PARTITIONS

Returns a list of all the partitioned tables that are part of a user-accessible spatial table.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
PARTITION_TABLE_NAME	name of the partitioned table
CLASS	class of partition: NODE or LEAF
COMMON_LEVEL	number of levels of resolution of the common HHCODE column for the partition
COMMON_HHCODE	common HHCODE substring for the partition
OFFLINE_STATUS	status of partition: ONLINE or OFFLINE
ARCHIVE_DATE	date of last archive

ALL_MD_TABLES

Returns a list of all the user-accessible spatial tables.

Column	Description
OWNER	owner of the table
MD_TABLE_NAME	name of the spatial table
CLASS	class of table: PARTITIONED or NON-PARTITIONED
PTAB_SEQ	number of last partitioned table created
HIGH_WATER_MARK	maximum number of rows that can be inserted into a partitioned table
OFFLINE_PATH	complete path name to directory where the table is archived
COUNT_MODE	count mode for estimating number of rows in a partition: ESTIMATE or EXACT

ALL_MD_TABLESPACES

Returns a list of all tablespaces used by spatial tables.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
TABLESPACE_NAME	name of tablespace
SEQUENCE	sequence number
STATUS	status of tablespace: ACTIVE or INACTIVE

DBA_MD_COLUMNS

Returns a list of all columns that are part of Spatial tables.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
COLUMN_NAME	name of the column
DATA_TYPE	data type of the column
DATA_LENGTH	length of the column in bytes
DATA_PRECISION	scale for NUMBER data type, binary precision for FLOAT data type, and NULL for all other data types
DATA_SCALE	digits to right of decimal point in an HHCODE column or a number
NDIM	number of dimensions in the HHCODE column (It is NULL for all other data types.)
MAX_LEVEL	maximum number of levels in the column
NULLABLE	indicates if column allows NULL values
PARTITION_KEY	indicates if column is the partition key column; only one is allowed per partitioned table
COLUMN_ID	sequence number of the column as created
DEFAULT_LENGTH	length of the default value for the column
NUM_DISTINCT	number of distinct values in each column of the table
LOW_VALUE	lowest value for tables with three or fewer rows (It is the second-lowest value in the column for tables with more than three rows.)
HIGH_VALUE	highest value for tables with three or fewer rows (It is the second-highest value in the column for tables with more than three rows.)

DBA_MD_DIMENSIONS

Returns a list of all dimensions that are a part of spatial tables.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
COLUMN_NAME	name of the column
DIMENSION_NAME	name of the dimension
DIMENSION_NUMBER	dimension number
LOWER_BOUND	lower boundary of the dimension range
UPPER_BOUND	upper boundary of the dimension range
SCALE	scale of the dimension
RECURSION_LEVEL	number of levels encoded in the HHCODE column

DBA_MD_EXCEPTIONS

Contains information about spatial tables that should be removed (dropped) as a result of some failed operation, such as a failed load.

Column	Description
OWNER	owner of the object
NAME	object name
OPERATION	operation during which the failure occurred
CCHH	common code HHCODE

DBA_MD_LOADER_ERRORS

Contains the current status of a file that was loaded into a table using SD*Loader.

Column	Description
OWNER	owner of the table where the error occurred
MD_TABLE_NAME	spatial table name
FILENAME	SLF file name
ROWS_LOADED	number of rows loaded before failure

DBA_MD_PARTITIONS

Returns a list of all the partitioned tables.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
PARTITION_TABLE_NAME	name of the partitioned table
CLASS	class of partition: NODE or LEAF
COMMON_LEVEL	number of levels of resolution of the common HHCODE column for the partition
COMMON_HHCODE	common HHCODE substring for the partition
OFFLINE_STATUS	status of partition: ONLINE or OFFLINE
ARCHIVE_DATE	date of last archive

DBA_MD_TABLES

Returns a list of all the spatial tables.

Column	Description
OWNER	owner of the table
MD_TABLE_NAME	name of the spatial table
CLASS	class of table: PARTITIONED or NON-PARTITIONED
PTAB_SEQ	number of last partitioned table created
HIGH_WATER_MARK	maximum number of rows that can be inserted into a partitioned table
OFFLINE_PATH	complete path name to directory where the table is archived
COUNT_MODE	count mode for estimating number of rows in a partition: ESTIMATE or EXACT

DBA_MD_TABLESPACES

Returns a list of all tablespaces used by spatial tables.

Column	Description
OWNER	owner of the object
MD_TABLE_NAME	name of the spatial table
TABLESPACE_NAME	name of tablespace
SEQUENCE	sequence number
STATUS	status of tablespace: ACTIVE or INACTIVE

USER_MD_COLUMNS

Returns a list of all the HHCODE columns that are part of tables owned by the user.

Column	Description
MD_TABLE_NAME	name of the spatial table
COLUMN_NAME	name of the spatial table
DATA_TYPE	data type of the column
DATA_LENGTH	length of the column in bytes
DATA_PRECISION	scale for NUMBER data type, binary precision for FLOAT data type, and NULL for all other data types
DATA_SCALE	digits to right of the decimal point in an HHCODE column or a number
NDIM	number of dimensions in the HHCODE column (It is NULL for all other data types.)
MAX_LEVEL	maximum number of levels in the column
NULLABLE	indicates if column allows NULL values
PARTITION_KEY	indicates if column is the partition key column; only one allowed per partitioned table
COLUMN_ID	sequence number of the column as created
DEFAULT_LENGTH	length of the default value for the column
NUM_DISTINCT	number of distinct values in each column of the table
LOW_VALUE	lowest value for tables with three or fewer rows (It is the second-lowest value in the column for tables with more than three rows.)
HIGH_VALUE	highest value for tables with three or fewer rows (It is the second-highest value in the column for tables with more than three rows.)

USER_MD_DIMENSIONS

Returns a list of all dimensions that are part of HHCODE columns owned by the user.

Column	Description
MD_TABLE_NAME	name of the spatial table
COLUMN_NAME	name of the column
DIMENSION_NAME	name of the dimension
DIMENSION_NUMBER	dimension number
LOWER_BOUND	lower boundary of dimension range
UPPER_BOUND	upper boundary of dimension range
SCALE	scale of the dimension
RECURSION_LEVEL	number of levels encoded in the HHCODE column

USER_MD_EXCEPTIONS

Contains information about spatial tables that should be removed (dropped) as a result of some failed operation, such as a failed load.

Column	Description
NAME	object name
OPERATION	operation during which the failure occurred
CCHH	common code HHCODE

USER_MD_LOADER_ERRORS

Contains the current status of a file that was loaded into a table using SD*Loader.

Column	Description
MD_TABLE_NAME	spatial table name
FILENAME	SLF file name
ROWS_LOADED	number of rows loaded before failure

USER_MD_PARTITIONS

Returns a list of all the partitioned tables that are part of spatial tables owned by the user.

Column	Description
MD_TABLE_NAME	name of the spatial table
PARTITION_TABLE_NAME	name of the partition
CLASS	class of partition: NODE or LEAF
COMMON_LEVEL	number of levels of resolution of the common HHCODE column for the partition
COMMON_HHCODE	common HHCODE substring for the partition
OFFLINE_STATUS	status of partition: ONLINE or OFFLINE
ARCHIVE_DATE	date of last archive

USER_MD_TABLES

Returns a list of all the spatial tables owned by the user.

Column	Description
MD_TABLE_NAME	name of the spatial table
CLASS	class of table: PARTITIONED or NON-PARTITIONED
PTAB_SEQ	number of last sequence created
HIGH_WATER_MARK	maximum number of rows that can be inserted into a partitioned table
OFFLINE_PATH	complete path name to directory where the table is archived
COUNT_MODE	count mode for estimating number of rows in a partition: ESTIMATE or EXACT

USER_MD_TABLESPACES

Returns a list of all tablespaces used by spatial tables.

Column	Description
MD_TABLE_NAME	name of the spatial table
TABLESPACE_NAME	name of tablespace
SEQUENCE	sequence number
STATUS	status of the tablespace: ACTIVE or INACTIVE

C.7 Messages and Codes

MDSQL-00001: partition is OFFLINE

Cause: An MDSQL operation was attempted on a partition that is OFFLINE.

Action: Restore the partition and try the operation again.

MDSQL-00002: PK is out of bounds

Cause: The partition key for the record being inserted belongs in another partition.

Action: Insert the record into the correct partition. The correct partition can be identified using the GET_PARTITION_NAME( ) function.

MDSQL-00003: updates that move the PK are not supported

Cause: The update of the partition key would result in the record belonging to another partition.

Action: Use the MD_DML.MOVE_RECORD( ) procedure to update the partition key and move the record to the correct partition.

---