Update an id column to be sequential

 Sometimes we come across a table in which a column that is supposed to store values in sequence , but over time they get values in the column are in random order to resolve this so that the id column has a sequence.

update parameter_table set parameter_id=rownum;

script to generate partition names

with dts as (

  select date'2013-01-01'+rownum-1 dt from dual

  connect by level <= 366

)

  select 'PARTITION' || 'P_BEFORE' || '_' || TO_CHAR(dt, 'YYYYMMDD') || 'VALUES LESS THAN (' || TO_CHAR(dt, 'YYYYMMDD') || ')'||  ';' from dts

  where  to_char(dt, 'FMDAY') = 'SATURDAY';

 

OUTPUT;

PARTITIONP_BEFORE_20130105VALUES LESS THAN (20130105);

PARTITIONP_BEFORE_20130112VALUES LESS THAN (20130112);

PARTITIONP_BEFORE_20130119VALUES LESS THAN (20130119);

PARTITIONP_BEFORE_20130126VALUES LESS THAN (20130126);

PARTITIONP_BEFORE_20130202VALUES LESS THAN (20130202);

PARTITIONP_BEFORE_20130209VALUES LESS THAN (20130209);

PARTITIONP_BEFORE_20130216VALUES LESS THAN (20130216);

PARTITIONP_BEFORE_20130223VALUES LESS THAN (20130223);

PARTITIONP_BEFORE_20130302VALUES LESS THAN (20130302);

In case you have to generate a list of partitions we can use this method to auto generate partitions.

divide records equally using ntile

select quartile, max(id), min(id), count(*) from (

SELECT id, NTILE(10) OVER (ORDER BY id DESC)

   AS quartile from tablename

) group by quartile

The above query will help you divide the number of record's into ten groups of equal size.
helpful when running procedures by range.

OLAP synch timestamp

 To determine the latest timestamp for records that have been synched

SELECT SCN_TO_TIMESTAMP(CURRENT_SCN) SYNC_UNTIL FROM V$DATABASE;

Rename filenames and Update database with replace function

echo "Change directory to /app/product/fileupload"
cd /app/product/fileupload
for f in *\ *; do mv "$f" "${f// /_}"; done
echo "complete space removal"

for f in *\:*; do mv "$f" "${f//:/_}"; done
echo "complete semi-colon removal"
cd -
echo "completed"

#!/bin/bash
#/temail="`cat dmvCheckDis`"
export ORACLE_HOME=/opt/app/oracle/product/11.2.0/client_1
$ORACLE_HOME/bin/sqlplus -s puser/passwrd@dbname << ENDOFSQL
set heading off
set linesize 1000
set pagesize 1000
set echo off
alter session set current_schema=siebel;
update SIEBEL.S_SR_ATT set file_name=replace(replace(file_name,' ','_'),':','_'), file_src_path=replace(replace(file_src_path,' ','_'),':','_') where file_name like 'WEB%' and (file_name like '% %' or file_name like '%:%');
commit;
exit;
ENDOFSQL

Virtual Columns in a Table

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The virtual column is also listed in the data dictionary. That column is not physically created but calculated always when accessed. It has the advantage of not having multiple schema objects, and another advantage is the optimizer can gather histograms on the column if needed or you may create an index on the column and also partition on the virtual column if desired.

The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

eg:

alter table plch_employees add (
   co_level varchar2(1) generated always as (
      case
         when upper(title) like 'C_O' then 'Y'
         else 'N'
      end
   ) virtual
)
/

eg:Below is an example creating a virtual column.

CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

Calculate time difference between timestamps in milliseconds

Used the below sql to calculate the difference in timestamps in milliseconds.

select TX_ID,

       extract(hour   from (END_TIMESTAMP - START_TIMESTAMP)) * 3600000

        + extract(minute from (END_TIMESTAMP - START_TIMESTAMP)) * 60000

        + extract(second from (END_TIMESTAMP - START_TIMESTAMP)) * 1000 diff_im_ms

