Archives June 2021

ORA-39001: invalid argument value-ORA-39035: Data filter SUBQUERY has already been specified.

Cause:

The error occur due to case sensitive characters used in query parameter of expdp

[oracle@orcl19x ~]$ expdp directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log tables=sys.TEST query="\'where num > 2\'" REUSE_DUMPFILES=Y

Export: Release 12.1.0.2.0 - Production on Tue Jun 29 23:55:01 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

Same happens for include and exclude parameters as well without specifying object name

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=tabx.dmp logfile=tabx.log  exclude=index,constraint,statistics

Export: Release 12.1.0.2.0 - Production on Fri Aug 13 18:42:19 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=exp dumpfile=tabx.dmp logfile=tabx.log exclude=index,constraint,statistics
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Fri Aug 13 18:42:44 2021 elapsed 0 00:00:20

Workaround:

Check for the extra quotes and characters in query parameter

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log tables=TEST.test query="'where num > 2'" REUSE_DUMPFILES=Y

Export: Release 12.1.0.2.0 - Production on Tue Jun 29 23:57:17 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log tables=TEST.test query='where num > 2' REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."TEST"                               5.617 KB      10 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /data01/view_exp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jun 29 23:57:43 2021 elapsed 0 00:00:20

ORA-00955: name is already used by an existing object

Cause:

The error states that the object which you try to create already exist on the database and so the database is not allowing to create a duplicate object with the same name

SQL> create view test_vu as select * from test;
create view test_vu as select * from test
                                     *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Workaround:

Use different new name for the object

SQL> create view test_vu1 as select * from test;

View created.

ORA-39001: invalid argument value-ORA-39000: bad dump file specification-ORA-31641: unable to create dump file “/data01/view_exp.dmp”-ORA-27038: created file already exists-Additional information: 1

Cause:

Error itself says that the dumpfile specified already exist on the directory

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Tue Jun 29 22:33:02 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/data01/view_exp.dmp"
ORA-27038: created file already exists
Additional information: 1

Workaround:

Locate to the export directory and remove the old dump file. Then export the object or specify a new dump file name during export

[oracle@orcl19x ~]$ rm -rf /data01/view_exp.dmp

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Tue Jun 29 22:45:07 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Job "SYS"."SYS_EXPORT_TABLE_01" completed

Another option is to use to overwrite the existing dump files

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=sys.test_vu REUSE_DUMPFILES=Y

Export: Release 12.1.0.2.0 - Production on Tue Jun 29 23:36:07 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Job "SYS"."SYS_EXPORT_TABLE_01" completed

EXPORT VIEWS USING DATAPUMP

In this article, we will see how to export a view using datapump from database using datapump

[oracle@orcl19x ~]$ expdp -help|egrep '^VIEWS|view'

VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.

Create a view for exporting from database

SQL> create view test_vu as select * from test;

View created.

You can also get the metadata of view using dbms_metadata package and get the script to create the view on another database. But this will be more manual way to create if there are multiple views to be exported

SQL> set numwidth 50
SQL> set long 10000000
SQL> select dbms_metadata.get_ddl('VIEW','TEST_VU') from dual;

DBMS_METADATA.GET_DDL('VIEW','TEST_VU')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."TEST_VU" ("NAME", "NUM") AS

  select "NAME","NUM" from test

In oracle database 12c version, a new feature was introduced to export and import views called ‘views_as_tables‘. Export the views using expdp like a table. By default if you dont specify any schema name, ‘sys’ user will be taken

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Mon Jun 28 23:28:09 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SYS"."TEST_VU"                             5.656 KB      13 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /data01/view_exp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 28 23:29:00 2021 elapsed 0 00:00:09

You can also import it directly to the database

[oracle@orcl19x /]$ impdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=imp_view_exp.log views_as_tables=test_vu

Import: Release 12.1.0.2.0 - Production on Mon Jun 28 23:50:12 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=imp_view_exp.log views_as_tables=test_vu
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."TEST_VU"                             5.656 KB      13 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jun 28 23:50:54 2021 elapsed 0 00:00:01

ORA-39002: invalid operation-ORA-39070: Unable to open the log file.-ORA-29283: invalid file operation-ORA-06512: at “SYS.UTL_FILE”, line 536-ORA-29283: invalid file operation

Cause:

While trying to export using expdp , the below error was thrown

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Mon Jun 28 23:25:14 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Workaround:

Check the export directory from database

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
DATA_PUMP_DIR
/app01/oracle/product/base/admin/orcl12x/dpdump/
            0

Check if the directory exist in filesystem

[oracle@orcl19x ~]$ ls -lrt /app01/oracle/product/base/admin/orcl12x/dpdump/
ls: cannot access /app01/oracle/product/base/admin/orcl12x/dpdump/: No such file or directory

Give directory which already exist on your filesystem. Also check for oracle ownership and permissions on the export directory

[oracle@orcl19x ~]$ ls -lrt /data01/
total 10292

[oracle@orcl19x /]$ ls -lrt |grep data01
drwxrwxrwx.   7 oracle oinstall  4096 Jun 28 23:28 data01


