\\10.200.1.

55\Department Storage\Information Technology\IT Infrastructure\IT

DataBase Admins\Qamber

1560327.1
10010081187862017

10.51.31.16

10.200.131.36
uhfdb
admin123/?

MFA KEY
*******
cylynsjpykqlzkcm

rehan.maqbool@jubileelife.com
Philips_99

faizerati@gmail.com PW:000Adj090

dba-primatics@primaticsfinancial.com

Matthew White PNC

Su Kyeong PNC

for cross join queries vikram tiwari

MOVE TABLES WITHIN TABLESPACE

-----------------------------

alter table X move tablespace Y

alter index indexname rebuild online;

select distinct segment_type from dba_segments where tablespace_name='USERS';

you can do :

select owner,segment_name, segment_type from dba_segments where tablespace_name =

'EABID_TABLESPACE';

select distinct segment_type from dba_segments where tablespace_name = 'USERS';

select owner,segment_name,bytes/1024/1024 MB from dba_segments where

tablespace_name = 'USERS' and segment_type='TABLE' and bytes/1024/1024 > 100;

select
OWNER SEGMENT_NAME
MB

REGRESSIONMETADATADATA_902 DSSMDOBJDEF2
506.1875
REGRESSIONMETADATADATA_902 DSSMDOBJDEF2
525

select owner,segment_name,bytes/1024/1024 MB from dba_segments where

tablespace_name = 'USERS' and segment_type='TABLE' and segment_name
='DSSMDOBJDEF2';

select owner,segment_name,bytes/1024/1024 MB,segment_type from dba_segments where

tablespace_name = 'USERS' and segment_name like '%DSSMDOBJDEF2%';

alter table REGRESSIONMETADATADATA_902.DSSMDOBJDEF2 move tablespace users;

alter index loa_data.I_PLC_CUSTID_UK rebuild online parallel 4;

alter index test_idx rebuild nologging parallel (degree 16 );

and :

select object_name, object_type

from dba_objects
where sysdate-created <2;

first one will give you all the segments in the tablespace you enter, and second
one will give you the objects that were created less than 2 days ago, that way you
can find out what filled your tablespace.

select owner, table_name, tablespace_name

from dba_tables
where table_name='EMP';

select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name

from dba_extents where tablespace_name = 'USERS' and file_id=22
union all
select file_id, block_id, block_id+blocks-1, 'free' from dba_free_space where
tablespace_name = 'USERS' and file_id=22 order by file_id, first_block
/

-------------------------------------------------------------------

select owner,index_name,index_type from dba_indexes where table_name in

(select table_name from dba_tables where tablespace_name='USERS' and table_name in
(select segment_name from dba_segments where tablespace_name = 'USERS' and
segment_type='TABLE' and bytes/1024/1024 > 100));

select owner,table_name from dba_tables where tablespace_name='USERS' and

table_name in (select segment_name from dba_segments where tablespace_name =
'USERS' and segment_type='TABLE' and bytes/1024/1024 > 100);

***********************************************************************************
***********************************************************************

TABLE MOVE
----------

select 'alter table '||owner||'.'||table_name||' move tablespace USERS;' from

dba_tables where tablespace_name='USERS' and table_name in (select segment_name
from dba_segments where tablespace_name = 'USERS' and segment_type='TABLE');

TABLE PARTITION MOVE

--------------------

select 'alter table '||table_owner||'.'||table_name||' move partition '||

PARTITION_NAME||' tablespace '||TABLESPACE_NAME||';' from dba_tab_partitions where
tablespace_name='USERS';

TABLE SUB PARTITION MOVE

------------------------

SELECT 'alter table '||table_owner||'.'||table_name||' move subpartition '||

subpartition_name||' tablespace '||tablespace_name||' ;' FROM dba_tab_subpartitions
WHERE tablespace_name='USERS';

LOB MOVE
--------

select 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store

as (tablespace '||tablespace_name||');' from dba_lobs where
tablespace_name='USERS';
ALTER TABLE OWNER.TABLE_NAME MOVE LOB(LOB_COLUMN) STORE AS (TABLESPACE
NEW_TABLESPACE_NAME);

LOB SUBPARTITION MOVE

---------------------
SELECT 'ALTER TABLE '||TABLE_OWNER ||'.'|| TABLE_NAME || ' MOVE SUBPARTITION '||
SUBPARTITION_NAME ||' TABLESPACE USERS LOB ('||COLUMN_NAME||') STORE AS
(TABLESPACE USERS);' FROM DBA_LOB_SUBPARTITIONS WHERE TABLESPACE_NAME='USERS';

REBUILD INDEX ONLINE

--------------------

select 'alter index '||owner||'.'||index_name||' rebuild online;' from dba_indexes

where table_name in
(select table_name from dba_tables where tablespace_name='USERS' and table_name in
(select segment_name from dba_segments where tablespace_name = 'USERS' and
segment_type='TABLE'));

select 'alter index '||owner||'.'||index_name||' rebuild online parallel 10;' from

dba_indexes where table_name ='TBLPRODUCTIONLOG' and status='VALID';

REBUILD PARTITION INDEX ONLINE

-------------------------------

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||

partition_name||' online tablespace_name='||tablespace_name||';' from
dba_ind_partitions where tablespace_name='USERS';

Select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||

partition_name||' ONLINE PARALLEL (DEGREE 10);' from dba_ind_partitions
where INDEX_NAME IN (SELECT INDEX_NAME from dba_indexes where
TABLE_NAME='TBLPRODUCTIONLOG' and status='N/A');

alter index IX_LOCAL_PART_IND logging noparallel;

-- Rebuild Sub Partition index

Select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||
subpartition_name||' ONLINE;' from dba_ind_subpartitions where
INDEX_NAME='PROVIDE_INDEX_NAME';

***********************************************************************************
*****************************************************************************

COLUMN owner format a30 heading 'owner'

COLUMN object_name format a50 heading 'Object|Name'
COLUMN object_type format a40 heading 'Object_Type'

select owner,object_name,object_type from dba_objects where owner

in('ZZ_STAGE_313','MABBAS_IVP_313','SIT_OLAP_FRESH211','SIT_INFORMATICA_115','DEVMS
TR_112_20','SK_SDA1_313','TC_UITEST_CFP',
'VP_MSTR','FUNTEST_313_GLOBAL','MABBAS_OLAP_313','CTXSYS','SIT_LVP_FRESH211','SIT_S
DA_FRESH211','MABBAS_ESI32',
'TCMULTI_SDADB_31,XDB') order by owner;

select tablespace_name, file_id, bytes/1048576 mb from dba_data_files where

tablespace_name = '&_tbs' order by 1,2 asc
SET SERVEROUTPUT ON

