sqloraclesqlplusexpdp

EXPDP doesn't recognise user names

I'm trying run EXPDP on an Oracle 19 database but it doesn't recognize any of the schema names apart from sys.

select * from dba_users;

Query output

when run in Oracle SQL Developers returns all the users I'd expect to see but the same query run in sqlplus / as sysdba returns lots of entries like the attached image which seem to be missing any actual user details.

I'm new to database back ups and exports so I'm very likely missing something obvious but I haven't been able to figure it out.

expdp username/password schemas=user_1 directory=DATA_PUMP_DIR dumpfile=export_08_07_2024.dmp logfile=export_08_07_2024_export.log

ORA-39001: invalid argument value ORA-39170: Schema expression 'USER_1' does not correspond to any schemas.

I'm hoping to set this to run once a month and save the back ups but so far I haven't been able to get it to work.

--EDIT FOR MORE DETAILS--

select dbid, name, cdb from v$database; Gives the same output in sqlplus as in SQL Dev so they should be looking at the same database.

expdp system/passwd schemas=user_1 directory=DATA_PUMP_DIR dumpfile=export.dmp logfile=export.log ORA-39001: invalid argument value ORA-39170: Schema expression 'USER_1' does not correspond to any schemas.

I've tried running it as the system user since it doesn't recognise the account I'm actually trying to export.

Strangely I can get it to run using exp instead of expdp but it fails when it hits a table it doesn't like. So exp recognises the user/schema but expdp doesn't.

Solution

  • run the following cmd from cmd line.

    expdp scott/****@db_name(PDB Name ex orclpdb) schemas=scott directory=DATA_PUMP_DIR dumpfile=scott_07232024.dmp logfile=scott_07232024.log

    Export: Release 21.0.0.0.0 - Production on Tue Jul 23 16:27:44 2024 Version 21.3.0.0.0

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

    Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@orclpdb schemas=scott directory=JAGAN_DIR dumpfile=scott_07232024.dmp logfile=scott_07232024.log Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER ...SO ON until the end of script Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

    Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: C:\JAGAN\ORACLE_SCRIPTS\SCOTT_07232024.DMP Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jul 23 16:29:56 2024 elapsed 0 00:02:07