ora-04021 timeout occurred while waiting to lock object package

June 24, 2014 arcsdegeo2 Comments

Problem Description:

While trying to re-create an Oracle Package, the execution took long time and finally the following error was thrown:

ora-04021 timeout occurred while waiting to lock object

Resolution:

This means that the package is being locked by another user.

To figure out the session that is locking the user, use the following queries:

select * from v$access where OWNER=’schema_account’ and OBJECT=’database_object_name’;

//take the ‘SID’ from the previous query and search for more details about the session with v$session

select * from v$session where SID=XX;

//where XX is the number found in the v$access query

These sessions can be killed using the command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

Then try to re-create the package and boooooom……package re-created 😉

Cheers!

ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine ORA-20085: Insert Spatial Reference SRID 4326 does not match SDE.GDB_ITEMS.SHAPE registered Spatial Reference SRID 0 ORA-06512: in “SDE.ST_DOMAIN_METHODS”, line 2000

June 24, 2014 arcsdegeoLeave a comment

While trying to re-name a field in a layer in my ArcGIS 10.2 Enterprise Geodatabase hosted in Oracle Database, I faced the below error:

ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-20085: Insert Spatial Reference SRID 4326 does not match
SDE.GDB_ITEMS.SHAPE registered Spatial Reference SRID 0
ORA-06512: in “SDE.ST_DOMAIN_METHODS”, line 2000
ORA-06512: in “SDE.ST_DOMAIN_METHODS”, line 2372

This error is related to ESRI bug NIM099594

Resolution:

SQL> SELECT index_name FROM user_ind_columns WHERE table_name = ‘GDB_ITEMS’ and column_name = ‘SHAPE’;

The Previous query will list for you the index associated with GDB_ITEMS table.

Then use the “drop” command to drop the index

SQL> DROP INDEX A8709_IX1;

Index dropped

For More information about this bug, check the following link resources:

http://support.esri.com/en/knowledgebase/techarticles/detail/42335

http://support.esri.com/en/bugs/nimbus/TklNMDk5NTk0

ORA-04063: package body “SYS.DBMS_DATAPUMP” has errors ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_DATAPUMP” ORA-06512: at line 6

June 23, 2014 arcsdegeoLeave a comment

if you face an error like the following:

ORA-04063: package body “SYS.DBMS_DATAPUMP” has errors
ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_DATAPUMP”
ORA-06512: at line 6

Solution:

SQL> select comp_id, comp_name, version, status from dba_registry;

if, there are invalid components then use utilrp to recompile all objects within your database:

SQL> @?/rdbms/admin/utlrp.sql

//——— another way is to manually check what is the problem:

SQL> alter package SYS.DBMS_DATAPUMP compile body;

SQL> show errors

** if the previous command doesn’t list the errors you can use the following query:

SQL> select * from dba_errors where OWNER=’SYS’ and NAME=’DBMS_DATAPUMP’;

Hope this helps……

Cheers !

Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine ORA-01031: insufficient privileges ORA-06512: at “SDE.SPX_UTIL”,line 847 ORA-06512: at “SDE.SPX_UTIL”,line 891

June 22, 2014 arcsdegeoLeave a comment

after converting my Geo-Database from Long Raw (SDEBINARY) to st_geometry, i found out that i can only see the feautre classes (layers) through schema accounts ONLY in ArcCatalog !!!

when i try to view the data with “application-user” accounts, i face the below error:

Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine
ORA-01031: insufficient privileges
ORA-06512: at “SDE.SPX_UTIL”,line 847
ORA-06512: at “SDE.SPX_UTIL”,line 891
ORA-06512: at “SDE.ST_DOMAIN_METHODS”, line 105

**** Solution:

since we follow strict security implimentation on the Oracle Databases, we have revoked many ‘PUBLIC’ role permissions in ArcSDE Geodatabase Systems, and it seems these permissions are required.

so you need to make sure that the following permissions listed are “granted“:

