Ho to combine or concatenate multiple column data into one single virtual column in oracle

drop table INTADM.TMP_APP_ADDRESS

CREATE TABLE INTADM.TMP_APP_ADDRESS
(
  ADDRESS_SEQ         VARCHAR2(255 CHAR)        NOT NULL,
  VERSION             NUMBER(19)                NOT NULL,
  CREATOR_ID          VARCHAR2(30 CHAR),
  CREATED_DATE        TIMESTAMP(6)              NOT NULL,
  MODIFIER_ID         VARCHAR2(30 CHAR),
  MODIFIED_DATE       TIMESTAMP(6)              NOT NULL,
  REALM_ID            NUMBER(19)                DEFAULT -1                    NOT NULL,
  ADDRESS_LINE3       VARCHAR2(80 CHAR),
  ADDRESS_LINE2       VARCHAR2(80 CHAR),
  ADDRESS_LINE1       VARCHAR2(80 CHAR),
  CITY                VARCHAR2(256 CHAR),
  COUNTRY             VARCHAR2(256 CHAR),
  STATE               VARCHAR2(21 CHAR),
  ZIP_CODE            VARCHAR2(13 CHAR),
  ZIP_CODE_EXTENSION  VARCHAR2(10 CHAR),
   ADDRESS_COMBINE VARCHAR2(800 CHAR) generated always as
      (
         concat(concat(CITY,COUNTRY),STATE)
       ) virtual
 
)
TABLESPACE APP_DATA01
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING





insert into INTADM.TMP_APP_ADDRESS( ADDRESS_SEQ ,VERSION,CREATOR_ID,CREATED_DATE,MODIFIER_ID,MODIFIED_DATE,REALM_ID,ADDRESS_LINE3,ADDRESS_LINE2,ADDRESS_LINE1,CITY,COUNTRY,STATE,ZIP_CODE,ZIP_CODE_EXTENSION)  select  ADDRESS_SEQ ,VERSION,CREATOR_ID,CREATED_DATE,MODIFIER_ID,MODIFIED_DATE,REALM_ID,ADDRESS_LINE3,ADDRESS_LINE2,ADDRESS_LINE1,CITY,COUNTRY,STATE,ZIP_CODE,ZIP_CODE_EXTENSION
from INTADM.APP_ADDRESS where ADDRESS_SEQ='1c17e7a7-5e9d-45db-b80a-98b848dfe2e3'


insert into INTADM.TMP_APP_ADDRESS( ADDRESS_SEQ ,VERSION,CREATOR_ID,CREATED_DATE,MODIFIER_ID,MODIFIED_DATE,REALM_ID,ADDRESS_LINE3,ADDRESS_LINE2,ADDRESS_LINE1,CITY,COUNTRY,STATE,ZIP_CODE,ZIP_CODE_EXTENSION)  select  ADDRESS_SEQ ,VERSION,CREATOR_ID,CREATED_DATE,MODIFIER_ID,MODIFIED_DATE,REALM_ID,ADDRESS_LINE3,ADDRESS_LINE2,ADDRESS_LINE1,CITY,COUNTRY,STATE,ZIP_CODE,ZIP_CODE_EXTENSION
from INTADM.APP_ADDRESS where rownum < 100 AND COUNTRY IS NOT NULL




alter table INTADM.TMP_APP_ADDRESS add (ADDRESS_COMBINE_2 VARCHAR2(800 CHAR) generated always as(concat(concat(ADDRESS_LINE3,ADDRESS_LINE2),ADDRESS_LINE1)) virtual)


select * from INTADM.TMP_APP_ADDRESS

How to drop and recreate a temp tablespace online and resolve ORA-01187: cannot read from file because it failed verification tests

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 1001: '/u01/oradata/pdbdbqpf1/temp_01.tpf'


SQL>
SQL>
SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';
SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP'
                                              *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 1001: '/u01/oradata/pdbdbqpf1/temp_01.tpf'






SQL>
SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp2_01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL>  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;
CREATE TEMPORARY TABLESPACE temp
*
ERROR at line 1:
ORA-01543: tablespace 'TEMP' already exists


SQL> drop tablespace temp including datafiles;
drop tablespace temp including datafiles
                               *
ERROR at line 1:
ORA-01911: CONTENTS keyword expected


SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL>
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL>
SQL>
SQL> drop tablespace temp2 including contents;

Tablespace dropped.

SQL>
SQL>
SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';
ERROR:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 1002: '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf'



