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".


  • 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? 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


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.