grant UPDATE on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant SELECT on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant INSERT on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant DELETE on sde.ALL_ST_GEOMETRY_COLUMNS_V to app_user;
grant EXECUTE on sde.ARCHIVE_UTIL to app_user;
grant EXECUTE on sde.BLOB_ARRAY_TAB to app_user;
grant EXECUTE on sde.BND_ROWID_TAB to app_user;
grant SELECT on sde.COLUMN_REGISTRY to app_user;
grant SELECT on sde.CONNECTION_ID_GENERATOR to app_user;
grant SELECT on sde.DBTUNE to app_user;
grant EXECUTE on sde.DBTUNE_UTIL to app_user;
grant EXECUTE on sde.FLT_ARRAY_TAB to app_user;
grant INSERT on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant SELECT on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant UPDATE on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant DELETE on sde.GDB_ITEMRELATIONSHIPS to app_user;
grant INSERT on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant SELECT on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant UPDATE on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant DELETE on sde.GDB_ITEMRELATIONSHIPS_VW to app_user;
grant UPDATE on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant SELECT on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant INSERT on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant DELETE on sde.GDB_ITEMRELATIONSHIPTYPES to app_user;
grant UPDATE on sde.GDB_ITEMS to app_user;
grant SELECT on sde.GDB_ITEMS to app_user;
grant INSERT on sde.GDB_ITEMS to app_user;
grant DELETE on sde.GDB_ITEMS to app_user;
grant UPDATE on sde.GDB_ITEMS_VW to app_user;
grant DELETE on sde.GDB_ITEMS_VW to app_user;
grant INSERT on sde.GDB_ITEMS_VW to app_user;
grant SELECT on sde.GDB_ITEMS_VW to app_user;
grant SELECT on sde.GDB_ITEMTYPES to app_user;
grant UPDATE on sde.GDB_ITEMTYPES to app_user;
grant INSERT on sde.GDB_ITEMTYPES to app_user;
grant DELETE on sde.GDB_ITEMTYPES to app_user;
grant SELECT on sde.GDB_REPLICALOG to app_user;
grant UPDATE on sde.GDB_REPLICALOG to app_user;
grant INSERT on sde.GDB_REPLICALOG to app_user;
grant DELETE on sde.GDB_REPLICALOG to app_user;
grant UPDATE on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant SELECT on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant DELETE on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant INSERT on sde.GDB_TABLES_LAST_MODIFIED to app_user;
grant EXECUTE on sde.GDB_UTIL to app_user;
grant SELECT on sde.GEOMETRY_COLUMNS to app_user;
grant UPDATE on sde.INSTANCES to app_user;
grant SELECT on sde.INSTANCES to app_user;
grant INSERT on sde.INSTANCES to app_user;
grant DELETE on sde.INSTANCES to app_user;
grant EXECUTE on sde.INSTANCES_UTIL to app_user;
grant SELECT on sde.INSTANCE_ID_GENERATOR to app_user;
grant EXECUTE on sde.INT_ARRAY_TAB to app_user;
grant EXECUTE on sde.KEYSET_UTIL to app_user;
grant SELECT on sde.LAYERS to app_user;
grant EXECUTE on sde.LAYERS_UTIL to app_user;
grant SELECT on sde.LAYER_ID_GENERATOR to app_user;
grant SELECT on sde.LAYER_LOCKS to app_user;
grant EXECUTE on sde.LAYER_STATS_UTIL to app_user;
grant SELECT on sde.LINEAGES_MODIFIED to app_user;
grant SELECT on sde.LOCATORS to app_user;
grant SELECT on sde.LOCATOR_ID_GENERATOR to app_user;
grant EXECUTE on sde.LOCATOR_UTIL to app_user;
grant EXECUTE on sde.LOCK_UTIL to app_user;
grant EXECUTE on sde.LOGFILE_UTIL to app_user;
grant SELECT on sde.METADATA to app_user;
grant SELECT on sde.METADATA_ID_GENERATOR to app_user;
grant EXECUTE on sde.METADATA_UTIL to app_user;
grant SELECT on sde.MVTABLES_MODIFIED to app_user;
grant SELECT on sde.OBJECT_LOCKS to app_user;
grant EXECUTE on sde.PINFO_UTIL to app_user;
grant SELECT on sde.PROCESS_INFORMATION to app_user;
grant SELECT on sde.R2 to app_user;
grant SELECT on sde.R3 to app_user;
grant SELECT on sde.R4 to app_user;
grant SELECT on sde.R5 to app_user;
grant SELECT on sde.R6 to app_user;
grant EXECUTE on sde.RASTERCOLUMNS_UTIL to app_user;
grant SELECT on sde.RASTERCOLUMN_ID_GENERATOR to app_user;
grant SELECT on sde.RASTER_COLUMNS to app_user;
grant EXECUTE on sde.REGISTRY_UTIL to app_user;
grant EXECUTE on sde.SDENUMTAB to app_user;
grant EXECUTE on sde.SDEXMLTOTEXT to app_user;
grant SELECT on sde.SDE_ARCHIVES to app_user;
grant SELECT on sde.SDE_LAYER_STATS to app_user;
grant SELECT on sde.SDE_LOGFILE_POOL to app_user;
grant UPDATE on sde.SDE_TABLES_MODIFIED to app_user;
grant SELECT on sde.SDE_TABLES_MODIFIED to app_user;
grant EXECUTE on sde.SDE_UTIL to app_user;
grant SELECT on sde.SDE_XML_COLUMNS to app_user;
grant UPDATE on sde.SDE_XML_DOC1 to app_user;
grant SELECT on sde.SDE_XML_DOC1 to app_user;
grant DELETE on sde.SDE_XML_DOC1 to app_user;
grant INSERT on sde.SDE_XML_DOC1 to app_user;
grant UPDATE on sde.SDE_XML_DOC2 to app_user;
grant SELECT on sde.SDE_XML_DOC2 to app_user;
grant INSERT on sde.SDE_XML_DOC2 to app_user;
grant DELETE on sde.SDE_XML_DOC2 to app_user;
grant UPDATE on sde.SDE_XML_DOC3 to app_user;
grant INSERT on sde.SDE_XML_DOC3 to app_user;
grant SELECT on sde.SDE_XML_DOC3 to app_user;
grant DELETE on sde.SDE_XML_DOC3 to app_user;
grant SELECT on sde.SDE_XML_DOC4 to app_user;
grant UPDATE on sde.SDE_XML_DOC4 to app_user;
grant INSERT on sde.SDE_XML_DOC4 to app_user;
grant DELETE on sde.SDE_XML_DOC4 to app_user;
grant SELECT on sde.SDE_XML_INDEXES to app_user;
grant SELECT on sde.SDE_XML_INDEX_TAGS to app_user;
grant EXECUTE on sde.SDO_UTIL to app_user;
grant SELECT on sde.SERVER_CONFIG to app_user;
grant SELECT on sde.SPATIAL_REFERENCES to app_user;
grant EXECUTE on sde.SPX_UTIL to app_user;
grant EXECUTE on sde.SP_GRID_INFO to app_user;
grant EXECUTE on sde.SREF_UTIL to app_user;
grant SELECT on sde.STATES to app_user;
grant SELECT on sde.STATE_LINEAGES to app_user;
grant SELECT on sde.STATE_LOCKS to app_user;
grant EXECUTE on sde.ST_AGGR_CONVEXHULL to app_user;
grant EXECUTE on sde.ST_AGGR_INTERSECTION to app_user;
grant EXECUTE on sde.ST_AGGR_UNION to app_user;
grant EXECUTE on sde.ST_AREA to app_user;
grant EXECUTE on sde.ST_ASBINARY to app_user;
grant EXECUTE on sde.ST_ASTEXT to app_user;
grant EXECUTE on sde.ST_BOUNDARY to app_user;
grant EXECUTE on sde.ST_BUFFER to app_user;
grant EXECUTE on sde.ST_BUFFER_INTERSECTS to app_user;
grant EXECUTE on sde.ST_CENTROID to app_user;
grant EXECUTE on sde.ST_CONTAINS to app_user;
grant EXECUTE on sde.ST_CONVEXHULL to app_user;
grant EXECUTE on sde.ST_COORDDIM to app_user;
grant SELECT on sde.ST_COORDINATE_SYSTEMS to app_user;
grant EXECUTE on sde.ST_CREF_UTIL to app_user;
grant EXECUTE on sde.ST_CROSSES to app_user;
grant EXECUTE on sde.ST_CURVE to app_user;
grant EXECUTE on sde.ST_DIFFERENCE to app_user;
grant EXECUTE on sde.ST_DIMENSION to app_user;
grant EXECUTE on sde.ST_DISJOINT to app_user;
grant EXECUTE on sde.ST_DISTANCE to app_user;
grant EXECUTE on sde.ST_DOMAIN_METHODS to app_user;
grant EXECUTE on sde.ST_DOMAIN_OPERATORS to app_user;
grant EXECUTE on sde.ST_DOMAIN_STATS to app_user;
grant EXECUTE on sde.ST_ENDPOINT to app_user;
grant EXECUTE on sde.ST_ENTITY to app_user;
grant EXECUTE on sde.ST_ENVELOPE to app_user;
grant EXECUTE on sde.ST_ENVINTERSECTS to app_user;
grant EXECUTE on sde.ST_EQUALS to app_user;
grant EXECUTE on sde.ST_EXTERIORRING to app_user;
grant DEBUG on sde.ST_FUNCS_ARRAY to app_user;
grant EXECUTE on sde.ST_FUNCS_ARRAY to app_user;
grant EXECUTE on sde.ST_GEOMCOLLECTION to app_user;
grant EXECUTE on sde.ST_GEOMETRY to app_user;
grant EXECUTE on sde.ST_GEOMETRYN to app_user;
grant EXECUTE on sde.ST_GEOMETRYTYPE to app_user;
grant SELECT on sde.ST_GEOMETRY_COLUMNS to app_user;
grant UPDATE on sde.ST_GEOMETRY_INDEX to app_user;
grant SELECT on sde.ST_GEOMETRY_INDEX to app_user;
grant INSERT on sde.ST_GEOMETRY_INDEX to app_user;
grant DELETE on sde.ST_GEOMETRY_INDEX to app_user;
grant EXECUTE on sde.ST_GEOMETRY_OPERATORS to app_user;
grant EXECUTE on sde.ST_GEOMETRY_SHAPELIB_PKG to app_user;
grant EXECUTE on sde.ST_GEOM fromTEXT to app_user;
grant EXECUTE on sde.ST_GEOM fromWKB to app_user;
grant EXECUTE on sde.ST_GEOM_COLS_UTIL to app_user;
grant EXECUTE on sde.ST_GEOM_UTIL to app_user;
grant EXECUTE on sde.ST_INTERIORRINGN to app_user;
grant EXECUTE on sde.ST_INTERSECTION to app_user;
grant EXECUTE on sde.ST_INTERSECTS to app_user;
grant EXECUTE on sde.ST_IS3D to app_user;
grant EXECUTE on sde.ST_ISCLOSED to app_user;
grant EXECUTE on sde.ST_ISEMPTY to app_user;
grant EXECUTE on sde.ST_ISMEASURED to app_user;
grant EXECUTE on sde.ST_ISRING to app_user;
grant EXECUTE on sde.ST_ISSIMPLE to app_user;
grant EXECUTE on sde.ST_LENGTH to app_user;
grant EXECUTE on sde.ST_LINE fromTEXT to app_user;
grant EXECUTE on sde.ST_LINE fromWKB to app_user;
grant EXECUTE on sde.ST_LINESTRING to app_user;
grant EXECUTE on sde.ST_M to app_user;
grant EXECUTE on sde.ST_MAXM to app_user;
grant EXECUTE on sde.ST_MAXX to app_user;
grant EXECUTE on sde.ST_MAXY to app_user;
grant EXECUTE on sde.ST_MAXZ to app_user;
grant EXECUTE on sde.ST_MINM to app_user;
grant EXECUTE on sde.ST_MINX to app_user;
grant EXECUTE on sde.ST_MINY to app_user;
grant EXECUTE on sde.ST_MINZ to app_user;
grant EXECUTE on sde.ST_MLINE fromTEXT to app_user;
grant EXECUTE on sde.ST_MLINE fromWKB to app_user;
grant EXECUTE on sde.ST_MPOINT fromTEXT to app_user;
grant EXECUTE on sde.ST_MPOINT fromWKB to app_user;
grant EXECUTE on sde.ST_MPOLY fromTEXT to app_user;
grant EXECUTE on sde.ST_MPOLY fromWKB to app_user;
grant EXECUTE on sde.ST_MULTICURVE to app_user;
grant EXECUTE on sde.ST_MULTILINESTRING to app_user;
grant EXECUTE on sde.ST_MULTIPOINT to app_user;
grant EXECUTE on sde.ST_MULTIPOLYGON to app_user;
grant EXECUTE on sde.ST_MULTISURFACE to app_user;
grant EXECUTE on sde.ST_NUMGEOMETRIES to app_user;
grant EXECUTE on sde.ST_NUMINTERIORRING to app_user;
grant EXECUTE on sde.ST_NUMPOINTS to app_user;
grant EXECUTE on sde.ST_ORDERINGEQUALS to app_user;
grant EXECUTE on sde.ST_OVERLAPS to app_user;
grant UPDATE on sde.ST_PARTITION_INDEX to app_user;
grant SELECT on sde.ST_PARTITION_INDEX to app_user;
grant DELETE on sde.ST_PARTITION_INDEX to app_user;
grant INSERT on sde.ST_PARTITION_INDEX to app_user;
grant EXECUTE on sde.ST_POINT to app_user;
grant EXECUTE on sde.ST_POINT fromTEXT to app_user;
grant EXECUTE on sde.ST_POINT fromWKB to app_user;
grant EXECUTE on sde.ST_POINTN to app_user;
grant EXECUTE on sde.ST_POINTONSURFACE to app_user;
grant EXECUTE on sde.ST_POLY fromTEXT to app_user;
grant EXECUTE on sde.ST_POLY fromWKB to app_user;
grant EXECUTE on sde.ST_POLYGON to app_user;
grant EXECUTE on sde.ST_RELATE to app_user;
grant EXECUTE on sde.ST_RELATION_OPERATORS to app_user;
grant EXECUTE on sde.ST_SPATIAL_INDEX to app_user;
grant SELECT on sde.ST_SPATIAL_REFERENCES to app_user;
grant EXECUTE on sde.ST_SPREF_UTIL to app_user;
grant EXECUTE on sde.ST_SRID to app_user;
grant EXECUTE on sde.ST_STARTPOINT to app_user;
grant EXECUTE on sde.ST_SURFACE to app_user;
grant EXECUTE on sde.ST_SYMMETRICDIFF to app_user;
grant EXECUTE on sde.ST_TOUCHES to app_user;
grant EXECUTE on sde.ST_TRANSFORM to app_user;
grant EXECUTE on sde.ST_TYPE_EXPORT to app_user;
grant EXECUTE on sde.ST_TYPE_USER to app_user;
grant EXECUTE on sde.ST_TYPE_UTIL to app_user;
grant EXECUTE on sde.ST_UNION to app_user;
grant EXECUTE on sde.ST_VERIFY to app_user;
grant EXECUTE on sde.ST_WITHIN to app_user;
grant EXECUTE on sde.ST_X to app_user;
grant EXECUTE on sde.ST_Y to app_user;
grant EXECUTE on sde.ST_Z to app_user;
grant EXECUTE on sde.SVR_CONFIG_UTIL to app_user;
grant SELECT on sde.TABLE_ID_GENERATOR to app_user;
grant SELECT on sde.TABLE_LOCKS to app_user;
grant SELECT on sde.TABLE_REGISTRY to app_user;
grant UPDATE on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant SELECT on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant INSERT on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant DELETE on sde.USER_ST_GEOMETRY_COLUMNS_V to app_user;
grant UPDATE on sde.USER_ST_GEOM_INDEX_V to app_user;
grant SELECT on sde.USER_ST_GEOM_INDEX_V to app_user;
grant INSERT on sde.USER_ST_GEOM_INDEX_V to app_user;
grant DELETE on sde.USER_ST_GEOM_INDEX_V to app_user;
grant SELECT on sde.VERSION to app_user;
grant SELECT on sde.VERSIONS to app_user;
grant SELECT on sde.VERSION_ID_GENERATOR to app_user;
grant EXECUTE on sde.VERSION_USER_DDL to app_user;
grant EXECUTE on sde.VERSION_UTIL to app_user;
grant SELECT on sde.XML_COLUMN_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC1_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC2_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC3_ID_GENERATOR to app_user;
grant SELECT on sde.XML_DOC4_ID_GENERATOR to app_user;
grant SELECT on sde.XML_INDEX_ID_GENERATOR to app_user;
grant SELECT on sde.XML_TAG_ID_GENERATOR to app_user;
grant EXECUTE on sde.XML_UTIL to app_user;