DECLARE
V_FILE_ID NUMBER;
V_BLOCK_SIZE NUMBER;
V_RESIZE_SIZE NUMBER;
BEGIN
V_FILE_ID := &_FILE_ID;
V_RESIZE_SIZE := &_RESIZE_FILE_TO_MB;
SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO
RESIZE THE FILE TO '||V_RESIZE_SIZE||' MB');
DBMS_OUTPUT.PUT_LINE('=============================================================
======');
DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
DBMS_OUTPUT.PUT_LINE('=============================================================
======');
for my_record in (
SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
FROM DBA_EXTENTS
WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
AND FILE_ID = V_FILE_ID
AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
ORDER BY 1) LOOP
DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE('=============================================================
======');
DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
DBMS_OUTPUT.PUT_LINE('=============================================================
======');
for my_record in (
SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' -
OBJECT TYPE = '||SEGMENT_TYPE) ONAME
FROM DBA_EXTENTS
WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
AND FILE_ID = V_FILE_ID
AND SEGMENT_TYPE LIKE '%PARTITION%'
ORDER BY 1) LOOP
DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
END LOOP;
END;
/

select table_owner,table_name,PARTITION_NAME,TABLESPACE_NAME from

dba_tab_partitions where tablespace_name='USERS' and table_owner='SK_SDA1_313' and
table_name='IMPAIREDCASHFLOW';
alter table partitioned move partition part_3 tablespace users;

expdp \"/ as sysdba\" directory=DEF_DIR1 dumpfile=exp_tablespace%U.dmp

logfile=exp_tablespace.log tablespaces='EABID_TABLESPACE' parallel=4

expdp \"/ as sysdba\" directory=data_pump_dir dumpfile=CEU-363%U.dmp logfile=CEU-

363.log schemas=sda_es188client28_41,olap_es188client28_41,lvp_es188client28_41
parallel=4 compression=all exclude=statistics

sda_es188client28_41,olap_es188client28_41,lvp_es188client28_41

expdp t1/t1 directory=EXP_FULL_BACKUP_DIR dumpfile=EVL-38365_%U.dmp logfile=EVL-

38365.log schemas=GLOBAL_R35_SAAD,OLAP_R35_SAAD,OLTP_R35_SAAD,SDA_R35_SAAD
compression=all exclude=statistics access_method=direct_path

select owner,segment_type,segment_name from dba_segments where

tablespace_name='USERS' order by 1;

impdp \"/ as sysdba\" directory=DEF_DIR1 dumpfile=exp_tablespace%U.dmp

logfile=imp_tablespace.log remap_tablespace=users:system sqlfile=asd.sql parallel=4

expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=test9876.dmp

content=METADATA_ONLY schemas=OLAP_ES30CLIENT30_351 include=index

expdp \"/ as sysdba\" directory=data_pump_dir logfile=asdf1234.log

content=METADATA_ONLY dumpfile=metadatatest1234.dmp schemas=OLAP_ES30CLIENT5_351
include=index

impdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=metadatatest1234.dmp

logfile=test9876_imp.log sqlfile=OLAP_ES30CLIENT5_351.sql include=index

table_exists_action=replace remap_tablespace=users:system

/u02/app/oracle/dpdump/fullbackup
COLUMN owner format a20 heading 'owner'
COLUMN table_name format a20 heading 'Table|Name'
COLUMN column_name format a20 heading 'Column|Name'
COLUMN tablespace_name format a20 heading 'Tablespace|Name'
COLUMN index_name format a20 heading 'index|Name'

select owner,table_name,column_name,tablespace_name,index_name from dba_lobs where

tablespace_name='EABID_TABLESPACE';

alter table NC_IVP_33.VALUATIONMODELJAR move lob (MODEL) store as (tablespace

EABID_TABLESPACE);

select a.segment_name,a.file_id from dba_extents a,dba_data_files b where

a.file_id=b.file_id and b.file_id=36;

COLUMN owner format a40 heading 'owner'

select owner,object_name from dba_objects where object_name='CASHFLOW';

DECLARE
l_total INTEGER := 10000;

CURSOR employee_id_cur
IS
SELECT employee_id
FROM plch_employees
ORDER BY salary ASC;

l_employee_id employee_id_cur%ROWTYPE;
BEGIN
OPEN employee_id_cur;

LOOP
FETCH employee_id_cur INTO l_employee_id;
EXIT WHEN employee_id_cur%NOTFOUND;

assign_bonus (l_employee_id, l_total);

EXIT WHEN l_total <= 0;
END LOOP;

CLOSE employees_cur;
END;

declare

cursor c1 is select owner, table_name from dba_tables where tablespace_name='USERS'

and table_name in (select segment_name from dba_segments where tablespace_name =
'USERS' and segment_type='TABLE' and bytes/1024/1024 > 100);
table_name1 varchar2(70);
mowner varchar2(100);
mcont number;
begin
select count(table_name) into mcont from dba_tables
where tablespace_name='USERS' and table_name in (select segment_name from
dba_segments where tablespace_name = 'USERS' and segment_type='TABLE' and
bytes/1024/1024 > 100);
open c1;
loop
fetch c1 into mowner, table_name1;
EXIT WHEN c1%NOTFOUND;
for i in 1..mcont loop
dbms_output.put_line('Currently moving table : '||mowner||'.'||
table_name1);
--dbms_output.put_line('Currently moving table : '||table_name1);
--execute immediate 'alter table '||trim(mowner)||'.'||
trim(table_name1)||' move tablespace users;';
end loop;

declare
cursor c2 is select owner,index_name from dba_indexes where
table_name in
(select table_name from dba_tables where tablespace_name='USERS' and table_name in
(select segment_name from dba_segments where tablespace_name = 'USERS' and
segment_type='TABLE' and bytes/1024/1024 > 100));

mownerind varchar2(100);
index_name1 varchar2(100);
mcont1 number;
begin

select count(index_name) into mcont1 from dba_indexes where table_name in

(select table_name from dba_tables where tablespace_name='USERS' and table_name in
(select segment_name from dba_segments where tablespace_name = 'USERS' and
segment_type='TABLE' and bytes/1024/1024 > 100));

open c2;
loop
fetch c2 into mownerind, index_name1;
EXIT WHEN c2%NOTFOUND;
for i in 1..mcont1 loop
dbms_output.put_line('Currently rebuilding index : '||
mownerind||'.'||index_name1);
--dbms_output.put_line('Currently moving table : '||table_name1);
--execute immediate 'alter index '||trim(mowner)||'.'||
trim(index_name1)||' rebuild online;';
end loop;
end loop;
--end loop;
close c2;
--close c1;
end;

Name FILE_ID Name

ALLOCATED_GB FREE_GB
---------------------------------------- ----------
-----------------------------------------------------------------------------------
------- ------------ ----------
USERS 33
/u01/app/oracle/oradata/ora11g/users07.dbf
10.8828125 6.41497803
USERS 4
/u01/app/oracle/oradata/ora11g/users01.dbf
31.9863281 25.0748291
USERS 21
/u01/app/oracle/oradata/ora11g/users03.dbf
31.9453125 22.4301147
USERS 20
/u01/app/oracle/oradata/ora11g/users02.dbf
29.3759766 22.3052368
USERS 26
/u01/app/oracle/oradata/ora11g/users06.dbf
30.90625 23.7432861
USERS 25
/u01/app/oracle/oradata/ora11g/users05.dbf
31.9999847 26.8425293
USERS 22
/u01/app/oracle/oradata/ora11g/users04.dbf
31.8691406 24.9429321

alter database datafile '/u01/app/oracle/oradata/ora11g/users07.dbf' resize 5G;

alter database datafile '/u01/app/oracle/oradata/ora11g/users01.dbf' resize 10G;

alter database datafile '/u01/app/oracle/oradata/ora11g/users03.dbf' resize 10G;

alter database datafile '/u01/app/oracle/oradata/ora11g/users02.dbf' resize 10G;

alter database datafile '/u01/app/oracle/oradata/ora11g/users06.dbf' resize 10G;

alter database datafile '/u01/app/oracle/oradata/ora11g/users05.dbf' resize 10G;

alter database datafile '/u01/app/oracle/oradata/ora11g/users04.dbf' resize 10G;

DEPLOY ADAPTER IN PRODUCTION

****************************

Zip files to be placed on

/opt/app/informatica_data/COMMON/deployAdapter/UATsrc

cd /opt/app/informatica_data/COMMON/deployAdapter/UATsrc
chmod 775 *.zip

Run the following script

/opt/app/informatica_data/COMMON/deployAdapter/UATshl/runAdpDeployment.sh
Zip files to be placed on
/opt/app/informatica_data/COMMON/deployAdapter/src

cd /opt/app/informatica_data/COMMON/deployAdapter/src
chmod 775 *.zip

Run the following script

/opt/app/informatica_data/COMMON/deployAdapter/shl/runAdpDeployment.sh

CREATE SUPERDATA LOADER ON PROD

*******************************

cd /opt/app/informatica_data/INFRA_ADMIN
sh createSuperDataLoaderFolder_UAT.sh
sh createSuperDataLoaderFolder.sh

GATHER STATS
************

exec DBMS_STATS.gather_schema_stats('SYS', estimate_percent =>

dbms_stats.auto_sample_size , method_opt =>'FOR ALL COLUMNS SIZE SKEWONLY',cascade
=> TRUE, degree => 10);

EXEC DBMS_STATS.gather_table_stats('OLAP_FIRSTHORIZON', 'TIMEDIM', estimate_percent

=> dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', cascade
=> TRUE, degree => 8);

EXEC DBMS_STATS.gather_index_stats('IRIS', 'MVTRAN_COMB_IND_1', estimate_percent =>

dbms_stats.auto_sample_size,degree => 10);

execute dbms_stats.gather_table_stats(ownname =>'STAGE_PNC', tabname =>

'LOAN',estimate_percent => dbms_stats.auto_sample_size, method_opt =>'FOR ALL
COLUMNS SIZE AUTO',cascade => TRUE,degree => 8);

for all indexed columns size auto

ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;

--LVP_FIRSTHORIZON.ETLREPORT
SDA_FIRSTHORIZON.ETLREPORT
SDA_FIRSTHORIZON.ETLREPORT
--STAGE_FIRSTHORIZON.ESIETLREPORT
OLAP_FIRSTHORIZON.OLAPETLREPORT
OLAPFIRSTHORIZON.TIMEDIM

exec
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'ALFRESCO_BKP_TEST',OPTIONS=>'GATHER',ESTIM
ATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'for all indexed columns size
auto',CASCADE=>TRUE,DEGREE=>10);

EXEC DBMS_UTILITY.compile_schema('BASELIII');

exec
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'OLAP_ES194CLIENT15',OPTIONS=>'GATHER',ESTI
MATE_PERCENT=>100,METHOD_OPT=>'for all indexed columns size
auto',CASCADE=>TRUE,DEGREE=>5);

LONGOPS
*******

set lines 999

set pagesize 50000
col OPNAME for a35

select
a.sid,a.serial#,b.username,b.status,a.opname,
to_char(a.start_time,' dd-MON-YYYY HH24:MI:SS') START_TIME,
to_char(a.last_update_time,' dd-MON-YYYY HH24:MI:SS') LAST_UPDATE_TIME,
a.time_remaining as "Time remaining ses",
a.time_remaining/60 as "Time remaining min"
from v$session_longops a, v$session b
where a.sid=b.sid and b.status='ACTIVE'
and b.username='OLAP_PNC_CONS_UAT' and b.sql_id is not null;

LONGOPS WITH SQL

****************

select s.username,s.sid,s.serial#,s.machine,sl.opname,
round(sl.elapsed_seconds/60) ||':' || mod(sl.elapsed_seconds,60) elapsed,
round(sl.time_remaining/60) ||':' || mod(sl.time_remaining,60) remaining,
round(sl.sofar/decode(sl.totalwork,0,1,sl.totalwork)*100, 2) progress_pct,
sql_fulltext from v$session s inner join v$session_longops sl on
s.sid=sl.sid and s.serial#=sl.serial# left outer join v$sqlstats SQ on
sq.sql_id=sl.sql_id
where status='ACTIVE'
and round(sl.sofar/decode(sl.totalwork,0,1,sl.totalwork)*100, 2) <> 100
order by round(sl.sofar/decode(sl.totalwork,0,1,sl.totalwork)*100, 2)

LONG OPS TIME ESTIMATION

************************

set lines 300

column machine format a30
column progress_pct format 99999999.00
column elapsed format a10
column remaining format a10

select s.sid,s.serial#,s.machine,
round(sl.elapsed_seconds/60) ||':' || mod(sl.elapsed_seconds,60) elapsed,
round(sl.time_remaining/60) ||':' || mod(sl.time_remaining,60) remaining,
round(sl.sofar/sl.totalwork*100, 2) progress_pct
from v$session s, v$session_longops sl
where s.sid=sl.sid and s.serial#=sl.serial#
and s.sql_id is not null;

PERCENTAGE OF JOBS RUNNING

**************************

set lines 200

col username for a10
col message for a58
col opname for a20

select
round(sofar/totalwork*100,2) "% Complete",
sid,opname,time_remaining,message,username,sql_id,
to_char(START_TIME,' dd-Mon-YYYY HH24:mi:ss') START_TIME
--,to_char(LAST_UPDATE_TIME,' dd-Mon-YYYY HH24:mi:ss') LAST_UPDATE_TIME
from
v$session_longops
where
sofar <> totalwork
and opname like'%&Give_DataPump_JobName%' -- Give Data Pump Job Name here
and round(sofar/totalwork*100,2) < 100
--and username like 'SYS'
order by sid;

===================================================
SELECT
opname
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(TIME_REMAINING /60) MAX_TIME_REMAINING_IN_MIN,
FLOOR(ELAPSED_SECONDS/60) TIME_SPENT_IN_MIN,
AR.SQL_FULLTEXT,
AR.PARSING_SCHEMA_NAME,
AR.MODULE client_tool
FROM V$SESSION_LONGOPS L, V$SQLAREA AR
FROM V$SESSION_LONGOPS L, V$SQLAREA AR
WHERE L.SQL_ID = AR.SQL_ID
AND TOTALWORK > 0
AND ar.users_executing > 0
AND sofar != totalwork;
=====================================================

select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2)
"%_Complete" from v$session_longops
where opname like 'RMAN%' and opname not like '%aggregate%' and totalwork <> 0 and
sofar <> totalwork order by 6;

select SID,OPEN_TIME, (sysdate-open_time)*24*60

ela_time_minutes,close_time,filename,(EFFECTIVE_BYTES_PER_SECOND/1024/1024/1024)*36
00 SPEED_GBPERHR,(TOTAL_BYTES/1024/1024/1024)
GBTOBEREAD_FROM_FILE,(bytes)/1024/1024/1024 GB_Written_sofar from
V$BACKUP_ASYNC_IO;

select sid, serial, long_waits, io_count, filename,

maxopenfiles,effective_bytes_per_second EFF_BYTES_SEC from v$backup_async_io;

set lines 1000

select b.sid, b.serial#, a.spid, b.client_info from v$process a, v$session b where
a.addr=b.paddr and client_info like 'rman%';
KILL RMAN SESSIONS
******************
select 'alter system kill session ''' ||b.sid|| ',' || b.serial#|| ''' immediate;'
from v$process a, v$session b where a.addr=b.paddr and client_info like 'rman%';

CHECK ASM DISK GROUP SIZE

*************************

select name "disk group name", total_mb/1024 GB, free_mb/1024 freeGB from
v$asm_diskgroup;

COMPRESS COLUMN DROP

********************

Following are the steps to resolve this type of problem in future. Kindly use this
for now.

Step-1: Run this statement, and then execute the statements in output.

select 'alter table '||table_name||' modify partition '||PARTITION_NAME||'

nocompress;' from user_tab_partitions where table_name = 'INSTRUMENTMASTER';

Step-2: Run this statement, and then execute the statements in output.

select 'alter table '||table_name||' move partition '||PARTITION_NAME||';' from

user_tab_partitions where table_name = 'INSTRUMENTMASTER';

Step-3: Execute this to mark column as unused.

alter table INSTRUMENTMASTER set unused column LIENPOSITION_DIM_ID;

Step-4: Drop unused columns

alter table INSTRUMENTMASTER drop unused columns;

Step-5: Run this statement, and then execute the statements in output.

select 'alter table '||table_name||' modify partition '||PARTITION_NAME||'

compress;' from user_tab_partitions where table_name = 'INSTRUMENTMASTER';

Step-6: Run this statement, and then execute the statements in output.

select 'alter table '||table_name||' move partition '||PARTITION_NAME||';' from

user_tab_partitions where table_name = 'INSTRUMENTMASTER';

1. Modify table partitions to no-compress.

2. Modify table to move Sub-Partition to Tablespace that is not compressed.
3. Modify Default Attributes of Sub-Partitons to the same no-compress
Tablespace.
4. Modify Default Attributes of Table to the no compressed Tablespace.
5. Modify table to no-compress.
6. Modify table to unused column needed to be dropped.
7. Modify table to drop unused columns.
8. Modify table to compress.
9. Modify table to move Sub-Partition to compressed Tablespace.
10. Modify Default Attributes of Sub-Partitons to the compressed Tablespace.
11. Modify table to move Partition to compressed Tablespace.
12. Modify Default Attributes of Table to the compressed Tablespace.
13. Modify table to compress.
14. Rebuild Subpartition Indexes online.
15. Gather Table Statistics.

DELETING DATAPUMP JOB

*********************

DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH('SYS_IMPORT_FULL_06','SYSTEM');
DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

where SYS_EXPORT_FULL_01 = name of job

and SYSTEM = owner of job

FIND BLOCKING SESSIONS

**********************

select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS
blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

select distinct
a.sid "waiting sid"
, d.sql_text "waiting SQL"
, a.ROW_WAIT_OBJ# "locked object"
, a.BLOCKING_SESSION "blocking sid"
, c.sql_text "SQL from blocking session"
from v$session a, v$active_session_history b, v$sql c, v$sql d
where a.event='enq: TX - row lock contention'
and a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#;

select l1.sid, ' IS BLOCKING ', l2.sid

from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;

BLOCKING SESSION IN RAC

***********************
CREATE OR REPLACE PROCEDURE kill_blocker
AS
sqlstmt VARCHAR2 (1000);
BEGIN
FOR x IN (SELECT gvh.SID sessid, gvs.serial# serial,
gvh.inst_id instance_id
FROM gv$lock gvh, gv$lock gvw, gv$session gvs
WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM gv$lock
WHERE lmode = 0)
AND gvh.id1 = gvw.id1
AND gvh.id2 = gvw.id2
AND gvh.request = 0
AND gvw.lmode = 0
AND gvh.SID = gvs.SID
AND gvh.inst_id = gvs.inst_id)
LOOP
sqlstmt :=
'ALTER SYSTEM KILL SESSION "'
|| x.sessid
|| ','
|| x.serial
|| ',@'
|| x.instance_id
|| "";
DBMS_OUTPUT.put_line (sqlstmt);

EXECUTE IMMEDIATE sqlstmt;

END kill_blovk;
END TEST;
/

set serveroutput on
exec kill_blocker;

==========================================
SELECT DECODE (l.BLOCK, 0, 'Waiting', 'Blocking ->') user_status
,CHR (39) || s.SID || ',' || s.serial# || CHR (39) sid_serial
,(SELECT instance_name FROM gv$instance WHERE inst_id = l.inst_id)
conn_instance
,s.SID
,s.PROGRAM
,s.osuser
,s.machine
,DECODE (l.TYPE,'RT', 'Redo Log Buffer','TD', 'Dictionary'
,'TM', 'DML','TS', 'Temp Segments','TX', 'Transaction'
,'UL', 'User','RW', 'Row Wait',l.TYPE) lock_type
--,id1
--,id2
,DECODE (l.lmode,0, 'None',1, 'Null',2, 'Row Share',3, 'Row Excl.'
,4, 'Share',5, 'S/Row Excl.',6, 'Exclusive'
,LTRIM (TO_CHAR (lmode, '990'))) lock_mode
,ctime
--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
,object_name
FROM
gv$lock l
JOIN
gv$session s
ON (l.inst_id = s.inst_id
AND l.SID = s.SID)
JOIN gv$locked_object o
ON (o.inst_id = s.inst_id
AND s.SID = o.session_id)
JOIN dba_objects d
ON (d.object_id = o.object_id)
WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
FROM gv$lock
WHERE request > 0)
ORDER BY id1, id2, ctime DESC;

WITH sessions AS
(SELECT /*+materialize*/
sid,SERIAL#, blocking_session, MACHINE,row_wait_obj#,
sql_id,OSUSER,STATUS,LOGON_TIME,BLOCKING_INSTANCE,program
FROM v$session
)
SELECT distinct
LPAD(' ', LEVEL ) || sid sid, s.SERIAL#,object_name,
blocking_session,program,
substr(sql_text,1,40) sql_text,
s.OSUSER,s.STATUS,s.LOGON_TIME,s.BLOCKING_INSTANCE
,'ALTER SYSTEM KILL SESSION ''' || SID || ',' || s.SERIAL# || '''
IMMEDIATE;' as Exec_cmd
FROM sessions s
LEFT OUTER JOIN dba_objects
ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql
USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions)
or blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
START WITH blocking_session IS NULL;

BLOCKING SESSIONS SQL FROM HISTORY

**********************************
select * from dba_hist_active_sess_history where event like '%enq: TX - row lock
contention%' order by sample_time desc;

BLOCKING SESSION DETAILS FOR LAST DAY

*************************************
SELECT DISTINCT a.sql_id,
a.blocking_session blocker_session_sid,
a.blocking_session_serial# blocker_serial#,
a.SESSION_ID blocked_session_sid,
a.SESSION_SERIAL# blocked_serial#,
a.EVENT,
a.module,
a.sample_time,
s.sql_text
FROM GV$ACTIVE_SESSION_HISTORY a, gv$sql s
WHERE a.sql_id = s.sql_id
AND blocking_session IS NOT NULL
AND a.user_id <> 0
AND a.sample_time >= SYSDATE - 1
and a.BLOCKING_SESSION_STATUS not in ('NO HOLDER','NOT IN WAIT' )
order by a.sample_time desc;

IDENTIFY RESOURCE BUSY MESSAGE USER

***********************************

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name

FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;

FIND OS PROCESS ID
******************

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal,

s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.sid=244;
STATE OF OS PROCESSES
*********************

ps axjf|grep 14011

PROCESS STATE CODES

R running or runnable (on run queue)
D uninterruptible sleep (usually IO)
S interruptible sleep (waiting for an event to complete)
Z defunct/zombie, terminated but not reaped by its parent
T stopped, either by a job control signal or because
it is being traced

ACL
****

/oradump/extractproperties.properties

select * from DBA_NETWORK_ACL_PRIVILEGES;

select * from DBA_NETWORK_ACLS;

=======================================================================

--GRANT/ADD FTP RIGHTS TO DB USER

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'ftp.xml',
principal => 'MSTR_COMPREHENSIVE_OLAP_6000',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'ftp.xml',
principal => 'MSTR_COMPREHENSIVE_OLAP_6000',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'ftp.xml',
host => '192.168.121.146');
COMMIT;
END;

