Oracle DBA Place

Hi, In our lab environments we test Data Guard on a daily basis, and we frequently “play” with failover, switchover, and flashback . The output of this playground is that we have some leftovers in the ASM; we call these leftovers orphan files . To solve this, I created SQL to query ASM views against database views. This query should run on the database (not ASM). SET VERIFY OFF SET LINESIZE 200 SET SERVEROUTPUT ON SET PAGESIZE 50000 DECLARE    cmd   CLOB; BEGIN    FOR c IN (SELECT name Diskgroup                FROM V$ASM_DISKGROUP)    LOOP       FOR l          IN (SELECT 'rm ' || files files                FROM                     (SELECT '+' || c.Diskgroup || files files, TYPE                        ...

When a given tns alias is not working, via SQL*Plus, database link or within data guard, traditionally I troubleshoot connectivity via tnsping utility, at first just to understand the target host, port and service name. In oracle 12.2 we have a new function called DBMS_TNS.RESOLVE_TNSNAME that enables to query the return string as we receive it from tnsping. Using oracle utility tnsping from the command line $ tnsping stndby_db TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 13-NOV-2017 14:45:39 Copyright (c) 1997, 2016, Oracle.  All rights reserved. Used parameter files: /oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stndby_host)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stndby_db))) OK (0 msec) Using dbms_tns SQL> select dbms_tns.resolve_tnsname ('stndby_db') as TNS_ALIAS from dual; ...

SQL script for calculating the total size of RMAN backups col TYPE for a20 Heading "Backup Type" col completion_time Heading "Completion Time" col MB for 99999999999  Heading "Size (MB)" col BCPTIME for 99999999999 Heading "Backup Time (minutes)" SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time        , TYPE        , ROUND (SUM (bytes) / 1048576)          MB        , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME     FROM (SELECT CASE                     WHEN s.backup_type = 'L' THEN 'Archive Log' --                    WHEN s.controlfile_included = 'YES' THEN 'Control File'                  WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'       ...

Wanting to count how many rows in emp table SQL> SELECT COUNT (*) FROM emp; COUNT(*) ---------- 100 Cool... now let me see how much is 5 percent of emp SQL> SELECT COUNT (*) FROM emp 2 FETCH FIRST 5 PERCENT ROWS ONLY; COUNT(*) ---------- 100 Mmmmm.... it can't be also 100 ... somthing is wierd .. Let's try other way SQL> SELECT COUNT (*) 2 FROM ( SELECT empno 3 FROM emp 4 FETCH FIRST 5 PERCENT ROWS ONLY); COUNT(*) ---------- 5 Cool, now it is working :) Now let's try in PL/SQL SQL> DECLARE 2 l_Percent_to_fetch PLS_INTEGER; 3 l_cnt PLS_INTEGER; 4 BEGIN 5 SELECT COUNT (*) 6 INTO l_cnt 7 FROM ( SELECT empno 8 FROM emp 9 FETCH FIRST l_Percent_to_fetch PERCENT ROWS ONLY); 10 END; 11 / DECLARE * ERROR at line 1: ORA-03113: end-of-file on communication channel ...

I am used to relay on tools for formatting and indenting SQL or PL/SQL code. Looking for the same for linux scripts (especially bash) I found the following code fmt.script #!/usr/bin/env perl # fmt.script - format scripts in awk, csh, ksh, perl, sh # # we do: # standardize indentation (an indent is one tab by default) # strip trailing whitespace # change ${var} to $var where possible # change ">x" to "> x" for shell scripts # change "[ ... ]" to "test ..." for Bourne shell scripts # # we may do someday, but these are harder: # convert $VAR to $var unless a setenv or default environment variable # possibly prepending stuff from template.sh # "if ... \nthen", for ... in\ndo", "while/until ... \ndo", "fn()\n{" # # to have fmt.script reformat itself (a fair test, yes?) try: # fmt.script fmt.script fmt.script.new # use tabs for indents # fmt.script -s4 fmt.script fmt.script.new # in...

Extract the filename from a full file path unix select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) FROM dba_data_files; windows select substr(file_name,(instr(file_name,'\',-1,1)+1),length(file_name)) FROM dba_data_files; Validates a long filename using Windows' rules: select file_name from table_of_files WHERE not REGEXP_LIKE(file_name,'^[^\\\./:\*\?\" \|]{1}[^\\/:\*\?\" \|]{0,254}$'); combinning these two SQLs: WITH files AS ( select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) base_filename FROM dba_data_files) select base_filename from files WHERE REGEXP_LIKE(base_filename,'^[^\\\./:\*\?\" \|]{1}[^\\/:\*\?\" \|]{0,254}$');