How to delete duplicate record from the table on a priority basis

Suppose we have table Name as “Dummy”

Query:- Select * from Dummy;

O/P:-   ID    | Name

----------

 1     | A

 1     | B

 1     | C

 2     | D

Condition 1:- When we need to delete the record from Dummy table where id=1 and found in first priority i.e updated record or we can say the latest record and delete all other records for id=1

Query:-Delete from Dummy where rowid not in (SELECT max(rowid) from Dummy group by id);

O/P:-   ID    | Name

---------------

  1    | C

  2    | D

Condition 2:-When we need to delete the record from Dummy table where id=1, Then left the only first record that we have inserted initially and delete all other records for id=1

Query:- Delete from Dummy where rowid not in (SELECT min(rowid) from Dummy group by id);

O/P:-   ID    | Name

---------------

  1    | A

  2    | D

Note:- ROWID is basically a pseudocolumn that returns the address of the row, It contains the information of object like the data block in the datafile in which the row resides or the position of the rows etc.

How to Add Comments on Table Level and Column Level in Oracle Database

Suppose we have a Table Emp in which we store the data of Employees and we want to add comments on the table and individual columns as well.

E.g:-

--------------------------------Dummy Table------------------------------------------
Create Table Emp
(Id Number(10),
Name Varchar2(100)
);
-------------------Add Comments on Table Level----------------------------------
COMMENT ON TABLE EMP  IS 'This table is used to store the name of the Employees';
--Check the comments for table level
select * from user_tab_comments where table_name='EMP';

-------------------Add Comments on Column Level---------------------------------
COMMENT ON COLUMN EMP.Id  IS 'This is the unique key of the Employees';
--Check the comments for column level
select * from user_col_comments where table_name='EMP';

ORA-14452

!!...ORA-14452: attempt to create, alter or drop an index on temporary table already in use...!!

Query :- Drop Global Temporary Table TEMPEMP;

O/P:- ORA-14452: attempt to create, alter or drop an index on temporary table already in use

When you are getting the above error then use the below command to resolve the issue.

Step 1:- Check the SID where your temporary table is currently using.

Query :- select  *   from  gv$lock   where id1 = (select  object_id from  dba_objects

where owner = ‘SCOTT' and object_name = 'TEMPEMP');

 O/P:- You will get the SID of this locked table, suppose SID=40 then

Step 2:- Check SID and SERIAL# from the session then running session

Query :- select SID,SERIAL#  from gv$session where sid=40;

O/P:-  You will get information about SID and SERIAL# with this query

Step 3:- Now kill the session, Suppose your SID=40 and SERIAL#=23 then

Query:-  Alter System Kill Session ’40,23’ Immediate;

After the execution of this query, your session for the temporary table will be killed.

Now again try to drop the temporary table i.e TEMPEMP this time your temporary table will be dropped..!! J

CONNECT DATABASE THROUGH SID IN ORACLE

To connect the oracle database use below steps:

Step 1:- Win+R -> cmd

Step 2:- SQLPLUS hr/hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.01)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))

Here

·         hr/hr  is my username/password

·         192.168.0.01 default IP address you need to pass the IP address of your oracle database server.

·         1521 is oracle database port number

·         In place of ORCL you need to pass the SID of your oracle database

DIFFERENCE B/W INNER JOIN AND INTERSECT OPERATOR

Suppose you have two tables and you want the distinct rows which are common in both the tables what will you do to get them? Most of us will answer that they will use INNER JOIN to get the common rows of two tables, right?     Wrong!!!!!!

This is a big misconception that INNER JOIN will always return all the common rows between two tables. In reality, "INNER JOIN treats two NULLS as two different values rather than a same value" so if you are joining a row based on a NULLable column there is a chance that if both tables have NULLs in that column then INNER JOIN will ignore those rows because

"Two NULLs are not same in TSQL"

ORACLE DATABASE LINK

To generate a database link between two different servers/database then

Query:-

CREATE DATABASE LINK DB_Test

CONNECT TO HR IDENTIFIED BY O123------à(Here UserName=” HR” and Password=” O123”)

USING

'(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=192.168.0.01 ) -------------------------------à(Need to pass the IP address of the server)

(PORT=1521))-----------------------------------------à(Port Number of the database)

(CONNECT_DATA=

(SID=Employees)))';----------------------------------à (SID of the database)

O/P:- database link DB_Test created.

Hint:- The whole connection-related information you will get from the TNS file.

With the help of this query database link will be created between two different databases, Now example to access the table through database link

Query:- select * from Salary@ DB_Test;

O/P:- This query will display the data of the Salary table with the help of a database link.

How to check Oracle Database Version?

The below query will show the currently running version of Oracle Database

Query:- Select banner From V$version;

Suppose your currently running oracle database version is “12.2.0.1.0” then the nomenclature of oracle release numbers are shown below:-

12 -> Major Release Number

2 -> Database Maintenance release number

0 -> Application release number

1 -> Component specific release number

0 -> Platform-specific release number

Generate Table/Index structure from metadata in Oracle Database

The below query will display the structure of the objects

For Table:-

Query :- SELECT DBMS_METADATA.GET_DDL ('TABLE','CUSTOMERS',’SCOTT’) FROM DUAL;

For Index:-

Query:- SELECT DBMS_METADATA.GET_DDL ('INDEX',’PK_CUSTOMERSID', ‘SCOTT’) FROM DUAL;

Description of parameters:- 

In the above query, we need to pass total three parameters

1^st Parameter:- To check the identity of the objects like table or index.

2^nd Parameter:- Pass the Name of the object

3^rd Parameter:- Name of the database User

Query to check the size of Oracle database

When we need to check to check the size of oracle database then

Query:- 
 SELECT
              ( SELECT sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
              ( SELECT nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
             ( SELECT sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
             ( SELECT sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from                  v$controlfile) "Size in GB"
from Dual;

Extract numbers from the string with the help of Regexp_Replace

Suppose we have a requirement to fetch the number from an alphanumeric string, then Regexp_Replace function help to extract the number from the string.

String :- 12ZXC3ASD456FGH8TED63

Query:- SELECT regexp_replace('12ZXC3ASD456FGH8TED63','\D') FROM dual;

O/P:-      123456863