Feeds:
Posts
Comments

Today, while going through Oracle Forum, I came across this really interesting solution!

Lets suppose, We have a table tab1 having following columns (never mind the data type):

TimeIn
DateIn
Locationin
UseridIn
Status

Now let suppose I need to delete the duplicate rows in tab1 and leave only the minimum DateIn and TimeIn in that table by UseridIn.

If I had this problem I would have solved the problem as follows:

DELETE FROM tab1
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM tab1
WHERE (UseridIn, TO_DATE(TO_CHAR(DateIn,’YYYYMMDD’) || TimeIn,’YYYYMMDDHH24:MI:SS’)) IN
( SELECT UseridIn, MIN(TO_DATE(TO_CHAR(DateIn,’YYYYMMDD’) || timein,’YYYYMMDDHH24:MI:SS’))
FROM tab1
GROUP BY UseridIn)
GROUP BY UseridIn
)

But I found solution provided by William Robertson interesting which was as follows:

DELETE tab1
WHERE rowid IN
( SELECT LEAD(rowid)
OVER(PARTITION BY useridin ORDER BY datein,timein)
FROM tab1 );

Where he is using LEAD analytic function (which gives the access to a row at a given physical offset) with OVER keyword, PARTITION it by useridin and order by datein and timein.

Now let’s check out, how it is working:

Now when I executed following sql

SELECT useridin,
timein,
rowid current_rowid,
LEAD(rowid) OVER(PARTITION BY useridin ORDER BY datein,timein) lead_rowid
FROM tab1

USE TIMEIN CURRENT_ROWID LEAD_ROWID
— ——– —————— ——————
U01 07:20:00 AAAOdoAAEAAAAKEAAC AAAOdoAAEAAAAKEAAB
U01 08:10:30 AAAOdoAAEAAAAKEAAB AAAOdoAAEAAAAKEAAA
U01 08:20:00 AAAOdoAAEAAAAKEAAA
U02 06:10:30 AAAOdoAAEAAAAKEAAF AAAOdoAAEAAAAKEAAE
U02 06:10:30 AAAOdoAAEAAAAKEAAE AAAOdoAAEAAAAKEAAD
U02 08:14:00 AAAOdoAAEAAAAKEAAD

As we can see for each “useridin”, result data is order in asc order by datain and timein. So minimum datein and timein for each useridin will be the first row in the resultset for each useridin if we partition it by useridin as shown above.

And this minimum row will be not any rows LEAD_ROWID as it is the first row for each resultset partition by useridin.

Note:
If you do not specify offset(as in case above), then default will be 1.
If you don not specify how to do order by, then its default will be ASC order.

OracleBrains.Com References

Before Oracle 9i Release 2, if we want to use the UTL_FILE package then we need to initialize UTL_FILE_DIR initialization parameter and restart the instance.

UTL_FILE_DIR = directory name with path

Note: The parameter specification UTL_FILE_DIR = * has a special meaning. This entry turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.

Not only was this, security wise there was no mechanism to protect these directories. All the users can access all the directories.

With the Oracle 9i Release 2, things have changed.

 

 

Now Instead of using the directory defined with UTL_FILE_DIR, we can create DIRECTORY schema object and use this object with UTL_FILE. Once this is done we can give rights on it to users based on our security policies.

CREATE OR REPLACE DIRECTORY directory object name AS directory name with path;

GRANT READ, WRITE ON DIRECTORY directory object name TO user;

OracleBrains.Com References

I was going through some papers about TNS Listener, so I though why not share what I know and what I learn from it. So here it is.

What is TNS Listener?

TNS (Transparent Network Substrate) Listener is a server process that listen for a database connection request on a specific port (By the default 1521, but can be configured to use other port) on the database server.

How TNS Listener works?

  1. Whenever a connection request with username, password and with particular SID or service name is received on that specific port.
  2. It check the SID or service name and if configured to listen to that SID or service name then forward the login information to that instance or will return an appropriate error message to the requester.
  3. After this database authenticates login information, the listener process redirect the client to any new available port and a session is create between client and the server on that new port.
  4. Once this flow is completed, it again start listening on original port leaving server process and client process to do their own work.

The job of listener is not limited to database connection. It also enable client to access to external procedure (example external programs in c language).

OracleBrains.Com References

This information for the guys who are new to SQL*Plus.

One can generate static HTML pages from SQL*Plus (8.1.6 and above) by setting the MARKUP option to HTML ON.

This can be done following two ways:

1. From command line when opening sqlplus specifying -MARKUP “HTML ON” as parameters

Example:

C:\>SQLPLUS -MARKUP “HTML ON”

2. From SQL*Plus, through “SET MARKUP HTML ON” command.

Example:

SQL> SET MARKUP HTML ON

SQL> SPOOL C:\dept.html

SQL> SELECT * FROM dept;

SQL> SPOOL OFF

 OracleBrains.Com References

Recently I came to know about very interesting procedure called “KSDWRT” in DBMA_SYSTEM.

It can be used to add our own custom entries to the alert log or trace file or both depending on the first parameter that we pass to this procedure.

DBMS_SYSTEM.KSDWRT(1,’Testing Writting to Trace File’);

DBMS_SYSTEM.KSDWRT(2,’Testing Writting to Alert File’);

DBMS_SYSTEM.KSDWRT(3,’Testing Writting to Trace & Alter File’);

Use 1 to write to the trace file

Use 2 to write to the alter file

Use 3 to write to both.

OracleBrains.Com References

Today I found out this very interesting fact about default value.

While inserting or updating we can use reserver word “DEFAULT”  to insert or update value of a column.

Example:

SQL> CREATE TABLE TEST1(  NO VARCHAR2(10) DEFAULT ‘TEST’, NO1 VARCHAR2(10));

Table created.

SQL> INSERT INTO TEST1 VALUES(DEFAULT, DEFAULT);

1 row created.

SQL>  UPDATE TEST1 SET NO=DEFAULT;

1 row updated.

Note: If DEFAULT value exist for a column it will be used or null will be used.

OracleBrains.Com References

Today my observation related to oracle roles as follows:

I login in in SQL*Plus as “system” user and gave following commands:
CREATE USER raj IDENTIFIED BY ‘raj’;
GRANT connect,resource TO raj;
CREATE ROLE test IDENTIFIED BY “test”;
GRANT test TO raj;

then I open another instance of SQL*Plus and login as “raj”:
When I gave following command:
CREATE TABLE table1 (col1 VARCHAR2(1));
It create a table for me.

Then I gave following command
SET ROLE ALL;
It generated a error, then I gave following command
SET ROLE test identified by “test”;
then gave following command:
CREATE TABLE table2 (col1 VARCHAR2(1));
It gave me error that I don’t have sufficient Privileges

OracleBrains.Com References

Follow

Get every new post delivered to your Inbox.