DBA Tips

Any usergroup problme:
/etc/oraInst.loc

Inital parameter

Location of spfile
$ORACLE_BASE/admin/XXX/bdump/alert_XXX.log
$ORACLE_HOME/dbs/spfileXXX.ora

Set the value of parameter
show parameter session_cached_cursors;
alter system set session_cached_cursors = 200 scope = spfile;
alter system set session_cached_cursors = 200 scope = both;
alter session set session_cached_cursors = 200;

Oracle Login

$ sqlplus /nolog
SQL> conn sys/oracle as sysdba

Change IP on Oracle DB10gr2

DB:
modified file /etc/hosts
modified file /etc/sysconfig/network
cmd: hostname XXX
EM:
cmd: emca -config dbcontrol db
modified file $ORACLE_HOME/HOSTNAME_SID/sysman/config/emoms.properties

Export Submit Failed

Question:
Export Submit Failed
ORA-20204: User does not exist: FIPERDBA ORA-06512: at "SYSMAN. MGMT_USER", line 122 ORA-06512: at "SYSMAN. MGMT_JOBS", line 139 ORA-06512: at "SYSMAN. MGMT_JOBS", line 78 ORA-06512: at line 1
Answer:
For the particular case:

In the Enterprise Manager Console, add the user who will run the Export/Import
DataPump job as an Administrator who can login to Enterprise Manager to perform
management tasks like set Blackouts, email notification schedules.

- login as user SYSTEM (or user SYS) to the 'Enterprise Manager 10g
Database Control'
- At the top right, click on the link 'Setup'
- On the page 'Administrators', click on the button 'Create'
- On the page 'Create Administrator: Properties', add the user who will run
the Export/Import DataPump job
- Click on the button: 'Finish'
- On the page 'Create Administrator: Review', click on the button: 'Finish'
- On the page 'Administrators', confirm that the user has been added.
- At the top right, click on the link 'Logout'

Now login as the user who will run the Export/Import DataPump job (SCOTT),
and re-create the DataPump job.

Missing dbms_shared_pool package

check if the object DBMS_SHARED_POOL actually exists on your database or if the
problem is that the package is actually missing.

To do this log in as the SYS user and issue the command:

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS, OWNER
     FROM ALL_OBJECTS
     WHERE OBJECT_NAME='DBMS_SHARED_POOL';

This should return the following:

OBJECT_NAME                    OBJECT_TYPE        STATUS  OWNER
------------------------------ ------------------ ------- --------
DBMS_SHARED_POOL               PACKAGE            VALID   SYS
DBMS_SHARED_POOL               PACKAGE BODY       VALID   SYS

If it does not, then you would need to recreate this manually.

To do this run ORACLE_HOME\rdbms\admin\dbmspool.sql script from
SQL*PLUS as user SYS on your backend server.

Once this has created OK (check by running script above once more),
then issue the command to check privileges:

SQL> select grantee, owner, table_name, grantor, privilege, 
     grantable
     from dba_tab_privs
     where table_name='DBMS_SHARED_POOL';

This should return:

GRANTEE              OWNER      TABLE_NAME           GRANTOR    PRIVILEGE     
-------------------- ---------- -------------------- ---------- -----------
EXECUTE_CATALOG_ROLE SYS        DBMS_SHARED_POOL     SYS        EXECUTE       
RXC                  SYS        DBMS_SHARED_POOL     SYS        EXECUTE   

ORA-01940: cannot drop a user that is currently connected

The user is still connected so you can't drop it.
Use the following query to see the user session:

select s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p
where s.username = '<your user>'
and p.addr (+) = s.paddr
/

e.g
select s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p
where s.username = 'OVS'
and p.addr (+) = s.paddr
/

Kill the session that has not been already kill with:

alter system kill session '<sid>,<serial#>';

If all the session are noted 'KILLED' then kill the Unix process (column spid) with "kill -9". Then after a while the session will be removed from v$session and you will be able to drop the user.

Basic Query

desc tab;
desc all_objects;
v$version;