Home » RDBMS Server » Server Administration » oracle 9i on linux problems creating users and tables
oracle 9i on linux problems creating users and tables [message #54420] Sat, 16 November 2002 20:51 Go to next message
Maria
Messages: 57
Registered: August 1999
Member
Hi there

I have Oracle 9.0.1. running on linux and I can't even create a user or a table, I can't even alter a tablespace I keep getting this error:

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

SQL>

This error messages is for everything that I try to do, does anyone ever came across this one?

Thanks
Maria
Re: oracle 9i on linux problems creating users and tables [message #54434 is a reply to message #54420] Mon, 18 November 2002 06:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
do a clean shutdown.
login in as sys and isue the follwing
[code]alter system set "_system_trig_enabled"=false;
@ORACLE_HOMErdbmscatnools.sql -- this is script for label security
do a clean shutdown and try your create table again

Re: oracle 9i on linux problems creating users and tables [message #56290 is a reply to message #54434] Mon, 17 March 2003 07:12 Go to previous message
Daniel Berrange
Messages: 1
Registered: March 2003
Junior Member
I've had this exact same problem after applying the 9.2.0.3.0 patch set. Of course, since I didn't ask for label security in the first place, the catnools.sql doesn't exist anywhere in $ORACLE_HOME. Fortunately I managed to find a copy of it elsewhere, which I reproduce below for any one else whose database has been unwittingly messed by the upgrade.


Rem
Rem $Header: catnools.sql 02-may-2001.15:36:35 srtata Exp $
Rem
Rem catnools.sql
Rem
Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved.
Rem
Rem NAME
Rem catnools.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem This script drops the OLS product and all of its LBACSYS
Rem objects from a database instance. All OLS policies will
Rem also be dropped but user tables will not have their OLS
Rem policy columns automatically dropped.
Rem
Rem NOTES
Rem Must be run as SYSDBA.
Rem
Rem MODIFIED (MM/DD/YY)
Rem srtata 05/02/01 - Add error handling.

WHENEVER SQLERROR EXIT;

BEGIN

-- Check the user who is executing this script.
if sys_context('userenv','session_user') != 'SYS' then
RAISE_APPLICATION_ERROR(-20000,
'Execute the script as user SYS as SYSDBA');
end if;
END;
/

WHENEVER SQLERROR CONTINUE;

-- drop OLS database triggers.
DROP TRIGGER LBACSYS.lbac$after_drop;
DROP TRIGGER LBACSYS.lbac$startup;
DROP TRIGGER LBACSYS.lbac$logon;
DROP TRIGGER LBACSYS.lbac$after_create;
DROP TRIGGER LBACSYS.lbac$before_alter;

-- copy system.aud$ back to sys.aud$.
DROP SYNONYM aud$;
CREATE TABLE SYS.aud$ AS SELECT * FROM SYSTEM.aud$;
CREATE INDEX SYS.i_aud1 ON SYS.aud$(sessionid, ses$tid);
DROP table SYSTEM.aud$;

-- cleanup OLS by removing any roles, contexts,or synonyms
-- owned by LBACSYS.
DECLARE

CURSOR lbacroles IS
SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'LBACSYS'
AND granted_role like '%_DBA'
AND admin_option = 'YES';

CURSOR lbaccontexts IS
SELECT namespace
FROM dba_context
WHERE schema = 'LBACSYS';

CURSOR lbacsynonyms IS
SELECT synonym_name
FROM dba_synonyms
WHERE table_owner = 'LBACSYS';

rolename VARCHAR2(30);

BEGIN

-- drop roles
FOR r IN lbacroles LOOP
dbms_output.put_line('Dropping role ' || r.granted_role );

BEGIN
EXECUTE IMMEDIATE 'DROP ROLE ' || r.granted_role;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop role ' || r.granted_role);
END;

END LOOP;

-- drop contexts
FOR c IN lbaccontexts LOOP
dbms_output.put_line('Dropping context ' || c.namespace);

BEGIN
EXECUTE IMMEDIATE 'DROP CONTEXT ' || c.namespace;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop context ' || c.namespace);
END;

END LOOP;

-- drop synonyms
FOR s IN lbacsynonyms LOOP
dbms_output.put_line('Dropping public synonym ' || s.synonym_name);

BEGIN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || s.synonym_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to drop synonym ' || s.synonym_name);
END;

END LOOP;

END;
/

-- now remove lbacsys account and it's content
DROP USER LBACSYS CASCADE;
DELETE FROM exppkgact$ WHERE PACKAGE = 'LBAC_UTL';
COMMIT;

REM end of catnools.sql
Previous Topic: Control file record section
Next Topic: Errors during export
Goto Forum:
  


Current Time: Fri Sep 20 03:32:54 CDT 2024