endian format

To determin the endian format for a platform:

select a.endian_format, a.platform_id
from v$transportable_platform a, v$database b
where a.platform_name=b.platform_name;

DBMS_SQLTUNE

Step 1: Creating Tuning Task DECLARE my_task_name VARCHAR2 (30); my_sqltext CLOB; BEGIN my_sqltext := 'SELECT e.last_name, d.department_name, d.department_id FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id = :bnd'; my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext, bind_list => sql_binds (anydata.convertnumber (9)), user_name => 'HR', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'vega_tuning_task', description => 'Tuning Task' ); END; / Create_tuning_task functions returns name of the task created. Step 2: Executing SQL Tuning Task BEGIN dbms_sqltune.execute_tuning_task (task_name => 'vega_tuning_task'); END; / Step 3: Checking Status of SQL Tuning Task SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'vega_tuning_task'; Step 4: Retrieving results of SQL tuning task After task is executed results can be obtained by calling REPORT_TUNING_TASK function SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'vega_sql_tuning_task') FROM DUAL;

Block-change tracking: How effective it is

Oracle block-change tracking let's us do incremental backups for only the blocks that have been changed. To check the effectiveness, use the following query:

SELECT file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks)*100 AS PCT_READ_FOR_BACKUP, avg(blocks)
FROM V$BACKUP_DATAFILE
WHERE used_change_tracking='YES' and incremental_level>0
GROUP BY file#

Ideally we wanted to see the PCT_READ_FOR_BACKUP with a small value. If this value is very large, we can increase the frequency of incremental backups.

Oracle Data Pump (impdp and expdp)

User Crtl + C to suspend a expdp job started from a command line.
to stop, kill, resume, attach, cancel, restart a datapump job
http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/



To enhance performance for large data imports with impdp, consider two things:
One: Using parallellims:
Degree=2 x number of CPUs
Degree <= number of dump files
Degree of parallelims can be adjusted on the fly
Oracle uses worker processes and parallel execution (PX) process. Worker processes are used in inter-segment parallelims using DIRECTPATH while PX processes are used in intra-segment parallelims using EXTERNAL TABLE.
Oracle will automatically choose which method to use in parallellims.
There are times when you can use a hidden parameter to only use DIRECTPATH method
access_method=direct_path
Note: if direct_path cannot be used for a segment and the above parameter is set, data for that segment is not going to be loaded.
Two equally sized segments will be loaded faster with access_method=direct_path

Indexes are created by one at a time by a single worker process using multiple paralle PX processes. Index creation could take a long time.

Two: Setting the following parameters before the load:
1. disk_asynch_io=true
2. noarchivelog (makes big difference)
3. db_block_checking=false
4. db_block_checksum=false
5. disable block change tracking
6. size data files properly before loading, do not use autoextend

Setting EXCLUDE/INCLUDE parameter will also exclude/include dependent objects
To check dependencies:
database_export_objects
schema_export_objects
table_export_objects

To import data into a database with archivelog mode:
1. put database in NOLOGGING mode, or
2. a) create tables with content=metadata_only exclude=indes, constraint, ref_constraint
b) put tables in nologging mode
c) import with table_exists_action=append
4) cretae index with nologging


TRACING:
TRACE=480300 for impdp and expdp
extended log information
not very excessive data output size
(metalink Note: 286496.1)

DATAPUMP NETWORK MODE (Without creating dump file)

STEP 1: grant exp_full_database role to socre shcema [IN REMOTE DB]
conn system/pass;

GRANT EXP_FULL_DATABASE to user_info;

STEP 2: [IN DESTINATION DB] Create destination user and grant necessary roles

conn system/pass;
CREATE USER user_info
IDENTIFIED BY pass
DEFAULT TABLESPACE USER
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT , RESOURCE TO user_info;

STEP 3: [IN DESTINATION DB] grant read/write on dump directory

conn system/pass;

GRANT read,write on DIRECTORY dump_directory to user_info;

Network import does not requer any dump file. This directory is only requer to write the import log file.

STEP 4: [IN DESTINATION DB] create public DB Link

conn system/pass;
CREATE PUBLIC DATABASE LINK SATURN
connect to user_info identified by pass
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_db)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=orcl.oracle.com)
(server=DEDICATED)))';

STEP 5: [IN DESTINATION DB MACHINE] execute impdp

impdp user_info/pass directory=dump_dir network_link=SATURN logfile=net_import_proddev.log EXCLUDE=GRANT,STATISTICS,SYNONYM,DB_LINK REMAP_SCHEMA=USER_INFO:USER_INFO

Optimizer Statistics Restore