no rows selected



oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 32505864192 Jun 29 22:20 pdbtemp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:19 temp01.dbf
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> CREATE TEMPORARY TABLESPACE temp3
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp3_01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp3;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.


oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 32505864192 Jun 29 22:25 pdbtemp_01.tpf
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:24 temp3_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop;

Database altered.

SQL> !

oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 32505864192 Jun 29 22:25 pdbtemp_01.tpf
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:24 temp3_01.dbf


SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including tempfiles;
alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including tempfiles
                                                                                 *
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including datafiles;
alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including datafiles
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf"




SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/temp_01.tpf' drop;
alter database tempfile '/u01/oradata/pdbdbqpf1/temp_01.tpf' drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/oradata/pdbdbqpf1/temp_01.tpf"



oracle@pprfg67db800.ie.cgban.net:/u01/app/oracle/product/11.2.0.3/bin->cd /u01/oradata/pdbdbqpf1
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:30 temp3_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->rm -rf temp_01.tpf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/temp2_01.dbf' drop;
alter database tempfile '/u01/oradata/pdbdbqpf1/temp2_01.dbf' drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/oradata/pdbdbqpf1/temp2_01.dbf"




oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:30 temp3_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->rm -rf temp2_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> drop tablespace temp3 including contents and datafiles;

Tablespace dropped.

SQL>
SQL>
SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/pdbdbqpf1/temp01.dbf

SQL>
SQL>
SQL>
SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/pdbdbqpf1/temp01.dbf

ALTER TABLESPACE TEMP add tempfile '/u01/oradata/pdbdbqpf1/temp04.dbf' size 4096M reuse;

ALTER TABLESPACE TEMP add tempfile '/u01/oradata/pdbdbqpf1/temp04.dbf' size 4096M reuse;

How to display column values in row using sql

There could be different way of displaying column values in a row.

First One:

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

Second one which is also known as Pivoting:

SQL> SELECT UPPER(o.order_mode) order_mode

  2   ,SUM(DECODE(o.order_status, 0, o.order_total)) stat_0

  3   ,SUM(DECODE(o.order_status, 1, o.order_total)) stat_1

  4   ,SUM(DECODE(o.order_status, 2, o.order_total)) stat_2

  5   ,SUM(DECODE(o.order_status, 3, o.order_total)) stat_3

  6   ,SUM(DECODE(o.order_status, 4, o.order_total)) stat_4

  7   ,SUM(DECODE(o.order_status, 5, o.order_total)) stat_5

  8   ,SUM(DECODE(o.order_status, 6, o.order_total)) stat_6

  9   ,SUM(DECODE(o.order_status, 7, o.order_total)) stat_7

 10   ,SUM(DECODE(o.order_status, 8, o.order_total)) stat_8

 11   ,SUM(DECODE(o.order_status, 9, o.order_total)) stat_9

 12   ,SUM(DECODE(o.order_status, 10, o.order_total)) stat_10

 13  FROM orders o

 14  GROUP BY o.order_mode

 15  ORDER BY 1

 16  /

ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10

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

DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601

ONLINE        25976.7              103834.4    56381.7   700068.1   183261.2    90411.8              322192.5    57062.4   225236.7

1

Our first example will be a simple demonstration of the PIVOT syntax. Using the EMP table, we will sum the salaries by department and job, but transpose the sum for each department onto its own column. Before we pivot the salaries, we will examine the base data, as follows.

SQL> SELECT job

  2  ,      deptno

  3  ,      SUM(sal) AS sum_sal

  4  FROM   emp

  5  GROUP  BY

  6         job

  7  ,      deptno

  8  ORDER  BY

  9         job

 10  ,      deptno;

JOB           DEPTNO    SUM_SAL

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

ANALYST           20       6600

CLERK             10       1430

CLERK             20       2090

CLERK             30       1045

MANAGER           10       2695

MANAGER           20     3272.5

MANAGER           30       3135

PRESIDENT         10       5500

SALESMAN          30       6160

9 rows selected.

We will now pivot this data using the new 11g syntax. For each job, we will display the salary totals in a separate column for each department, as follows.

SQL> WITH pivot_data AS (

  2          SELECT deptno, job, sal

  3          FROM   emp

  4          )

  5  SELECT *

  6  FROM   pivot_data

  7  PIVOT (

  8             SUM(sal)        --<-- pivot_clause

  9         FOR deptno          --<-- pivot_for_clause

 10         IN  (10,20,30,40)   --<-- pivot_in_clause

 11        );

