How to grant view all DDL in an Oracle database to unprivilege user

SQL>grant select on dba_objects to <username>;

SQL>conn <username>/<password>

SET PAGESIZE 0
SET LONG 100000
SET LONGCHUNKSIZE 10000
SET LINESIZE 1000
–SET TERMINATOR ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,TRUE)
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = ‘PACKAGE’ AND OWNER = ‘POSITION_OWNER’
–SAME FOR OTHER OBJECTS E.G. TABLE, PROCEDURE, ETC
;

How to find the tapes needed for restore if you are using RMAN catalog to write directly to tape?

If you are using the RMAN and using the RMAN catalog to write directly to tape you can use the following query to find the tapes needed to restore or the tapes that were used.

# The following example find the tapes used to backup b/w 10-Jun-2008 4:00 AM and 11-Jun-2008 4:00 AM by specifying the database name or DB ID

$ sqlplus rmancatalog/rmancatalog
SQL> select distinct p.MEDIA
from rc_backup_piece p, rc_database d
where d.NAME = ‘TEMP’
– OR d.dbid = 3903930932
and p.COMPLETION_TIME >= TO_DATE(’10-JUN-2008 04:00′, ‘DD-MON-YYYY HH24:MI’)
and p.COMPLETION_TIME <= TO_DATE(’11-JUN-2008 04:00′, ‘DD-MON-YYYY HH24:MI’)
and p.db_id = d.dbid;

MEDIA
———
A00635
A00999

How to change SYSMAN password in OEM (OMS)

To change the Sysman password:
1. Stop the OMS instances that are associated with the repository
a. opmnctl stopall

2. Change the sysman password in the database:
a. Logon to sqlplus as a dba user
b. Issue the command: alter user sysman identified by newpassword;
c. Exit Sqlplus

3. On each management server host, cd to the OMS $ORACLE_HOME/sysman/config directory
a. backup the emoms.properties file
b. edit the emoms.properties file and modify the parameters;
eml.mntr.emdRepPwd=new_password
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

NOTE: When you restart the OMS, the FALSE setting will change to TRUE and the password will become encrypted in the file

4. Still on Management Repository host, cd to the AGENT_HOME/bin and stop the agent:
./emctl stop agent
5. Next, cd to the AGENT_HOME/sysman/emd directory
a. Edit the targets.xml file and modify the oracle_emrep target definition with the following in bold:
<Target TYPE=”oracle_emrep” NAME=”Management Services and Repository” VERSION=”1.0″>
<Property NAME=”MachineName” VALUE=”host.us.oracle.com”/>
<Property NAME=”Port” VALUE=”1521″/>
<Property NAME=”SID” VALUE=”sid”/>
<Property NAME=”UserName” VALUE=”552f2080d6d8c3c6″ ENCRYPTED=”TRUE”/>
<Property NAME=”password” “newpassword” ENCRYPTED”FALSE”/>
..
..
b. Save the file. The TRUE value will change to FALSE and the newpassword will become encrypted when you restart the Agent.

6. Issue the command (from the Agent O_H/bin):
./emctl start agent

7. Restart each of the OMS’s
emctl start oms

8.Start the OMS instances that are associated with the repository
opmnctl startall

Note that after change all the config files shown in the document, the OEM still had issues to connect to the repository due to the sysman user been locked. After unlock the sysman account everything was back to normal.

Entries found in the $OMS_HOME/sysman/log/emoms.log

2008-07-17 10:40:54,685 [AJPRequestHandler-ApplicationServerThread-7] ERROR conn.ConnectionService verifyRepositoryEx.818 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

How does one trace a session based on user

CREATE OR REPLACE TRIGGER sys.session_trace_on
– to be created by sys user
AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
ora_username VARCHAR2(30) DEFAULT NULL;
os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE audsid = userenv(’sessionid’)
– and upper(machine) like ‘ABC123′
and username = ‘EPCBATCH’;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename,v_program;
IF c1%FOUND THEN
DBMS_SESSION.set_sql_trace (TRUE);
END IF;
CLOSE c1;
END;
/

How to modify SQL*Plus prompt