[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Mon Jun 28 23:28:09 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SYS"."TEST_VU"                             5.656 KB      13 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /data01/view_exp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 28 23:29:00 2021 elapsed 0 00:00:09

ORA-02097: parameter cannot be modified because specified value is invalid-ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

Cause:

When try setting up FRA, the below error is triggered due to size not specified

SQL> alter system set db_recovery_file_dest='/data01';
alter system set db_recovery_file_dest='/data01'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

Workaround:

Specify the FRA size and set FRA location then

SQL> alter system set db_recovery_file_dest_size=5G;

System altered.


SQL> alter system set db_recovery_file_dest='/data01';

System altered.

DOES NULL VALUE WITH NOT IN OPERATOR RETURN ROWS?

Create a test table

SQL> create table test (name varchar2(10),num number(10));

Table created.

Insert some rows into it

SQL> insert all into test  values('xyz',1)
  2  into test  values('abc',2)
  3  into test  values('mno',3)
  4  into test  values('nhc',4)
  5  select * from dual;

4 rows created.

SQL> commit;

Commit complete.
SQL>  insert all into test  values('xyz',5)
  2  into test  values('abc',6)
  3  into test  values('mno',7)
  4  into test  values('nhc',8)
  5  into test  values('tnc',9)
  6  into test  values('jmk',10)
  7  select * from dual;

6 rows created.

SQL> commit;

Commit complete.

Select all the rows from table

SQL> select * from test;

NAME              NUM
---------- ----------
xyz                 1
abc                 2
mno                 3
nhc                 4
xyz                 5
abc                 6
mno                 7
nhc                 8
tnc                 9
jmk                10

Select rows with either ‘in’ or ‘exists’ clause

SQL>  select * from test where num in (1,2);

NAME              NUM
---------- ----------
xyz                 1
abc                 2

Select the same with ‘not in’ clause

select * from test where num not in (1,2);

NAME              NUM
---------- ----------
mno                 3
nhc                 4
xyz                 5
abc                 6
mno                 7
nhc                 8
tnc                 9
jmk                10

8 rows selected.

Add null to the values and see the results. You dont see any output with zero rows selected. Why is it? Why the other two values 1 and 2 rows are not returned? Here as we dont have null values in the num column, the boolean expression should not return true or false. So oracle does not return any rows here

SQL> select * from test where num not in (1,2,null);

no rows selected

Do the same with ‘in’ clause adding a null value. You can see the same results as earlier

SQL> select * from test where num in (1,2,null);

NAME              NUM
---------- ----------
xyz                 1
abc                 2

INSERT MULTIPLE ROWS IN TABLE USING INSERT ALL ORACLE

Use ‘insert all’ to insert multiple rows in a table at same time

Create a test table

SQL> create table test (name varchar2(10),num number(10));

Table created.

Insert multiple rows in one table at same time using insert all. Otherwise you would have to use insert statement for individual statements multiple times

SQL>  insert all into test  values('xyz',5)
  2  into test  values('abc',6)
  3  into test  values('mno',7)
  4  into test  values('nhc',8)
  5  into test  values('tnc',9)
  6  into test  values('jmk',10)
  7  select * from dual;

6 rows created.

SQL> commit;

Commit complete.

Select the rows for verification

SQL> select * from test where num not in (1,2);

NAME              NUM
---------- ----------
mno                 3
nhc                 4
xyz                 5
abc                 6
mno                 7
nhc                 8
tnc                 9
jmk                10

8 rows selected.

To insert multiple rows into multiple tables

SQL> insert all into test  values('xyz',5)
  2  into test  values('abc',6)
  3  into test1 values('mno',7)
  4  select * from dual;

3 rows created.

SQL> commit;

Commit complete.

ORA-00922: missing or invalid option

This error is caused by syntax error or missing keyword during sql query execution

SQL> alter system reset "_OPTIMIZER_ADAPTIVE_PLANS";
alter system reset "_OPTIMIZER_ADAPTIVE_PLANS"
*
ERROR at line 1:
ORA-00922: missing or invalid option

Check the correct syntax and reexecute the query

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

While startup of the instance, the deprecated message always pop up due to unsupported parameters in newer oracle version

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
Obsolete system parameters with specified values:
  _optimizer_adaptive_plans
End of obsolete system parameter listing
Deprecated system parameters with specified values:
  sql_trace
End of deprecated system parameter listing

Check the obsolete parameters

SQL> desc v$obsolete_parameter;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(64)
 ISSPECIFIED                                        VARCHAR2(5)
 CON_ID                                             NUMBER

SQL> select count(*) from v$obsolete_parameter;

  COUNT(*)
----------
       172

Filter out only the obsolete parameter with ISSPECIFIED as true

SQL> select * from v$obsolete_parameter where ISSPECIFIED='TRUE';

NAME                 ISSPE     CON_ID
-------------------- ----- ----------
_optimizer_adaptive_ TRUE           0
plans

You can also check the parameters from v$parameter and v$spparameter views

SQL> select name,value from v$parameter where isdeprecated='TRUE'; 
--v$spparameter

NAME                 VALUE
-------------------- --------------------
lock_name_space
instance_groups
resource_manager_cpu 1
_allocation

active_instance_coun
t

db_block_buffers     0
buffer_pool_keep
buffer_pool_recycle
log_archive_start    FALSE
cluster_database_ins 1
tances

fast_start_io_target 0
serial_reuse         disable
rdbms_server_dn
remote_os_authent    FALSE
sec_case_sensitive_l TRUE
ogon

unified_audit_sga_qu 1048576
eue_size

cursor_space_for_tim FALSE
e

plsql_v2_compatibili FALSE
ty

plsql_debug          FALSE
background_dump_dest /data01/product/19.0
                     .0/rdbms/log

user_dump_dest       /data01/product/19.0
                     .0/rdbms/log

commit_write
sql_trace            TRUE
parallel_adaptive_mu FALSE
lti_user

asm_preferred_read_f
ailure_groups


24 rows selected.

If you try to set a value to the deprecated parameter, then you get error message

SQL> alter system set "_optimizer_adaptive_plans"=false;
alter system set "_optimizer_adaptive_plans"=false
*
ERROR at line 1:
ORA-25138: _OPTIMIZER_ADAPTIVE_PLANS initialization parameter has been made obsolete

Remove the deprecated parameter from pfile and Create spfile from pfile

SQL> create spfile from pfile;

Bounce the instance