Skip to main content

Posts

DATAPUMP EXPORT USING MULTIPLE DIRECTORIES IN ORACLE DATABASE

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select name from v$database; NAME --------- ORCL SQL> conn sys as sysdba Enter password: Connected. SQL> create directory krishna as 'e:\krishna\dump'; Directory created. SQL> create directory lattu as 'd\:krishna\dump'; Directory created. SQL> grant read,write on directory krishna to system; Grant succeeded. SQL> grant read,write on directory lattu to system; Grant succeeded. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP and Data Mining options C:\Documents and Settings\u19791>expdp system/manager dumpfile=krishna:k.dmp,lattu:Ll.dmp logfile=krishna:tst.log parallel=2 SCHEMAS=SCOTT Export: Release 11.2.0.4.0 - Production on Wednesday, 30 November, 2018 22:59:20 . Connected to: Oracle Database 11g Enterprise Edition Release 11.2
Post a Comment
Read more
Recent posts

LOCATE ALL RUNNING DATAPUMP JOBS IN ORACLE DATABASE

-- locate Data Pump jobs: SET lines 200 COL owner_name FORMAT a10; COL job_name FORMAT a20 COL state FORMAT a12 COL operation LIKE state COL job_mode LIKE state SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' and state='NOT RUNNING' ORDER BY 1,2; spool off ;
Post a Comment
Read more

CHECK ALL EXECUTION PLAN ID'S OF A SQLID IN ORACLE DATABASE

spool check_execution_plan_details.log set linesize 600 select a.sql_id,a.snap_id,to_char(b.begin_interval_time,'mm/dd/yyyy hh24:mi') snap_tm, a.INSTANCE_NUMBER,a.PLAN_HASH_VALUE,a.SORTS_DELTA, a.EXECUTIONS_DELTA,a.disk_reads_delta,a.buffer_gets_delta, a.rows_processed_delta,a.cpu_time_delta/1000000 cpu_time_delta, a.elapsed_time_delta/1000000 iowait_delta, a.clwait_delta/1000000 clwait_delta from dba_hist_sqlstat a, dba_hist_snapshot b where a.sql_id='&sql_id' and a.SNAP_ID=b.snap_id and a.instance_number=b.instance_number and a.dbid=b.dbid order by a.instance_number,a.snap_id desc,a.plan_hash_value; spool off;
Post a Comment
Read more

SCRIPT TO CONNECT TO ALL DATABASE IN A SERVER

iru@/home/oracle/dba/scripts: cat check_all_database.sh #!/bin/sh ORATAB=/home/oracle/dba/scripts/oratab export ORATAB start() {         sqlplus -s '/as sysdba'<<! set head off set pagesize 100 select instance_name from v\$instance; select username from dba_users where username like '%AM%'; select username from dba_users where username like '%MA%'; select count(1) from all_objects where owner='RKEHOSKIE'; exit ! } cat $ORATAB| egrep -v '#|^$|ASM' | while read i do (         ORACLE_HOME=`echo ${i} | awk -F":" '{ print $2 }'`         ORACLE_SID=`echo ${i}| awk -F":" '{ print $1 }'`         export ORACLE_HOME ORACLE_SID         PATH=$ORACLE_HOME/bin:$PATH         export PATH         start echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"         sleep 2 ) | tee -a  check.log done
Post a Comment
Read more

CHECK SCHEMA STATS SCRIPT IN ORACLE DATABASE

 scripts]$ cat check_schema_stats.sh #!/bin/bash # #periodic analyze schema cron script # # to run #  analyze_schema <sid> <schema owner> # # # # ORACLE_SID=$1 ORAENV_ASK=NO SCHEMA_OWNER=$2 echo $SCHEMA_OWNER export ORACLE_SID export SCHEMA_OWNER export SCHEMA_PASSWD ORAENV_ASK=NO sqlplus -s '/as sysdba' <<eof ; spool /tmp/analyze_schema_$SCHEMA_OWNER.log SET SERVEROUTPUT ON SET TIME ON SET TIMING ON SET ECHO ON SELECT TRUNC(LAST_ANALYZED) LAST_ANALYZED, COUNT(*) TABLES FROM ALL_TABLES WHERE OWNER = '$SCHEMA_OWNER' GROUP BY TRUNC(LAST_ANALYZED) ORDER BY 1; SELECT TRUNC(LAST_ANALYZED) LAST_ANALYZED, COUNT(*) INDICES FROM ALL_INDEXES WHERE OWNER = '$SCHEMA_OWNER' GROUP BY TRUNC(LAST_ANALYZED) ORDER BY 1; spool off ; disconnect quit; eof
Post a Comment
Read more

ACTIVE SESSION DETAILS SCRIPT IN ORACLE DATABASE

irut@/home/oracle/dba/scripts: cat active_session_history.sql spool active_session_history.log set pages 5000 set lines 500 set trims on col sample_time format a25 col event format a30 col sql_id format a15 col sql_text format a200 col session_id format 99999 col p1text format a10 col p2text format a30 select sh.inst_id,        sh.sample_time,        sh.sql_id,        sh.session_id,        sh.blocking_session,        sh.wait_time,        sh.time_waited,        sh.event,        sh.p1,        sh.p1text,        sh.p2,        sh.p2text,        (select distinct substr(t.sql_text,1,200)         from gv$sqlarea t         where sh.sql_id = t.sql_id) sql_text from gv$active_session_history sh where sh.event is not null --and sh.sample_time > sysdate-10/1440 and sh.session_id=&SID order by sh.sample_time,sample_id; spool off;
Post a Comment
Read more