How much memory is free on my Linux box?

 [109]$ free -g -t -o
             total       used       free     shared    buffers     cached
Mem:            47         44          2          0          0         40
Swap:            1          0          1
Total:          49         45          3

 In this case, 40 GB are cached, ready to be used.

Linux: I've deleted my file , but it is still in use?? lsof to the rescue

Example:

[steaua@MYDB]/u01/app/oracle/users/Florin >lsof |grep oradata |grep deleted
oracle     4028    oracle  300u      REG             253,69     2105344     49160 /oradata/ora_data05/refusg_MYDB_02.dbf (deleted)

How to find the objects/extents residing on a specific datafile?

This can be useful, for example, if we want to drop a datafile, and it has to be empty in order to do this.

How can we check what objects are there in a datafile?

select distinct a.owner,a.segment_name
from dba_extents a,dba_data_files b
where a.file_id=b.file_id and b.file_name=<your datafile name with path>;

Note: the query above is usually very slow, since it has to full scan a few big fixed tables.

AWR Global RAC report: awrgrpt.sql

Nice site:

http://www.dba-oracle.com/t_rac_awr_awrgrpt_global_report.htm

 SQL> @?/rdbms/admin/awrgrpt.sql

How to avoid Cartesian join between data dictionar views?

In one of our databases, which is 11.2.0.4, the below join is doing some merge join cartesian and it takes a very long time to complete.

SELECT
distinct sid, serial#, substr(username,1,12) username,
       substr(osuser,1,10) osuser, process, program, a.sql_id,hash_value,command
FROM v$session  a,v$sql b
WHERE sid > 7
  AND status = 'ACTIVE' and username like '$USER_NAME'
and a.sql_id=b.sql_id
/

Execution Plan

----------------------------------------------------------

Plan hash value: 4038803543

--------------------------------------------------------------------------------

| Id  | Operation                  | Name              | Rows  | Bytes | Cost  |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                   |     1 |    78 |     5 |

|   1 |  HASH UNIQUE               |                   |     1 |    78 |     5 |

|   2 |   NESTED LOOPS             |                   |     1 |    78 |     1 |

|   3 |    NESTED LOOPS            |                   |     1 |    74 |     1 |

|   4 |     MERGE JOIN CARTESIAN   |                   |    32 |  1344 |     1 |

|*  5 |      FIXED TABLE FULL      | X$KGLCURSOR_CHILD |     1 |    34 |     1 |

|   6 |      BUFFER SORT           |                   |    32 |   256 |       |

|*  7 |       FIXED TABLE FULL     | X$KSLWT           |    32 |   256 |     1 |

|*  8 |     FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)   |     1 |    32 |     1 |

|*  9 |    FIXED TABLE FIXED INDEX | X$KSLED (ind:2)   |     1 |     4 |     1 |

--------------------------------------------------------------------------------

 Since we don't want to collect fresh stats for the fixed objects, a quick fix is to run the alter session below:

SQL> alter session set "_optimizer_cartesian_enabled" =false;

 The new execution plan is below and the query is very fast.

Execution Plan

----------------------------------------------------------

Plan hash value: 2088405718

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name                      | Rows  | Bytes | Cost  |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                           |     1 |    78 |     4 |

|   1 |  HASH UNIQUE                |                           |     1 |    78 |     4 |

|   2 |   NESTED LOOPS              |                           |     1 |    78 |     1 |

|   3 |    NESTED LOOPS             |                           |     1 |    74 |     1 |

|   4 |     NESTED LOOPS            |                           |     1 |    40 |     1 |

|*  5 |      FIXED TABLE FULL       | X$KSLWT                   |    32 |   256 |     1 |

|*  6 |      FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)           |     1 |    32 |     1 |

|*  7 |     FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) |     1 |    34 |     1 |

|*  8 |    FIXED TABLE FIXED INDEX  | X$KSLED (ind:2)           |     1 |     4 |     1 |

-----------------------------------------------------------------------------------------

How to check for which tables is the supplemental logging enabled in the database?

Displaying Supplemental Log Groups at a Source Database

To check whether one or more log groups are specified for the table at the source database, run the following query:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20

SELECT 
    LOG_GROUP_NAME, 
    TABLE_NAME, 
    DECODE(ALWAYS,
             'ALWAYS', 'Unconditional',
             'CONDITIONAL', 'Conditional') ALWAYS,
    LOG_GROUP_TYPE
  FROM DBA_LOG_GROUPS;

Your output looks similar to the following:

                                     Conditional or
