SELECT DISTINCT S1.USERNAME || '@' || S1.
MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
2 3 || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || '
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 S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;
du -a /home/oracle/ | sort -n -r | head -n 20
===========schema level gathers==================
begin
dbms_stats.gather_schema_stats(ownname=>'SERVICE_QUAL_PROD', cascade=>true,
degree=>4,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
options=>'GATHER',
method_opt =>'FOR ALL COLUMNS SIZE AUTO');
end;
schema size:
SELECT sum(bytes) / 1024 / 1024 / 1024 as "Size in GB"
FROM dba_segments
WHERE owner = UPPER('ORDMGMTDOM');
[3/17/2020 3:59 PM] Barma, Srinivasa R:
select OWNER,SUM(BYTES)/1024/1024/1024 "GB" FROM DBA_SEGMENTS group by owner;
Total size of the DB
=====================
SQL> SELECT SUM(bytes)/1024/1024/1024 FROM dba_data_files;
SUM(BYTES)/1024/1024/1024
-------------------------
438.308594
Total used size of the DB
=========================
SQL> SELECT SUM(bytes)/1024/1024/1024 FROM dba_segments;
SUM(BYTES)/1024/1024/1024
-------------------------
377.090942
Total free size of the DB
=========================
SQL> SELECT sum(bytes)/1024/1024/1024 "free space in gb" FROM dba_free_space;
free space in gb
----------------
61.2130737
August)#2020
DB start time:
select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance;
6.05.04.32.02
SQL> set lines 999 pages 999
SQL> col OSUSER for a20
SQL> col MACHINE for a35
spool APR2020.sql
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
AL_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='SCHEMA_NAME' and
object_type='TABLE';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
IT_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='IT202003';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
MT_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='MT202003';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
NF_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='NF202003';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
NB_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='NB202003';
spool off
spool Jan2020.sql
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
AL_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='AL201912';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
IT_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='IT201912';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
MT_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='MT201912';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
NF_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='NF201912';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
NB_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='NB201912';
spool off;
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO WANG_SHU;' FROM
DBA_OBJECTS WHERE OWNER='AL202001';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO MIKE_MACDONALD;' FROM
DBA_OBJECTS WHERE OWNER='AL202001';
select 'grant select on ALP.'||table_name||' to WANG_SHU;' from dba_tables where
owner='ALP';
"WANG_SHU
MIKE_MACDONALD
col OSUSER for a20
col USERNAME for a20
col machine for a20
select sid,serial#,osuser,username,machine from gv$session where
username='PILATDBA';
Low:1
Medium:2
MARYSE_LAHIERE
ORDER_MANAGER.OMEDW_PRODUCT_ORDER,ORDER_MANAGER.YFS_INBOX,EGAIN.EGDM_INT_FCT_EMAILA
CTIVITY_D & EGAIN.EGDM_CMN_DIM_QUEUE
Insight: OMEDW_PRODUCT_ORDER
Webform: YFS_INBOX
Egain: EGDM_INT_FCT_EMAILACTIVITY_D & EGDM_CMN_DIM_QUEUE
Create user MARYSE_LAHIERE identified by Canada2021$;
Grant connect,create session to MARYSE_LAHIERE;
grant select on EGAIN.EGDM_CMN_DIM_QUEUE to MARYSE_LAHIERE;
Canada2021!
scp *.zip sudhakar.pulavarthi@cdopld-245:/home/oraoemag
scp *.zip oraoemag@cdopld-245:/home/oraoemag
sudo su - oraoemag
@$ORACLE_HOME/rdbms/admin/ashgrpt.sql
SQL> Create user DARCY_MACDONALD identified by Canada2021$;
User created.
SQL> Grant connect,create session to DARCY_MACDONALD;
Grant succeeded.
spool 05032020.sql
set lines 999 pages 999
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO IVR_EXPRESS;' FROM
DBA_OBJECTS WHERE OWNER='DSSB';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO IVR_EXPRESS;' FROM
DBA_OBJECTS WHERE OWNER='DSSBI';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO IVR_EXPRESS;' FROM
DBA_OBJECTS WHERE OWNER='DSSBNB';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO IVR_EXPRESS;' FROM
DBA_OBJECTS WHERE OWNER='DSSBNF';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO IVR_EXPRESS;' FROM
DBA_OBJECTS WHERE OWNER='SQDATA_NB';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO IVR_EXPRESS;' FROM
DBA_OBJECTS WHERE OWNER='SQDATA_NL';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO IVR_EXPRESS;' FROM
DBA_OBJECTS WHERE OWNER='SQDATA_NS ';
find /backup/PRACG -name "*.bak" -type f -mtime +20 -exec rm -f {} \;
find /u02/EBPP920/archive -name "*.gz" -type f -mtime +20 -exec rm -f {} \;
Security Reminder – I Acknowledge
find /local/apps/oracle/BPMAPR2/admin/BPMAPR2/adump/files* -mtime +15 -exec del
{} \;
spool D:\orabkp\query21022020.log
set echo on
set feedback on
set lines 999 pages 999
select name,open_mode from v$database;
@D:\orabkp\query.sql
select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where
segment_type='TABLE' and segment_name=upper('&TABLE_NAME') group by segment_name;
Can you Please confirm us when you are recieving these alerts in that time during
the day
Take Backup of NC_OBJECTS, NC_PARAMS and NC_REFERENCES tables
create user AMANDA_SIMMONDS identified by Welcome123$;
"All of the tables associated with bahm:
grant select on BAHM.AUDIT_CONTACTS to AMANDA_SIMMONDS
grant select on BAHM.AUDIT_DEVICE to AMANDA_SIMMONDS;
grant select on BAHM.AUDIT_CUST_ORDER to AMANDA_SIMMONDS;
Can you please confirm us the timings when you are received these alerts.
AL202002, IT202002, MT202002, NF202002 and NB202002
grant select on AL202002 to AL_SCHEMAS_OBJECTS_READ_ONLY;
grant select on IT202002 to IT_SCHEMAS_OBJECTS_READ_ONLY;
grant select on MT202002 to MT_SCHEMAS_OBJECTS_READ_ONLY;
grant select on NF202002 to NF_SCHEMAS_OBJECTS_READ_ONLY;
grant select on NB202002 to NB_SCHEMAS_OBJECTS_READ_ONLY;
set lines 999 pages 999
spool Feb2020.sql
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
AL_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='AL202002';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
IT_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='IT202002';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
MT_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='MT202002';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
NF_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='NF202002';
SELECT 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO
NB_SCHEMAS_OBJECTS_READ_ONLY;' FROM DBA_OBJECTS WHERE OWNER='NB202002';
spool off;
expdp sys as sysdba directory=expdp_dir dumpfile=tableview.dmp
logfile=tableview.log content=metadata_only include=table,view
impdp directory=BACKUP_DIR logfile=impdp_tables_view_metadata.log
dumpfile=fulldb.dmp sqlfile=tables_view_metadata.sql content=metadata_only
exclude=ALTER_FUNCTION,ALTER_PROCEDURE,CONSTRAINT,CONTEXT,DB_LINK,DIRECTORY,FUNCTIO
N,INDEX,MATERIALIZED_VIEW,PACKAGE,PACKAGE_BODY,PACKAGE_SPEC,PROCEDURE,PROCOBJ,PROFI
LE,REF_CONSTRAINT,ROLE,SEQUENCE,SYNONYM,TABLESPACE,TRIGGER,TYPE,TYPE_BODY,TYPE_SPEC