undefine usr db
col usr new_value usr
col db new_value db
set termout off
select lower(user) usr, instance_name db
from v$instance
/
set termout on
set sqlprompt ‘&&usr.@&&db.> ‘

How to view session stats?

To see current stats of your session, one can run the following query.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic#;

To view session stats of another session one can run the query below passing the session id.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic# and sid = &v_sid;

How to tell if the database was started with spfile or pfile?

There are couple of ways of finding if the database was started with spfile or pfile.

One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile.

– belows show database was started by spfile
SQL> show parameter spfile;

NAME TYPE VALUE
—— —— ————————————————
spfile string /u01/apps/oracle/10g/dbs/spfileorcltest.ora

Another way to find would be set the parameter with scope=spfile, if database was started with spfile one will be able to set the value if it’s started with pfile you will see the “ORA-32001: write to spfile requested but no SPFILE specified at startup”. Note: You can set the new value to be the same as current value of the parameter, it doesn’t have to be a different one.

SQL> ALTER SYSTEM set open_cursors=300 scope=spfile;
ALTER SYSTEM set open_cursors=300 scope=spfile;
*
ERROR at line 1:
ORA-32001: write to spfile requested but no SPFILE specified at startup

How to identify parameters that has been modified since instance started?

To identify the parameter one can query v$parameter view to find the parameters that were modified. The column ISMODIFIED is FALSE by default when the instance starts up, when the value is changed it is SYSTEM_MOD indicating the value is changed at the SYSTEM level and if it’s MODIFIED then it’s changed at session level.

-- Before parameter change displaying value of ISMODIFIED
SQL> select name, value, ismodified from v$parameter where name = 'open_cursors';

NAME                 VALUE ISMODIFIED
-------------------- ----- ----------
open_cursors         301   FALSE

SQL> alter system set open_cursors=300;

System altered.

SQL> select name, value, ismodified from v$parameter where name = 'open_cursors';

NAME                 VALUE ISMODIFIED
-------------------- ----- ----------
open_cursors         300   SYSTEM_MOD

Trigger to monitor successful logins in the database

By having a logon trigger in a database one can monitor or audit OS user, programs, DB users, time users logging into a database. It could also be used the change a behavior of a session by altering user’s session’s based on certain criteria.

CREATE TABLE sys.logon_audit
(
logon_time DATE,
username VARCHAR2(30), — DB user
osuser VARCHAR2(30), — OS user
machine VARCHAR2(64), — machine
program VARCHAR2(48 )
) TABLESPACE dba_tbs;

CREATE OR REPLACE TRIGGER sys.connection_audit
AFTER LOGON ON database
DECLARE
– use sys_context for 9i and above
– for 8i use userenv(’sessionid’)
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session;
– WHERE audsid = sys_context(’USERENV’,’sessionid’);
rec_c1 c1%ROWTYPE;

BEGIN
rec_c1 := NULL;

OPEN c1;
FETCH c1 INTO rec_c1;
– record will always be found, so need to check record not found
INSERT INTO user_audit(logon_time, username, osuser, machine, program)
VALUES (sysdate, user, rec_c1.osuser, rec_c1.machine, rec_c1.program);
CLOSE c1;
END;
/

Note: You may also implement a routine to delete data from the table as if the tablespace gets full users won’t be able to login.

How to find files with duplicate names in filesystem?

If you are doing migrating to a new filesystem and moving files, it may be a good idea to check before migrating the filenames are unique across all filesystems by running the following query you find the files that have the same name.

If the query returns no rows found then you don’t have duplicate filenames.

SELECT SUBSTR(file_name, instr(file_name, ‘/’, -1))
FROM (SELECT file_name FROM dba_data_files
UNION ALL
SELECT file_name FROM dba_temp_files
UNION ALL
SELECT member as file_name FROM v$logfile
UNION ALL
SELECT name as file_name FROM v$controlfile)
GROUP BY file_name
HAVING COUNT(1) > 1;

Note: On windows the filenames are not case-sensitive so adding upper/lower to convert to same case would be required to ensure filenames are unique.