Home » SQL & PL/SQL » Client Tools » SQLDEVELOPER Could not copy database (ORACLE11 EXPRESS EDITION)
SQLDEVELOPER Could not copy database [message #654201] Thu, 28 July 2016 04:50 Go to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Hi


I could not copy database with SQLDeveloper Tools

I make
-> Tools
-> Copy database

Select source and destionation
Select copy all objects


Then Next button and Next too
But when i make "Search" nothing" is visible ?? Why ?
I have tested with normal user from source Database then with SYS and SYSTEM user.

Perhaps rights and role problem, but which !

Regards
Phil
Re: SQLDEVELOPER Could not copy database [message #654219 is a reply to message #654201] Thu, 28 July 2016 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SYS has no rights problem.

Re: SQLDEVELOPER Could not copy database [message #654224 is a reply to message #654219] Thu, 28 July 2016 07:35 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
OK, but with SYS user i could not "copy all object" Too ? Do you have an idea ?
Re: SQLDEVELOPER Could not copy database [message #654236 is a reply to message #654224] Thu, 28 July 2016 11:39 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
GRANT SELECT ON SYS.ALL_QUEUE_TABLES TO xxxx;

Quote:
rreur SQL : ORA-04063: view "SYS.ALL_QUEUE_TABLES" has errors
04063. 00000 - "%s has errors"
*Cause: Attempt to execute a stored procedure or use a view that has
errors. For stored procedures, the problem could be syntax errors
or references to other, non-existent procedures. For views,
the problem could be a reference in the view's defining query to
a non-existent table.
Can also be a table which has references to non-existent or
inaccessible types.
*Action: Fix the errors and/or create referenced objects as necessary.
Re: SQLDEVELOPER Could not copy database [message #654238 is a reply to message #654236] Thu, 28 July 2016 12:13 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
SYS.LOGSTDBY_SUPPORT_TAB_11_2B has errors

An Idea to resolve problem ?
Regards
Re: SQLDEVELOPER Could not copy database [message #654239 is a reply to message #654238] Thu, 28 July 2016 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

I don't know what you have or what you do.

My car has error.
tell me how to make my car go.
Re: SQLDEVELOPER Could not copy database [message #654249 is a reply to message #654238] Thu, 28 July 2016 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And as always, what is your version, with 4 decimals.

Re: SQLDEVELOPER Could not copy database [message #654273 is a reply to message #654249] Fri, 29 July 2016 03:02 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Oracle VERSION
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Re: SQLDEVELOPER Could not copy database [message #654275 is a reply to message #654273] Fri, 29 July 2016 03:08 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
select object_type, status, count(*)
from sys.all_objects
group by object_type, status
order by object_type, status
__
PROCEDURE INVALID 4
SYNONYM INVALID 207
PACKAGE BODY INVALID 93
PACKAGE INVALID 1
MATERIALIZED VIEW INVALID 1
FUNCTION INVALID 7
TRIGGER INVALID 33
VIEW INVALID 256
PACKAGE VALID 966
FUNCTION VALID 215
RULE SET VALID 13
LOB VALID 156
TYPE BODY VALID 153
RESOURCE PLAN VALID 10
JOB VALID 14
RULE VALID 1
SEQUENCE VALID 190
TYPE VALID 1624
LIBRARY VALID 172
CONTEXT VALID 4
INDEX PARTITION VALID 286
TRIGGER VALID 591
LOB PARTITION VALID 1
JOB CLASS VALID 14
PROGRAM VALID 19
QUEUE VALID 17
SCHEDULER GROUP VALID 4
XML SCHEMA VALID 31
SCHEDULE VALID 3
MATERIALIZED VIEW VALID 2
TABLE VALID 1982
EDITION VALID 1
PACKAGE BODY VALID 801
CLUSTER VALID 10
TABLE SUBPARTITION VALID 32
DESTINATION VALID 2
INDEX VALID 2935
PROCEDURE VALID 120
VIEW VALID 3873
UNDEFINED VALID 11
INDEXTYPE VALID 8
WINDOW VALID 9
CONSUMER GROUP VALID 25
TABLE PARTITION VALID 262
SYNONYM VALID 3521
DIRECTORY VALID 4
EVALUATION CONTEXT VALID 9
OPERATOR VALID 45
Re: SQLDEVELOPER Could not copy database [message #654286 is a reply to message #654275] Fri, 29 July 2016 06:08 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Ok I search to copy all objets with SqlDevelopper from one database to Another and it seems SqlDevloper could not because
PL/SQL: ORA-04063: view "SYS.LOGSTDBY_SUPPORT_TAB_11_2B" has errors
And it seems there is INVALID objects too

Regards
Re: SQLDEVELOPER Could not copy database [message #654294 is a reply to message #654286] Fri, 29 July 2016 08:02 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
pcouas wrote on Fri, 29 July 2016 04:08
Ok I search to copy all objets with SqlDevelopper from one database to Another and it seems SqlDevloper could not because
PL/SQL: ORA-04063: view "SYS.LOGSTDBY_SUPPORT_TAB_11_2B" has errors
And it seems there is INVALID objects too

Regards


Please step back & think about Oracle's reality.
In order to use SQLDeveloper an exist database must already exist so that you can connect to it.
This means that all the SYS objects must already exist to have a functioning database; so there is NO need to "copy" any objects owned by SYS.

What problem are you really trying to solve?
Re: SQLDEVELOPER Could not copy database [message #654303 is a reply to message #654294] Fri, 29 July 2016 10:18 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Hi

Yes i could connect to this database and this Production database is running
My original problem is i search copying all objects from this database with SqlDevelopper to another that's not running.

I have found on oracle error ORA-04063: view "SYS.ALL_QUEUE_TABLES" has errors with command SELECT * FROM SYS.ALL_QUEUE_TABLES;

Then i found invalid objects with Query

select object_type, status, count(*)
from sys.all_objects
group by object_type, status
order by object_type, status

Regards
Re: SQLDEVELOPER Could not copy database [message #654351 is a reply to message #654303] Sun, 31 July 2016 23:47 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
It seemq problem is corrupt data view in Oracle Express edition.
I have tried to compil object without succes

Regards
Re: SQLDEVELOPER Could not copy database [message #654373 is a reply to message #654351] Mon, 01 August 2016 07:13 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
pcouas wrote on Sun, 31 July 2016 23:47
It seemq problem is corrupt data view in Oracle Express edition.
I have tried to compil object without succes

Regards
Please provide your evidence.

Re: SQLDEVELOPER Could not copy database [message #654377 is a reply to message #654373] Mon, 01 August 2016 08:33 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
select object_type, status, count(*)
from sys.all_objects
group by object_type, status
order by object_type, status
____________________________________________

PROCEDURE INVALID 4
SYNONYM INVALID 203
PACKAGE BODY INVALID 93
PACKAGE INVALID 1
MATERIALIZED VIEW INVALID 1
FUNCTION INVALID 7
TRIGGER INVALID 33
VIEW INVALID 256
Re: SQLDEVELOPER Could not copy database [message #654378 is a reply to message #654377] Mon, 01 August 2016 08:53 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
have tried too

select NULL ENTRY, inner.* from (



select null name, -1 LINE, -1 COL, null USAGE,



case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end TYPE,



OWNER, case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end OBJECT_TYPE,



obj.OBJECT_NAME



from sys.dba_objects obj where rownum <= 99999999 and obj.object_type != 'TABLE PARTITION' and obj.object_type != 'TABLE SUBPARTITION' and obj.object_type != 'JAVA CLASS' AND NOT EXISTS (SELECT 1 FROM SYS.ALL_MVIEWS WHERE MVIEW_NAME = obj.OBJECT_NAME AND OWNER = obj.OWNER and obj.object_type = 'TABLE') AND NOT EXISTS (SELECT 1 from all_queue_tables WHERE QUEUE_TABLE = obj.OBJECT_NAME AND OWNER = obj.OWNER and obj.object_type = 'TABLE') and object_type in ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13 ) and owner in ( :14 )



union all



select null name, -1 LINE, -1 COL, null USAGE,



'MATERIALIZED VIEW LOG' TYPE, log_owner OWNER,



'MATERIALIZED VIEW LOG' OBJECT_TYPE, master||'('||LOG_TABLE||')' OBJECT_NAME



from all_snapshot_logs where rownum <= 99999999 and log_owner in ( :15 )



union



select null name, -1 LINE, -1 COL, null USAGE,



'DATABASE LINK' TYPE, owner OWNER,



'DATABASE LINK' OBJECT_TYPE, db_link OBJECT_NAME



from dba_db_links where rownum <= 99999999 and owner in ( :16 )



) inner



where rownum <= 99999999



order by owner, type, name, CASE WHEN USAGE = 'DECLARATION' OR USAGE = 'DEFINITION' THEN 0 ELSE 1 END, usage, object_name, line, col;





Result

ORA-04063: view "SYS.ALL_QUEUE_TABLES" has errors

04063. 00000 - "%s has errors"

Regards
Re: SQLDEVELOPER Could not copy database [message #654381 is a reply to message #654378] Mon, 01 August 2016 10:02 Go to previous message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

FORMAT!!!
Previous Topic: ora-28040 no matching authentication protocol
Next Topic: Tutorial for Toad
Goto Forum:
  


Current Time: Thu Dec 14 04:54:58 CST 2017

Total time taken to generate the page: 0.07528 seconds