Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, December 8, 2008

In which tablespace are procedures and functions stored

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:

Mikael said...

That has always been the case. Only data is stored in non system table spaces as far as I know.

Vikram Das said...

Hi Mikael,

Yes I realize that now. However, it was fun to prove it by an example.

- Vikram

Sundar Kannan said...

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

Sundar Kannan said...

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

Sundar Kannan said...

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

Sundar Kannan said...

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

Sundar Kannan said...

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

Unknown said...

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