How to export/import a very big compressed table?

The challenge here is to transfer a very big table from one DB to another, both fast and using as little space as possible. Since the table is compressed in the source DB, we'll need to take advantage of this.
The table will stay compressed throughout the process if we'll use expdp and impdp, with the option compression=ALL.
See example below of par file for expdp:



 >cat expdp.par
userid=myuser/mypass
dumpfile=big_dir:big_table_1011.dmp
logfile=big_dir:big_table_1011.exp.log
compression=ALL


 To check expdp status:

 expdp myuser/mypass attach=SYS_EXPORT_SCHEMA_01

 Export>Status

How to find out CPU details on the UNIX box?

# of CPUs + details:

vi /proc/cpuinfo

How to pause UNIX script and wait until we press ?

Sometimes the above scenario makes sense, we want to check some log files before proceeding with the next step.
The solution is to use:

"read -p"

More details in the above nice blog entry:

http://www.cyberciti.biz/tips/linux-unix-pause-command.html

Where is the "glance" utility located on Linux?

/opt/perf/bin/glance

How to restore an old execution plan, using dbms_spm.load_plans_from_sqlset

A real scenario: one specific query changed its execution plan and started to run for a very long time. We could not find any reason for this, after chacking statistics, indexes status and so on. We could not even revert to the old good plan using hints.
Luckily, we still had the AWR snapshots where we had the good execution plan, so we could follow the steps below:

 1) Identify when did it run correctly the last time and write down the AWR specific snapshots.
 2) Create an sql tuning set, based on this AWR snapshots.
 3) Load this sql set.
 4) Load SQL execution plans from SQL set into SQL baseline.


 A very good site which is decribing all these steps is:

https://aprakash.wordpress.com/2012/07/05/loading-sql-plan-into-spm-using-awr/

Linux "history" command with timestamp

While trying to find out exactly at which time some Linux command ran, the history command can help, especially combined witht the below settings, to get the timestamp also:

 >export HISTTIMEFORMAT="%F %T "
>history 10
 1094  2015-06-04 11:56:32 showsess -a
 1095  2015-06-04 11:56:34 showsess -a
 1096  2015-06-04 11:56:36 cd Log/
 1097  2015-06-04 11:56:37 ll -tr
 1098  2015-06-04 11:58:59 export HISTTIMEFORMAT="%F %T "
 1099  2015-06-04 11:59:02 history -5
 1100  2015-06-04 11:59:05 history 5
 1101  2015-06-04 11:59:14 clear
 1102  2015-06-04 11:59:17 export HISTTIMEFORMAT="%F %T "
 1103  2015-06-04 11:59:20 history 10

On which table/index am I waiting?

Sometimes is handy to know on which object our query is working hard and there is a nice query that could help us with this:

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

Of course, this will work when the wait event in v$session_wait is pointing on table/index scan (db buffer wait, scattered read and so on).

How to trace a specific query, using its SQL_ID?

The answer is to use "set events":

alter system set events 'sql_trace[sql:gb07958tf8xdk] bind=true,wait=false';

A few more detailed examples at:
http://oraclue.com/2009/03/24/oracle-event-sql_trace-in-11g/

Use vi to replace some pattern, but only for specific lines

Sometimes is very handful to use the Unix/Linux utility "vi" to replace a specific pattern, but there is a special syntax if we only want to do it for a specific range of rows, see exmaple below:

Use find and replace on line ranges (match by line numbers)

You can also make changes on range of lines i.e. replace first occurrence of foo with bar on lines 5 through 20 only, enter:
:5,20s/foo/bar/