Solve ORA-39170: Schema expression 'OPS' does not correspond to any schemas

Error Description:
Data pump expdp failed with following error message.
$ expdp directory=exp_dir schemas=ops$jobs dumpfile=exp.dmp logfile=exp.log
Export: Release - Production on Thu Sep 4 07:30:24 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Release - 64bit Production
ORA-39001: invalid argument value
ORA-39170: Schema expression 'OPS' does not correspond to any schemas.

How to Find out / Check SGA and PGA size of Oracle Database?

SGA Size 

Generally SGA_TARGET shows the value of SGA. It can be increased up to sga_max_size. But if you have enabled automatic memory management the (non-zero values for Memory_ parameters) the value for the memory_target will be consisderred for the SGA.

Find out Database Growth Month by Month Using V$datafile - Creation_time

In v$datafile there is a field called creation_time and this field is having the datafile creation date. Suppose if you are database's space addition generally happening by adding the data file then you can easily find out the database growth using this column.

This method will not be effective in the following cases.

a. If you are resizing the existing file this method will not be effective as it records the datafile creation date.

How to Check the Required Data is Present in Flashback Database or not for Recovery?

Here is the query to find out whether you can recover the lost data using the flashback database option.

SQL>select to_char(OLDEST_FLASHBACK_TIME,'dd-mm-yyyy hh:mi'), to_char(sysdate,'dd-mm-yyyy hh:mi') FROM V$FLASHBACK_DATABASE_LOG;

---------------- ----------------
28-06-2014 01:34 28-08-2014 02:19

Here in above result we have the data from 28th of June as the transaction is very less with the database. Almost 2 months data can be flashback.


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2) Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer