TIP 85 # : ORA-4030 error in well tunned instance/well tunned SQL

A couple days ago, I was asked for a tuning exercise for a client. The instance and SQL statement were tuned very well, however a SQL statement was failing at runtime with ORA-4030.
PGA setting and maximum pga setting on instance (_pga_max_size) were tuned well, after investigation, I found that it is ulimit setting for Oracle OS user which prevents database session to get enough memory (PGA) to execute the query.
In my situation, ulimit was set to the followings :

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

---- Change data to unlimited

ulimit -d unlimited
ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

After restarting the listener to create a new session with the new ulimit settings, the issue was fixed.


TIP 84# : Create private DB link for a user without knowing password

Have you ever faced with a situation that you were asked to create a private database link or create a materialized view or submit a job as a user which you do not know its password?
As a DBA, you have the following options :

- Ask the password. (Usually it is not desired)
- Change password temporarily (This could break the application)
- Use dbms_sys_sql

With dbms_sys_sql, you are able to parse and execute SQL as other users.
Here is example on how to create private database link in scott user.



declare
uid number;
sqltext varchar2(1000) := 'create database link test_link connect to target_user?? identified by target_user_password?? using ''target_DB_TNS??''';
myint integer;
begin
select user_id into uid from all_users where username like 'SCOTT';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;


select owner,db_link from dba_db_links where db_link like 'TEST_LINK%';

OWNER DB_LINK
------------------------------ ----------------------------------------
SCOTT TEST_LINK.WORLD