Useful DB query

Creating synonym from remote DB

-        1.   Create a Public database link on remote database.

        Example :   CREATE PUBLIC DATABASE LINK db_link_name CONNECT TO remote_username IDENTIFIED BY password USING 'remotedb';

-         2.  Once the link is created, we would create the synonym on remote database.

Example :  create synonym synonym_name for schema.table@db_link;

Does oracle allow multiple "WITH AS" in a single sql statement

Yes it allows :

WITH abc AS( select

             FROM ...)

, XYZ AS(select

               From abc ....) /*This one uses "abc" multiple times*/

              Select 

  From XYZ....   /*using abc, XYZ multiple times*/



Concatination :

update catentdesc a set (thumbnail,fullimage) = (select 'XXXXXX' || b.partnumber || '.jpg', 'XXXXXXXXXX/' || b.partnumber || '.jpg' from catentry b where a.catentry_id = b.catentry_id) where a.thumbnail is null;


REPLACE :
UPDATE catentdesc SET fullimage = REPLACE(FULLIMAGE, '11351', '10151') 

Size of table
with t1 as (SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, SUM(DS.BYTES) / (1024 * 1024) AS MB
  FROM USER_SEGMENTS DS
  WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM USER_TABLES) and SEGMENT_TYPE='TABLE'
 GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME
 order by SEGMENT_NAME) select * from t1 where t1.SEGMENT_NAME in ('USERREG','USERS','MBRATTRVAL','MBRATTR','MBRREL','ADDRESS','ADDRBOOK','ORDERS','ORDERITEMS','MBRROLE','WMMMAP','MEMBER','ORDITPROMOCD','OICOMPREL');