=======================================================================

--DELETE/REVOKE GRANTED FTP PRIVILEGES

BEGIN
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
acl => 'ftp.xml',
principal => 'MSTR_COMPREHENSIVE_OLAP_6000',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
acl => 'ftp.xml',
principal => 'MSTR_COMPREHENSIVE_OLAP_6000',
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/

==========================================================

----CREATE NEW ACL IF DOESN'T EXIST (ONLY ONCE)

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'ftp.xml',
description => 'FTP ACL',
principal => 'MSTR_COMPREHENSIVE_OLAP_6000',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'ftp.xml',
principal => 'MSTR_COMPREHENSIVE_OLAP_6000',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'ftp.xml',
host => '192.168.121.146');
COMMIT;
END;
/

KILLING SESSION SCRIPT

**********************

select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from
v$session where username='QHUSSAIN' ;

GATHER DATABASE STATS

*********************

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'GATHER STALE' ,
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'for all indexed
columns size auto',CASCADE=> TRUE, DEGREE => 10 );
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' ||
ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
EXCEPTION
when VALUE_ERROR then
dbms_output.put_line('VALUE_ERROR exception raised');
END;
IDENTIFY STALE DICTIONARY STATS
********************************

select owner,table_name from dba_tab_statistics where stale_stats='YES';

DATABASE UPTIME
***************
select instance_name,
to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time
from v$instance;

DBMS_DATA_PUMP
*************

DECLARE
handle NUMBER;
status VARCHAR2(20);
BEGIN
handle := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA');
DBMS_DATAPUMP.ADD_FILE (handle, 'multi_schema%U.dmp', 'DATA_PUMP_DIR');
dbms_datapump.add_file (handle => handle,directory => 'DATA_PUMP_DIR',filename =>
'multi_schema.log',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.METADATA_FILTER (handle, 'SCHEMA_EXPR', 'IN
(''SDA_FANNIEMAE_RA_41'')');
DBMS_DATAPUMP.METADATA_FILTER (handle, 'INCLUDE_PATH_EXPR', 'IN (''INDEX'')');
DBMS_DATAPUMP.set_parallel (handle, 8);
DBMS_DATAPUMP.SET_PARAMETER(handle, 'INCLUDE_METADATA', 1);
DBMS_DATAPUMP.START_JOB (handle);
DBMS_DATAPUMP.WAIT_FOR_JOB (handle, status);
END;

SQLFILE IN DATAPUMP
*******************

expdp \"/ as sysdba\" directory=data_pump_dir dumpfile=metadatatest1234.dmp

logfile=asdf1234.log content=METADATA_ONLY schemas=OLAP_ES30CLIENT5_351
include=index

impdp directory=DATA_PUMP_DIR dumpfile=alm8_8074.dmp logfile=alm8_8074_sqlfile.log

sqlfile=alm8.sql schemas=alm8 remap_schema=alm8:alm8 parallel=6
include=constraint,ref_constraint,MATERIALIZED_VIEW,DB_LINK,FUNCTION,INDEX,PACKAGE,
PACKAGE_BODY,PROCEDURE,SEQUENCE,SYNONYM,TRIGGER,TYPE,VIEW

SELECT to_char(DBMS_METADATA.GET_DDL ('ACCOUNTINGFEETREATMENT', table_name, owner))

FROM dba_tables
WHERE owner=upper('SDA_FANNIEMAE_RA_41') and table_name=upper('SDALOANSNAPSHOT');;

SELECT to_char(DBMS_METADATA.GET_DDL ('INDEX', index_name, table_owner))

FROM dba_indexes
WHERE table_owner=upper('OLAP_ES30CLIENT5_351') and
table_name=upper('ACCOUNTINGFEETREATMENT');
TABLESPACE EXTEND MAPPING
*************************

SET SERVEROUTPUT ON SIZE 1000000

SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF

DECLARE
l_tablespace_name VARCHAR2(30) := UPPER('&1');
l_file_id VARCHAR2(30) := UPPER('&2');

CURSOR c_extents IS
SELECT owner,
segment_name,
file_id,
block_id AS start_block,
block_id + blocks - 1 AS end_block
FROM dba_extents
WHERE tablespace_name = l_tablespace_name
AND file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))
ORDER BY file_id, block_id;

l_block_size NUMBER := 0;
l_last_file_id NUMBER := 0;
l_last_block_id NUMBER := 0;
l_gaps_only BOOLEAN := TRUE;
l_total_blocks NUMBER := 0;
BEGIN
SELECT block_size
INTO l_block_size
FROM dba_tablespaces
WHERE tablespace_name = l_tablespace_name;

DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);

FOR cur_rec IN c_extents LOOP
IF cur_rec.file_id != l_last_file_id THEN
l_last_file_id := cur_rec.file_id;
l_last_block_id := cur_rec.start_block - 1;
END IF;

IF cur_rec.start_block > l_last_block_id + 1 THEN

DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' ||
cur_rec.start_block || ')' ||
' FileID=' || cur_rec.file_id ||
' Blocks=' || (cur_rec.start_block-l_last_block_id-1) ||
' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) *
l_block_size)/1024/1024,2)
);
l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;
END IF;
l_last_block_id := cur_rec.end_block;
IF NOT l_gaps_only THEN
DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, '
') ||
' (' || cur_rec.start_block || ' -> ' ||
cur_rec.end_block || ')');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks *
l_block_size)/1024/1024,2));
END;
/

PLAN OUTPUT
***********

explain plan for ;

set lines 2000

SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

identify output of query using DBA_HIST_SQLTEXT dictionary view

SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY_AWR('454rug2yva18w'));

RUNNING SQL TUNING ADVISOR FOR A QUERY

**************************************
set serveroutput on

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
sql_id => 'f8b7774cy16vt',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 7200,
task_name => 'test_tuning1011',
description => 'tuning task for statement
f8b7774cy16vt');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

exec dbms_sqltune.execute_tuning_task(task_name => 'test_tuning1011');

set long 100000;

set long 100000;
set longchunksize 1000
set pagesize 10000
set linesize 100

select dbms_sqltune.report_tuning_task('test_tuning1011') as recommendations from

dual;

RUNNING SQL TUNING ADVISOR AGAINST QUERY NOT IN CURSOR

******************************************************

select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='&SQL_ID'

and plan_hash_value='&plan_hash_value' order by snap_id desc;
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 345,
end_snap => 349,
sql_id => '43qef4f7g5h32',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '43qef4f7g5h32_AWR_tuning_task',
description => 'Tuning task for statement 43qef4f7g5h32 in AWR');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');

SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

SET LONG 10000;

SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 240
SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;

execute dbms_sqltune.drop_tuning_task('&TASK_NAME');

RUNNING SQLHC FOR TUNING

************************
DocID 1366133.1 (download sqlhc.zip)

# sqlplus / as sysdba
SQL> START sqlhc.sql "T" djkbyr8vkc64h

NUMBER OF REPORTS GENERATED PER HOUR PER DAY

********************************************

SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,

SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
"00-01",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
"01-02",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
"02-03",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
"03-04",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
"04-05",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
"05-06",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
"06-07",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
"07-08",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
"08-09",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
"09-10",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
 "10-11",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
"11-12",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
"12-13",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
"13-14",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
"14-15",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
"15-16",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
"16-17",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
"17-18",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
"18-19",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
"19-20",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
"20-21",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
"21-22",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
"22-23",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
"23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES' and dest_id=1
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

ARCHIVE APPLY TIME (ARCHIVE MUST BE APPLYING)

********************************************

set linesize 400

col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",to_char(item)||'
= '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy
hh24:mi') "Values" from v$recovery_progress where start_time=(select
max(start_time) from v$recovery_progress);

IDENTIFY FRAGMENTATION IN INDEXES

*********************************

analyze index idx_obj_id validate structure;

select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats; (if >

15% then rebuild)

SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio FROM

INDEX_STATS;

SIZE OF SCHEMA AND THEIR DEFAULT TABLESPACE

*******************************************

select username,default_tablespace from dba_users;

select a.owner,sum(a.bytes)/1024/1024/1024 as size_gig, b.created from dba_segments

a,dba_users b where a.owner=b.username and a.owner in (select username from
dba_users) group by a.owner,b.created order by b.created;

SIZE OF SCHEMA AND THEIR TABLE COUNT

************************************
select a.owner, size_gig, created, tb_cnt from (
SELECT a.owner,
SUM(a.bytes)/1024/1024/1024 AS size_gig,
b.created
FROM dba_segments a,
dba_users b
WHERE a.owner=b.username
AND a.owner IN
(SELECT username FROM dba_users
)
GROUP BY a.owner,
b.created
ORDER BY b.created) a left outer join
(select owner,count(*) tb_cnt from dba_tables group by owner) b
on a.owner=b.owner;

SIZE OF TOP TABLES

*******************
SELECT
owner,
table_name,
TRUNC(sum(bytes)/1024/1024) MEG,
ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc;
RESIZING DATAFILES
******************

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save

FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576
hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents
GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

set linesize 1000 pagesize 0 feedback off trimspool on

with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all
joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno
relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024)
hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to
current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize
'||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

*********************************************
create table DBA_ADMIN.dba_extents as select /*+ PARALLEL(8) */ * from
sys.x$ktfbue;

select 'alter database datafile '||a.file_id||' resize ' ||

ceil(((nvl(hwm,1)*32768)/1024/1024) + 10 ) || 'm;' cmd
from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm
from DBA_ADMIN.dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*32768/1024/1024) - ceil( (nvl(hwm,1)*32768)/1024/1024 ) > 0;
********************************************

SET SERVEROUTPUT ON

DECLARE
V_STMT VARCHAR2 (500);
V_TBLSPC VARCHAR2 (30) := 'CZD';

CURSOR C1
IS
SELECT FILE_ID
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = V_TBLSPC;
BEGIN
FOR LINE IN C1
LOOP
SELECT 'ALTER DATABASE DATAFILE '
|| ''''
|| D.FILE_NAME
|| ''''
|| ' RESIZE '
|| NVL (CEIL (D.BYTES / 1024 / 1024 - TAKE_BACK.TAKE_BACK_MB),
D.BYTES / 1024 / 1024)
|| 'M;'
SQL
INTO V_STMT
FROM DBA_DATA_FILES D,
(SELECT SUM (BYTES) / 1024 / 1024 TAKE_BACK_MB
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = V_TBLSPC AND FILE_ID = LINE.FILE_ID
AND BLOCK_ID >=
NVL (
(SELECT (A.BLOCK_ID + (A.BYTES / B.BLOCK_SIZE))
FROM DBA_EXTENTS A, DBA_TABLESPACES B
WHERE A.BLOCK_ID =
(SELECT MAX (BLOCK_ID)
FROM DBA_EXTENTS
WHERE FILE_ID = LINE.FILE_ID
AND TABLESPACE_NAME =
V_TBLSPC)
AND A.FILE_ID = LINE.FILE_ID
AND A.TABLESPACE_NAME = V_TBLSPC
AND B.TABLESPACE_NAME = V_TBLSPC),
0)) TAKE_BACK
WHERE D.FILE_ID = LINE.FILE_ID;

DBMS_OUTPUT.PUT_LINE (V_STMT);
END LOOP;
END;
/

FIND HIDDEN PARAMETER IN ORACLE

*******************************

column ksppinm format a100

column ksppstvl format a50

select
ksppinm,
ksppstvl
from
x$ksppi a,
x$ksppsv b
where
a.indx=b.indx and
substr(ksppinm,1,1) = '_';

FLUSH SINGLE SQL PROFILE

***********************

select ADDRESS, HASH_VALUE from V$sqlarea where SQL_ID ='33nk15rgpk848';

BEGIN
DBMS_SHARED_POOL.PURGE('C0000008F304C620',2219163920,'C');
END;
/

SQL> select address, hash_value from v$sqlarea where sql_id like '4q5fmg2zgs5zv';

ADDRESS HASH_VALUE
---------------- ----------
00000000A9F34F98 1682024353

SQL> exec dbms_shared_pool.purge('000000007E199650, 3204192251','C');

PL/SQL procedure successfully completed.

If you check the shared pool again after the purge successfully completes, you'll
find that the query returns no rows.

SQL> select address, hash_value from v$sqlarea where sql_id like '97y7vc8x7jhmc';

no rows selected

http://www.bobbydurrettdba.com/2013/07/10/using-coe_xfr_sql_profile-sql-to-force-a-
good-plan/

DELETE SQLPROFILE OF QUERY

**************************

select sql_handle, plan_name, accepted, fixed,optimizer_cost from

dba_sql_plan_baselines where sql_handle='SYS_SQL_32814757f4b84f6b';

declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_na
me=>null);
END;
/

CREATE SQLPROFILE FOR BETTER PLAN

