The MDSYS.SDO_GEOR_ADMIN package contains subprograms (functions and procedures) for administrative operations related to GeoRaster. This chapter presents reference information, with one or more examples, for each subprogram.
This function returns an array of comma-delimited list of GeoRaster system data entries that are invalid. It checks for errors such as the following:
The RDT name is not unique.
The GeoRaster table does not exist.
The GeoRaster column does not exist.
The GeoRaster objects does not exist.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID).
If you call this function while connected as the MDSYS user, the entries in the ALL_SDO_GEOR_SYSDATA view instead of the USER_SDO_GEOR_METADATA view are checked.
The USER_SDO_GEOR_DATA and ALL_SDO_GEOR_SYSDATA views are described in Section 2.4.
The following example checks the USER_SDO_GEOR_SYSDATA view for invalid entries.
SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.checkSysdataEntries FROM DUAL); COLUMN_VALUE -------------------------------------------------------------------------------- The RDT name "RDT1" is not unique The GeoRaster object GEOR_TEST1.TABLE1.GEOR: RDT=RDT2 RID=3 is associated with a non-existing RDT table! The specification of GeoRaster column GEOR_TEST1.TABLE1.c1 is not correct. The GeoRaster object GEOR_TEST1.TABLE1.geor: RDT=dt3 RID=2 doesn't exist! The GeoRaster table GEOR_TEST1.t1 doesn't exist!
Checks if the specified raster data table (RDT) name is unique among RDT names in the database.
You can use this function to check, before you create an RDT, if the RDT name that you plan to use is unique.
This function returns the string TRUE if the name is unique and the string FALSE if the name is not unique.
This function returns an array of comma-delimited list of GeoRaster system data entries and GeoRaster columns and objects that are invalid. It can report errors such as the following:
System data entry error, the RDT name is not unique.
System data entry error, the RDT/RID pair is not unique.
System data entry error, the GeoRaster table does not exist.
System data entry error, the GeoRaster column does not exist.
System data entry error, the GeoRaster object does not exist.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID).
There is a non-registered pair of (GeoRaster column, GeoRaster object).
If you call this function while connected as the MDSYS user, the GeoRaster system data entries and GeoRaster data for the entire database are checked.
The following example checks the GeoRaster system data entries and GeoRaster data. It assumes that you are connected as the MDSYS user.
SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.isUpgradeNeeded FROM DUAL);
COLUMN_VALUE
--------------------------------------------------------------------------------
The following GeoRaster columns aren't registered:
SCHEMA:GEOR_TEST TABLE:TABLE1 COLUMN:GEOR
The following GeoRaster objects aren't registered:
SCHEMA:GEOR_TEST TABLE:TABLE1 COLUMN:GEOR RDT:RDT RID:3
SCHEMA:GEOR_TEST TABLE:TABLE1 COLUMN:GEOR RDT:RDT RID:4
This function returns an array of comma-delimited list of GeoRaster columns with their registration status. The list contains the following information:
Schema name (only if you are connected as the MDSYS user)
GeoRaster table name
GeoRaster column name
Status: registered (a DML trigger is created for the GeoRaster column) or unregistered (no DML trigger is created for the GeoRaster column)
If you call this function while connected as the MDSYS user, all GeoRaster columns defined in the database are listed.
The following example lists the GeoRaster columns defined in the current schema.
SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listGeoRasterColumns FROM DUAL);
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('TEST_TABLE1', 'GEOR', 'registered')
SDO_STRING2_ARRAY('TEST_TABLE2', 'GEOR', 'registered')
This function returns an array of comma-delimited list of GeoRaster objects with their registration status. The list contains the following information:
Schema name (only if you are connected as the MDSYS user)
GeoRaster table name
GeoRaster column name
RDT name
Raster ID
Status: registered (the GeoRaster object has been registered is the SYSDATA table) or unregistered (the GeoRaster object has not been registered is the SYSDATA table)
If you call this function while connected as the MDSYS user, all GeoRaster objects defined in the database are listed.
The following example lists the GeoRaster objects defined in the current schema.
SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listGeoRasterObjects FROM DUAL);
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('TEST_TABLE1', 'GEOR', 'RDT_REGULAR_01', '1', 'registered')
SDO_STRING2_ARRAY('TEST_TABLE2', 'GEOR', 'RDT_REGULAR_01', '2', 'registered')
This function returns an array of comma-delimited list of GeoRaster tables. The list contains the following information:
Schema name (only if you are connected as the MDSYS user)
GeoRaster table name
If you call this function while connected as the MDSYS user, all GeoRaster tables defined in the database are listed.
The following example lists the GeoRaster tables defined in the database. It assumes that you are connected as the MDSYS user.
SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listGeoRasterTables FROM DUAL);
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('GEOR_TEST', 'TEST_TABLE1')
SDO_STRING2_ARRAY('GEOR_TEST', 'TEST_TABLE2')
Checks the GeoRaster system data entries and GeoRaster data, and lists all dangling raster data.
Raster data table (RDT) rows might exist for nonexistent GeoRaster objects or GeoRaster objects that are not referred to in the SYSDATA table. The raster blocks associated with such rows are referred to dangling blocks. The dangling raster blocks cause wasted disk space in the RDT although otherwise they do not present a problem as long as the necessary primary key is defined on the RDT. To find these dangling blocks in the current schema or in all schemas, call the SDO_GEOR_ADMIN.listDanglingRasterData function.
Before you call this function, you should call SDO_GEOR_ADMIN.registerGeoRasterObjects to register all existing GeoRaster objects.
To remove the dangling raster block data from an RDT, delete the rows associated with the problems discovered by the SDO_GEOR_ADMIN.listDanglingRasterData function.
This function returns an array of comma-delimited list of dangling raster data. The list contains the following information:
Schema name (only if you are connected as the MDSYS user)
RDT name
Raster ID
If you call this function while connected as the MDSYS user, all dangling raster data in the database is listed.
This function returns an array of comma-delimited list of RDTs. The list contains the following information:
Schema name (only if you are connected as the MDSYS user)
RDT name
If you call this function while connected as the MDSYS user, all RDTs defined in the database are listed.
The following example lists the RDTs defined in the current schema.
SELECT * FROM THE (SELECT SDO_GEOR_ADMIN.listRDT FROM DUAL);
COLUMN_VALUE
---------------------------------------------------------------------------------
SDO_STRING2_ARRAY('RDT_REGULAR_01')
SDO_STRING2_ARRAY('RDT_REGULAR_02')
Lists the registered raster data tables (RDTs) defined in the current schema. An RDT is registered if at least one entry in the SYSDATA table refers to it.
This function returns an array of comma-delimited list of RDTs. The list contains the following information:
Schema name (only if you are connected as the MDSYS user)
RDT name
If you call this function while connected as the MDSYS user, all registered RDTs defined in the database are listed.
Lists the unregistered raster data tables (RDTs) defined in the current schema. An RDT is unregistered if no entries in the SYSDATA table refer to it.
This function returns an array of comma-delimited list of RDTs. The list contains the following information:
Schema name (only if you are connected as the MDSYS user)
RDT name
If you call this function while connected as the MDSYS user, all unregistered RDTs defined in the database are listed.
Checks the USER_SDO_GEOR_SYSDATA view for any invalid entries, and takes corrective action as appropriate.
This function performs the same checks as the SDO_GEOR_ADMIN.checkSysdataEntries function, and it takes the corrective action that is appropriate (if any). For each of the following errors, the function does the following:
The RDT name is not unique. If you are connected as a user other then MDSYS, no action is taken; if you are connected as user MDSYS, duplicate RDTs are renamed so that their names are unique.
The GeoRaster table does not exist. The entry is deleted.
The GeoRaster column does not exist. The entry is deleted.
The GeoRaster objects does not exist. The entry is deleted.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist. The entry is deleted.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID). The entry is deleted.
If you call this function while connected as the MDSYS user, the entries in the ALL_SDO_GEOR_SYSDATA view instead of the USER_SDO_GEOR_METADATA view are checked.
The USER_SDO_GEOR_DATA and ALL_SDO_GEOR_SYSDATA views are described in Section 2.4.
The following example checks the USER_SDO_GEOR_SYSDATA view for invalid entries, and performs corrective action as appropriate.
DECLARE
ret SDO_STRING2_ARRAY;
BEGIN
ret:=sdo_geor_admin.MAINTAINSYSDATAENTRIES;
for i in 1..ret.count loop
dbms_output.put_line(ret(i));
end loop;
END;
/
The RDT name GEOR_TEST1.RDT2 is renamed to GEOR_TEST1.RDT1!
The sysdata entry (SCHEMA=GEOR_TEST1 RDT=dt1 RID=1) is deleted!
PL/SQL procedure successfully completed.
You should not normally need to execute this procedure. You should execute it only if some error or other condition has resulted in GeoRaster columns without associated DML triggers.
If you execute this procedure while connected as the MDSYS user, DML triggers are created for all GeoRaster columns defined in all schemas.
If you execute this procedure while connected as the MDSYS user, all GeoRaster objects defined in all schemas are registered.
Checks the GeoRaster system data entries and GeoRaster data for the current schema, and performs any corrective action as appropriate.
This function performs the same checks as the SDO_GEOR_ADMIN.isUpgradeNeeded function, and it takes the corrective action that is appropriate (if any) for the following errors:
System data entry error, the RDT name is not unique.
System data entry error, the RDT/RID pair is not unique.
System data entry error, the GeoRaster table does not exist.
System data entry error, the GeoRaster column does not exist.
System data entry error, the GeoRaster object does not exist.
The GeoRaster object is non-empty or nonblank, but the RDT does not exist.
Duplicate GeoRaster objects exist (that is, one or more non-unique combinations of RDT and raster ID).
There is a non-registered pair of (GeoRaster column, GeoRaster object).
If you call this function while connected as the MDSYS user, the GeoRaster system data entries and GeoRaster data for the entire database are checked, and any appropriate corrective actions are taken.
The following example checks the GeoRaster system data entries and GeoRaster data for the current schema, and performs any corrective action as appropriate.
DECLARE
ret SDO_STRING2_ARRAY;
BEGIN
ret:=sdo_geor_admin.upgradeGeoraster;
for i in 1..ret.count loop
dbms_output.put_line(ret(i));
end loop;
END;
/