Query based export/import using datapump

#########################################
Query based export/import using datapump
#########################################

Here is the scenario/requirement for export the output of the below query should be imported to another schema of Non-Prod database.

select *
from inv.mtl_mat_trans
where transaction_date >= '29-JUN-2013'
and transaction_type_id in
(
13
,26
,24
,41
,32
,24
,18
,14
,29
,31
,99
);

Steps to be performed:

[oracle@host]$ cat expdp_q.par
DIRECTORY = EXPDIR
DUMPFILE  = expdp%U.dmp
FILESIZE  = 5G
LOGFILE   = expdp_query.log
SCHEMAS   = INV
INCLUDE   = TABLE:"IN ('MTL_MAT_TRANS')"
QUERY     = INV.MTL_MAT_TRANS:"WHERE transaction_date >= '29-JUN-2013' and transaction_type_id in(13,26,24,41,32,24,18,14,29,31,99)"


impdp system/****
DIRECTORY=EXPDIR
FILE=expdp%U.dmp
LOGFILE=impdp_q.log 
remap_schema=INV:SUPPORT REMAP_TABLESPACE=APPS_TS_TX_DATA:SUPPORT_DATA 
CONTENT=data_only


ex:
expdp system/***** parfile=expdp_q.par 
impdp system/***** parfile=impdp_q.par 

TOP SEGMENTS OF A ORACLE DATABASE

####################################################
TOP 5 LARGE SEGMENT GROWTH(OBJECT GROWTH)
####################################################

Please find the below query to identify the top 5 segments in an oracle database with resepective to its size.

col SEGMENT_NAME for a30
col owner for a20
col SEGMENT_TYPE for a20
SELECT * FROM
(
select
   owner,
   SEGMENT_NAME,
   SEGMENT_TYPE,
   BYTES/1024/1024/1024 GB,
   TABLESPACE_NAME
from
   dba_segments order by 4 desc 
) WHERE
ROWNUM <= 5;


OWNER                SEGMENT_NAME                   SEGMENT_TYPE                      GB TABLESPACE_NAME
-------------------- ------------------------------ -------------------- --------------- --------------------
ABC              POI_ATNL_KIT_DATA_KT            TABLE                634.56091308594 USERS
ABC              POS_ATNL_DATA_BKP_KIT         TABLE                      477.84375 USERS
ABC              POS_ADDTNL_DATA_ADD_KT_A     TABLE                 140.8427734375 USERS
XYZ              AT_DW_SALES_RPT            TABLE                      114.28125 USERS
YXAD             P_DATA_UDTC_BKLD                 TABLE                103.59356689453 USERS

Determine the process which is locking an Oracle account with incorrect password

Here is the procedure to Determine the process which is locking an Oracle account with incorrect password, which search from the dba_audit_session view for records with a returncode equal to 1017 which indicate a failed logon.

column username format a15
column userhost format a25
column terminal format a15
column timestamp format a15
column action_name format a15
select username, userhost, terminal, timestamp, action_name from sys.dba_audit_trail where RETURNCODE='1017';

USERNAME        USERHOST                  TERMINAL   TIMESTAMP   ACTION_NAME
--------------- ------------------------- ---------- ----------- -----------
appsread              <HOSTNAME>                                    02-APR-14       LOGON
appsread              <HOSTNAME>                                    02-APR-14       LOGON
appsread             <HOSTNAME>                                     02-APR-14       LOGON
appsread             <HOSTNAME>                                     02-APR-14       LOGON
appsread             <HOSTNAME>                                     02-APR-14       LOGON

HIGH WATER USAGE (HWM USAGE)

##############################
HIGH WATER USAGE (HWM USAGE)
##############################

To resize any datafile to reclaim some space on the datafile. Need to check for the High water mark usage and based on that Water mark we may have to resize the respective datafile to get the space reclaimed.

We had an critical space issue on the dataware house environment to reclaim the space identified the datafiles using below query and resized the respective datafiles where we can get some space through this process.

Note: This is for a temporary Fix and have to plan for a better storage.

set verify off
column file_name format a60 word_wrapped
column smallest format 9999,990 heading "Smallest|Size|Poss."
column currsize format 9999,990 heading "Current|Size"
column savings  format 9999,990 heading "Poss.|Savings"
set pages 100
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
SELECT FILE_NAME,  CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL((NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS
where tablespace_name ='USERS' GROUP BY FILE_ID ) DBAFS
WHERE DBADF.TABLESPACE_NAME='USERS' and DBADF.FILE_ID = DBAFS.FILE_ID(+) and
(CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )) > 300;
                                                              Smallest
                                                                  Size   Current     Poss.
FILE_NAME                                                        Poss.      Size   Savings
------------------------------------------------------------ --------- --------- ---------
+DATA_PRD/PROD/datafile/users_data.363.825564829          31,396    31,744       348
+DATA_PRD/PROD/datafile/users_data.1042.866689707         16,076    16,512       436
                                                                                 ---------
sum                                                                                    784

How to check whether patch is applied via Hotpatch or Not in R12.2.3

#######################################################
TO CHECK WHETHER PATCH IS APPLIED VIA HOTPATCH OR NOT
########################################################

set pagesize 200;
set linesize 160;
column adop_session_id format 999999999999;
column bug_number format a15;
column status format a15;
column applied_file_system_base format a23;
column patch_file_system_base format a23;
column adpatch_options format a15;
column node_name format a15;
column end_date format a15;
column clone_status format a15;
select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS
from ad_adop_session_patches where BUG_NUMBER='19677937' order by end_date desc;

ADOP_SESSION_ID BUG_NUMBER      STATUS          APPLIED_FILE_SYSTEM_BAS PATCH_FILE_SYSTEM_BASE  ADPATCH_OPTIONS NODE_NAME       END_DATE        CLONE_STATUS
--------------- --------------- --------------- ----------------------- ----------------------- --------------- --------------- --------------- ---------------
             32 19677937        Y               /u01//app/oracle/prod/fs                         hotpatch        <HOSTNAME>        27-MAR-14

Above information provides you that it uses session id 32 while applying as well as it applied in Hotpatch mode on so and so date.

Tha

Logfile Locations in R12.2.3

Please find the below logfiles of Admin server/oacore/forms and so on.

Admin server - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/AdminServer/logs/AdminServer.log
oacore logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.log
oacore out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.out
acore diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1-diagnostic.log
oafm logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.log
oafm outfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.out
oafm diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1-diagnostic.log
form server log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.log
form server access log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/access.log
form server out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.out
form server diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1-diagnostic.log
forms-c4ws_server1 log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.log
forms-c4ws_server1 out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.out
forms-c4ws_server1 diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1-diagnostic.log

How to find database growth on a Monthly wise

###################################
DATABASE GROWTH ON A MONTHLY WISE
##################################

select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from   v$datafile
group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')
order by   1, 2;

YEAR MO              GB
---- -- ---------------
2011 09              74
2012 06            4579
2012 07             334
2012 08             523
2012 09             652