*********************************

We will use Sqltxplain's coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE] as

SYSDBA.

@$ORACLE_HOME/sqlt/utl/coe_xfr_sql_profile.sql '8qu7mmcwnygtv' '2874684176';

Execute coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql

http://oradba11g.blogspot.com/2015/01/pinning-execution-plan-for-sqlid-using.html

INSTALL SQLT
************

http://www.dbas-oracle.com/2013/06/Install-Use-SQL-Query-Performance-
Troubleshooting-Tool-SQLT-SQLTXPLAIN-6-Steps.html

expdp directory=DP_DIRONBACKUP dumpfile=ETICKET_PROD_%U.dmp

logfile=ETICKET_PROD.log schemas=resdba,ressale,dcsnew,history parallel=4
compression=all exclude=statistics

MOVE FILES OLDER THAN 30 DAYS TO NEW DESTINATION

************************************************

find * -mtime +30 -exec mv "{}"

/apps/SAIERP/Application/PROD/inst/apps/PROD_erpapp01/logs/ora/10.1.2/reports/
cache/cache_old \;

SIZE OF DATABASE
****************

select
"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)"
"Used_Space(MB)","Free_Space(MB)"
from(
select
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);

SAR REPORT OUTPUT

------------------

sar -P ALL -f /var/log/sa/sa25

RSYNC TO SYNCHRONIZE FOLDERS IN LINUX
-------------------------------------

rsync -avzhe ssh --progress --delete /mnt/170_36/

oracle@172.16.24.30:/archive/arch_shared/usbbkp_27211/170_36/
rsync -avzhe ssh --progress --delete /mnt/24_55/
oracle@172.16.24.30:/archive/arch_shared/usbbkp_27211/24_55/
rsync -avzhe ssh --progress --delete /mnt/150_27_SFPSDB/
oracle@172.16.24.30:/archive/arch_shared/usbbkp_27211/150_27_SFPSDB/

BACKUP MYSQL DATABASE

---------------------

mysqldump -u root -p openaudit > openaudit_13032017.sql

mysql -u root -p db_safts < db_saft_260417.sql

update user set authentication_string=password('admin123') where user='root';

systemctl status mysql.service

sudo mysqld_safe --skip-grant-tables &

mysql -uroot
use mysql
UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY
'Admin123/?';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Admin123/?';

RUN EXACHK COMMAND

-------------------

cd /opt/oracle.SupportTools/exachk
./exachk –cell saiceladm01, saiceladm02, saiceladm03
./exachk

EXADATA STOP START COMMANDS

***************************
#> /u01/app/11.2.0.4/grid/bin/crsctl stop crs
$> srvctl status database -d tawa
srvctl start database -d tawa
srvctl stop database -d tawa

#> /u01/app/11.2.0.4/grid/bin/crsctl start cluster -all

crsctl check cluster -all

CHANGE DB NAME WITH ORACLE NID

******************************

nid target=sys/oracle dbname=uat setname=YES

IDENTIFY DELETED LOCK PROCESS IN LINUX

**************************************
lsof +L1

REPLACE STRING WITH SED

************************

find . -type f | xargs sed -i 's/gd$dba2351/resdba_123/g'

:%s/dbms_stats.auto_sample_size/100/g
:%s/encrypt/decrypt/g

FIND FILES OLDER THAN X DAYS THEN SCP

*************************************

find /u01 -mtime -20 -name "*"

find /u01 -mtime -5 -exec cp "{}" /u01/copy_file/ \;

find . -name '*.csv' -exec cp --parents \{\} /target \;

--------------------------------------------------
find /u01 -mtime -50 -name "*" > output.txt
while read output
do
scp $output oracle@172.16.24.55:$output
done < output.txt
--------------------------------------------------

STARTING STOPPING MQ SERVER

****************************

service start
cd /opt/mqm/bin/
./strmqm SLPNRPM

service stop
cd /opt/mqm/bin/
./endmqm SLPNRPM

service status
cd /opt/mqm/bin/
./dspmq

vnc---application----programming----mqexplorer

CHECKING DISK READ WRITE SPEED IN LINUX

***************************************

dd if=/dev/zero of=/proddata/test1.img bs=5G count=1 oflag=dsync write speed

dd if=/proddata/test1.img of=/dev/null bs=8k read speed

hdparm -tT /dev/sda1

START SVN SERVICE

*****************

ps -eaf|grep trac
kill -9

/usr/bin/tracd --port=8000 --basic-auth="*,/var/www/htpasswd-dev,My Trac" -s

/var/www/htdocs/trac/ >> ~/trac.log &
svn switch --relocate svn+ssh://172.16.24.34/var/svn/eticketcp/trunk/sales
svn+ssh://172.16.170.47/var/svn/eticketcp/trunk/sales

svn switch --relocate svn+ssh://172.16.24.34/var/svn/sfps/trunk

svn+ssh://172.16.170.47/var/svn/sfps/trunk

/var/www/htdocs/resstaging

svn info

DISK FULL BUT IT IS NOT

***********************

lsof +L1
kill -9 `ps -ef | grep deleted | awk '{print $2}'`

kill -9 `ps -ef | grep lsof +L1 | awk '{print $2}'`

REMOTE SESSION TRACING

***********************

ALTER SESSION SET max_dump_file_size = unlimited;

EXEC DBMS_MONITOR.session_trace_enable(session_id =>1185, serial_num=>9915,

waits=>TRUE, binds=>TRUE);

EXEC DBMS_MONITOR.session_trace_disable (session_id => 1185, serial_num => 9915);

$ORACLE_HOME/bin/tkprof SFPS_j000_30942.trc translated_new1.txt explain=sfps/sfps

sys=no waits=yes

use tkprof

http://www.oracle-base.com/articles/10g/PerformanceTuningEnhancements10g.php

trcsess output=session.trc session=1185.9915

MARIADB USER CREATION

*********************

mcsmysql -uroot -pstr0ng

create database REPORT_ES30CLIENT13_TEST;

create user REPORT_ES30CLIENT13_TEST@'localhost' identified by 'str0ng';
create user REPORT_ES30CLIENT13_TEST@'%' identified by 'str0ng';
grant all privileges on REPORT_ES30CLIENT13_TEST.* to REPORT_ES30CLIENT13_TEST@'%';
grant all privileges on REPORT_ES30CLIENT13_TEST.* to
REPORT_ES30CLIENT13_TEST@'localhost';
grant all privileges on infinidb_vtable.* to REPORT_ES30CLIENT13_TEST@'localhost'
identified by 'str0ng';
grant all privileges on infinidb_vtable.* to REPORT_ES30CLIENT13_TEST@'%'
identified by 'str0ng';
grant all privileges on mstr_temp_tables.* to REPORT_ES30CLIENT13_TEST@'localhost';
grant all privileges on mstr_temp_tables.* to REPORT_ES30CLIENT13_TEST@'%';
grant create view on REPORT_ES30CLIENT13_TEST.* to REPORT_ES30CLIENT13_TEST@'%';
grant create view on REPORT_ES30CLIENT13_TEST.* to
REPORT_ES30CLIENT13_TEST@'localhost';
flush privileges;
mcsmysql -uSTAGE_ES40CLIENT12 -pstr0ng -hlocalhost STAGE_ES40CLIENT12

MARIADB SERVICE RESTART

***********************

cd /opt/evolv/mariadb/columnstore/bin

./columnstore stop
./columnstore start

evolv

cd /opt/evolv/mariadb/columnstore/mysql/share

./mysql.server start

mcsadmin startsystem

mcsadmin stop system

cd /opt/evolv/mariadb/columnstore/bin
./columnstore start

Password for evolv is "Pr1m@t1cs99"

mcsadmin getSystemStatus

INFORMATICA FOLDER DELETION,CREATION AND PERMISSIONS

****************************************************

pmrep connect -r coloRepository -d Domain_iadtcinfrasit01.evolvsuite.local -n

Administrator -s Native -x *******

pmrep deletefolder -n TCINFRAP1

pmrep deletefolder -n DYNA_TCINFRAP1

pmrep CreateFolder -n TCINFRAP1

pmrep CreateFolder -n DYNA_TCINFRAP1

pmrep AssignPermission -o folder -n TCINFRAP1 -u TCINFRAP1 -p rwx

pmrep AssignPermission -o folder -n TCINFRAP1 -u EvolvOperator -p rwx
pmrep AssignPermission -o folder -n DYNA_TCINFRAP1 -u TCINFRAP1 -p rwx
pmrep AssignPermission -o folder -n DYNA_TCINFRAP1 -u EvolvOperator -p rwx

INFORMATICA USER CREATION

*************************

./infacmd.sh createUser -dn Domain_iadtcinfrasit01.evolvsuite.local -un

Administrator -pd adminsit_ev01v -sdn Native -nu TCINFRAP31 -np str0ng -nf
TCINFRAP31 -ds TCINFRAP31

SEARCHING, DOWNLOADING & UPLOADING FILES IN S3

**********************************************
s3cmd ls s3://adhoc-backups/PRBK_EMSS-66346*
s3cmd get -r s3://adhoc-backups/PRBK_EMSS-66346*
s3cmd put /oradump/RAU-1234_abc*.* s3://adhoc-backups/

s3cmd ls s3://prod_datadump_backup/
s3cmd ls s3://mongodbea/ProdMongoDB/

