ORA-08103: object no longer exists while revoking DBA privilege from user

while revoking “DBA” role from an Oracle users, i faced the following error:

SQL> revoke DBA from oracle_user;

ORA-08103: object no longer exists



you have 2 methods to resolve this issue:


  • create a new tablespace with new “data files”.
  • take a backup of the sql definition of the schema user.
  • use the data pump utility (expdp) to export the schema’s data.
  • drop the user schema.
  • drop the tablespace with the datafiles using the sql command:
  • re-create the tablespace with “NEW” data file(s) and and create the user schema again.
  • import the schema’s data again.



  • create a new tablespace with new data files.
  • alter user and grant the schema user quota on the new tablespace:
  • alter user emaployee quota unlimited on tablespace ts_employee_data_02;
  • use the “ALTER MOVE” command to move oracle tables and indexes from tablepsace 1 to tablespace 2 (NEW)
  • you may require rebuilding indexes.


Important Remark:

This is faced with legacy databases that have been upgraded over the years from 9i……to 11g due data files header issue.





TDE in Oracle 11g

What is TDE ?

TDE stands for “transparent data encryption”. TDE is part of Oracle Advanced Security Option for Enterprise Edition. Data at rest are encrypted and ONLY authorized users who have access control privilege will read de-crypted data. So, TDE protects your from direct access to the database host server or backup media that has copies of your data files.

TDE Master Key encryption can be managed through Oracle Wallet Manager.

Example of column data encryption:

The following table will be created with column “salary_information”

SQL> create table EMPLOYEE.EMPLOYEE_INFORMATION(first_name varchar2(20),last_name varchar2(30), EMPLOYEE_ID char(18), salary_information number(10,2) ENCRYPT using ‘AES256’ NO SALT);


For Tablespace Encryption:

  • You cannot encrypt an existing tablespace.
  • You can use” Data Pump” method OR “alter table move” to move data to newly encrypted tablespace.


Example of creating encrypted tablespace :

CREATE TABLESPACE tablespace_new

DATAFILE  ‘/app/oracle/oradata/tablespace2.ORA’  SIZE 200M


ora-28031 maximum of 148 enabled roles exceeded

if you face an error like this:



Description of the Problem: 

When a new session is initiated Oracle will attempt to enable all the default roles of the account if their number will be less than ( (value of the MAX_ENABLED_ROLES parameter) – 2 ). The default value of MAX_ENABLED_ROLES is 150. If the account is granted (directly or recursively) more than 148 roles then the session creation will fail with ORA-28031.

SQL> show parameter max_enabled_roles 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

max_enabled_roles                    integer     150


To Resolve this error:

You need to remove the role from the user as a “default” one.

So, the only way is to revoke then re-grant the role again without the “default option”. 





ora-13282 failure on initialization of coordinate transformation

before going to the solution part, execute the following verification queries to make sure your Oracle spatial component is valid:

select comp_name,version_status from dba_registry;

select * from dba_objects where status <> ‘VALID’ and owner=’MDSYS’;


Solution Steps:

you need to Correct the WKT strings as follows:

1. Connect as SYSTEM, SYS or MDSYS

2. execute the command:
alter session set nls_numeric_characters = ‘.,’;

3. Regenerate the WKTs for all EPSG coordinate systems using the following command:
execute sdo_cs.update_wkts_for_all_epsg_crs;

4. Now, try to execute the following query:
select SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,41015,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1,3,1,0),MDSYS.SDO_ORDINATE_ARRAY(813483.176346326,83853.5516374355,1,1.42608769437217E-8)),27572) from dual;

if it executes successfully then…..your problem has been resolved 🙂


Remark: please note that i didn’t face this error (ora-13282) with Oracle  version

ORA-28575: unable to open RPC connection to external procedure

i have faced the following error while configuring the external procedure for st_gemoetry in oracle database.

ORA-28575: unable to open RPC connection to external procedure

ORA-06512: at “SDE.ST_GEOMETRY_SHAPELIB_PKG”,line 1325

ORA-06512: at “SDE.ST_GEOM_UTIL”, line823


Before going to the solution of this error, please review the following crucial points:

  • Other problems such as file corruption could also cause the extproc program to not be executable. Perform the following steps to verify whether the extproc program is valid and executable: 
  • Log in to the system as the applicable user.
  • cd to the $ORACLE_HOME/bin directory.
  • Execute the following command at the prompt: 

$  ./extproc 


  • starting form Oracle 11g release, you don’t need to configure the listener for external procedure, this can be directly implemented through “extproc” file.



under $ORACLE_HOME/hs/admin directory edit the extproc.ora and

either use


(This is not advisable for security reasons)



and thats it you are FINISHED !!!!!!


However, make sure of the following:

when you perform the following query (access the database as “sde” account user).


it should point to the directory where your “.so” file is located, in our example its “$ORACLE_HOME/libst_shapelib.so”

after extensive troubleshooting, i found out that this will occur in one of two situations:

1. the libst_shapelib.so external procedure library is corrupted.


2. the libst_shapelib.so external procedure library is a 32 bit…….to check that use the Unix Command ( filelibst_shapelib.so) the output should give you 64bit

in my situation, the library was corrupted (for some reason)…..when i copied the the library form my local machine (C:\Program Files (x86)\ArcGIS\Desktop10.1\DatabaseSupportto the database server and used the external procedure method ‘ONLY’ it worked fine and i was able to execute the query: select * from gdb_items_vw;


reference links: