Optimizer statistics do not get automatically purged (object lock of WRI$_OPTSTAT_HISTHEAD_HISTORY) /// SYSAUX and SYSTEM tablespaces have been continually growing

object lock of WRI$_OPTSTAT_HISTHEAD_HISTORY

The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value is 31 days.

The SYSAUX and SYSTEM tablespaces have been continually growing due to

the databases where a high amount of import/exports and RMAN are taking place.

SELECT  occupant_name "Item",
    space_usage_kbytes/1048576 "Space Used (GB)",
    schema_name "Schema",
    move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1

/

Item                      Space Used (GB) Schema                    Move Procedure

SM/OPTSTAT 12.45  SYS


To resolve this I set the stats retention period to 5 days.

SQL> exec dbms_stats.alter_stats_history_retention(5);


To find out the oldest available stats you can issue the following:

SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
28-SEP-13 00.00.00.000000000 +04:00

To find out a list of how many stats are gathered for each day between the retention the current date and the oldest stats history issue the following:

SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by  trunc(SAVTIME) order by 1;
TRUNC(SAV COUNT(1)
--------- ----------
28-SEP-13 2920140
29-SEP-13 843683
30-SEP-13 519834
01-OCT-13 958836
02-OCT-13 3158052
03-OCT-13 287
04-OCT-13 1253952
05-OCT-13 732361
06-OCT-13 507186
07-OCT-13 189416
08-OCT-13 2619
09-OCT-13 1491
10-OCT-13 287
11-OCT-13 126324
12-OCT-13 139556
13-OCT-13 181068
14-OCT-13 4832
15-OCT-13 258027
16-OCT-13 1152
17-OCT-13 287
18-OCT-13 27839
21 rows selected.

What has happened here is that the job run by MMON every 24hrs has checked the retention period and tried to run a purge of all stats older than the retention period. As the job has not compeleted within 5 minutes because of the high number of stats collected on each day, the job has given up and rolled back. Therefore the stats are not being purged.

As each day continues the SYSAUX table is continuing to fill up because the job fails each night and cannot purge old stats.

To resolve this we have to issue a manual purge to clear down the old statistics. This can be UNDO tablespace extensive so it’s best to keep an eye on the amount of UNDO being generated. I suggest starting with the oldest and working fowards.

To manually purge the stats issue the following:

SQL> exec dbms_stats.purge_stats(to_date('28-SEP-13','DD-MON-YY'));PL/SQL procedure successfully completed.

OR

exec DBMS_STATS.PURGE_STATS(SYSDATE-5);

Purge stats older than 5 days 

(best to do this in stages if there is 

a lot of data (sysdate-30,sydate-25 etc)

Then I tried rebuilding the stats indexes and tables as they would now be fragmented.

If you are only running standard edition then you can only rebuild indexes offline. Online index rebuild is a feature of Enterprise Edition.


SELECT
sum(bytes/1024/1024) Mb,
segment_name,
segment_type
FROM
dba_segments
WHERE
tablespace_name = 'SYSAUX'
AND
segment_type in ('INDEX','TABLE')
GROUP BY
segment_name,
segment_type
ORDER BY Mb;
MB  SEGMENT_NAME                             SEGMENT_TYPE
--  ---------------------------------------  ----------------
2   WRH$_SQLTEXT                             TABLE
2   WRH$_ENQUEUE_STAT_PK                     INDEX
2   WRI$_ADV_PARAMETERS                      TABLE
2   WRH$_SEG_STAT_OBJ_PK                     INDEX
3   WRI$_ADV_PARAMETERS_PK                   INDEX
3   WRH$_SQL_PLAN_PK                         INDEX
3   WRH$_SEG_STAT_OBJ                        TABLE
3   WRH$_ENQUEUE_STAT                        TABLE
3   WRH$_SYSMETRIC_SUMMARY_INDEX             INDEX
4   WRH$_SQL_BIND_METADATA_PK                INDEX
4   WRH$_SQL_BIND_METADATA                   TABLE
6   WRH$_SYSMETRIC_SUMMARY                   TABLE
7   WRH$_SQL_PLAN                            TABLE
8   WRI$_OPTSTAT_TAB_HISTORY                 TABLE
8   I_WRI$_OPTSTAT_TAB_ST                    INDEX
9   I_WRI$_OPTSTAT_H_ST                      INDEX
9   I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX
12  I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
12  I_WRI$_OPTSTAT_IND_ST                    INDEX
12  WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
14  I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX
20  WRI$_OPTSTAT_IND_HISTORY                 TABLE
360 I_WRI$_OPTSTAT_HH_ST                     INDEX
376 WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE
488 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX

To reduce these tables and indexes you can issue the following:

Note that you cannot enable row movement and shrink the tables as the indexes are function based

alter table WRI$_OPTSTAT_IND_HISTORY enable row movement;
alter table WRI$_OPTSTAT_IND_HISTORY shrink space;
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object

select 'alter table '||segment_name||'  move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
Run the rebuild table commands – note that this does cause any gather_stats jobs to fail

alter table WRI$_OPTSTAT_TAB_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR  move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV  move tablespace sysaux;

Script to generate rebuild statements

select 'alter index '||segment_name||'  rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
Once completed it is best to check that the indexes (indices) are usable


select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt
where  di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc

/


Show how big the tables are and rebuild after stats have been purged

select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc

Show how big the indexes are ready for a rebuild after stats have been purged

select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'

group by segment_name,segment_type order by 1 asc

[SOLVED] Crontab does not run crontab scripts

[SOLVED] Crontab does not run crontab scripts


[oracle@111 db_back]$ ps aux | grep cron
oracle   14930  0.0  0.0 103232   860 pts/0    S+   23:21   0:00 grep cron
[oracle@111 db_back]$ service crond restart
User has insufficient privilege.
[root@111 ~]# service crond restart
Stopping crond:                                            [FAILED]
Starting crond:                                            [  OK  ]


SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/
# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name command to be executed
* * * * * oracle run-parts /scripts/expback
* * * * * oracle run-parts /scripts/rmanback
15 * * * * oracle run-parts /etc/cron.hourly



First, the correct place for this is probably in /etc/cron.d not in /etc/crontab. If you really want to keep it where it is now,
I'd suggest looking in /var/log/cron and making sure that it is executing at all. I'd look at `aureport -a`
 and see if anything is logged as an selinux denial around the time you expect this to execute
 and then use `ausearch -a nnn` where nnn is the number from the far right hand end of the aureport output line(s).
  Trying it in permissive mode by running `setenforce 0` would be a good test of this.

NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL Code

NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL Code

Oracle has two methods of passing passing OUT and IN OUT parameters in PL/SQL code:

• Pass By Value : The default action is to create a temporary buffer (formal parameter), copy the data from the parameter variable (actual parameter) to that buffer and work on the temporary buffer during the lifetime of the procedure. On successful completion of the procedure, the contents of the temporary buffer are copied back into the parameter variable. In the event of an exception occurring, the copy back operation does not happen.
• Pass By Reference : Using the NOCOPY hint tells the compiler to use pass by reference, so no temporary buffer is needed and no copy forward and copy back operations happen. Instead, any modification to the parameter values are written directly to the parameter variable (actual parameter).

    CONN / AS SYSDBA

    GRANT SELECT ON v_$statname TO Ashokan ;
    GRANT SELECT ON v_$mystat TO Ashokan;
    GRANT CREATE PROCEDURE TO Ashokan;



    CONN Ashokan/Ashokan

    CREATE OR REPLACE PACKAGE Ashokan_nocopy AS

    PROCEDURE in_out_time;
    PROCEDURE in_out_memory;
    PROCEDURE in_out_nocopy_time;
    PROCEDURE in_out_nocopy_memory;

    END;
    /

    CREATE OR REPLACE PACKAGE BODY Ashokan_nocopy AS

    TYPE     t_tab IS TABLE OF VARCHAR2(32767);
    g_tab    t_tab := t_tab();
    g_start  NUMBER;

    FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER;
    PROCEDURE in_out (p_tab  IN OUT  t_tab);
    PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab);

    -- Function to return the specified statistics value.
    FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER AS
      l_return  NUMBER;
    BEGIN
      SELECT ms.value
      INTO   l_return
      FROM   v$mystat ms,
             v$statname sn
      WHERE  ms.statistic# = sn.statistic#
      AND    sn.name = p_stat;
      RETURN l_return;
    END get_stat;


    -- Basic test  procedures.
    PROCEDURE in_out (p_tab  IN OUT  t_tab) IS
      l_count NUMBER;
    BEGIN
      l_count := p_tab.count;
    END in_out;

    PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab) IS
      l_count NUMBER;
    BEGIN
      l_count := p_tab.count;
    END in_out_nocopy;


    -- Time a single call using IN OUT.
    PROCEDURE in_out_time IS
    BEGIN
       g_start := DBMS_UTILITY.get_time;

       in_out(g_tab);

       DBMS_OUTPUT.put_line('IN OUT Time         : ' ||
                            (DBMS_UTILITY.get_time - g_start) || ' hsecs');
    END in_out_time;


    -- Check the memory used by a single call using IN OUT.
    PROCEDURE in_out_memory IS
    BEGIN
       g_start := get_stat('session pga memory');

       in_out(g_tab);

       DBMS_OUTPUT.put_line('IN OUT Memory       : ' ||
                            (get_stat('session pga memory') - g_start) || ' bytes');
    END in_out_memory;


    -- Time a single call using IN OUT NOCOPY.
    PROCEDURE in_out_nocopy_time IS
    BEGIN
       g_start := DBMS_UTILITY.get_time;

       in_out_nocopy(g_tab);

       DBMS_OUTPUT.put_line('IN OUT NOCOPY Time  : ' ||
                            (DBMS_UTILITY.get_time - g_start) || ' hsecs');
    END in_out_nocopy_time;


    -- Check the memory used by a single call using IN OUT NOCOPY.
    PROCEDURE in_out_nocopy_memory IS
    BEGIN
       g_start := get_stat('session pga memory');

       in_out_nocopy(g_tab);

       DBMS_OUTPUT.put_line('IN OUT NOCOPY Memory: ' ||
                            (get_stat('session pga memory') - g_start) || ' bytes');
    END in_out_nocopy_memory;


    -- Initialization block to populate test collection.
   
    BEGIN
      g_tab.extend;
      g_tab(1) := '1234567890123456789012345678901234567890';
      g_tab.extend(999999, 1);  -- Copy element 1 into 2..1000000
    END;
    /
   

