private dblink

DB ldccm

User ldccm

Private Dblink cmldlink connect to LD database

Now how user will access ldfibs table of other database using private link.

Create view vwldfibs as select * from ldfibs@cmldlimk;

Grant view vwldfibs to user1;

Calculating ages in years

Calculating ages in years isn't as easy as

( current date - birth date ) / 365

@connor_mc_d has a solution - convert the dates to numbers:

trunc ((

 to_number (to_char ( sysdate, 'YYYYMMDD' )) - 

 to_number (to_char ( birth, 'YYYYMMDD' ))

 ) / 10000

)

https://t.co/gVoEijf874 https://t.co/6iSyzBXdUh

Great thanks Connor Sir

Gather Stats of Table Partition Latest/Current Month Partition

 

BEGIN

 FOR CUR_REC IN (select * from (select table_owner,table_name,partition_name,partition_position,last_analyzed from dba_tab_partitions where table_name  ='POWEROFATTORNEYSTOCKS' order by partition_position desc) where rownum=1) LOOP

    BEGIN

 EXECUTE IMMEDIATE 'begin dbms_stats.gather_table_stats(ownname =>''LDBO'' ,Tabname =>''' || cur_rec.table_name || ''',Partname =>''' || cur_rec.partition_Name || ''',cascade => true, DEGREE=>DBMS_STATS.DEFAULT_DEGREE); end;';

    EXCEPTION

      WHEN OTHERS THEN

        NULL;

    END;

  END LOOP;

END;

/

Kill Third Party Tool Like PLSQL Developer Session

BEGIN

 FOR CUR_REC IN (select ss.username,ss.terminal,ss.sid,ss.serial#,ss.inst_id from gv$session ss where module='PL/SQL Developer') LOOP

  BEGIN

 EXECUTE IMMEDIATE 'alter system disconnect session''' || CUR_REC.sid || ',' || CUR_REC.serial# || ',@' || CUR_REC.inst_id || ''' immediate';

 EXCEPTION

      WHEN OTHERS THEN

        NULL; 

    END;

  END LOOP;

END;

/