s3cmd put /backup/mongo_full/*.tar.gz s3://mongodbea/ProdMongoDB/

s3cmd ls s3://decom-client-backups/

s3cmd ls s3://memsql-db-backups/sandbox/
s3cmd ls s3://memsql-db-backups/prod/

aws s3 ls s3://ea-dbs-backup/sandbox/MongoDB
AWSCMD="/usr/local/bin/aws"
$AWSCMD s3 cp s3://ea-dbs-backup/sandbox/MongoDB/mongodb_full_bkp_310519_2305 . --
recursive

aws s3 ls s3://mybucket --recursive --human-readable --summarize

:%s/adhoc-backups/decom-client-backups/g

AMAZON REDSHIFT USER CREATION

*****************************

Connect with user greensightea using sql developer:

create schema usr_carnival;

create user usr_carnival with password 'prod_EAev01v';
alter user usr_carnival set search_path=usr_carnival,public;
grant all privileges on all tables in schema usr_carnival to usr_carnival;
grant all privileges on schema usr_carnival to usr_carnival;
grant execute on all functions in schema usr_carnival to usr_carnival;

grant all on database greensightea to usr_carnival;

MEMSQL SCHEMA BACKUP AND RESTORE

********************************

stage_es40client14

BACKUP DATABASE T570_R_IM_ESI_LIGHT_IM_51 TO

"/memsql-data/T570_R_IM_ESI_LIGHT_IM_51/"

RESTORE DATABASE T570_R_IM_ESI_LIGHT_IM_51 FROM

"/tmp/T570_R_IM_ESI_LIGHT_IM_51/T570_R_IM_ESI_LIGHT_IM_51.backup" (Donot
create database with name in which you want to restore)

Ensure backups are copied to remote location from master aggregator as well as from
all leaf nodes. If data is deleted from any one leaf restoration will fail.

for all databases

-----------------

mysqldump -h 127.0.0.1 -u root -P 3306 --all-databases > full_backup.sql

INDEX INVISIBLE FOR PNC POPULATE AGGREGATE

******************************************

alter index OLAP_PNC.PT_6_UI invisible;

MONGODB SIT
***********

export KUBECONFIG=/opt/evolv/environments/evolea-sit-rancher/kubeconfig_sit
kubectl get pods -n greensight-mongodb

kubectl --namespace greensight-mongodb exec mongo-shardsctl-rc-2nkrj -it -- bash

mongo

use perftest4

db.createUser(
{
user: "perftest4", pwd: "str0ng", roles: [ "readWrite", "dbAdmin" ]
}
);

ALL OPEN TICKETS IN JIRA

************************

project in ("Evolv Managed Services Shared","Colo Environment Update","Release

Acceptance/Deployment and Upgrade") AND status != Closed AND assignee=qhussain

assignee in (prafay, mkhoso, rsohi, dba-primatics) AND status != closed AND project
in ("Colo Environment Update", Evolv, "Evolv Managed Services Shared", "Release
Acceptance/Deployment and Upgrade")

project in (Engineering) AND status != Closed AND reporter = qhussain

OEM PRODUCTION LINK

*******************

https://iadoracleoem.evolvsuite.local:7803/em/

DELETE MSTR USER

****************

delete user "pbailey" cascade profiles;

ALTER USER "user login name" DISABLED;
LOAD PROJECT "PROJECT NAME" TO CLUSTER ALL SERVERS;
ALTER PROJECT "PROJECT NAME" NAME "NEW PROJECT NAME";

DEPENDENCY FINDER
*****************

shutdown mstr server at interim server.

copy metadata from Prod (P_MICOR_10) to interim server (PROD_MICRO_10) at database
level.
change password of interim server database user (PROD_MICRO_10) to jupiter_ev01v
restart mstr services of interim server.
connect with interim server on MSTR Desktop using Prod password h@ppierprod (of
Prod in this case)
connect with interim server using Prod password in this case then change password
in MSTR Desktop to pat1234 tools --> change password
load all projects;
Run the VBS utility S:\Qamber Hussain\DepFinder\DepFind\nohup.vbs

RESTART ASM SERVICE ON DEV MACHINE

**********************************

As root
nohup /etc/init.d/init.ohasd run &

sudo su - grid

srvctl start asm

sqlplus / as sysasm
startup
lsnrctl start

sudo su - oracle
sqlplus / as sysdba
startup

RELEASE LOCK IN MARIADB

***********************

cd /opt/evolv/mariadb/columnstore/bin

./viewtablelock

There are 2 table locks

Table LockID Process PID Session

Txn CreationTime State DBRoots
munawar_maria3.his_inst_attribute_snapshot 1 DDLProc 24806 389
7699 Mon Oct 15 09:26:31 2018 LOADING 1
tc_data_analytics_532.his_inst_attribute_snapshot 2 DDLProc 18722 368
10995 Mon Oct 15 14:04:58 2018 LOADING 1

kill -9 18722
-bash: kill: (18722) - No such process

./cleartablelock 2 (where 2 is the LockID you want to delete)

Rolling back and clearing table lock for table
tc_data_analytics_532.his_inst_attribute_snapshot; table lock 2

SHOW GLOBAL STATUS LIKE "Questions";

mcsmysql -u root -pstr0ng

mcsmysql -uwaseem_db2 -pstr0ng -hlocalhost waseem_db2

SIT POSTGRES USER CREATION

**************************

iadpostgresit01
docker-iadranchersit01

psql --host=evolveasitdb.czycv2q7fpax.us-east-1.rds.amazonaws.com --port=5432 --

username=EvolvEASITMS --dbname=postgres
psql --host=evolveasitnewdb.czycv2q7fpax.us-east-1.rds.amazonaws.com --port=5432 --
username=EvolvEASITMS --dbname=postgres

Str0ng#321

create user DEV_SIT_KEYCLOAK with password 'gr33nS1ght';

grant "dev_sit_keycloak" to "EvolvEASITMS";
create database DEV_SIT_KEYCLOAK with owner=DEV_SIT_KEYCLOAK;

PGPASSWORD=str0ng pg_dump -h evolveasitdb.czycv2q7fpax.us-east-1.rds.amazonaws.com

-Fp -O -v -U sit_metadata_10_1 sit_metadata_10_1 > /tmp/sit_metadata_10_1.sql 2>
/tmp/sit_metadata_10_1.log

psql --host=iadpostgresit01.evolvsuite.local --port=5432 --

username=sit_metadata_10_1 --dbname=sit_metadata_10_1 <
'/tmp/sit_metadata_10_1.sql'

AWS REDSHIFT SIT CONNECTION SETTINGS

************************************
54.210.247.41
User name Administrator
Password aP=o-qGR6hs6RE5AnAm3bc!PBgPbN(yH

greensight.ctd6stkxa6wb.us-east-1.redshift.amazonaws.com
greensight

username: greensight password: gr33nS1ght

database: greensight port: 5439 host: greensight.ctd6stkxa6wb.us-east-
1.redshift.amazonaws.com

jdbc:redshift://greensight.ctd6stkxa6wb.us-east-1.redshift.amazonaws.com:5439/
greensight

revoke all on schema testbank1 from testbank1 ;

revoke usage on schema testbank1 from testbank1 ;
revoke all on all tables in schema testbank1 from testbank1 ;
revoke all on database greensight from testbank1;
alter schema testbank1 owner to dba_test;
select 'alter table '+schemaname+'.'+tablename+' owner to testbank1;' from
pg_tables where tableowner like 'dba_test';
drop user testbank1 ;

ANALYZE effective_date_dim_vw;

create schema perftest44;

create user perftest44 with password 'gr33nS1ght';
alter user perftest44 set search_path=perftest44,public;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA perftest44 TO perftest44;
grant all privileges on schema perftest44 to perftest44;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA perftest44 to perftest4;

select 'alter table '+schemaname+'.'+tablename+' owner to perfbank1;' from

pg_tables where schemaname='perfbank1' and tableowner='greensight';

select * from pg_tables where schemaname='perfbank1' and tableowner='greensight';

 (identifying which objects are owned by user)

SANDBOX EVOLV EA POSTGRES USER CREATION

***************************************

10.161.10.183

psql --host=evolveadb.clqnjhbybc71.us-east-1.rds.amazonaws.com --port=5432 --

username=EvolvEAMS --dbname=postgres

Str0ng#321

create user DEV_SIT_KEYCLOAK with password 'gr33nS1ght';

alter user DEV_SIT_KEYCLOAK set search_path=dev_sit_keycloak,public;
grant "dev_sit_keycloak" to "EvolvEAMS";
create database DEV_SIT_KEYCLOAK with owner=DEV_SIT_KEYCLOAK;

PROD EVOLV EA POSTGRES USER CREATION

***************************************

10.161.10.183

psql --host=evolveaproddb.clqnjhbybc71.us-east-1.rds.amazonaws.com --port=5432 --

username=EvolvEAMaster --dbname=Evolveaproddb

EAProddb#435

Db name: dev_sit_keycloak
Username: user_ea_dev_sit_keycloak

drop database dev_sit_keycloak;

drop owned by user_ea_test_global;
drop user user_ea_test_global;
drop schema dev_sit_keycloak;

REASSIGN OWNED BY user_ea_test_global TO postgres;

DB NAME: dev_sit_global
Username : user_ea_dev_sit_global

create user user_ea_dev_sit_keycloak with password 'str0ng';

create database dev_sit_keycloak owner user_ea_dev_sit_keycloak;

\connect dev_sit_keycloak

CREATE TABLESPACE dev_sit_keycloak owner user_ea_dev_sit_keycloak LOCATION

'/opt/pgsql_tablespaces/dev_sit_keycloak';
ALTER ROLE user_ea_dev_sit_keycloak SET default_tablespace = dev_sit_keycloak;
grant all privileges on database dev_sit_keycloak to user_ea_dev_sit_keycloak;

jdbc:postgresql://iadpostgresit01.evolvsuite.local/ea_test_global

select 'alter table '+schemaname+'.'+tablename+' owner to perfbank1;' from

pg_tables where schemaname='perfbank1' and tableowner='greensight';
select * from pg_tables where schemaname='dev_sit_global' and
tableowner='greensight'; (identifying which objects are owned by user)

create user user_ea_test_global with password 'eaEv01v_s1t';

alter user user_ea_test_global set search_path=user_ea_test_global,public;
grant "user_ea_test_global" to "EvolvEASITMS";
create database ea_test_global with owner=user_ea_test_global;
create schema ea_test_global;
grant all privileges on all tables in schema ea_test_global to user_ea_test_global;
grant all privileges on schema ea_test_global to user_ea_test_global;
grant execute on all functions in schema ea_test_global to user_ea_test_global;

revoke all on schema ea_test_global from user_ea_test_global ;

revoke usage on schema testbank1 from testbank1 ;
revoke all on all tables in schema testbank1 from testbank1 ;
revoke all on database greensight from testbank1;
alter schema testbank1 owner to dba_test;
select 'alter table '+schemaname+'.'+tablename+' owner to testbank1;' from
pg_tables where tableowner like 'dba_test';
drop user testbank1 ;

Db instance id

Evolveaproddb

Master user

EvolvEAMaster

Password

DB Name

EvolvEAKeycloak

STEPS TO RECONFIGURE ASM AFTER HOSTNAME CHANGED

***********************************************

(Doc ID 986740.1)

SPLITING BIG FILE INTO SMALL FILES IN LINUX

*******************************************

split -l 100000 abc.csv abc_

CREATION OF CSV IN MARIADB

**************************

CREATION OF SPOOL FILE

mysql> tee c:\trash\qwe.txt

Logging to file 'c:\trash\qwe.txt'

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| host |
| tables_priv |
| user |
+-----------------+
5 rows in set (0.02 sec)

mysql> notee

CREATION OF CSV

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.csv'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customers;

MEMSQL SERVICE START

********************

sudo memsql-ops agent-start --all

memsql-ops memsql-stop --all

memsql-ops memsql-start --all

memsql-ops memsql-path

/etc/my.cnf
/home/evolv/repositories/Evolv/install/config/my.cnf

memsql> show variables like 'char%';

+--------------------------
+----------------------------------------------------------------+
| Variable_name | Value
|
+--------------------------
+----------------------------------------------------------------+
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | utf8
|
| character_set_filesystem | binary
|
| character_set_results | utf8
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir |
/opt/memsql_install_dir/master-3306-MI5fed5b5e/share/charsets/ |
+--------------------------
+----------------------------------------------------------------+
8 rows in set (0.00 sec)

memsql> show variables like 'collation%';

+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
collation_server='utf8_bin';
init-connect='SET NAMES utf8'
character-set-server = utf8

MEMSQL REPORT GENERATION AND TROUBLESHOOTING

********************************************
memsql-ops report

memsql-report collect
memsql-report check -–report-path /home/memsql/report-2019-12-20T012005.tar.gz

BLOCKING SESSION IN AWS REDSHIFT

********************************

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as

table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as
blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||
datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||
datediff(s,a.txn_start,getdate())%3600/60||' mins '||
datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted='f' and b.granted='t'
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where a.relation is not null;

select user_name, db_name, pid, query, starttime

from stv_recents
where pid = <pid>;

select pg_terminate_backend(8585);

MEMSQL CLUSTER REPORT

*********************
memsql-ops report
MEMSQL READ ONLY ACCESS ROLE
****************************

CREATE ROLE 'ROLE_RO';

GRANT SELECT ON *.* TO ROLE 'ROLE_RO' WITH GRANT OPTION;
GRANT SELECT ON infinidb_vtable.* TO ROLE 'ROLE_RO' WITH GRANT OPTION;
GRANT RELOAD ON *.* TO ROLE 'ROLE_RO';
GRANT SHOW METADATA ON *.* TO ROLE 'ROLE_RO';
GRANT EXECUTE ON *.* TO ROLE 'ROLE_RO';
CREATE GROUP 'READONLY_GROUP';
GRANT ROLE 'ROLE_RO' TO 'READONLY_GROUP';
GRANT GROUP 'READONLY_GROUP' TO 'olap_qhussain'@'%';
GRANT GROUP 'READONLY_GROUP' TO 'olap_qhussain'@'localhost';

SIZE OF SCHEMA IN MEMSQL

************************

select database_name, table_name, format(sum(memory_use),0) as TOTAL_MEMORY_USE_MB

from information_schema.table_statistics WHERE STORAGE_TYPE = "INMEMORY_ROWSTORE"
group by 1, 2 ORDER BY 3 DESC;

select database_name, table_name, format(sum(compressed_size), 0) size

from information_schema.COLUMNAR_SEGMENTS
group by 1, 2;

MEMSQL PER TABLE MEMORY CONSUMPTION

***********************************
execute the following query to view per-table memory consumption and row count in
your MemSQL cluster.

SELECT
DATABASE_NAME,
TABLE_NAME,
MIN(ROWS),
MAX(ROWS),
FLOOR(AVG(ROWS)) AS avg_rows,
ROUND(STDDEV(ROWS)/AVG(ROWS),3) AS row_skew,
MIN(MEMORY_USE),
MAX(MEMORY_USE),
SUM(MEMORY_USE)/(1024*1024) AS total_memory_mb,
FLOOR(AVG(MEMORY_USE)) AS avg_memory,
ROUND(STDDEV(MEMORY_USE)/AVG(MEMORY_USE),3) AS memory_skew
FROM INFORMATION_SCHEMA.TABLE_STATISTICS
GROUP BY 1, 2
HAVING SUM(ROWS) > 10000;

MEMSQL TOTAL MEMORY AVAILABLE PER LEAF

**************************************

#!/bin/sh
. $HOME/.bash_profile

export MYSQL_PWD=$memsqlPWD
email=dba-
primatics@primaticsfinancial.com,mvellanoore@primaticsfinancial.com,imomin@primatic
sfinancial.com
#email=qhussain@primaticsfinancial.com
max_mem_ma=`memsql -s --local-infile=1 -uroot <<EOF
select sum(MAX_TABLE_MEMORY_MB) from information_schema.MV_NODES where type='MA';;
EOF`

x=`memsql -s --local-infile=1 -uroot <<EOF

select sum(MEMORY_USED_MB) from information_schema.MV_NODES where type='MA';
EOF`

y=`echo $x | xargs printf "%.*f\n" 0`

used_mem_ma=$(( 100*$y ))

use_mem_ma1=$((used_mem_ma / max_mem_ma))

max_mem=`memsql -s --local-infile=1 -uroot <<EOF

select sum(MAX_TABLE_MEMORY_MB) from information_schema.MV_NODES where type='LEAF';
EOF`

b=`memsql -s --local-infile=1 -uroot <<EOF

select sum(MEMORY_USED_MB) from information_schema.MV_NODES where type='LEAF';
EOF`

#c=`echo $b | cut -d' ' -f 2`

d=`echo $b | xargs printf "%.*f\n" 0`
alloc_mem=$(( 100*$d ))

use_mem=$((alloc_mem / max_mem))

if [ $use_mem_ma1 -ge 50 ] && [ $use_mem -ge 75 ]

then
echo " " | mail -s " SANDBOX MemSQL Master Agreegator memory and leaf node memory
utilization more than 50% " $email
elif [ $use_mem_ma1 -ge 50 ] && [ $use_mem -le 75 ]
then
echo " " | mail -s " SANDBOX MemSQL Master Agreegator memory utilization more than
50% " $email
elif [ $use_mem_ma1 -le 50 ] && [ $use_mem -ge 80 ]
then
echo " " | mail -s " SANDBOX MemSQL leaf node memory utilization more than 80% "
$email
else
echo "";
fi

DATAPUMP WITH SCN

*****************

select current_scn from v$database;

expdp system/ramtech@noida directory=dpump schemas=hr dumpfile=flashback_hr.dmp
logfile=flashlog.log flashback_scn=1140271

SIZE OF S3 BUCKET IN BYTES

**************************

aws s3api list-objects --bucket BUCKETNAME --output json --query

"[sum(Contents[].Size), length(Contents[])]"

GENERATING CSV WITH HEADER USING ORACLE PROCEDURE

************************************************

CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2

,p_dir IN VARCHAR2
,p_file IN VARCHAR2
) IS
v_finaltxt VARCHAR2(32000);
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
v_fh UTL_FILE.FILE_TYPE;
BEGIN
execute immediate 'alter session set nls_date_format=''YYYY-MM-DD''';
execute immediate 'alter session set NLS_TIMESTAMP_FORMAT=''YYYY-MM-DD
HH:MI:SS.FF''';
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
END CASE;
END LOOP;
-- This part outputs the HEADER
v_fh := UTL_FILE.FOPEN(upper(p_dir),p_file,'w',32767);
FOR j in 1..col_cnt
LOOP
v_finaltxt := ltrim(v_finaltxt||'|'||upper(rec_tab(j).col_name),'|');
END LOOP;
UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
-- This part outputs the DATA
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
v_finaltxt := NULL;
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
v_finaltxt := ltrim(v_finaltxt||'|"'||v_v_val||'"','|');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
v_finaltxt := ltrim(v_finaltxt||'|'||v_n_val,'|');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
v_finaltxt := ltrim(v_finaltxt||'|'||to_char(v_d_val,'YYYY-MM-DD'),'|');
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
v_finaltxt := ltrim(v_finaltxt||'|"'||v_v_val||'"','|');
END CASE;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
END LOOP;
UTL_FILE.FCLOSE(v_fh);
DBMS_SQL.CLOSE_CURSOR(c);
END;
/

exec run_query('select * from

STAGE_WSFS_BETA.esietlreport','DATA_PUMP_DIR','esietlreport.csv');

LOAD CSV IN MEMSQL

******************

#!/bin/sh
. $HOME/.bash_profile

usage()

{
echo "Usage: Lack of Command Line Arguments
#sh load_csv_orcl_memsql.sh source_schema_name table_name target_schema_name"
}

if [ $# -lt 3 ]
then
usage
exit;
fi

export MYSQL_PWD=m3mJup_ev01v
#one=$1;
two=$2;
three=$3;
#four=$4;
p=$1
one=`echo $p | tr [a-z] [A-Z]`

cd /memsql-backup/$one/
a=$(head -n 1 /memsql-backup/$one/$2.csv)
b=`echo $a| sed -e 's/|/,/g' | sed -e 's/"//g'`

memsql --local-infile=1 -uroot <<!

use $3;
LOAD DATA INFILE '/memsql-backup/$one/$2.csv' INTO TABLE $2
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(${b});

echo "CSV has been loaded into $3.$2 ";

REBUILD UNUSABLE INDEXES

************************

procedure check_all_indexes is
 begin
for i in (
select index_owner, index_name, partition_name, 'partition' ddl_type
from all_ind_partitions
where (index_owner,index_name) in
( select owner, index_name
from all_indexes
where table_owner = upper(p_owner)
and table_name = upper(p_table_name)
)
and status = 'UNUSABLE'
union all
select index_owner, index_name, subpartition_name, 'subpartition' ddl_type
from all_ind_subpartitions
where (index_owner,index_name) in
( select owner, index_name
from all_indexes
where table_owner = upper(p_owner)
and table_name = upper(p_table_name)
)
and status = 'UNUSABLE'
union all
select owner, index_name, null, null
from all_indexes
where table_owner = upper(p_owner)
and table_name = upper(p_table_name)
and status = 'UNUSABLE'
)
loop
if i.ddl_type is null then
ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild');
else
ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild '||
i.ddl_type||' '||i.partition_name);
end if;
end loop;
end;
/

MSTR CACHE PURGE

****************
UPDATE SCHEMA REFRESHSCHEMA RECALTABLEKEYS RECALTABLELOGICAL RECALOBJECTCACHE FOR
PROJECT "ES30CLIENT14";
PURGE ELEMENT CACHING IN PROJECT "ES30CLIENT14";
PURGE OBJECT CACHING IN PROJECT "ES30CLIENT14";
PURGE REPORT CACHING IN PROJECT "ES30CLIENT14";

COMPARE MSTR USERS WITH LDAP

****************************
All ldap users
/usr/bin/ldapsearch -x -LLL uid=* | grep -i "dn: uid" | awk -F " " '{print $2}' |
awk -F "," '{print $1}' | awk -F "=" '{print $2}' | sort > ldap

All MSTR users

LIST LOGIN FOR USERS IN GROUP "Everyone"; > mstr

[evolv@iadutil01 msalman]$ cat compare.sh

#!/bin/bash
for i in `cat mstr`
do
count=`grep -i $i ldap | wc -l`
if [[ $count -eq 0 ]]; then
echo $i
fi
done

ROLE_PRIVS
**********
select (select host_name from v$instance), systimestamp,
username,account_status,created
from dba_users where default_tablespace='END_USERS'
and username not like 'GL_REP%'
and username not like 'UTIL%'
and username not IN ('OEM_RO_USER','PRTG_CHECK','RUNBOOK_UTILITY') ORDER BY 3;

select (select host_name from v$instance), systimestamp, a.*

from dba_role_privs a where a.grantee in (select username from dba_users where
default_tablespace='END_USERS'
and username not like 'GL_REP%'
and username not like 'UTIL%'
and username not IN ('OEM_RO_USER','PRTG_CHECK','RUNBOOK_UTILITY')) ORDER BY 3;

select (select host_name from v$instance), systimestamp, a.*

from dba_sys_privs a where a.grantee in (select username from dba_users where
default_tablespace='END_USERS'
and username not like 'GL_REP%'
and username not like 'UTIL%'
and username not IN ('OEM_RO_USER','PRTG_CHECK','RUNBOOK_UTILITY')) ORDER BY 3;

PASSWORD PROFILE
****************
select systimestamp,a.* from dba_profiles a where a.profile='XX_END_USER';

FIND NUMBER OF SESSIONS IN A DAY EVERY HOUR

*******************************************

select
s.instance_number,
s.end_interval_time,
rn.current_utilization
from
dba_hist_snapshot s
join dba_hist_resource_limit rn on (s.snap_id = rn.snap_id and s.instance_number
= rn.instance_number)
where
rn.resource_name = 'sessions'
and s.end_interval_time > sysdate - 1
order by
s.end_interval_time
;

INCREMENTAL BACKUP MEMSQL

*************************

BACKUP DATABASE test_db WITH INIT to "/test_db_backups_week_1";

BACKUP DATABASE test_db WITH DIFFERENTIAL to "/test_db_backups_week_1";
BACKUP DATABASE test_db WITH DIFFERENTIAL to "/test_db_backups_week_1";
SELECT backup_id, incr_backup_id, database_name, start_timestamp FROM
information_schema.mv_backup_history ORDER BY backup_id;

RESTORE DATABASE test_db FROM "/test_db_backups_week_1" WITH FILE = 12;

In this clause, specify the incr_backup_id of the incremental backup that you want
to restore.
CLEAR BACKUP_HISTORY;

UNCHECK DATABASE INSTANCE FROM PROJECT

**************************************
REMOVE DBINSTANCE "Evolv MySQL DB Instance" FROM PROJECT "EASTERNBANK_BETA";

FREE FORM SQL REPORT LIST

*************************
select o.object_id, o.object_name
from DSSMDOBJDEPN d
join DSSMDOBJINFO o
on d.OBJECT_ID = o.OBJECT_ID and d.PROJECT_ID = o.PROJECT_ID
where DEPNOBJ_TYPE = 15 and d.OBJECT_TYPE = 3
and d.PROJECT_ID = ‘?’;

LIST OF DISKS USED IN ORACLE ASM

********************************
[root@oraclevm ~]# blkid

as grid user run the following command:

[root@oraclevm ~]$ oracleasm listdisks

DATA1
[root@oraclevm ~]$ oracleasm querydisk -d DATA1
Disk "DATA1" is a valid ASM disk on device [8,17]
[root@oraclevm ~]$ ls -ltr /dev/sd*
brw-rw----. 1 root disk 8, 0 Dec 14 18:18 /dev/sda
brw-rw----. 1 root disk 8, 2 Dec 14 18:18 /dev/sda2
brw-rw----. 1 root disk 8, 1 Dec 14 18:18 /dev/sda1
brw-rw----. 1 root disk 8, 16 Dec 14 18:18 /dev/sdb
<strong>brw-rw----. 1 root disk 8, 17 Dec 14 18:18 /dev/sdb1</strong>

POSTGRES SCHEMA CREATION

************************
create user alfresco_poc_alt with password 'str0ng';
create database alfresco_poc_alt owner alfresco_poc_alt;
\connect alfresco_poc_alt
CREATE TABLESPACE alfresco_poc_alt owner alfresco_poc_alt LOCATION
'/opt/pgsql_tablespaces/alfresco_poc_alt';
ALTER ROLE alfresco_poc_alt SET default_tablespace = alfresco_poc_alt;
grant all privileges on database alfresco_poc_alt to alfresco_poc_alt;
create schema alfresco_poc_alt;
grant all privileges on schema alfresco_poc_alt to alfresco_poc_alt;

FEATURES BEING USED IN ORACLE DATABASE

**************************************
SELECT u1.name,
u1.detected_usages,
u1.currently_used,
 u1.version
FROM dba_feature_usage_statistics u1
WHERE u1.version = (SELECT MAX(u2.version)
FROM dba_feature_usage_statistics u2
WHERE u2.name = u1.name)
AND u1.detected_usages > 0
AND u1.dbid = (SELECT dbid FROM v$database)
ORDER BY currently_used desc;

IDENTIFY HIDDEN PARAMETER IN ORACLE

***********************************

SELECT
ksppinm,
ksppstvl
FROM
x$ksppi a,
x$ksppsv b
WHERE
a.indx=b.indx
AND
substr(ksppinm,1,1) = '_' and ksppinm like '%use_osm%'
ORDER BY ksppinm;

QUERIES RUNNING AT A SPECIFIC TIME

**********************************
select a.sql_id,dbms_lob.substr(b.sql_text,4000,1) from
dba_hist_active_sess_history a, dba_hist_sqltext b
where sample_time between to_date('12-JAN-2021 12:30','DD-MON-YYYY HH24:MI')
and to_date('12-JAN-2021 12:40','DD-MON-YYYY HH24:MI') and b.sql_id=a.sql_id
union all
select a.sql_id ,dbms_lob.substr(b.sql_text,4000,1)from v$active_session_history
a ,v$sqlarea b
where sample_time between to_date('12-JAN-2021 12:30','DD-MON-YYYY HH24:MI') and
to_date('12-JAN-2021 12:40','DD-MON-YYYY HH24:MI') and b.sql_id=a.sql_id;

QUERY CURRENT STATUS

********************
select * from (
select
a.sid session_id
,a.sql_id
,a.status
,a.elapsed_time
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets
,a.disk_reads
,b.sql_text sql_text
from v$sql_monitor a
,v$sql b
where A.SID=2144 AND a.sql_id = b.sql_id and a.sql_id='7fh9125rfkr2v'
order by a.cpu_time desc)
where rownum <=10;

TABLESPACE GROWTH ANALYSIS

**************************
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT count(*) INTO v_count FROM dba_hist_tbspc_space_usage where
tablespace_id=v_ts_id;
IF v_count = 0 THEN
RAISE not_in_awr;
END IF ;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name =
v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY
HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from
dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into

v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where
tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into
v_ts_end_allocated_space from dba_hist_tbspc_space_usage where
tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into
v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and
snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into
v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and
snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||'
('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||
round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage:
'||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||
v_ts_begin_allocated_space||' MB'||'
('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||
v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||'
GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||
v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||
v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||
v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||
round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPCE '||
V_TS_NAME||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||
round(v_ts_growth/v_numdays,2)||' MB'||'
('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '||
round((v_ts_growth/v_numdays)*30,2)||' MB'||'
('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '||
round((v_ts_growth/v_numdays)*60,2)||' MB'||'
('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '||
round((v_ts_growth/v_numdays)*90,2)||' MB'||'
('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE DOES NOT EXIST !!!');
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');

END;
/

TOP 20 TABLE GROWTH ANALYSIS LAST 7 DAYS

****************************************

select * from
(SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
t.NAME "Tablespace", s.growth/(1024*1024) "Growth in MB",
(SELECT sum(bytes)/(1024*1024*1024)
FROM dba_segments
WHERE segment_name=o.object_name) "Total Size(GB)"
FROM DBA_OBJECTS o,
( SELECT TS#,OBJ#,
SUM(SPACE_USED_DELTA) growth
 FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0
ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS# and o.owner='OBDX_PROD' and o.object_type='TABLE'
ORDER BY 6 DESC) where rownum<20;

OBJECTS IN A DATAFILE
*********************
select *
from (
select owner, segment_name, segment_type, block_id
from dba_extents
where file_id = ( select file_id
from dba_data_files
where file_name = '/home/ora9ir2/oradata/ora9ir2/system.dbf' )
order by block_id desc
)
where rownum <= 5
/

DATABASE GROWTH
***************
with snpid as
(
select to_date(trunc(END_INTERVAL_TIME)) int_dt,min(snap_id) start_id,max(snap_id)
end_id from dba_hist_snapshot
where SUBSTR(END_INTERVAL_TIME, 0,9) >SUBSTR(sysdate-10, 0,9) and
SUBSTR(END_INTERVAL_TIME, 0,9) <SUBSTR(sysdate, 0,9)
group by trunc(END_INTERVAL_TIME)
order by 1 desc
)
select
sp.int_dt "Date",sp.start_id "Min Snap ID",sp.end_id "Max Snap ID",
round(sum(SPACE_ALLOCATED_DELTA/1024/1024/1024),3) as "Data Growth GB"
from DBA_HIST_SEG_STAT st, snpid sp
where st.snap_id between sp.start_id and sp.end_id
group by sp.int_dt,sp.start_id,sp.end_id
order by sp.int_dt;

DELETE ARCHIVELOG
*****************
delete force noprompt archivelog until time 'SYSDATE - 2';
DELETE force NOPROMPT ARCHIVELOG UNTIL TIME "SYSDATE-12/24";

FRAGMENTATION % IN TABLE (IF >= 20% remove fragmentation)

********************************************************
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB'
"FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/
round(((blocks*16/1024)),2))*100 "percentage"
from dba_tables WHERE owner='UIG' and table_name='UIGTH';

alter table uig.UIGTH enable row movement;

alter table <table_name> move tablespace <new_tablespace>
alter table uig.UIGTH disable row movement;

EXEC DBMS_STATS.gather_table_stats('UIG', 'UIGTH', estimate_percent =>

dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', cascade =>
TRUE, degree => 5);
EXEC DBMS_STATS.gather_table_stats('UIG', 'UIGTH', estimate_percent =>
dbms_stats.auto_sample_size, method_opt => 'for all indexed columns size auto',
cascade => TRUE, degree => 5);

rebuild indexes online.

INDEX REBUILD BENEFIT %

***********************
-- select only those indexes with an estimated space saving percent greater than
25%
VAR savings_percent NUMBER;
EXEC :savings_percent := 5;
-- select only indexes with current size (as per cbo stats) greater then 1MB
VAR minimum_size_mb NUMBER;
EXEC :minimum_size_mb := 1;
SET SERVEROUT ON ECHO OFF FEED OFF VER OFF TAB OFF LINES 300;
COL report_date NEW_V report_date;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS') report_date FROM DUAL;
spool /tmp/indexes_2b_shrunk.txt;
DECLARE
l_used_bytes NUMBER;
l_alloc_bytes NUMBER;
l_percent NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('PDB: '||SYS_CONTEXT('USERENV', 'CON_NAME'));
DBMS_OUTPUT.PUT_LINE('---');
DBMS_OUTPUT.PUT_LINE(
RPAD('INDEX_NAME', 35)||' '||
RPAD('TABLE_NAME', 35)||' '||
LPAD('SAVING %', 10)||' '||
LPAD('CURRENT SIZE', 20)||' '||
LPAD('ESTIMATED SIZE', 20));
DBMS_OUTPUT.PUT_LINE(
RPAD('-', 35, '-')||' '||
LPAD('-', 10, '-')||' '||
LPAD('-', 20, '-')||' '||
LPAD('-', 20, '-'));
FOR i IN (SELECT x.owner,x.table_name, x.index_name, SUM(s.leaf_blocks) *
TO_NUMBER(p.value) index_size,
REPLACE(DBMS_METADATA.GET_DDL('INDEX',x.index_name,x.owner),CHR(10),CHR(32)) ddl
FROM dba_ind_statistics s, dba_indexes x, dba_users u, v$parameter p
WHERE u.oracle_maintained = 'N'
AND x.owner = u.username
AND x.tablespace_name NOT IN ('SYSTEM','SYSAUX')
AND x.index_type LIKE '%NORMAL%'
AND x.table_type = 'TABLE'
AND x.status = 'VALID'
AND x.temporary = 'N'
AND x.dropped = 'NO'
AND x.visibility = 'VISIBLE'
AND x.segment_created = 'YES'
AND x.orphaned_entries = 'NO'
AND p.name = 'db_block_size'
AND s.owner = x.owner
AND s.index_name = x.index_name
GROUP BY
x.owner, x.table_name,x.index_name, p.value
HAVING
SUM(s.leaf_blocks) * TO_NUMBER(p.value) > :minimum_size_mb * POWER(2,20)
ORDER BY
index_size DESC)
LOOP
DBMS_SPACE.CREATE_INDEX_COST(i.ddl,l_used_bytes,l_alloc_bytes);
IF i.index_size * (100 - :savings_percent) / 100 > l_alloc_bytes THEN
l_percent := 100 * (i.index_size - l_alloc_bytes) / i.index_size;
DBMS_OUTPUT.PUT_LINE(
RPAD(i.owner||'.'||i.index_name, 35)||' '||
RPAD(i.table_name, 35)||' '||
LPAD(TO_CHAR(ROUND(l_percent, 1), '990.0')||' % ', 10)||' '||
LPAD(TO_CHAR(ROUND(i.index_size / POWER(2,20), 1), '999,999,990.0')||' MB', 20)||'
'||
LPAD(TO_CHAR(ROUND(l_alloc_bytes / POWER(2,20), 1), '999,999,990.0')||' MB', 20));
END IF;
END LOOP;
END;
/

CALCULATE TRANSACTION PER SECOND IN ORACLE

******************************************
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
WITH hist_snaps
AS (SELECT instance_number,
snap_id,
round(begin_interval_time,'MI') datetime,
( begin_interval_time + 0 - LAG (begin_interval_time + 0)
OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
FROM dba_hist_snapshot), hist_stats
AS (SELECT dbid,
instance_number,
snap_id,
stat_name,
VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY
snap_id)
delta_value
FROM dba_hist_sysstat
WHERE stat_name IN ('user commits', 'user rollbacks'))
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"
FROM hist_snaps sn, hist_stats st
WHERE st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
AND diff_time IS NOT NULL
GROUP BY datetime
ORDER BY 1 desc;

MANUALLY CREATE AWR SNAPSHOT

****************************
exec dbms_workload_repository.create_snapshot;

CPU UTILIZATION OF LAST 7 DAYS

*******************************
By default only activity of last 7 days are saved, to change it, please check:
cd /etc/sysconfig/sysstat
To check for example yesterday (05 October):

sar -P ALL -f /var/log/sa/sa05

DATABASE RESPONSE TIME

**********************
select inst_id,to_char(begin_time,'hh24:mi') time, value "Response Time"
from Gv$sysmetric
where metric_name='SQL Service Response Time';

FIND SPECIAL CHARACTER IN COLUMN

********************************
select EmpNo, SampleText
from test
where NOT regexp_like(SampleText, '[ A-Za-z0-9.{}[]|]');

TOP 10 TABLES WITH SIZE AND NUMBER OF ROWS

******************************************
select * from (
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024*1024))
"GB",num_rows
from all_tables
where owner not like 'SYS%' -- Exclude system tables.
and num_rows > 0 -- Ignore empty Tables.
order by GB desc) -- Biggest first.
where rownum <= 11;

JDBC Thin connection methods

****************************
SINGLE INSTANCE
For single instance Oracle database, the connection string need to be like:
jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID.

RAC
But for Oracle RAC, the connection string need to be:
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

EXPORT FOR BLOB TABLES HAVING SNAPSHOT TOO OLD

**********************************************
alter table obdx_prod.DIGX_EP_ACT_LOG_B_ARCH MODIFY LOB (ACT_LOG_OBJECT_BLOB)
(PCTVERSION 20);
alter table obdx_prod.DIGX_EP_ACT_LOG_B_ARCH MODIFY LOB (ACT_LOG_OBJECT_BLOB)
(RETENTION);

DATE WISE TRANSACTION COUNT FROM MVTRANSACTIONLOG

*************************************************
select /* +PARALLEL(20) */