When running the test procedures, it makes sense to reconnect every time to make sure you get a new session with a clean PGA allocation.

CONN Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_time;

    CONN Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_nocopy_time;

    CONN Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_memory;

    CONN Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_nocopy_memory;

When we run these , the output looks something like this.

    Connected.
    IN OUT Time         : 126 hsecs

    PL/SQL procedure successfully completed.

    Connected.
    IN OUT NOCOPY Time  : 0 hsecs

    PL/SQL procedure successfully completed.

    Connected.
    IN OUT Memory       : 99549184 bytes

    PL/SQL procedure successfully completed.

    Connected.
    IN OUT NOCOPY Memory: 0 bytes

    PL/SQL procedure successfully completed.

    SQL>

From this we can make the following conclusions:

    Elapsed Time: When we use an IN OUT parameter to pass this large collection, it takes over a second to perform the memory allocation, copy forward and copy back for a single call. In comparison, the time taken to make the call using the IN OUT NOCOPY parameter is not measurable in hundredths of a second, because there is no management of a temporary buffer. So using pass by reference for large parameters gives us a considerable performance boost.
    Memory Usage: As expected, when passing a large IN OUT parameter by value, the session requires extra memory for the temporary buffer. When the parameter is defined as IN OUT NOCOPY, no extra memory is required as there is no temporary buffer. So using pass by reference for large parameters reduces the memory required by the session.