Oracle 12c In-Memory

June 13, 2014 arcsdegeoLeave a comment

On June 10 2014 Larry Ellison CEO of Oracle Announced the the new 12c “in-memory” feature.

The following is highlights summary:

The 12c feature will be available for the public in July 2014.
12c In-Memory can be installed and will be transparent to application (no changes required).
One of the primary goals for Oracle with in-memory database is to increase the performance for both Queries and Transactions.
Both Row and Column formats are used in 12c Database (Column is for Analytics).
Indexes being removed will speed up OLTP while using mixed column formats in 12c in-memory.
In-memory option can be used in EXADATA with Real-Application Clusters (multiple nodes).
Only “Active data” are being resided in memory (hosted on Flash or DRAM) and cold data will be residing on Disk. This approach is better in efficiency and economic (since most data will be residing on low cost disks).
High Availability:Backup/Recoverability/Manageability remains the same no changes is required.

Fault Tolerance:

*Similar to storage mirroring

* Duplicate in-memory columns on another node, this will lead to downtime elimination bys using the other duplicate after failure.

Youtube Link:

http://www.youtube.com/watch?v=dZO1CD8VgY0

Oracle in-Memory Vs. SAP HANA:

http://www.oracle.com/technetwork/database/options/dbim-vs-sap-hana-2215625.pdf?ssSourceSiteId=ocomen