DATE_LOC_TRAN_DT,
--SUBSTR(TIME_LOC_TRAN,0,2) AS HR,
COUNT(*)
from IRIS.MVTRANSACTIONLOG
where DATE_LOC_TRAN_DT>='09-DEC-2021'
--and RESP_CODE in ('091','036')
GROUP BY DATE_LOC_TRAN_DT --, SUBSTR(TIME_LOC_TRAN,0,2);

DAY/MONTH/YEAR WISE TRANSACTION COUNT FROM TBLTRANSACTIONLOG

************************************************************
select /* +PARALLEL(10) */
to_char(DB_DATE_TIME,'yyyy'),
to_char(DB_DATE_TIME,'MM'),
to_char(DB_DATE_TIME,'DD'),
--SUBSTR(TIME_LOC_TRAN,0,2) AS HR,
COUNT(*)
from IRIS.tbltransactionlog
PARTITION (SYS_P3222)
GROUP BY
to_char(DB_DATE_TIME,'yyyy'),
to_char(DB_DATE_TIME,'MM'),
to_char(DB_DATE_TIME,'DD');

PORTS OPENED ON LINUX OS

************************
lsof -i -P -n | grep LISTEN

DB2 ADVISORY
*************
db2advis -d dwhprd -s "SELECT distinct A.TO_DT, A.BRANCH_CD ,
digits(a.branch_cd)||'-'||brn.branch_name as branch, A.CUSTOMER_NO,
A.CUSTOMER_ID,a.account_type_id, ACCOUNT_DESC ,digits(a.branch_cd)||'-'||
digits(a.account_type)||'-'||digits(a.customer_no)||'-'||digits(a.run_no)||'-'||
digits(a.chk_digt) as ACCOUNT_NO, A.ACCOUNT_ID ,A.ACCOUNT_TYPE, ACCOUNT_TITLE,
LOCAL_EQV, ACCOUNT_OPEN_DT, MARKET_SRC , BDU FROM prd.branch brn inner join
PRD.account A on brn.branch_cd=a.branch_cd and A.ACCOUNT_OPEN_DT >= '2012-01-01'
inner join prd.account_type t on a.account_type_id=t.account_type_id LEFT OUTER
JOIN PRD.cust_market_src_tl B ON A.CUSTOMER_ID =B.CUSTOMER_ID LEFT JOIN PRD.BDU_TL
C ON C.EMP_NAME||'-'||C.EMP_NO = B.BDU"

