Oracle Point, Oracle Life.

The Program Global Area (PGA) is a memory region that contains data and control information for a single process (server or background).

The UGA, or User Global Area, is allocated in the PGA for each session connected to Oracle in a dedicated server environment.

(refer to http://www.idevelopment.info/data/Oracle/DBA_tips/Tuning/TUNING_16.shtml#PGA%20Memory)

The following script can be used to display both the PGA and UGA memory requirements for all user sessions, including background processes. 

(script of “display both the PGA and UGA memory requirements for all user sessions including background processes”)

Also, run the following script to check ratio of memory sort to disk sort.

(Script of “monitor sort ratio of memory to disk”)

Popularity: 8% [?]

One of our developers reported that she experienced the following errors while she was running a simple update statement to update an oracle table.

ORA-12096: error in materialized view log on “SATURN”.”SSBSECT”
ORA-01536: space quota exceeded for tablespace ‘DATADDS’

The update statement issued is as simple as followings.

update ssbsect set ssbsect_ssts_code=’A’
where ssbsect_user_id=’WSTUMGR’;

In order to figure out this problem, the following steps were conducted in specific order.

Step 1: check if the user by which that update statement run has Unlimited Tablespace privilege

Answer: yes, it has and also tablespace of target table has considerable free space

Step 2: find any dependent objects over target table.

select NAME from dba_dependencies where REFERENCED_NAME=’SSBSECT’

Answer: yes, there are some dependent objects.

Step 3: check the owner of those dependent objects

select OWNER, object_name, object_type from dba_objects where OBJECT_NAME in (select NAME from dba_dependencies where REFERENCED_NAME=’SSBSECT’);

Step 4: check if above owners have Unlimited Tablespace privilege on target tablespace

Answer: All above owners have Unlimited Tablespace privilege except for PUBLIC

Step 5: Grant Unlimited Tablespace privilege to PUBLIC

Grant unlimited tablespace to public;

The problem is solved. (Once done, I revoked Unlimited Tablespace privilege from Public and re-ran the update statement, it worked very well without error. It’s clear that  the quota has been reset.)

Note: What’s Public? A user, a role, a privilege? Refer to Oracle Metalink Note:234551.1

Further discussion: PUBLIC acts as a default role granted to every user in an Oracle database. Any authenticated database user can exercise privileges that are granted to PUBLIC. Therefore, granting unlimited tablespace privilege to public is kind of risky. (Reversely, revoking Unlimited Tablespace is also risky somehow.) The above granting is conducted based on the following two facts.

1. It’s done on development system.

2. We have tough space resource and are unable to extend target tablespace considerably.

Popularity: 7% [?]