Log Group            Table           Unconditional  Type of Log Group
-------------------- --------------- -------------- --------------------
LOG_GROUP_DEP_PK     DEPARTMENTS     Unconditional  USER LOG GROUP
SYS_C002105          REGIONS         Unconditional  PRIMARY KEY LOGGING
SYS_C002106          REGIONS         Conditional    FOREIGN KEY LOGGING
SYS_C002110          LOCATIONS       Unonditional   ALL COLUMN LOGGING
SYS_C002111          COUNTRIES       Conditional    ALL COLUMN LOGGING
LOG_GROUP_JOBS_CR    JOBS            Conditional    USER LOG GROUP

Linux: how to transfer files and bypass the "jump" server

If we need to transfer files from the server A to the server C, but we need to jump through server B, since we don't have connectivity straight to C, we can use the setup below:

 On server A, put the lines below in the file : .ssh/config:

# MYPROJ

Host C*

ProxyCommand ssh serverB nc -w 43200 %h 22 2> /dev/null

StrictHostKeyChecking=no

How to determine the ascii value of a specific character within a row?

How to determine the ascii value of a specific character within a row?
Let's say we are searching for the character § inside the DB.



SQL> select dump('§') from dual;

DUMP('§')
---------------------
Typ=96 Len=2: 194,167

SQL> select * from k where col1 like '%'||chr(194)||'%'||chr(167)||'%';

COL1
--------------------------------------------------------------------------------
§

Thanks to the site below:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2143700800346224648

How often are the archived logs generated?

SQL> set line 200 pages 80
SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

Session altered.

Hourly report:


  select trunc(COMPLETION_TIME,'HH') Hour,thread# , count(*) Archives from v$archived_log
  where COMPLETION_TIME > sysdate-1
  group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1;




HOUR                  THREAD#   ARCHIVES
------------------ ---------- ----------
07-mar-17 15:00:00          1          3
07-mar-17 16:00:00          1          6
07-mar-17 17:00:00          1          6
07-mar-17 18:00:00          1          9
07-mar-17 19:00:00          1         19
07-mar-17 20:00:00          1          5
07-mar-17 21:00:00          1         20
07-mar-17 22:00:00          1          8
07-mar-17 23:00:00          1          4
08-mar-17 00:00:00          1          4
08-mar-17 01:00:00          1         19
08-mar-17 02:00:00          1         15
08-mar-17 03:00:00          1         25
08-mar-17 04:00:00          1         10
08-mar-17 05:00:00          1          5
08-mar-17 06:00:00          1         12
08-mar-17 07:00:00          1          4
08-mar-17 08:00:00          1         16
08-mar-17 09:00:00          1          6
08-mar-17 10:00:00          1         20
08-mar-17 11:00:00          1          6
08-mar-17 12:00:00          1          6
08-mar-17 13:00:00          1         19
08-mar-17 14:00:00          1          7
08-mar-17 15:00:00          1          3

25 rows selected.

Daily report:

 select trunc(COMPLETION_TIME,'DD') Day,thread# , count(*) Archives from v$archived_log
  where COMPLETION_TIME > sysdate-10
  group by trunc(COMPLETION_TIME,'DD'),thread#  order by 1
/

 Another question would be, how many archived logs per day are generated, on average?

On average we generate 77 logs/day.

SQL> l

  select avg(count(*)) Archives from v$archived_log

  where COMPLETION_TIME > sysdate-100

  group by trunc(COMPLETION_TIME,'DD')

SQL> /

  ARCHIVES

----------

77.0566038



Update:
Querying v$log_history produces a nice table:

http://ajwatblog.blogspot.com/2011/04/redo-logs-switching-frequency.html


In case the archivelog mode is OFF:

select count(*),trunc(FIRST_TIME,'hh')
from v$log_history
group by trunc(FIRST_TIME,'hh')
order by 2

/

How to allocate an extent for a partitioned table?

Example:

SQL> alter table MY_PART_TABLE  modify partition PR198 ALLOCATE EXTENT;

Table altered.

The need for the above might arise after creating a partition table with deferred segment allocation.

How to allocate an extent for a partitioned table?

Example:

SQL> alter table MY_PART_TABLE  modify partition PR198 ALLOCATE EXTENT;

Table altered.

The need for the above might arise after creating a partition table with deferred segment allocation.

Manual recovery until specific time (not with RMAN)

RECOVER DATABASE UNTIL TIME '1992-12-31:12:47:30' USING BACKUP CONTROLFILE;

How to startup the oracle database automatically on the Linux box?

The script being called when the Linux box is starting up is:

/etc/rc.d/init.d/oracle

It has inside calls to "dbstart" and "dbshut"; both of these scripts exist in $ORACLE_HOME/bin.

How to format my SQL query? (oracle)

The output from querying v$sql.sql_text is not formatted.
A nice site that will turn this output into a a formatted SQL is:

      http://www.dpriver.com/pp/sqlformat.htm