This feature looks promising 😉

Converting Long Raw to BLOB in Oracle

June 12, 2014 arcsdegeoLeave a comment

Per Oracle Documentation “Oracle strongly recommends that you convert LONGRAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns “Reference: http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements001.htm#SQLRF50993

So if you have a legacy database that has data stored in LONG RAW you need to move it to BLOB or any other format still supported by Oracle.

In this document I am exploring the conversion from LONG RAW to BLOB and the different techniques for doing it.

To Find all schema’s that have tables with “LONG RAW” column, use the following query:

select * from DBA_TAB_COLUMNS where DATA_TYPE=’LONG RAW’;

selectdistinct(OWNER)from DBA_TAB_COLUMNS where DATA_TYPE=’LONG RAW’;

For illustration, let us first create a simple table with LONG RAW column it.

In this example, assume the schema name is “AIS” and the table name is “t1”.

CREATETABLE AIS.t1(idNUMBER, doc LONGRAW);

//after creation of the empty table you need to insert records

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Test to go into LONG RAW column’));

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Hello Dear’));

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Hello Dear2’));

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Hello Ya M3alem’));

Methodology I : ALTER STATEMENT

This is before conversion:

After executing the sql command:

ALTERTABLE AIS.t1 MODIFY( doc BLOB);

Methodology II: TO_LOB Operator

Since the table with LONG RAW column exists, you need to create another table with the “blob” column data type, and insert the records from the original table.

CREATETABLE AIS.t2 (idNUMBER, doc2 BLOB);

INSERTINTO AISDBA.t2 SELECTid,TO_LOB(doc)FROM AISDBA.t1;

The Records were inserted to the second table successfully!!

After performing the insertion you need to “COMMIT” your transaction.

Then, you need to create a public synonym for the new table so applications can refrence this new table with the “BLOB” data type.

CREATE SYNONYM t2 FOR t1;

Methodology III: Data Pump

You can export the tables where there it has “LONG RAW” then drop the original ones in the database and re-create them with “BLOB” column data type. And then, try to re-import using impdp command.

	Ali Ahmed on Execute privileges on required…
	Oracle datapump erro… on How To Apply Data Pump Recomme…
	Troubleshooting SYSA… on Oracle SYSAUX size is exponent…
	Juan Aguilera Abarzu… on ORA-31633: unable to create ma…
	Beau S on Oracle PSU July 2018 error…

Geodata Master

Oracle Database Technical Articles & GeoSpatial Data Technology

Month: June 2014