db2advis -d dwhstg -s "SELECT * from qamber"

In case of long query create a temporary view and use select * from view in double
quotes.

RUNSTAT & REORG ON DB2

**********************
RUNSTATS ON TABLE PRD.ALL_TRANSACTION_FOR_SBP WITH DISTRIBUTION AND DETAILED
INDEXES ALL
CALL ADMIN_CMD('RUNSTATS ON TABLE PRD.CPU_E_STMT_FREQ_TL WITH DISTRIBUTION AND
DETAILED INDEXES ALL');
CALL ADMIN_CMD('REORG TABLE PRD.CUSTOMER_MARKET_SOURCE');

IDENTIFYING AREA OF SQL SLOWNESS IN DB2

***************************************
db2batch -d <dbname> -a <userid>/ <userpasswd> -f slow.sql -i complete -o f -1 p 2
o 5
db2batch -d dwhstg -a db2admin/Halfapple2020/? -f query.sql -i complete -o f -1 p 2
o 5

FINDING QUERIES OF TOP EVENTS

*****************************
SELECT event_id,
event,
COUNT(1) cnt
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 43046 AND 43049
AND wait_class_id=3871361733
AND event IN ('gc cr block busy','gc cr multi block request')
GROUP BY event_id,
event
ORDER BY 3;

SELECT sql_id,
COUNT(1)
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 43046 AND 43049
AND event_id IN (1520064534,661121159) group by sql_id;

Select sql_id,sql_text from dba_hist_sqltext where sql_id in

('1kwfs6yt0ntbx','520n3vqs0ct44','fug7z5vxbqc55');

MEMORY UTILIZATION SOLARIS SPARC

*********************************
echo "::memstat" | mdb -k

COUNT OF NUMBER OF OCCURENCE OF QUERY

*************************************
select sql_id , count(sql_id) exec_count from v$active_session_history where
sql_id='csqwktpgrmxpb' group by sql_id;

select count(*) from dba_hist_sqlstat where sql_id = '399cd00amamzd';

WHEN WAS SQLID FIRST TIME EXECUTED

***********************************
SELECT
h.sample_time,
u.username,
s.sql_text,
h.sql_id
FROM
DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_USERS u,
DBA_HIST_SQLTEXT s
WHERE sample_time >= SYSDATE - 15
AND h.user_id=u.user_id
AND h.sql_id = s.sql_iD
and h.sql_id='2wd5sz6wv5s84'
ORDER BY h.sample_time

MANUALLY REFRESH MATERIALIZED VIEW

**********************************
execute dbms_mview.refresh('emp_dept_sum','f');
V$PX_PROCESS
V$PX_SESSION

REFRESH MATERIALIZED VIEW IN PARALLEL

*************************************
Ensure base table has degree > 1
alter table iris.tbltransactionlog parallel (degree 10);

create materialized view log

add parallel hint in the select and create statement of MV

once MV has been created revert base table degree to 1

pre-build mechanism to rebuild materialize view
***********************************************
create table BAHL_MO_ISSUANCE_MV
(
request_number VARCHAR2(60),
move_order_number VARCHAR2(90) not null,
transaction_date DATE,
item_code VARCHAR2(120),
description VARCHAR2(720),
primary_uom_code VARCHAR2(9),
issue_quantity NUMBER,
transaction_id NUMBER not null,
line_num NUMBER,
inventory_item_id NUMBER not null,
organization_id NUMBER not null,
stat_line_id VARCHAR2(450)
);

CREATE MATERIALIZED VIEW BAHL_MO_ISSUANCE_MV

REFRESH FORCE
ON DEMAND
ON PREBUILT TABLE
AS
SELECT * FROM bahl_mo_issuance_v@Bahlapex;

SET NEW NAME IN RMAN

********************

Without OMF Configuration

==========================
set echo off pages 0 feed off sqlp #
spool setnewnamedf.lst
select 'set newname for datafile '||file#||' to '''||name||''';' from v$datafile;
spool off

With OMF Configuration

======================
Configure db_create_file_dest parameter to ASM disk group

set echo off pages 0 feed off sqlp #

spool /path/setnewnamedf.lst
select 'set newname for datafile '||file#||' to ''+DG'';' from v$datafile;
spool off

run
{
set newname for database to '+DATADG/FCCM/DATAFILE/%b';
restore database;
switch datafile all;
switch tempfile all;
}
recover database;

FORMAT df -h LINUX COMMAND

**************************
df -h | awk '!$2{getline x;$0=$0 x}{printf "%-35s %10s %6s %6s %4s %s\n",
$1,$2,$3,$4,$5,$6}'
df -h | awk '{print $1","$2","$3","$4","$5","$6}' >> excel.csv (output in csv
format)
df -h | awk '{print $1","$2","$3","$4","$5","$6}' |grep -E
"Filesystem|/backups|/proddata" (output only /backups and /proddata in csv
format with header)
df -h |grep -Ei "/backups|/proddata" (list only /backups and /proddata)
pscp -P 50014 -pw Oracle_1234
dbqamber@10.200.30.31:/home/dbqamber/scripts/excel.csv D:\scripts\CSV\excel.csv
(copy from remote server to local server)
df -h | awk 'NR>1 {print "Hello,",$1","$2","$3","$4","$5","$6}' (skip first
row)

MV MANUAL REFRESH
*****************
BEGIN dbms_mview.refresh('MVTRANSACTIONLOG','F'); END;

yum install oracle-database-preinstall-19c

CLEANUP TEMPORARY SEGMENTS OCCUPYING PERMANENT TABLESPACE

*********************************************************
select ts# from sys.ts$ where name = 'tablespace name';

Suppose it comes out to be 10, use the following command to cleanup temporary
segments:
alter session set events 'immediate trace name DROP_SEGMENTS level 11';
level is ts#+1 i.e 10+1=11 in this case.

QUERY TO FIND ALL CHILD TABLES FOR PRIMARY KEY

**********************************************
SELECT owner,constraint_name,table_name,delete_rule
FROM dba_constraints
WHERE owner ='BASELIII'
AND CONSTRAINT_TYPE = 'R'
AND r_constraint_name=
(SELECT constraint_name
FROM dba_constraints
WHERE owner ='BASELIII'
AND TABLE_NAME = 'STG_PRODUCT_MASTER'
AND CONSTRAINT_TYPE = 'P'
);

QUERY TO FIND ALL FOREIGN KEY COLUMNS FOR TABLE

