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.
8 comments:
That has always been the case. Only data is stored in non system table spaces as far as I know.
Hi Mikael,
Yes I realize that now. However, it was fun to prove it by an example.
- Vikram
it's not true that packages are "always" stored in system ts.
Initially when you create a package it's been stored in user's default ts. If the user dont have default ts (in our case, default ts has been deleted), then these objects are moved to system ts, before dropping default ts.
Sundar K
Sr Apps DBA
it's not true that packages are "always" stored in system ts.
Initially when you create a package it's been stored in user's default ts. If the user dont have default ts (in our case, default ts has been deleted), then these objects are moved to system ts, before dropping default ts.
Sundar K
Sr Apps DBA
it's not true that packages are "always" stored in system ts.
Initially when you create a package it's been stored in user's default ts. If the user dont have default ts (in our case, default ts has been deleted), then these objects are moved to system ts, before dropping default ts.
Sundar K
Sr Apps DBA
it's not true that packages are "always" stored in system ts.
Initially when you create a package it's been stored in user's default ts. If the user dont have default ts (in our case, default ts has been deleted), then these objects are moved to system ts, before dropping default ts.
Sundar K
Sr Apps DBA
it's not true that packages are "always" stored in system ts.
Initially when you create a package it's been stored in user's default ts. If the user dont have default ts (in our case, default ts has been deleted), then these objects are moved to system ts, before dropping default ts.
Sundar K
Sr Apps DBA
In your example, you start your connection as "/ as sysdba" and you never switch to user scott => although code is indeed stored in the systemtablespace, The piece of code you wrote did even belong to the sys schema and not to scott
Post a Comment