QUERY parameter in Oracle datapump import

Environment

Oracle 11.2.0.4

Symptoms

Using parameters
TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY="where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"

output
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
UDI-00014: invalid value for parameter, 'query'

I believe Oracle cannot identify a table of the first claim_ID.

Solutions

Syntax

QUERY = [schema.][table_name:] query_clause

Using [schema.][table_name:] for the same query
TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY=CDR.F_CLAIM_WORK_CAPACITY:"where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"

that works (the schema was remapped from CDR to Z)
. . imported "Z"."F_CLAIM_WORK_CAPACITY"    9.391 MB  154122 out of 157095 rows

The table alias used by Data Pump for the table being unloaded is KU$. Using KU$.
TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY="where KU$.claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"
works
. . imported "Z"."F_CLAIM_WORK_CAPACITY"     9.391 MB  154122 out of 157095 rows

It works for multiple tables too, elapsed 0 00:17:43
TABLES= CDR.F_CLAIM_WORK_CAPACITY,CDR.K_CLAIM_KEY_FIGURES
. . imported "Z"."K_CLAIM_KEY_FIGURES"     4.088 GB 36215408 out of 41843789 rows
. . imported "Z"."F_CLAIM_WORK_CAPACITY"   9.391 MB  154122 out of 157095 rows

The following two parameters do not work and return the same error:
UDI-00014: invalid value for parameter, 'query'

QUERY="where exists (select 1 from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"
QUERY="where exists (select claim_ID from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"
I understand that "select claim_ID" is ambiguously defined, but I expected "select 1" would work.
Anyway the following works fine.
QUERY="where exists (select rc.claim_ID from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"

. . imported "Z"."K_CLAIM_KEY_FIGURES"    4.088 GB 36215408 out of 41843789 rows
. . imported "Z"."F_CLAIM_WORK_CAPACITY"  9.391 MB  154122 out of 157095 rows

and it is a bit faster (elapsed 0 00:12:27) than "where claim_id in ()". Maybe just because of cache :-)

Cron jobs are no longer running

Environment: Red Hat Enterprise Linux Server release 6.6 (Santiago)

# crontab -e

Authentication token is no longer valid; new one required
You (root) are not allowed to access to (crontab) because of pam configuration.

there were no changes in /etc/security/access.conf

the problem is root`s expired password

# chage -l root

Last password change                                    : Mar 20, 2015

Password expires                                        : Apr 29, 2015

Password inactive                                       : never

Account expires                                         : never

Minimum number of days between password change          : 0

Maximum number of days between password change          : 40

Number of days of warning before password expires       : 7

# passwd root

and check 

# crontab -l

HTTP 500: Internal Server Error when login to a new cloned environment

Environment

Oracle e-Business Suite 12.1.3 (Oracle EBS)

Oracle Linux 5.7

Symptoms

The following error occurs when trying to login to the new cloned environment.

500 Internal Server Error

File application.log in $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1 shows

Caused by: oracle.apps.jtf.base.resources.FrameworkException:

ORA-01578: ORACLE data block corrupted (file # 12, block # 180784)

ORA-01110: data file 12: '/data/oradata/TEST12/APPS_TS_SUMMARY01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

Solution

To identify the corrupted object use:

select s.*

from dba_extents s

where file_id = 12 -- change

and 180784 -- change

between block_id and (block_id + blocks -1);

It happened to be APPLSYS.WF_LOCAL_USER_ROLES

The solution is described in Oracle Support Doc ID 781413.1

sqlplus apps/password

set serveroutput on size 100000;

declare

begin

WF_LOCAL_SYNCH.BULKSYNCHRONIZATION(

P_ORIG_SYSTEM => 'ALL',

P_PARALLEL_PROCESSES => null,

P_LOGGING => null,

P_RAISEERRORS => TRUE);

exception

when others then

dbms_output.put_line('sqlerrm = ' || sqlerrm);

end;

/

Useful info

Partitions in Workflow Local Tables are Automatically Switched to NOLOGGING (Doc ID 433280.1)

Http 500 : Internal Server Error When Login To A New Cloned Environment (Doc ID 781413.1)

How to find whether an oracle database patch was applied

Using SQL:
select * from sys.registry$history;
It is useful for patch set levels, but not for a particular bug/patch.

OPatch, in Linux
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory | grep something
some useful flags
-bugs_fixed Reports bugs fixed by installed patches with bug descriptions and extra info
-patch ..order based on installed time
-patch_id ..order based on patch numbers

In OEM 12c
Targets > All Targrets > 
Targer Type > Others > Oracle Home > click your Target
on Oracle Home page, see tab Patches Applied. In the table below, you can find Bugs Fixed, Files and Components.

For multiple targets, you can create an OEM job to run a SQL script on multiple databases or opatch command on multiple hosts.

In OEM 12c
Enterprise > Configuration > Inventory and Usage Details
Show "Database Installations"
In the table below, column "Patches Applied", click "Yes" (or No, but in this case there is nothing to see)

Compiling public synonyms

Environment: Oracle database 11.2, Toad for Oracle 12.6.0.53

There is an invalid public synonym F_GET_PARTY_NAME. The target object exists and valid.
select s.* , o.*
--'alter public synonym ' || synonym_name || ' compile;'
from dba_synonyms s, dba_objects o
where s.owner=o.owner and s.synonym_name=O.OBJECT_NAME
and o.status ='INVALID' 
and s.owner='PUBLIC'
;
When connected as SYSTEM or SYS in “TOAD Script runner” or in “TOAD for Oracle 12.6.0.53” (looks like a TOAD’s bug)
alter public synonym F_GET_PARTY_NAME compile;

alter public synonym
Error at line 1
ORA-00995: missing or invalid synonym identifier

F_GET_PARTY_NAME compile;
Error at line 1
ORA-00900: invalid SQL statement

When connected as SYSTEM in sqlplus
alter public synonym F_GET_PARTY_NAME compile;
alter public synonym F_GET_PARTY_NAME compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

When connected as SYS in sqlplus
SQL> alter public synonym F_GET_PARTY_NAME compile;

Synonym altered.

Validating CSV file with regular expressions / Проверка CSV файла регулярным выражением

The CSV file does not contain double quote.
14 commas (field delimiter) are expected in each row.
A row delimiter is CRLF (\r\n).

Searching for a row with 15 commas in Notepad++ :
^([^,\r]*,){15}
[^,\r]* matches any char except comma and CR