Oracle 11g: how to validate that a specific archived redo log is corrupted or not? Hint: Log Miner to the rescue

Very simple, according to the MOS doc ID 1268590.1.

Example:

 SQL> execute DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/users/Florin/MYDB_1799337628_202043.arc');

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.START_LOGMNR(options => -
                                dbms_logmnr.dict_from_online_catalog);
>
PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select count(1) from v$logmnr_contents;

  COUNT(1)
----------
   2874703

So there is no issue with the archived log :-)

Oracle 11g: How to select french characters, using SQL Plus , on Linux

The solution is to define the NLS_LANG as AL32UTF8.

Issue description:

 SQL> select BCK_ALT_DSC from BACKOUT_REASON_CODE where BCK_CODE='BDCBK';

BCK_ALT_DSC
------------------------------

Refus paiement d¦bit bancaire

Solution:


/u01/app/oracle > export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
/u01/app/oracle > sqlplus florinm/florinm

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Apr 15 14:17:48 2016

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> select BCK_ALT_DSC from BACKOUT_REASON_CODE where BCK_CODE='BDCBK';

BCK_ALT_DSC
--------------------------------------------------------------------------------
Refus paiement débit bancaire

Oracle 11g: how to control the frequency of the redo log switches?

Let's assume that we have a request to switch redo logs every 15 minutes, so we could ship the archived logs to a third party application.

I've found a very nice entry about this at the blog below:

http://dba-masters.blogspot.ca/2012/08/11g-r2-managing-redo-logs.html

SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 900;

System altered.

The value is in seconds, so 900 seconds = 15 minutes.

You can force all redo log threads to switch their CURRENT redo logs at regular time intervals.  In a Primary / Standby database configuration, changes are made available to the standby database by archiving redo logs at the primary site, and then shipping them across to the Standby site and applying them to the Standby database.  The standby database must wait for the redo log files to be archived and shipped across to it before it can apply the latest changes.  With the ARCHIVE_LAG_TARGET initialization parameter you can specify in seconds, how long that lag should be.

When ARCHIVE_LAG_TARGET is set, then the database examines the current redo log of the database periodically.  If the following conditions are met, then the instance will switch the logfile.

·         The current redo log file was created prior to n seconds ago, and the estimated archival time for the current log = m seconds.  m seconds is calculated according to the number of redo blocks in the current redo log file.  If n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter, we have a yes for a redo log file switch

·         The current log contains redo records.  A log switch will not occur if there are no records in the current redo log file.

In a RAC(Real Application Clusters) environment, the instance will also cause other threads from other instances to switch and archive their redo log files, if they are falling behind.  This is a useful mechanism if one instance in a RAC environment is more active than the other instances.  In a RAC environment, the value of ARCHIVE_LAG_TARGET must be the same on all instances, otherwise you can expect unpredictable behaviour.

The ARCHIVE_LAG_TARGET is a dynamic parameter, so you can set it with ALTER SYSTEM … SCOPE=BOTH; to make it permanent.  The value of ‘0’ disables it, and is also the default value.  A typical value for this initialization parameter is half an hour:

SQL> alter system set ARCHIVE_LOG_TARGET=1800 scope=both;

On which object is my query stuck (spending) time? Using v$session_wait and dba_extents

How to find the object name where our query is spending time?
In case the wait event in v$session_wait is "file sequential read", we can use the join below, to identify the index_name:



SQL> select segment_name,segment_type,owner,tablespace_name from
    dba_extents,v$session_wait
    where file_id=p1
   and p2 between block_id and block_id + blocks -1
   and sid=4933;

SEGMENT_NAME                                                                      SEGMENT_TYPE       OWNER                          TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------
TABLE_TEST_4IX                                                               INDEX PARTITION    MY_USER                        APL_LARGE_IX

invoker_rights_clause to the rescue

The issue: we are trying to call a stored procedure defined in a different user, using a synonym, to truncate a table in our account. This is failing, since by default the procedure is ran using the "definer rights".

The solution: define the procedure/package to run using "invoker rights", as below:

CREATE PACKAGE     "TRUNC_TAB" AUTHID CURRENT_USER AS
    PROCEDURE TRUNCATE_TABLE (i_table_name in varchar2);
    PROCEDURE TRUNCATE_PARTITION (i_table_name in varchar2,i_partition_name  in varchar2);
END TRUNC_TAB ;
/

How to get run time statistics for a query?

Step 1:
Add the hint gather_plan_statistics to the select statement:


SELECT /*+ GATHER_PLAN_STATISTICS */
name,address,code
from address_name_table;

Step 2:
Generate the plan and the run time statistics:


select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));