Issues

There are a number of issues associated with using the NOCOPY hint that you should be aware of before adding it to all your OUT and IN OUT parameters.

    NOCOPY is a hint. There are a number of circumstances where the compiler can ignore the hint, as described here.
    If you are testing the contents of the parameter as a measure of successful completion of a procedure, adding NOCOPY may give unexpected results. For example, suppose I pass the value of NULL and assume if the parameter returns with a NOT NULL value the procedure has worked. This will work without NOCOPY, since the copy back operation will not happen in the event of an exception being raised. If I add NOCOPY, all changes are instantly written to the actual parameter, so exceptions will not prevent a NOT NULL value being returned. This may seem like a problem, but in my opinion if this affects you it is an indication of bad coding practice on your part. Failure should be indicated by raising an exception, or at worst using a status flag, rather than testing for values.
    Parameter Aliasing. If you use a single variable as an actual parameter for multiple OUT and/or IN OUT parameters in a procedure, using a mix of pass by value and pass by reference, you may get unexpected results. This is because the final copy back from the pass by value parameters will wipe out any changes to the pass by reference parameters. This situation can be compounded further if the actual parameter is a global variable that can be referenced directly from within the procedure. Although the manual describes possible issues, once again it is an indication that you are writing terrible code, rather than a limitation of pass by reference. You can read more about parameter aliasing here.

SYS.ANYDATA (Generic Data Type) is an object type that used to store different data types in Table, or create variable that can setted by any data-type.

SYS.ANYDATA (Generic Data Type)  is an object type that  used to store different data types in Table, or create variable that can setted by any data-type.