JOB               10         20         30         40

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

CLERK           1430       2090       1045

SALESMAN                              6160

PRESIDENT       5500

MANAGER         2695     3272.5       3135

ANALYST                    6600

5 rows selected.83261.2    90411.8              322192.5

How can we see the Bind variable values when they are not in Timestamp and display the cursor plan

We can see the bind variable values with the below query.

 select * from table(dbms_xplan.display_cursor('4b5c6cp4gkup6',null,'+peeked_binds'));


How to display the cursor plan:

Run the cursor and execute the below sql:

select * from table(dbms_xplan.display_cursor);

Resetting the same old password in oracle or resolving password cannot be reused ORA-28007: or ORA-28003: password verification failed in oracle

Note:Password resetting is user own decision as per requirement.
Not to be copy pasted from this article and should not be used in production without CR.
This steps are described below keeping developement or perf testing environment in mind.

You might be doing this but still getting the above error:

SQL> select USER#,NAME,PASSWORD FROM USER$ where name='IPPADM' ;

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        26 IPPADM                         C42D1F57A9936520

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

Then you might be setting the password to the older value;

alter user IPPADM identified by values 'C42D1F57A9936520';

not working same message.


select * from dba_profiles where profile='DEFAULT' and  RESOURCE_NAME in ('PASSWORD_REUSE_TIME') ;

alter profile DEFAULT limit PASSWORD_REUSE_TIME UNLIMITED;


alter user IPPADM identified by values 'C42D1F57A9936520';

No help not working.

Finally execute the following:

How to set password expiry to unlimited for a schema account in the DB and reset with the same old password:

select profile from dba_users where username='IPPADM'

DEFAULT

Check the different resource and limit for this profile.

select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME Like 'PASS%'

PASSWORD_LIFE_TIME
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
PASSWORD_VERIFY_FUNCTION
PASSWORD_LOCK_TIME
PASSWORD_GRACE_TIME

Change all the profile limit to unlimited including PASSWORD_VERIFY_FUNCTION which should be set to null.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;




How to set a samll amount for account lock time for a DB user:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 0.001;



How to change the password expiry to unlimited for a service account:

Check the current status of the user say oracle.


[root@pdevspdb ~]# chage -l oracle
Last password change                                    : May 04, 2016
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 99999
Number of days of warning before password expires       : 7


chage -I -1 -m 0 -M 99999 -E -1 username

How to do a proper cleanup of Oracle Stream configuration for a fresh install of Oracle Streams:

How to do a proper cleanup of Oracle stream configuration for a fresh install of Oracle Streams:


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

EXEC DBMS_APPLY_ADM.STOP_APPLY (APPLY_NAME => 'APPLY_STREAM');

EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE (CAPTURE_NAME => 'CAPTURE_STREAM');

begin
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(PROPAGATION_name  => 'pdsmar_TO_nixar');
end;
/

exec DBMS_PROPAGATION_ADM.DROP_PROPAGATION(propagation_name => 'pdsmar_TO_nixar',drop_unused_rule_sets => TRUE);

EXEC DBMS_CAPTURE_ADM.DROP_CAPTURE(CAPTURE_NAME=>'CAPTURE_STREAM',drop_unused_rule_sets => TRUE);

EXEC DBMS_APPLY_ADM.DROP_APPLY(APPLY_NAME=>'APPLY_STREAM',drop_unused_rule_sets => TRUE);

connect / as sysdba

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.APPLY_q',cascade=>true,drop_unused_queue_table=>true);

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.CAPTURE_q',cascade=>true,drop_unused_queue_table=>true);


drop user STRMADMIN cascade;


----
If above steps  required repeated for multiple environments we can write something line tis:

 BEGIN

    DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

  EXCEPTION
   WHEN OTHERS THEN



     -- Drop the APPLY queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_APPLY_Q1',cascade=>true,drop_unused_queue_table=>true);

     -- Drop the CAPTURE queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_CAPTURE_Q1',cascade=>true,drop_unused_queue_table=>true);
  END;
/

drop user STRMADMIN cascade;

Resolving issues for Oracle_Home is not set for Toad even Oracle client is available

Most often after installing Toad developer use to face this problem while starting Toad for Oracle.

Resolution:
Install Oracle 32 bit client on your machine for the same OS if Toad is below version 11.

Install Oracle 64 bit client in your machine for the same OS if Toad version is atleast 11.0...