from t_transaction

where TX_ID IN (6587728628,6587728639,6587728647);

167

To remove control characters from strings

In case column has string data that is padded with junk characters that are not visible

You can use REGEXP_REPLACE to remove them.

SELECT length(REGEXP_REPLACE(column_name,'[[:cntrl:]]', '')), length(column_name),column_name from table_name;

Display currency in Oracle

  select   replace( replace( TO_CHAR ( -14500.77 , 'FML999999D99PR'),'<;',')')  ,'<;','(') from dual;

<$14500.77>
 

Delete Records SQL

When you want to delete rows from one table that are not present in another table:

delete from queue_table q where  not  exists ( select 1 from man_table r  where r.trx_id=q.trx_id);

to_number to handle character data

When you have column that could have data the to_number( ) function fails.

To correctly convert a field you can use the below sql:

 COALESCE(TO_NUMBER(REGEXP_SUBSTR( column_name, '^\d+')), 0)  column_name,
or

 COALESCE(TO_NUMBER(REGEXP_SUBSTR( column_name, '\d+')), 0)  column_name,

In SQL Server:

eg: SRCTAID='000000128'  get converted to '128'

SRCTAID='1-7383' does not get converted 

select SUBSTRING(a.SRCTAID, PATINDEX('%[^0]%',a.SRCTAID), LEN(a.SRCTAID)) as srctaidnew  a.srctaid
from  TABLE_NAME A ;

Oracle Change Password

You can change your password with the following sql command.

alter user user_name  identified by new_password ;

or

 alter user user_name  identified by new_password replace old_password;

If you want to include special characters in the password, mention the new password in quotes.

eg:
alter user scott identified by "NewYear20!2" replace "Winter20!2";

v$instance database information

v$instance can be used to determine some basic information about a database:

select version from v$instance;
  11.1.0.7.0

Generate Sequence

This query generates the same number '1' 10 times.
SELECT 1
FROM DUAL
CONNECT BY LEVEL <= 10;


This query generates a sequence of number from 1 to 31.
SELECT ROWNUM FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= 31;

This query can be used to generate a sequence of date from 20100201 to 20100228
SELECT TRUNC(TO_DATE('20100201','yyyymmdd'),'MM')+ROWNUM -1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE('20100201','YYYYMMDD'),'MM'),1) - TRUNC(TO_DATE('20100201','YYYYMMDD'),'MM');

By generating this sequence of dates or numbers you can join to another table to see if any data exists for these numbers or dates.For example below I wanted to find if there were any records in a very big table if data existed for certain dates. I could have done a distinct but i chose this method.
select d.*,c.* from

(
SELECT TRUNC(TO_DATE('20100201','yyyymmdd'),'MM')+ROWNUM -1 DATES FROM
(
SELECT 1
FROM Dual
GROUP BY CUBE (2, 2, 2, 2, 2)
)
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE('20100201','YYYYMMDD'),'MM'),1) - TRUNC(TO_DATE('20100201','YYYYMMDD'),'MM')
) d left outer join  eim_split_skill c on d.dates = c.current_date
-- where upper(split_skill)='GENERAL'
order by dates;

(+) On Which Side

I've always got confused on which side to put the (+) sign while doing a join.

Example:
select stmt_run_id, q.id from t_stmt_run_date ,(select 2893 id from dual )q
where  q.id(+)=stmt_run_id;

This will display all the rows from t_stmt_run_date, so here's my mechanism for the (+) sign , put the (+) sign on the side that might not contain the value.The (+) acts like a shot of medicine to help display it's values.

Can you put a (+) sign on both sides?
 The answer is no.The query will fail.

Oracle Session Id

Everytime you connect to oracle you start a new session.Each session is identified by a session_id. The sesssion_id can be very useful to the DBA to monitor your queries.

select sys_context('USERENV','SID') from dual;
or
select sid from v$mystat where rownum <=1;
or
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
or
select distinct sid from v$mystat;