Oracle-Basics And unix: January 2017

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;

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

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

Oracle-Basics And unix

Monday, January 9, 2017

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

How to display column values in row using sql

Total Pageviews

Blog Archive

About Me