Four Approaches about Digging IP Address Connecting to Oracle Database Server

A couple of days ago my customer asked me a question – whether he could find out all of IP Addresses even connecting to an Oracle Database Server on Production System. With his question whom I once researched/studied for a while.

Hence today’s note I’ll try my best to introduce total 4 approaches about digging IP Address connecting to Oracle Database Server. Of course every approach has its own advantage and disadvantage, which all depends on how you treat and distinguish them.

Actually to classify these 4 number of methods means to divide them into two kinds (types) of forms. They’re as follows,

• Checking oracle dynamic performance view “v$session”;
• Exploring oracle listener log file “log.xml”;

Among the previous two types of forms every form has also been divided into two number of specific stuff. You can see the following bullet list,

• v$session
  • creating an oracle trigger on SYS schema
  • creating an oracle function on SYS schema
• log.xml
  • writing a Linux SHELL script on ORACLE user of OS
  • writing an Oracle SQL script on ORACLE user of OS

Let me start to respectively introduce them.

WordPress (itself)’s issue or bug?

From time to time I’ve written my blog post and note on WordPress so far there has approximately two years.

On the while (overall) wordpress is very friendly and convenient for writing and publishing some articles for me because my oracle guys and buddies have been using it all the time. They’re Jonathan Lewis, Connor McDonald, Richard Foote, David Fitzjarrell, Stew Ashton, Mohamed Houri and so on (here I can’t all list them).

Batch grant select – 5

My index list of serialized posts of batch grant select is as follows:

• Batch grant select
• Batch grant select – 2
• Batch grant select – 3
• Batch grant select – 4

The day before yesterday I’ve finally updated my 4th episode of a series of blog notes for batch grant select. Actually these updated stuff is pretty simple – I just use a materialized view u_tables to replace initial common view usr_tables. Somebody who you have read my some notes might know that the content of my post is not always fixed and permanent, perhaps either I also came up with a new idea in the morning/evening of the next day or it’s really necessary to supplement what I missed or never mentioned.

Originally I intend to write the previous update section (by the way my 1st code “bgs_role_syn_tab_2.sql” is Oracle SQL format) to this note, at that moment I roughly estimate its content will not have too much. Today I’ve also found some clue that I haven’t clarified yet, in other words I feel my demo should be improved. So this note on one hand I’ll improve the demo prior mentioned, on the other hand I will use some piece of PL/SQL code to cover/remedy the shortage (disadvantage) of “bgs_role_syn_tab_2.sql”.

Batch grant select – 4

So far I’ve written a series of notes of batch grant select if you’re not familiar to them you can separately/respectively read every note from the following list in order.

• Batch grant select
• Batch grant select – 2
• Batch grant select – 3

Throughout I was still not able to find out a perfect solution because of  a couple of days ago my developer colleague threw/gave me a new question that although I did several processes to make her discover all of table names from grantor schema “PROD” on grantee schema “QWZ” she didn’t think that she was able to find new table(s) when created new table(s) on grantor schema “PROD”. Here I need to clarify a point she never mentioned/cited if dropped a table on grantor schema “PROD” this table will also instantly/immediately doesn’t exist (disappear) on grantee schema “QWZ” (attention: you should never drop table on production system).

Showing Legacy Mode Parameter: “statistics=none” when expdp data of table

The day before yesterday my oracle developer co-worker asked me to send him a dmp file with EXPDP for two tables on critical schema of our oracle production system, and I added this special parameter “statistics=none” when executing expdp command to mean that no including extra statistics information on that dmp file.

Unfortunately I encountered/noticed this messages (tips) “Legacy Mode Parameter: “statistics=none” Location: Command Line, ignored.” after I was inputting necessary expdp statement and pressed the Enter key on my keyboard.

Batch grant select – 3

On my previous note I once used oracle role and public synonym to accomplish for batch grant (only) select privilege on specific schema (“PROD”)’s all of tables to a new user (“QWZ”). Now I’m facing a new issue that how to identify/find out which tables on schema “PROD” you can access if you connect to schema “QWZ” by oracle SQL*Plus utility, so which’s also I am getting great feedback from my oracle Developer colleague.

Batch grant select – 2

At the end of last month I wrote a note about how to batch grant (only) select privilege on all of tables on specific schema “PROD” to a new user “QWZ”. A couple of days ago Mark D Powell on Oracle Developer Community (now it’s named to “ODC” by Oracle Corp, the original name is “OTN”) gave me his good opinions and advices, which recommended me first grant select privilege to a role and next grant this role to a user. Yep, he is very right. When we need to grant more than one user, first granting a role to specific privileges that we wanna give next granting this role to a user. Because role could be focus on administrating a set of privileges that can be granted to database users or to other roles.