Real world problems

Dynamic PL/SQL

Dynamic PL/SQL declare cursor cur_obj is select object_name from user_objects where object_type = ‘TABLE’ and status = ‘INVALID’ and object_name = ‘IF_TASK_TODO’; v_usr varchar2(15); v_dsql varchar2(256); begin select user into v_usr from dual; for rec in cur_obj loop execute immediate ‘begin dbms_aqadm.drop_queue_table ( queue_table => ”’||v_usr||’.’||rec.object_name||”’,force=>true); end;’; end loop; end; / More dynamic PL/SQL SET …

SQLPLUS

SQLPLUS connect | sqlplus / as sysdba — find some useful information SQL> define DEFINE _DATE = “05-SEP-17” (CHAR) DEFINE _CONNECT_IDENTIFIER = “DB11G” (CHAR) DEFINE _USER = “SYS” (CHAR) DEFINE _PRIVILEGE = “AS SYSDBA” (CHAR) DEFINE _SQLPLUS_RELEASE = “1101000600” (CHAR) DEFINE _EDITOR = “ed” (CHAR) DEFINE _O_VERSION = “Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 …

Backup control file

Backup control file 1. copy and past the file. SQL> show parameter control NAME TYPE VALUE ———————————— ———– —————————— control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/DB11G/control01.ctl, /u01/app/oracle/oradata/DB11G/control02.ctl, /u01/app/oracle/oradata/DB11G/control03.ctl control_management_pack_access string DIAGNOSTIC+TUNING copy the control file to a different location now. 2. backup control file to trace. select logical_path||’/’||logical_file trace_file from x$dbgdirext where logical_file like ‘%.trc’ and rownum <= …

Configuring Archive Log Destinations

Configuring Archive Log Destinations LOG_ARCHIVE_DEST_n where n can be a numeric value 1 – 10. SQL> show parameter log_archive_dest; NAME TYPE VALUE ———————————— ———– —————————— log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_2 string log_archive_dest_3 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string Find the values of the parameters. select * from …