Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Tuesday, 5 May 2009

Temp Tablespace Usage

A useful script to check on users/processes using up temp tablespace.
SELECT distinct s.username
     , s.sid
     , s.serial#
     , s.osuser
     , u.tablespace
     , u.contents
     , u.segtype
     , u.extents
     , u.blocks
  FROM v$session s
     , v$sort_usage u
 WHERE s.saddr=u.session_addr
order by s.username, s.osuser;

Note: blocks * size of blocks = mbytes

Create User Scripts

There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which can then be executed on the target database.

I have in this example assumed that the tablespaces etc in the source and target database are identical in name and size. I have also chosen to set all the user roles (by default) as Default.

select 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''' DEFAULT TABLESPACE ' || default_tablespace || ' TEMPORARY TABLESPACE ' || temporary_tablespace || ' PROFILE ' || profile || ' ACCOUNT UNLOCK;' from dba_users where username in (&USERS);

select 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') from dba_role_privs where grantee IN (&USERS);

select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';') from dba_tab_privs where grantee IN (&USERS);

select 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED ON ' || TABLESPACE_NAME || ';', max_bytes/1024 || 'K ON ' || TABLESPACE_NAME || ';') from dba_ts_quotas where username in (&USERS);

Monday, 4 May 2009

DAD Config - Note To Self

In Oracle 10, the modplsql Database Access Descriptors may be set in the following file:

$ORACLE_HOME/Apache/modplsql/conf/dads.conf

<Location /pls/dad name>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseConnectString server:1521-port:dbname.server ServiceNameFormat
PlsqlAuthenticationMode Basic
PlsqlDefaultPage homepage
</Location>

Friday, 1 May 2009

Report Manager - Note to Self

Data source for Report Manager Administration page is PS_CDM_LIST_VW.

Data source for Report Manager List Page is PSRF_PGLET_VW. This requires EIPs to be working.

Related: PSRF_REPORT_FOLDERS channel/queue. PSRF_REPORT_xxxx messages.