For example I will create new table below

CREATE TABLE ANYDATA_TABLE
(
   ID           NUMBER NOT NULL,
   ADT_COLUMN   SYS.ANYDATA
);


Now I will try to insert different data types in col2

insert into anydata_table values(1,sys.anydata.convertnumber(1))
/
insert into anydata_table values(2,sys.anydata.convertdate(sysdate))
/
insert into anydata_table values(3,sys.anydata.convertvarchar2('AShokan B'))
/


Note that I use anydata.convertnumber, anydata.convertdate and anydata.convertvarchar2 to specify data type of inserted data

let's now query data from table

SELECT * FROM ANYDATA_TABLE;
The data of ADT_COULMN is unreadable in result set as below picture

So I need to retrieve readable value of  ADT_COLUMN from query
We can do this by below steps
1-Determine data type of inserted data in ADT_COLUMN
2-Create different functions to return equivalent data type for every inserted data in ADT_COLUMN
    I will create these functions in package called ANYDATA_PKG
3-Use ANYDATA built-in member functions to do equivalent of ANYDATA_PKG in #2



1-Determine data type of inserted data in ADT_COLUMN
we can use SYS.ANYDATA.GETTYPENAME(ANYDATA_TABLE.ADT_COLUMN) to return data type of inserted data

SELECT ANYDATA_TABLE.*, SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) TYPE_NAME
  FROM ANYDATA_TABLE;


2-Create different functions to return equivalent data type for every inserted data in ADT_COLUMN
I will create ANYDATA_PKG which contains three functions
a-Get_number to return number value type
b-Get_varchar2 to return varchar2 type
c-Get_Date to return date type

 CREATE OR REPLACE PACKAGE ANYDATA_PKG 
 AS 
   GN$TMP  NUMBER; 
   FUNCTION GET_NUMBER (IN_ANYDATA IN SYS.ANYDATA) 
    RETURN NUMBER; 

   FUNCTION GET_VARCHAR2 (IN_ANYDATA IN SYS.ANYDATA) 
    RETURN VARCHAR2;

   FUNCTION GET_DATE (IN_ANYDATA IN SYS.ANYDATA) 
    RETURN DATE; 
 END; 


 CREATE OR REPLACE PACKAGE BODY ANYDATA_PKG 
 AS 
   FUNCTION GET_NUMBER (IN_ANYDATA IN SYS.ANYDATA) 
    RETURN NUMBER 
   IS 
    LN$NUMBER_VALUE  NUMBER; 
   BEGIN 
    GN$TMP := IN_ANYDATA.GETNUMBER (LN$NUMBER_VALUE); 
    RETURN (LN$NUMBER_VALUE); 
   END; 

   FUNCTION GET_VARCHAR2 (IN_ANYDATA IN SYS.ANYDATA) 
    RETURN VARCHAR2 
   IS 
    LC$VARCHAR2_VALUE  VARCHAR2 (4000); 
   BEGIN 
    GN$TMP := IN_ANYDATA.GETVARCHAR2 (LC$VARCHAR2_VALUE); 
    RETURN (LC$VARCHAR2_VALUE); 
   END; 

   FUNCTION GET_DATE (IN_ANYDATA IN SYS.ANYDATA) 
    RETURN DATE 
   IS 
    LD$DATE_VALUE  DATE; 
   BEGIN 
    GN$TMP := IN_ANYDATA.GETDATE (LD$DATE_VALUE); 
    RETURN (LD$DATE_VALUE); 
   END; 

 END; 


Let's now write query again to retrieve data and add our new functions to get data in readable fashion.
SELECT ANYDATA_TABLE.*,
       SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) TYPE_NAME,
       CASE
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.NUMBER'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_NUMBER (ADT_COLUMN))
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.VARCHAR2'
          THEN
             ANYDATA_PKG.GET_VARCHAR2 (ADT_COLUMN)
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.DATE'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_DATE (ADT_COLUMN), 'DD-MM-RRRR')
       END
          READABLE_VALUE
  FROM ANYDATA_TABLE;

3-Use ANYDATA built-in members functions to do equivalent of ANYDATA_PKG in #2
 ANYDATA have built-in memebrs functions that can do what we do in ANYDATA_PKG
a- ANYDATA.ACESSNUMBER to return number value mapped to ANYDATA_PKG.GET_NUMBER
b-  ANYDATA.ACESSVARCHAR2 to return varchar2 value mapped to  ANYDATA_PKG.GET_VARCHAR2
c-  ANYDATA.ACESSDATE to return Date value mapped to ANYDATA_PKG.GET_DATE