***********************************************
select b.owner, b.table_name child_table,
c.column_name FK_column, b.constraint_name
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner
and b.table_name=c.table_name
and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name
and b.constraint_type='R'
and a.owner='&owner'
and a.table_name='&TABLE_NAME'
and a.CONSTRAINT_TYPE='P';

DATA CORRUPTION IRIS

********************
select * from iris.tbltransactionlog where db_date_time between to_date('28-MAY-
2023','DD-MON-RR')
and to_date('29-MAY-2023','DD-MON-RR') and
iris.uipkgglobal.isnumber(sys_trace_audit_no) = '0';

alter session set current_schema=IRIS;

SELECT /*+ PARALLEL(6) */ TRXLOGID,
to_number( TRXLOGID) AS TRXLOGID_INT,
rel_id,
ACQUIRING_CHANNEL_ID,
PROC_CODE,
NVL(TRAN_CODE, SUBSTR(PROC_CODE, 0, 2) ) AS TRAN_CODE,
DEP_AMTS,
AMT_TRAN,
CURR_CODE_TRAN,
DATE_LOC_TRAN,
to_date( DATE_LOC_TRAN, 'yyyymmdd') AS DATE_LOC_TRAN_DT,
TIME_LOC_TRAN,
to_date( DATE_LOC_TRAN
|| TIME_LOC_TRAN, 'yyyymmddHH24MISS') AS TIME_LOC_TRAN_DT,
to_number( trim(SYS_TRACE_AUDIT_NO)) AS SYS_TRACE_AUDIT_NO,
SYS_TRACE_AUDIT_NO AS STAN,
C_ACCEP_NAME_LOC,
Resp_Code,
AUTHORIZER,
C_ACCEP_TERM_ID,
INSTITUTIONID,
NVL(SUBSTR(rel_id, 1,
CASE
WHEN instr(rel_id, '=') = 0
THEN LENGTH(rel_id)
ELSE instr(rel_id, '=') - 1
END),PAN) AS RELID,
Channel_id,
TSN,
SUBSTR(PAN,0,6) AS IMD,
product_id,
merchant_id AS MERCHANTID,
txn_originator,
companyid,
customer_id,
amt_fee,
consumer_id,
amt_tran_base,
retr_ref_no,
internal_branch_id,
trim((
CASE
WHEN instr(acct_id_1,' ')>1
THEN SUBSTR(acct_id_1,1,20)
ELSE acct_id_1
END)) AS ACCOUNTID,
trim((
CASE
WHEN instr(acct_id_2,' ')>1
THEN SUBSTR(acct_id_2,1,20)
ELSE acct_id_2
END)) AS ACCOUNTID2,
customer_id AS CUSTOMERID,
amt_sett AS AMT_SETT,
 curr_code_sett,
record_data,
acq_interface,
SUBSTR(acq_inst_id_code,0,6) AS acq_inst_imd,
CASE date_sett
WHEN NULL
THEN to_date( date_sett, 'yyyyMMdd')
END AS DATE_SETT_DT,
txn_destination,
acct_id_1,
acct_id_2,
acq_inst_id_code,
auth_id_resp,
c_accep_id_code,
merch_type,
conv_rate_c_hldr_bill,
conv_rate_base,
conv_rate_sett,
FROM_MODULE,
FROM_NODE,
ATM_ID,
POS_ENT_MODE,
TO_MODULE,
TO_NODE,
Date_Sett,
settlement_status,
narration,
CURR_CODE_C_BILL,
AMT_C_HLDR_BILL,
PARENT_PRODUCT_ID,
MSG_TYPE,
' ' AS AUTH_AGENT_ID_CODE,
ACCT_CURRENCY_1,
DATE_EXP,
POS_COND_CODE,
ACQ_INST_CTRY_CODE,
TRANSM_DATE_TIME ,
REC_INST_ID_CODE,
ADD_DATA,
FORW_INST_ID_CODE,
VISA_BOADJUSTED,
VISA_TRAN_IND,
EMV_TAG_DATA,
CARD_SEQ_NUM,
DATE_CONV,
DATE_CAPT,
ORIG_DATA_ELEM,
REP_AMTS,
TRAN_DESC,
PAN,
RESERVED,
cust_inst_date_time
--FROM TBLTRANSACTIONLOG WHERE TRXLOGID BETWEEN '1226233652' AND '1226233654';
FROM TBLTRANSACTIONLOG WHERE TRXLOGID = '1226233654';

--1226233761

SELECT /*+ parallel(6) */ * FROM IRIS.TBLTRANSACTIONLOG WHERE SYS_TRACE_AUDIT_NO

LIKE '%R3%';
select TRXLOGID,SYS_TRACE_AUDIT_NO From iris.tbltransactionlog where trxlogid
>1749473175
and NOT REGEXP_LIKE(SYS_TRACE_AUDIT_NO, '^[0-9]+$')
order by trxlogid asc;

SPEED UP TAR CREATION

*********************
pigz-2.3.4-1.el7.x86_64.rpm
tar -I pigz -cvf apps_AFTR_12.2.10.tar.z /d01/apps/*

tar -I pigz -xvf apps_AFTR_12.2.10.tar.z /d01/apps/*

SESSION HISTORY INFORMATION ORACLE

***********************************
select * from dba_hist_active_sess_history
WHERE sample_time between to_date('26-06-2023 16:00:00','dd-mm-yyyy hh24:mi:ss')
and to_date('26-06-2023 16:30:00','dd-mm-yyyy hh24:mi:ss') and session_type not in
('BACKGROUND') ;

SQL QUERY RUNNING FOR MORE THAN 30 MIN

**************************************
SELECT
sid,serial#,inst_id,process,osuser,schemaname,machine,status,program,sql_id,sql_exe
c_start,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as LOGGED_ON_MIN,
ROUND(LAST_CALL_ET/60,1) as CURRENT_SQL_MIN
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL
AND TYPE = 'USER'
and ROUND(LAST_CALL_ET/60,1) > 30 -- more than 30 minutes
ORDER BY LAST_CALL_ET DESC ;

REMOVE ALL FILES EXCEPT LAST 10 DAYS

************************************
find . -name "*.aud" -mtime +10 -exec rm -rf {} \;

SQL PROFILES ASSOCIATED WITH SQLID

**********************************
select distinct
p.name sql_profile_name,
s.sql_id
from
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile;

STATUS OF SQL PROFILE

*********************
SELECT NAME,STATUS,SQL_TEXT FROM DBA_SQL_PROFILES;

ENABLE DISABLE SQL PROFILE

**************************
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'name_value', attribute_name=>'STATUS',
value=>'ENABLED');
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'name_value', attribute_name=>'STATUS',
value=>'DISABLED');

Exec dbms_sqltune.drop_sql_profile(name=>'profile_name_value');

IOPS OF ASM DISKGROUP

*********************
asmcmd> iostat -t -G DATADG 5

select * from V$ASM_DISK_STAT;

SELECT (select name from v$database) as

DBNAME,a.sid,a.serial#,a.inst_id,a.osuser,a.schemaname,a.machine,a.terminal,a.progr
am,a.sql_id,a.sql_exec_start,
to_char(a.logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-a.LOGON_TIME)*(24*60),1) as LOGGED_ON_MIN,
ROUND(a.LAST_CALL_ET/60,1) as CURRENT_SQL_MIN,substr(b.sql_text,1,200) SQOL_Text,
(select to_char(sysdate,'DD-MON-YYYY HH12:MI:SS AM') from dual) as "Current_Date"
From gv$session a, v$sql b
WHERE a.sql_id=b.sql_id
and a.STATUS='ACTIVE'
AND a.USERNAME IS NOT NULL
AND a.TYPE = 'USER'
and ROUND(LAST_CALL_ET/60,1) > 30 -- more than 30 minutes
ORDER BY LAST_CALL_ET DESC ;

PGA CURRENT USAGE AND HISTORY

*******************************
Current
=======
select a.inst_id,max(case when a.NAME LIKE '%total PGA inuse%' THEN VALUE ELSE 0
END) AS PGA_IN_USE,
max(case when a.NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0 END)
AS AGG_PGA,
(max(case when a.NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0
END)
- max(case when NAME LIKE '%total PGA inuse%' THEN VALUE ELSE 0 END)) diff,
max(case when a.NAME LIKE '%total PGA inuse%' THEN VALUE ELSE 0 END)/
max(case when a.NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0 END)
*100 as percent
from gv$pgastat a
where name like '%total PGA inuse%' or NAME LIKE '%aggregate PGA target parameter%'
group by a.inst_id
having
max(case when NAME LIKE '%total PGA inuse%' THEN VALUE ELSE 0 END)/
max(case when NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0 END)
*100 >50;

History
=======
select a.snap_id,b.BEGIN_INTERVAL_TIME,a.instance_number,max(case when a.NAME LIKE
'%total PGA inuse%' THEN VALUE ELSE 0 END) AS PGA_IN_USE,
max(case when a.NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0 END)
AS AGG_PGA,
(max(case when a.NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0
END)
- max(case when NAME LIKE '%total PGA inuse%' THEN VALUE ELSE 0 END)) diff,
max(case when a.NAME LIKE '%total PGA inuse%' THEN VALUE ELSE 0 END)/
max(case when a.NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0 END)
*100 as percent
from DBA_HIST_pgastat a, dba_hist_snapshot b
where a.snap_id=b.snap_id --and a.SNAP_ID>=67485
group by a.snap_id,a.instance_number,b.BEGIN_INTERVAL_TIME
having
max(case when NAME LIKE '%total PGA inuse%' THEN VALUE ELSE 0 END)/
max(case when NAME LIKE '%aggregate PGA target parameter%' THEN VALUE ELSE 0 END)
*100 >91;

SESSION USAGE
**************
SELECT NVL (username, 'SYS-BKGD') username, --sess.sid,
SUM (VALUE)/1024/1024
"Current session memory in MB"
FROM v$session sess, v$sesstat stat, v$statname NAME
WHERE sess.sid = stat.sid
AND stat.statistic# = NAME.statistic#
AND NAME.NAME LIKE 'session % memory' --and NVL (username, 'SYS-BKGD') not
like '%SYS%'
GROUP BY username -- sess.sid
order by SUM (VALUE)/1024/1024 desc ;

IDENTIFY USER WITH FAILED LOGIN ATTEMPTS

****************************************
select username,
os_username,
userhost,
client_id,
trunc(timestamp),
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
timestamp > sysdate -7
group by username,os_username,userhost, client_id,trunc(timestamp);

PAUSE AND RESUME DATAPUMP JOB

******************************
impdp attach=SYS_IMPORT_TABLE_01

stop_job
start_job

CONTINUE_CLIENT to show progress after restart job

PARTITION TABLE CREATION

***********************
CREATE TABLE "OPEN_CONNECT"."MX_MESSAGE"
( "ID" NUMBER(19,0),
"ASSIGNMENT_ID" VARCHAR2(35 CHAR),
"BUSINESS_MESSAGE_IDENTIFIER" VARCHAR2(35 CHAR),
"CASE_ID" VARCHAR2(35 CHAR),
"CREATED_DATE_TIME" TIMESTAMP (6),
"IS_INCOMING" NUMBER(1,0),
"MESSAGE_DEFINATION_IDENTIFIER" VARCHAR2(255 CHAR),
"MESSAGE_ID" VARCHAR2(35 CHAR),
"ORIGINAL_MESSAGE_DEFINATION_IDENTIFIER" VARCHAR2(255 CHAR),
"ORIGINAL_MESSAGE_ID" VARCHAR2(35 CHAR),
"PAYLOAD" CLOB,
"QUEUE_CORRELATION_ID" VARCHAR2(10 CHAR),
 "QUEUE_REPLY_TO" VARCHAR2(1000 CHAR),
"RECEIVER" VARCHAR2(12 CHAR),
"REJECT_CODE" VARCHAR2(255 CHAR),
"REJECT_REASON" VARCHAR2(1000 BYTE),
"SENDER" VARCHAR2(12 CHAR),
"STATUS" VARCHAR2(4 CHAR),
"TRACE_REFERENCE_ID" VARCHAR2(36 CHAR),
"TRANSACTIONS_LOG_REQ_RESP_ID" NUMBER(19,0),
"MESSAGE_CREATED_DATE" VARCHAR2(30 CHAR),
"INQUIRY_STATUS" VARCHAR2(100 CHAR),
"RESPONSE_CODE" VARCHAR2(20 BYTE),
"CERTIFICATE" VARCHAR2(10 BYTE),
"RTP_ID" VARCHAR2(100 BYTE),
"REF_DOC_INFO" VARCHAR2(10 BYTE),
"LCLINSTRM" VARCHAR2(10 BYTE),
"CATEGORY_PURPOSE_CODE" VARCHAR2(3 BYTE),
PRIMARY KEY ("ID") DISABLE
)
PARTITION BY RANGE ("CREATED_DATE_TIME") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION "MX_MSG1" VALUES LESS THAN (TIMESTAMP' 2020-01-01 00:00:00') SEGMENT
CREATION IMMEDIATE
TABLESPACE "OPEN_CONNECT"

MONITOR INDEX USAGE

*******************
ALTER INDEX <index_name> MONITORING USAGE;

SELECT index_name, used, monitoring FROM V$OBJECT_USAGE WHERE index_name =

'<index_name>'

REAL APPLICATION TESTING 19c

*****************************

DATABASE REPLAY
===============
https://www.youtube.com/watch?v=TJsxbLM5dlY (Part 1)

https://www.youtube.com/watch?v=_w_85kDRCDk (Part 2)

https://oracle-base.com/articles/11g/database-replay-11gr1

SQL PERFORMANCE ANALYZER

=======================
https://www.youtube.com/watch?v=omQC0csdKGM

https://oracle-base.com/articles/11g/sql-performance-analyzer-11gr1

name varchar2(20),
sid varchar2(10),
serial# varchar2(10),
inst_id varchar2(10),
osuser varchar2(60),
schemaname varchar2(50),
machine varchar2(100),
terminal varchar2(100),
program varchar2(200),
sql_id varchar2(50),
sql_exec_start varchar2(50),
logon_time varchar2(50),
LOGGED_ON_MIN, varchar2(50),
SQL_Text, varchar2(160),
Current_Date varchar2(50)