0

How can I use the following query with database link:

set pages 999
col "Size MB" format 999,999,999
col "Objects" format 999,999,999
col OWNER for a25
select obj.owner "Owner", 
       obj_cnt "Objects", 
       decode(seg_size, NULL, 0, seg_size) "Size MB" 
  from (select owner, count(*) obj_cnt 
          from dba_objects 
         group by owner) obj, 
       (select owner, ceil(sum(bytes)/1024/1024) seg_size 
          from dba_segments 
         group by owner) seg 
 where obj.owner=seg.owner(+) 
 order by 3 desc, 2 desc, 1;

I tried different ways but in all I got the error ORA-00933 "SQL command not properly ended".

Regards.

Improve this question
7
  • What do you mean 'use it with a database link'? There's no reference to a db link in there. And as far as I can tell, there should be no need for one. A database link is to allow one database to act as a client to another, and it is accessed by appending the name of the db link to the name of the table in the remote database: SELECT * FROM REMOTE_TABLE@LINK_NAME;
    – EdStevens
    Aug 28, 2021 at 20:21
  • @EdStevens Hi, actually here we will compare two databases. I want to see the result of remote vs current database using minus for this query. If you know how I can do this with the select I sent, can you help? Also, I already created a database link. I'm asking how can I use it with this query.
    – jrdba123
    Aug 28, 2021 at 20:40
  • What line and column are you getting an error on? The query you posted appears to work for me. Are you running a different query to try to incorporate the database link?
    – Justin Cave
    Aug 28, 2021 at 23:14
  • @JustinCave The query works of course. When I use DBLINKNAME after from, it doesn't work. I'm asking how can I do this, that's all.
    – jrdba123
    Aug 29, 2021 at 10:31
  • every Oracle database has a table DUAL, that you can select by SELECT * FROM DUAL;. Do you know how to select the DUAL table from the remote database that can be accessed by the database link?
    – miracle173
    Aug 29, 2021 at 13:54

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.