Let's now use previous functions in our query
SELECT ANYDATA_TABLE.*,
       SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) TYPE_NAME,
       CASE
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.NUMBER'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_NUMBER (ADT_COLUMN))
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.VARCHAR2'
          THEN
             ANYDATA_PKG.GET_VARCHAR2 (ADT_COLUMN)
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.DATE'
          THEN
             TO_CHAR (ANYDATA_PKG.GET_DATE (ADT_COLUMN), 'DD-MM-RRRR')
       END
          READABLE_VALUE,
       CASE
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.NUMBER'
          THEN
             TO_CHAR (SYS.ANYDATA.ACCESSNUMBER (ADT_COLUMN))
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.VARCHAR2'
          THEN
             SYS.ANYDATA.ACCESSVARCHAR2 (ADT_COLUMN)
          WHEN SYS.ANYDATA.GETTYPENAME (ADT_COLUMN) = 'SYS.DATE'
          THEN
             TO_CHAR (SYS.ANYDATA.ACCESSDATE (ADT_COLUMN), 'DD-MM-RRRR')
       END
          READABLE_VALUE2
  FROM ANYDATA_TABLE;

Conclusion

I illustrated how to use ANYDATA with scalar data types.
You can do your best practice to use ANYDATA with collection and object types.
If you take decision to do this use sys.anydata.convertcollection, sys.anydata.convertobject to insert collection and object types, then create your custom function to get inserted collection or object type in readable fashion.

Data Pump vs EXP/IMP: Difference or Comparison Between Data Pump Expdp/impdp and Conventional EXP/IMP

Data Pump vs EXP/IMP: Difference or Comparison Between Data Pump Expdp/impdp and Conventional EXP/IMP

Datapump introduced in Oracle 10g whereas conventional exp/imp was used for logical backups in prior versions of oracle 10g. Exp/imp works even in all versions of Oracle.

Conventional exp/imp can utilize the client machine resource for taking the backups but, the datapump works only in server.

XML schema, XML types are supported in expdp/impdp but not in exp/imp

Parallel execution is possible in datapump which is not supported in conventional exp/imp. Using the parallel option the datapump generates multiple dump files simultaneously.

Datapump cannot export the data into sequential medias like tapes.

Datapump has better control than exp/imp on the backup job with START, STOP and RESTART options.

Datapump gives 15 – 50% performance improvement than exp/imp.

Table Extent compression can be done using COMPRESS option whereas in datapump COMPRESSION does the dumpfile compression.

Linux shell scripting: bad interpreter: No such file or directory

Linux shell scripting: bad interpreter: No such file or directory 

This error pops up for a couple of reasons. At the top of the script there will probably be a line that looks like this:

#!/bin/sh


This is telling Linux that this script should be interpreted using the /bin/sh program. So your first step is to verify that program exists. I tend to use:

which sh


This will typically come back with a response like this:

/bin/sh


This is telling us that the path to the sh program is in fact /bin/sh, matching the path specified at the top of the script. Ok, so what gives? Well, it's possible that this script was made on an operating system that has line ending characters different than linux. This could have been on on a Mac or PC, or the file could have been converted when it was packaged. In this case, you get the relatively misleading bad interpreter: No such file or directory message, which is really trying to look for sh, although you don't get any indication of the fact.

So, how to fix? Read on.
There are various ways to fix the problem, but I find one of the simplest being the use of vi which is standard on most unix systems, and in linux comes in the form of the vim package. Load the script up in vim, by typing vi filename

vi is a text based dinosaur in the day of wysiwyg editors, so if you don't know your way around, make sure you follow these steps carefully.

Once the file is loaded type:

:set fileformat=unix


And hit Enter/Return.

You won't notice anything, but the file has already been fixed. Now all you need to do is save and exit.

:wq!


Again Return, and you should be back in your shell. Run the shell script, and if all goes well, it should now execute properly, and without the dreaded bad interpreter: No such file or directory message.
Defined tags for this entry: bad interpreter, Bash, line endings, Linux, vi
Related entries by tags:

Run statspack snapshot every 30 minutes

For example : Run statspack snapshot every 30 minutes

SQL>

SQL>

DECLARE
    jobno number;
    instno number;
BEGIN
    SELECT instance_number INTO instno FROM v$instance;
    DBMS_JOB.SUBMIT(jobno, 'statspack.snap(i_snap_level => 7);',
    trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60),
    'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, instno);
    COMMIT;
END;
/