I was under the impression that the procedures, functions and packages which belong to a user are stored in the default tablespace allocated to that user. However it doesn't seem so. It seems the code is stored in SYSTEM tablespace. Here's a test I did to prove that the code is not stored in the user's default tablespace:
The obvious choice to do this is SCOTT schema. But SCOTT schema is not a part of ERP. Here's what you do:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
conn / as sysdba
@utlsampl.sql
This will create the scott schema. But scott's default tablespace is SYSTEM. We'll change that.
CREATE TABLESPACE SCOTTD DATAFILE '/stage11i/dbdata/data1/scottd1.dbf' SIZE 10M;
ALTER USER SCOTT DEFAULT TABLESPACE SCOTTD;
CREATE OR REPLACE PACKAGE PACK1 AS
PROCEDURE PROC1;
FUNCTION FUN1 RETURN VARCHAR2;
END PACK1;
/
CREATE OR REPLACE PACKAGE BODY PACK1 AS
PROCEDURE PROC1 IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi a message from procedure PROC1');
END PROC1;
FUNCTION FUN1 RETURN VARCHAR2 IS
BEGIN
RETURN ('Hello from function FUN1');
END FUN1;
END PACK1;
/
set serveroutput on
SQL> EXEC PACK1.PROC1
Hi a message from procedure PROC1
PL/SQL procedure successfully completed.
SQL> select pack1.fun1 from dual;
FUN1
--------------------------------------------------------------------------------
Hello from function FUN1
SQL>
CONN / AS SYSDBA
DROP TABLESPACE SCOTTD INCLUDING CONTENTS AND DATAFILES;
conn scott/tiger
SQL> EXEC PACK1.PROC1
Hi a message from procedure PROC1
PL/SQL procedure successfully completed.
SQL> select pack1.fun1 from dual;
FUN1
--------------------------------------------------------------------------------
Hello from function FUN1
SQL>
Even after dropping the default tablespace of scott, the package pack1 exists. So source code like is not stored in default tablespace of a schema. It is stored in SYSTEM tablespace.
Posts
