ایجاد database link

create public database link MASTER_REMOTE connect to USER1 identified by PASS using 'PDB1';

تغییر owner جداول یک سری از synonymها

set serveroutput on; 
set verify off; 

declare 

  sql_str varchar2(2000); 
  cursor cur_syn is
    select owner,synonym_name,table_owner,table_name
      from dba_synonyms
     where table_owner='USER1';   

begin

  for rec_syn in cur_syn loop
    sql_str := 'CREATE OR REPLACE SYNONYM ' || rec_syn.owner ||'.'|| rec_syn.synonym_name || ' for USER2.' || rec_syn.TABLE_NAME ;
    execute immediate sql_str;
    dbms_output.put_line (sql_str);
  end loop; 

end;
 

online move datafile in 12c

SQL> alter database move datafile

'+DATA/FBPPRD/5E0295675F4B5286E0534911140A1EE8/DATAFILE/test_sdat.302.993730507'

to

'+SDATA';

convert timzone

select to_char((from_tz(cast(sysdate as timestamp), 'UTC') at time zone

         'ASIA/Tehran'),

         'yyyy-mm-dd hh24:mi:ss')

  from dual;