dba_optstat_operations: start and end time for all DBMS_STATS operations
dba_tab_stats_history: history of table statistics modifications for the past 31 days. This means you can use DBMS_STATS.RESTORE to restore optimizer statistics up to 31 days
dba_scheduler_jobs

If you user ANALYZE command to collect statistics, these statistics information will not be stored in the data dictionary.

dbms_stats.restore_table_stats(,,timestamp)
dbms_stats.restore_schema_stats
dbms_stats.restore_database_stats
dbms_stats.restore_dictionary_stats
dbms_stats.restore_fixed_objects_stats
dbms_stats.restore_system_stats
dbms_stats.purge_stats();
dbms_stats.alter_stats_history_retention ();
dbms_stats.get_stats_history_availability

how to find unindexed foreign keys in oracle

select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from dba_cons_columns ) a,
dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
/

How to find the deleted open file without rebbot on solaris

How to find the deleted open file without rebbot on solaris

1. Use the following command to find out open but deleted files
find /proc/*/fd -type f -links 0 -exec ls -lrt {} \;

which looks for files with zero links, i.e. that have been deleted but
a running process still has the file open. Using the PID from the
/proc//fd/xxxxx names returned you can determine which process is keepomg the file open

2. using cat /dev/null to empty the open file to release the hidden storage
3. check fs storage using df before and after

Example:

bonnet(RAM)/proc>df -h
Filesystem size used avail capacity Mounted on
/ 0K 4.0G 11G 27% /
/RETUNL 500G 79G 421G 16% /RETUNL
/dev 15G 4.0G 11G 27% /dev
/newwork 590G 430G 160G 73% /newwork
/opt 20G 11M 20G 1% /opt
/u01 15G 10G 4.7G 69% /u01
/u02 180G 142G 38G 80% /u02
/var/core 38G 7.0G 31G 19% /var/core
proc 0K 0K 0K 0% /proc
ctfs 0K 0K 0K 0% /system/contract
mnttab 0K 0K 0K 0% /etc/mnttab
objfs 0K 0K 0K 0% /system/object
swap 209G 280K 209G 1% /etc/svc/volatile
/platform/SUNW,T5440/lib/libc_psr/libc_psr_hwcap2.so.1
15G 4.0G 11G 27% /platform/sun4v/lib/libc_psr.so.1
/platform/SUNW,T5440/lib/sparcv9/libc_psr/libc_psr_hwcap2.so.1
15G 4.0G 11G 27% /platform/sun4v/lib/sparcv9/libc_psr.so.1
fd 0K 0K 0K 0% /dev/fd
swap 4.0G 155M 3.8G 4% /tmp
swap 209G 16K 209G 1% /var/run

bonnet(RAM)/proc>find /proc/*/fd -type f -links 0 -exec ls -lrt {} \;
-rw-r----- 0 oracle dba 0 May 4 06:54 /proc/11953/fd/8
-rw-r----- 0 oracle dba 0 May 4 06:57 /proc/7566/fd/8
-rw-r----- 0 oracle dba 24751652864 May 21 09:37 /proc/7566/fd/258
-rw-r----- 0 oracle dba 0 May 4 06:54 /proc/8755/fd/8
bonnet(RAM)/proc>cat /dev/null >/proc/7566/fd/258
bonnet(RAM)/proc>df -h
Filesystem size used avail capacity Mounted on
/ 0K 4.0G 11G 27% /
/RETUNL 500G 79G 421G 16% /RETUNL
/dev 15G 4.0G 11G 27% /dev
/newwork 590G 430G 160G 73% /newwork
/opt 20G 11M 20G 1% /opt
/u01 15G 10G 4.7G 69% /u01
/u02 180G 119G 61G 67% /u02
/var/core 38G 7.0G 31G 19% /var/core
proc 0K 0K 0K 0% /proc
ctfs 0K 0K 0K 0% /system/contract
mnttab 0K 0K 0K 0% /etc/mnttab
objfs 0K 0K 0K 0% /system/object
swap 209G 280K 209G 1% /etc/svc/volatile
/platform/SUNW,T5440/lib/libc_psr/libc_psr_hwcap2.so.1
15G 4.0G 11G 27% /platform/sun4v/lib/libc_psr.so.1
/platform/SUNW,T5440/lib/sparcv9/libc_psr/libc_psr_hwcap2.so.1
15G 4.0G 11G 27% /platform/sun4v/lib/sparcv9/libc_psr.so.1
fd 0K 0K 0K 0% /dev/fd
swap 4.0G 155M 3.8G 4% /tmp
swap 209G 16K 209G 1% /var/run


Pay attention to /u02, 24Gb space is released.