Display the Database Version and Server Operating System Name

Display the Database Version and Server Operating System Name:-
-----------------------------------------------------------------------------

The following query gives the version of the Oracle Database.

SELECT banner FROM v$version;

Example:-

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

From above information, we can say this is 10g Database and Server Operating system is Linux.

The following query can be used to get the version of the Server Operating system.

BEGIN
dbms_output.put_line('Port String: '||dbms_utility.port_string);
END;

Example:-

Port String: Linuxi386/Linux-2.0.34-8.1.0

From the above information, we can say this is Linux Operating system and version is 2.0.34-8.1.0

Note:- If you are login into the server with some ID then you can also know the name and version of the Server Operating system directly by typing

> uname

Duplicate rows in the table

Duplicate rows in the table:-
--------------------------------

The following query can be used to get the duplicate records from table.

SELECT * FROM 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');

Example:-

SELECT * FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY ename);

To eliminate/delete the duplicate rows from the table, you can use the following query.

DELETE 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');

Example:-

DELETE emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY
ename);

Display the number value in Words

Display the number value in Words:-
------------------------------------------

The following query can be used to display the number in the words.

select 'Your Number', (to_char(to_date('Your Number','j'), 'jsp')) from dual;

Example:-

select 211, (to_char(to_date(211,'j'), 'jsp')) from dual;

Thanks to Chandra k Kadali for posting this Article.