Export Problems in 10g OEM

by Andrew Albertson
by Andrew Albertson

I was working with one of our Oracle 10g databases the other day when I tried to do an data pump export using the OEM.
It seemed to be doing it, but then spat out this error:
ORA-20204: User does not exist:
EXP 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

The error has something to do with the permissions for the user you are logged in as when doing the export.

Read More »

XP to Windows7 networking

by pascal.charest
by pascal.charest

I had to rebuild my Windows7 Media Center PC a little while ago, that all went OK and its working fine now. I can watch all the media OK and add new media to it from me Windows Vista PC OK. So I thought everything was OK with.

However, I built and XP laptop over the weekend for testing, and couldn’t connect to the Windows7 PC at all. It was freaking me out for a while. It all looked fine, I could connect from my VistaPC, what the heck was going on!

So, as you do I turned to Google for the answer. I found the answer at Windows7 Forums.

Read More »

Exporting data from Oracle Databases

by Andrew Albertson
by Andrew Albertson

There are two ways to export data from an Oracle 10g database. Data Pump or the Export utility.

The export utility has been around for quite some time, and prior to 10g was the only way to export data. But 10g introduced a new method called data pump.

Data Pump seems to be allot faster and more useful, but export is still necessary if you plan to then import the data into an older database. Although I have found you might have to use the version of export utility that is compatible with the database you plan to import into, rather than the ‘new’ version that comes with 10g.

The other thing to note is, that the command line syntax is very different.

Read More »

SQL to show code of stored procedure

by lordog
by lordog

I was doing some system checking the other day and I needed to find out what a certain stored procedure did. But I couldn’t readly see how to view the code of the stored procedure. After some digging of google I found how to use SQL command to show code of stored procedure in Oracle at AjaxApp.com.

I had to make a few changes to view what I wanted and came up with this

SELECT text FROM dba_source WHERE name=’proc_name';

Of course you will need to replace proc_name with the name of the stored procedure you want to view the code of.

Auditing Oracle Database Logons

by voyageAnatolia.blogspot .com

I’m running a project at work at the moment to clean up some of our old version 8 and 9 databases. To do this I had to somehow track if anyone was logging into them and who. Luckly the team at Burleson consulting had some good information about Auditing and reporting Oracle user activity.

It consists of a table to store the information and a trigger that fires when anyone logs into the database.

create table stats$user_log
(
 user_id           varchar2(30),
 session_id           number(8),
 host              varchar2(30),
 last_program      varchar2(48),
 last_action       varchar2(32),
 last_module       varchar2(32),
 logon_day                 date,
 logon_time        varchar2(10),
 logoff_day                date,
 logoff_time       varchar2(10),
 elapsed_minutes       number(8)
);
create or replace trigger logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
 user,
 sys_context('USERENV','SESSIONID'),
 sys_context('USERENV','HOST'),
 null,
 null,
 null,
 sysdate,
 to_char(sysdate, 'hh24:mi:ss'),
 null,
 null,
 null
);
END;
/

To view who has logged into the database you simply need to query the stats$user table like this.

select distinct user_id, logon_day from sys.stats$user_log;

I found it very helpful and was able to identify 4 databases that weren’t being used.

NOTE : I would be careful how long you leave the trigger in place. If the database is allot busier than you think it is, then the table could grow is size quite rapidlly.