diskmgmt.msc
isql -Uusername -Sserver -w 99999 -p
curl -T "/u02/app/grid19/diag/asm/+asm/+ASM1/trace/alert +ASM1.log" -o output -u "user:pass" "https://transport.oracle.com/upload/issue/3-32154073491/"
cat /etc/oracle-release
du -BG /u02/app/grid19/crsdata/server | sort -n -r head -n 30
dcli -l oracle -g dbs_group "df -kh /u01"
du BG /mount/shared/nfs/housekeeping | sort -n -r head -n 30
du -sh * | sort -n | grep G
begin
rdsadmin.rdsadmin_util.grant_sys_object
(
p_obj_name => 'DEMS XA',
p_grantee => 'XMETASR',
p_privilege => 'EXECUTE'
);
end;
/
rdsadmin.rdsadmin_util.grant_sys_object
(
p_obj_name => 'DEMS XA',
p_grantee => 'XMETASR',
p_privilege => 'EXECUTE'
);
end;
/
alter system set db_files=4500 scope=spfile sid='*';
[root@server log]# ls -la |wc -1
[root@server log]# ls -la |more
$ORACLE_HOME/OPatch/opatch lsinventory
crsctl query css votedisk
delete from WQUANT OWNER_V1.WOLFEQES_MODEL_DATA_GVKEY where dated > to_date('31-12-2004', 'dd-mm-yyyy');
commit;
Copying folders into another folder (folder in folder):
cp -r css images js backups ar/
Note: this is different from copying just the contents themselves (contents of folders in folder):
cp -r css/ images/ js/ backups/ ar/
select type, time, name, cause, status, message from PDB_PLUG_IN_VIOLATIONS where type = 'ERROR' and status != 'RESOLVED' order by name, type;
BEGIN
FOR r IN (select sid, serial# from v$session where username = 'TC34SCSTG02')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# || '''';
END LOOP;
END;
checking the last refreshed time of the materilased view
============================================================
SELECT owner, mview_name, TO_CHAR (last_refresh_date, 'DD-MON-YYYY HH24:MI:SS')
FROM dba mviews
WHERE owner = 'DATAPUB'
AND mview_name = 'GCM DERIVATIVES_IDR_FEED';
select owner, table_name, num_rows from dba_tables where owner in ('MATRIX', 'MATRIX_APP', 'MATRIX_DZ', 'MATRIX STG', 'TASKDRIVER') order by 1:
SQL> show parameter recovery
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 6500G scope=both sid='*';
copying from windows server \\server_name\E$ to ...
select name, open_mode from v$database;
select sum (bytes)/1024/1024/1024 size_in_gb from dba_data_files;
select from nls_database_parameters where parameter='NLS_CHARACTERSET';
show parameter SGA
show parameter PGA
show parameter CPU
cat \etc\oratab
alter user SERVICE_USER IDENTIFIED BY New_password PASSWORD EXPIRE;
scp prvuxabmitra@server:/backup/data/2018-07-08-05-00_FULL_databackup_3_4/DVDs/EP3toEQ3_DBbackup_07092018/ .
scp dbasupport@server:/backup/oracle01/orapwd_files/reconprd/orapwreconprd.
add db_name for output of each select query
=============================================
select name, db_name from v$database join schema.table on username like '%ABMITRA%';
select (select global_name from global_name) db_name, role from dba_roles;
select host_name from v$instance;
select from dba_directories;
SELECT sum (bytes)/1024/1024/1024 sizeinGB FROM dba_segments WHERE Owner = 'SMF';
select owner, object_name, object_type from dba_objects where owner = 'CGOTTESM';
select granted role, admin_option, default_role from dba_role_privs where grantee='SERVICE_INFOPOINT'; select from dba_source where owner not in ('SYS') and upper (text) like '%@IDW%';
EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;
aws s3 cp /backup/oracle01/Pdm_stgArchive s3://bucket_name/Export_Dump/ --sse --recursive --profile
select owner, sum (bytes/1024/1024) MB from dba_segments where TABLESPACE_NAME='MISC_DATA' group by owner;
select from user_role_privs;
SELECT SEQUENCE#, APPLIED FROM VSARCHIVED LOG;
nohup expdp \'/ as sysdba\' directory = DATA_PUMP_DIR2 dumpfile=exp_TRIDATA_trgprod_0324_%U.dmp logfile=exp_TRIDATA_trgprod_0324.log schemas=AIGCONFLUENCE parallel=12 exclude=statistics version=12.1 &
nohup impdp \'/ as sysdba\' directory=DATA_PUMP_DIR2 dumpfile=exp_dump_0914_%U.dmp logfile=imp_log_joho.log
REMAP_schema=AIREAS_ADMIN:JOHO tables=AIREAS_ADMIN.TRI_RAW_INTL_AIREAS remap_tablespace=MISC_DATA:USERS &
nohup impdp admin/passwd@RDS directory=DATA_PUMP_DIR dumpfile=exp_TRGDEV_ownr_acs.dmp logfile=imp_TRGDEV_RDS_ownr_acs_0329.log remap_tablespace=PDB_TEMP:TEMP EXCLUDE= SCHEMA:\"IN\(\'OWBSYS\',\'OWBSYS_AUDIT\'\)\" &
nohup expdp sys@SANDDB DIRECTORY=DATA_PUMP_DIR2 EXCLUDE=STATISTICS PARALLEL=8 Full=Y DUMPFILE=EXPDP_Full_SANDDB_20210210_U.dmp logfile=EXPDP_Full_SANDDB_20210210.log &
nohup impdp \"/ as sysdba\" directory=DATA_PUMP_DIR2 dumpfile=exp_dump_misp_1009_%U.dmp logfile=imp_dump_misp_1009.log REMAP_TABLESPACE=MISP:MISD_DATA remap_schema=MISP:SVC_MISPP parallel=8 &
nohup impdp \'/as sysdba\' directory=DATA_PUMP_DIR2 dumpfile=exp_DATAPUB_TFS_62628_%U.dmp logfile=imp_DATAPUB_62628.log remap_table=DATAPUB.SMF_ISSUS_CLASSIFICATION:SMF_ISSUE_CLASSIFICATION_N, DATAPUB.SMF_EQUITY:SMF EQUITY_N table_exists_action=append parallel=4 &
expdp \"/ as sysdba\" DIRECTORY=DATA_PUMP_DIR2 schemas=SRMNV INCLUDE=TABLE content=METADATA_ONLY dumpfile=SRMNV_tables_%U.dmp logfile=SRMNV_tables.log parallel=4
expdp \'/ as sysdba\' directory=DATA_PUMP_DIR4 dumpfile=exp_dump_REQ1727973.dmp logfile=exp_REQ1727973.log schemas=MS_UAT_MD_10 version=12.1 parallel=3 exclude=statistics ENCRYPTION=ALL ENCRYPTION_ALGORITHM=AES128 ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=Pass123 COMPRESSION=ALL
nohup expdp \'/ as sysdba\' directory=DATA_PUMP_DIR2 dumpfile=exp_QWANG U.dmp logfile=exp_QWANG.log schemas=OPS\$QWANG exclude=statistics
==========
SELECT name, ctime, TO CHAR(ptime, YYYY-MM-DD HH24:MI:SS') LAST CHANGED FROM sys.users WHERE name = 'SERVICE_TAB_ADR';
SELECT TO CHAR (SYSDATE, 'YYYY-MM-DD') FROM dual;
col NAME for a20
col CREATION TIME for a20
col PASSWORD_CHANGED ON for a20
SELECT name, TO_CHAR(ctime, 'YYY-MM-DD HH24:MI:SS') CREATION TIME, TO_CHAR (ptime, 'YYYY-MM-DD HH24:MI:SS') PASSWORD, CHANGED ON FROM sys.users WHERE name = 'ACTUARY DEVOPS';
SELECT FROM (SELECT current_schema () current_schema, current_warehouse (), current_role (), current_database());
CREATE DATABASE LINK ACTUARYP_L
CONNECT TO USER
IDENTIFIED BY <password>
USING 'ACTUARYP';
srvctl config service -d IC190L1Q
srvctl add service -db IC190L1Q -service sicnclsqa_pdb -pdb NCLSQA -preferred IC190L1Q2 -available IC190L1Q4 -tafpolicy BASIC -clbgoal SHORT
---> srvctl start service -db COR19L1Q -service sicappiant_pdb
srvctl config service -d IC190L1Q
srvctl stop service -db IC190L10 -service sicnclsqa_pdb -instance IC190L102
srvctl enable service -db IC190L1Q -service sicnclsqa_pdb
srvctl relocate service -db IC190L1Q -service sicnclsqa_pdb -oldinst IC190L1Q2 -newinst IC190L1Q4
DBMS_MVIEW.REFRESH ('OLF.OL AIG LM TCM BALANCES VIEW', 'C'');
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','|| serial# ||''' IMMEDIATE;' FROM gv$session where username = 'UCHEETI';
srvctl stop service -db INV19F1P -service sicdsuprod_tmp
srvctl remove service -db INV19F1P -service sicdsuprod_tmp
awk '/dbhome/ {print}' /etc/oratab | cut -d ":" -f 1
[oracle@server
ermprd
aigprd
select 'exec dbms_java.dropjava ('''||dbms_java.longname (object_name) ||''');' from user_objects where object_type like '%JAVA%';
exec dbms_java.dropjava ('EY');
exec dbms_java.dropjava ('EY');)
exec dbms_java.dropjava ('SecUtilsJavah');
exec dbms_java.dropjava ('SecUtilsJavah');
PL/SQL procedure successfully completed.
directory=DATA PUMP_DIR2
dumpfile=export_mv_0810.dmp
logfile=exp_mv_0810.log
schemas=ADMIN
include=materialized_view: "IN ('SP_RATINGSXPRESS', 'V_EMPLOYEE_NAMES', 'DUMMY_SNAP')"
export ORACLE_PDB_SID=PRD
Generating an AWR report in AWS RDS
get snap id
select snap_id, startup_time, begin_interval time, end_interval_time from dba_hist_snapshot order by snap_id desc;
genertate the AWR report
exec rdsadmin.rdsadmin_diagnostic_util.awr_report (7, 8, 'TEXT');
exec rdsadmin.rdsadmin_diagnostic_util.awr_report (2082, 2084, 'HTML');
select segment_name, sum (bytes)/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name in
( 'WDS APPR CURRENT', 'WDS_ERROR_XML', 'V_WDS LIMIT_DO', 'V_WDS STARFISH', 'WDS_ERROR_MASTER', 'WDS_APPR_CURRENT', 'WDS_ERROR_XML', 'WDM LOG', 'STG_THRESHOLD') group by segment_name order by 1;
SELECT name, to_char (ctime, 'MM-DD-YYYY HH24:MI:SS') created, to char (ptime, 'MM-DD-YYYY HH24:MI:SS') last_password_change FROM sys.users WHERE name='EDMZUS';
SQL> select guid, name from v$pdbs;
srvctl modify service -db IC121L1D -service service_name -available avail_inst_name -toprefer [-force]
srvctl modify service -db IC121L1D -service sicplutref -pdb PLUSREF -modifyconfig -preferred INV19D2
srvctl start service -db IC121L1D -service sicplutusref
srvctl modify service -db db_unique_name -service service_name -modifyconfig -preferred "preferred_list"
srvctl modify service -db IC121L1D -service sicplutref -pdb PLUTUSREF -modifyconfig -preferred CSCD1 -available INV11D4
srvctl modify service -db CSC01D ASH -service scbdsudev -pdb DSUDEV -modifyconfig -preferred CSC1D1 -available CS0104
srvctl status home -oraclehome /u02/app/oracle/product/19.0.0 -statefie /tmp/server_dbhome.txt -node server
===============
select (select global_name from global_name) db_name, owner from dba_source where lower (text) like '%smtp.apc.com%'
with
a as
(select from global_name),
b as
(
select count(*) from dba_source where lower (text) like '%smtp.aigfpc.com%
)
select a.*, b.* from a,b;
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (CBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
select dbms_metadata.get_ddl ('VIEW', 'VW_VAL_HEADCOUNT_REPORT', 'AIGTABLEAU') from dual;
select dbms_metadata.get_ddl ('INDEX', 'A5_IX1', 'SVC_GIS') from dual;
tune2fs -L ROOT PART /dev/sdal
e2label /dev/nvme label_name
to remove use " "
lsblk -f
nvme --list
lsblk -o +SERIAL
-‒For container
dbca -silent -configureDatabase -sourceDB IND_ASH -sysDBAUserName sys -sysDBAPassword C_min_IND -registerWithDirService true -dirServiceUserName "cn=orcladmin" -dirServicePassword or123 -walletPassword or123
--pdbs :
dbca -silent -configurePluggableDatabase -sourceDB INA_ASH -pdbName ACTD -sysDBAUserName sys -sysDBAPassword C_min_IND registerWithDirService true -dirServiceUserName "cn=orcladmin" -dirServicePassword ora123 -walletPassword ora123
select tablespace_name from dba_tablespaces where encrypted = 'YES';
select t.name tablespace_name, e.encryptionalg algo from v$tablespace t, vşencrypted_tablespaces e where t.ts# = e.ts# order by 1;
set serverout on
begin
for i in (select filename from (select * from
table (RDSADMIN.RDS_FILE_UTIL.LISTDIR ('DATA PUMP_DIR')) order by mtime) where filename like 'exp%')
loop
UTL FILE. FREMOVE ('DATA PUMP DIR', i.filename);
end loop;
end;
expdp \'/ as sysdba\' tables=APPIAN LITE.\"uamdailysyncaudit\", APPIAN_LITE.\"uammanagewdsmap\", APPIAN_LITE.\"uamuseraudit\" DIRECTORY=DATA_PUMP_DIR2 dumpfile=exp_Appiano_uam.dmp logfile=exp_Appiano_uam.log exclude=statistics
ps -ef| grep postfix
select NAME, SEQUENC#, APPLIED, to_char (COMPLETION_TIME, 'MM-DD-YYYY HH24:MI:SS') time, STATUS from v$archived_log where SEQUENCE = '231060';
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TEMPORARY TABLESPACE TEMP TEMPFILE
'+DATAC1' SIZE 49G AUTOEXTEND ON NEXT 16G MAXSIZE 34359738344K
ENCRYPTION USING 'AES128'
DEFAULT STORAGE (ENCRYPT)
TABLESPACE GROUP."
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE BIGFILE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'+DATACI' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 34359738344K
TABLESPACE GROUP " "
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TEMPORARY TABLESPACE TEMP TEMPFILE
'+DATAC1' SIZE 49G AUTOEXTEND ON NEXT 16G MAXSIZE 34359738344K ENCRYPTION USING 'AES128'
DEFAULT STORAGE (ENCRYPT)
TABLESPACE GROUP"
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;
UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'DB_NAME'
COMMIT;
ORA-01017
alter system set events '28043 trace name context forever, level 9';
alter system set events '28043 trace name context off';
ORA-28043
GRANT ANALYZE ANY DICTIONARY
GRANT ANALYZE ANY
"Resize Redolog -
Make sure number of groups and size "
col member for a55
select group#, type, member from v$logfile;
col "redo file name" for a45
select 1.group #, 1. thread#, 1. sequence #, 1.archived, 1.status, f.member "redo file name"
from v$log 1 join v$logfile f
on 1.group#=f.group#
order by 1.status;
select GROUP, THREAD#, SEQUENCE#, bytes/1024/1024, MEMBERS, STATUS from v$log;
###DSUT####
00 4 * * * /mount/shared/cifs/bin/db_stat_dst.sh dsu > /mount/shared/cifs/house...
00 3 * * * /mount/shared/cifs/bin/db_exp_full_dst.sh dsat full 2>&1 >/dev/null
I
select from dba_db_links;
select from dba directories;
select from dba_network_acls;
select from dba_network_acl_privileges;
select from global_name;
select t.name tablespace_name, e.encryptionalg algo from v$tablespace t, v$encrypted_tablespaces e where t.ts#= e.ts# order by 1:
select t.name tablespace_name, e.encryptionalg algo from v$tablespace t, v$encrypted_tablespaces e where t.ts#= e.ts# and e.encryptionalg = 'AES128' order by 1:
select distinct (t.name) tablespace_name, e.encryptionalg algo from v$tablespace t, vsencrypted_tablespaces e where t.ts#= e.ts# order by 1:
select distinct (t.ts#), t.name tablespace_name, e.encryptionalg algo from v$tablespace t, vsencrypted_tablespaces e where t.ts# = e.tst order by 1:
select from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
BEGIN
rdsadmin.rdsadmin_util.kill (sid=>13, serial =>37770,method => 'PROCESS');
END;
/
srvctl add service -db CSC_ASH -service SCBAPPI -preferred CSCD1 -available CSCD4 -pdb APPIA -role PRIMARY
srvctl start service -db CSC_ASH -service SCBAPPI
srvctl status service -d CSC_ASH
ALTER SYSTEM SET standby_file_management='manual';
alter session set container = AIGP;
alter database create datafile /u02/app/oracle/product/19.0.0.0/dbhome_2/dbs/UNNAMED00088' as '+DATAC2' SIZE 1G;
alter session set container=CDBSROOT;
alter system set standby_file_management = 'auto';
alter database recover managed standby database using archived logfile disconnect;
=========
Variable Size Database Buffers
Redo Buffers
Database mounted.
5637144576 bytes 6140461056 bytes
15175680 bytes
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:1
ORA-16005: database requires recovery
SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.1811E+10 bytes
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
Database opened.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
18375224 bytes
5637144576 bytes
6140461056 bytes 15175680 bytes
SQL> alter database open read only;
=========================
BEGIN
DBMS STATS.GATHER_SCHEMA_STATS(
ownname => <owner name>',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.auto_sample_size,
cascade => TRUE,
degree => 6
);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ADR_ODS',
tabname => 'AXS_SLR_CONSOLIDATION',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent=> DBMS_STATS. auto_sample_size,
cascade=> TRUE,
degree=> 12);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => <owner_name>',
tabname => '<table_name>',
partname => '<partion name>',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate percent => DBMS_STATS. auto_sample_size,
cascade => TRUE,
degree => 12);
END;
=========================
Copying password files using SCP
[oracle@liv203 1rd]$ scp dbasort@li2:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwlsprd /backup/oracle01/crapwd_files/1sprd
extract user / schema passowId :
---------------------------------
select name, password from SYS.users where name='ABPRA';
SQL> select name, password from SYS.users where name upper ('edmzr');
extract grants:
------------------
select 'Grant ' || privilege || ' ON ' || owner||'.'||table_name||' to '||Grantee ||';' from dba_tab_privs where owner
in ('TRIDA', 'ATABLEAU', 'ADATA', 'TRERT")
set lines 9999
set long 9999
set pages 9999
select dbms_metadata.get_ddl ('USER', 'TRITA') from dual;
==============================
Remove files more than 3 days:
-----------------------------
find /backup/log -mtime +2 -name 'log_backup*' -exec rm -rf {} \;
find /backup/oracle01/dump -mtime +5 -name 'exp_dump_ocrep2db_full_0401*.dmp' -exec rm -rf {} \;
find /backup/oracle/dump -mtime +5 -name 'logsegment_000* -exec rm -rf {} \ ;
find /backup/oracle/dump -name 'logsegment_000*' -mtime +5;
find /backup/oracle01/dump -name 'exp_dump_Idwprd pmf* -mtime +1 -exec rm -rf {} \;
find /oracle/PRD/saparch01 -mtime +7 -name 'PRDarch1_*' -exec mv {} /saparchive/arch01 \;
find /u01/app/oracle/diag/rdbms/crd/crd1/trace -type f -amin +120 -exec mv {} /backup/oracle01/db_trace_log_files/ \;
find /u01/app/oracle/diag/rdbms/crd/crd1/trace -name 'cdhprdi_ora_1*.tre' -amin +240 -exec mv {} /backup/oracle01/db_trace_log_files/ \;
find /backup/oracle01/orabackup/RMAN/ird/BKP -mindepth 1 -mtime +10 -depth -print
find /backup/data/D1/DB_D1 -mindepth 1 -mtime +14 -delete -print
find -maxdepth 1 -mindepth 1 -type f -name 'VALUATION_CASHFLOW_*' -a ! -name '*VALUATION_CASHFLOW_prod_*'
find -maxdepth 1 -mindepth 1 -type f -name 'VALUATION_CASHFLOW_*' -a ! -name '*VALUATION_CASHFLOW_prod_*' - print
find -maxdepth 1 -mindepth 1 -type f -name 'VALUATION_CASHFLOW_*' -a ! -name '*VALUATION_CASHFLOW_prod_*' - delete
find /var/opt/oracle/log/ru/creg -mtime +15 -type f -name 'creg_2020-01*.log' -exec mv {} /mount/shared/nfs/rdp/ \
ALTER TABLESPACE DATA autoextend on maxsize 4500G;
ALTER TABLESPACE temp autoextend on maxsize 200g;
ALTER TABLESPACE temp RESIZE 200g;
impdp admin/Aig5\$work@DEV attach=SYS_IMPORT_SCHEMA_01
select INSTALL_ID, PATCH ID, PATCH_TYPE, ACTION, STATUS, ACTION_TIME, DESCRIPTION, LOGFILE, RU_LOGFILE from dba_registry_sqlpatch;
select from sys.registry$history;
SELECT dbms metadata.get_ddl ('ROLE', role) FROM dba_roles;
SELECT dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&& your role_name') FROM dual;
SELECT dbms metadata.get_granted_ddl ('SYSTEM GRANT', '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl ('OBJECT_GRANT','&&your_role_name') FROM dual;
-
[oracle@wlt7exa01 dump]$ impdp \'/ as sysdba\' attach=SYS_IMPORT_SCHEMA_01
Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes
TABLE LOCK:
==============
column oracle username format a15
column os user name format a15
column object name format a37
column object type format a37
select a.session_id, a.oracle_username, a.os user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type, a.locked mode from (select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, (select object_id, owner, object_name, object_type From aba_objects) b
where a.object_id=b.object_id;
SET LINESIZE 400
SET PAGESIZE 100
COLUMN action time FORMAT A20 COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO CHAR (action time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM sys.dba_registry sqlpatch
ORDER by action time;
SELECT patch_id, version, status FROM dba_registry_sqlpatch;
select CON ID,
TO CHAR(action time, 'YYYY-MM-DD') AS action._time,
PATCH_ID,
PATCH TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET VERSION
from CB_REGISTRY_SQLPATCH
order by CON ID, action time, patch_id;
set lines 999
SELECT patch_id, patch_type, action, description FROM dba_registry_sqlpatch;
SQL> set lines 999
SQL> SELECT patch_id, patch_type, action, description FROM dba_registry_sqlpatch;
srvctl add service -db CSSRIMSQ -service scsschdfm -pdb SCHDFM -preferred CSSRIMSQI -available CSSRIMSQ3 -tafpolicy BASIC -clbgoal SHORT
srvctl add service -db CSSRIMSQ -service scsschdfq -pdb SCHDFQ -preferred CSSRIMSQ1 -available CSSRIMSQ3 -tafpolicy BASIC -clbgoal SHORT
alter system set events '28033 trace name context forever, level 9';
select os username, username, userhost, terminal, to_char (timestamp, 'MM-DD-YYYY HH24:MI:SS'),
action_name, logoff_time, RETURNCODE from DBA AUDIT SESSION where RETURNCODE in (1017, 28000)
and username= 'DADIU' order by timestamp;
gv$s views = fixed object stats
SGA HEAT MAP
v$sga_resize_ops
select os username, username, userhost, terminal, COMMENT_TEXT, to_char (timestamp, 'MM-DD-YYYY HH24:MI:SS'), action_name, logoff time, RETURNCODE from DBA_AUDIT_TRAIL where RETURNCODE in (1017,28000, 0) and username = 'DAP' order by timestamp desc;
select USERID, USERHOST, RETURNCODE, COMMENTS TEXT, NTIMESTAMP# from sys,auds where USERID='AFOT and RETURNCODE='1017' order by 5 desc;
select os USERNAME, USERNAME, USERHOST, to_char (timestamp, mm/dd/yyyy hh24:mi:ss') timestamp, OBJ_NAME, ACTION, ACTION_NAME, SESSIONID, SQL_TEXT
from dba_audit_trail WHERE TIMESTAMP between to date ('2018-03-14 22:08:20', 'YYYY-MM-DD HH24:MI:SS')
and to date('2018-03-15 00:09:00', 'YYYY-MM-DD HH24:MI:SS');
select from DBA_AUDIT_STATEMENT where upper (sql_text) like '%ENOTEBOOKS%';
select from gv$access where object in ('PK_DATA_LOAD', 'PK REPORT') and owner='FINANCIAL RISK' order by 2;
last sequence number
=================
SELECT last number
FROM all_sequences
WHERE sequence owner= '<sequence owner>'
AND sequence_name = '<sequence_name>';
RMAN
======
select to char (START_TIME, 'DD-MON-YY HH24:MI) START_TIME,to_char (END_TIME, 'DD-MON-YY HH24:MI') END_TIME,OUTPUT_BYTES/1000000 PROCESSED IN MB, STATUS from v$rman status where trunc (START TIME)= trunc (sysdate);
select from dba_role_privs
where grantee in ('SERVICE ADH','SERVICE AWP', 'ABMITRA')
order by 1,2;
SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA TAB PRIVS
WHERE TABLE NAME='PRE STG_TXN_LGAAP';
select from dba_role_privs where granted_role='DBA';
Date wise archive generation count
===================================
select to char (COMPLETION TIME, 'DD-MON-YYYY'), count(*)
from v$archived_log group by to char (COMPLETION TIME, 'DD-MON-YYYY')
order by to char (COMPLETION TIME, 'DD-MON-YYYY');
desc v$flash_recovery_area_usage
select space_limit/1024/1024/1024 GB, space_used/1024/1024/1024 GB from v$recovery_file_dest;
[Warning] ORA-01720: grant option does not exist for 'ADR DM.AXS MST MONTH DIM'
grant select on adr_dm.axs_mst_month_dim to mcf dm with grant option:
select from gv$access where object='LOSS_FINAN';
SQL> select spid from v$process p, v$session s where s.paddr=p.addr and s.sid=2477;
SELECT b.spid, a.sid, a. serial#, a.username, a.osuser FROM gv$session a, gvsprocess b
WHERE a.paddr = b.addr AND b.spid =142694 ORDER BY b.spid;
select from gv$session where sid=2141;
show pdbs
col NAME foramt al0;
select CON ID, DBID, NAME, OPEN_MODE from v$pdbs;
select to char (startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance;
set lines 200
select PATCH_ID, PATCH_UID, TARGET_VERSION, ACTION, ACTION_TIME, STATUS, DESCRIPTION from dba_registry_sqlpatch order by action time;
select description, action, to_char (action_cime, 'DD/MM/RR HH24:MI:SS') action_date, version from dba_registry_sqlpatch;
@?/rdbms/admin/utlrp.sql
Temp space
============
https://oracle-base.com/dba/script?category=monitoring&file=user_temp_space.sql
select
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks, a.sid,
a.serial#,
a.username,
a.osuser,
a.status,
srt.extents
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;
select grantee, Privilege || ' on ' || owner || '.' || table name, grantable
from dba_tab_privs
where owner= 'INVFIN' and table_name='PK_FIP_PERF EXTRACT';
explain plan for
================
select from ALM.ALM AEL where GEMS ID = 47332;
SELECT PLAN TABLE OUTPUT FROM TABLE (DBMS XPLAN.DISPLAY());
=====
select * from
(
select
sql_id,
sql_plan_hash_value, event, sql_exec_id,
sql_exec_start, current_obj#, sql_plan_line_id, sql_plan_operation, sql_plan_options,
SUM (delta_read_io_requests) lio_read, SUM (delta_read_io_bytes) pio_read, count (*) count_1
from
dba hist_active_sess_history
where
sql_id='76jkgwwnypqr9'
and
snap_id >= 139078
and
snap_id <= 139080
group by
sql_id,
sql_plan_hash_value,
event, sql_exec_id,
sql_exec_start,
current_obj#,
sql_plan_line_id,
sql_plan_operation, sql_plan_options)
order by count_1 desc;
==========
select snap_id, snap_level,
to_char (begin_interval time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot order by 1;
select from v$parameter where name like '%log%';
select dest_name, status, destination, error from V$ARCHIVE_DEST where STATUS !='INACTIVE';
alter system switch logfile;
ALTER SYSTEM SET log_archive_dest_state_3=DEFER SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_dest_state_3=ENABLE SCOPE=BOTH SID='*;
SELECT sid, serial#, osuser, username, status, logon_time, program FROM gv$session WHERE sid like '3994';
ALTER SYSTEM KILL SESSION '3986,40791' IMMEDIATE;
ALTER SYSTEM KILL SESSION '2479,9758' IMMEDIATE;
We got alert saying that rebalancing completed for this disk.
Could you please run this command in any ASM instance and confirm?
select from gv$asm_operation;
The utlrp.sql script recompiles all invalid objects.
Run the script immediately after installation, to ensure that users do not encounter invalid objects. Start SQL*Plus:
sqlplus "/ AS SYSDBA"
Run the utlrp.sql script, where Oracle_home is the Oracle home path:
SQL> @Oracle_home/rdbms/admin/utlrp.sql
select from dba_ts_quotas where tablespace_name='AIREAS_DATA';
select inst_id, username, status, machine, count (*) as active_conn_count
from gvssession group by inst_id, username, status, machine order by active_conn_count desc;
SELECT SID, Serial, UserName, Status, SchemaName, Logon_Time FROM V$session WHERE Status='ACTIVE' AND UserName IS NOT NULL:
vm.max_map_count=2147483647 [root@paws320 ~]# sysctl -a | grep shmall
kernel.shmall 67108864
dcli -l oracle -g dbs_group "cat /etc/oratab | grep oemrepp"
:%s/search_string/replacement_string/g
nohup expdp \'/ as sysdba\' directory=DATA_PUMP_DIR2 dumpfile = exp_dump_PLUT_0125_%U.dmp logfile=exp_log_PLUTU_0125.log schemas = OL,OL_XT,MERRY exclude=TABLE:\"IN\(\'AMR_PRO_DATA\'\)\" exclude=statistics parallel=8 &
exclude=table: "in\ ('EMP'\, 'DEPT'\)"
exclude=TABLE: "IN \(\'AMR_PROCESS_DATA'\)"
egrep -1 ORA-59035 *.trc
select inst_id, sid, serial#, sql_id, username, osuser, program, machine, status, to_char (LOGON_TIME, 'DD-MM-YY hh24:mm:ss') LOGON_TIME,
event from gvssession where username ='RDP';
last -n2 -x shutdown reboot
impdp schemas-AH remap_tablespace=MIS_DATA:AH_DATA directory=DATA_PUMP_DIR2 dumpfile=exp_AH_ID_20200.dmp logfile-imp_AH_AH5.1og ENCRYPTION_PASSWORD=as_test exclude=grant
sys/I_asd@GOU as sysdba
du -h --max-depth=1 2>/dev/null |grep G
du -a . | sort -n -r head -n 10
du -sh * | sort -hr | head
du -BG . | sort -n -r |head -n 30
du -BG /u02/app/grid19/diag/asm/+asm/+ASM3/trace | sort -n -r | head -n 30
select tablespace_name, username, bytes 1024/1024/1024 "Used GB", max_bytes/1024/1024/1024 as "Max GB" from dba_ts_quotas
where tablespace_name = 'MISC_DATA' and username = 'EXTDS'
delete noprompt archivelog all completed before 'SYSDATE-2/24';
set pagesize 500
set linesize 400
SELECT FROM VSFLASH_RECOVERY_AREA_USAGE;
AUDIT UPDATE TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY USERNAME BY ACCESS:
select space_limit/1024/1024/1024 GB, space_used/1024/1024/1024 GB from v$recovery_file_dest;
completed before 'SYSDATE-12/24';
rman target /
Ø delete noprompt archivelog all completed before 'SYSDATE-2';
Ø crosscheck archivelog all;
Ø delete noprompt expired archivelog all;
grep ORA- "imp_log_plutusd_OLF.log" | wc -l
find . -print | xargs grep -i "SYS.DBMS_CRYPTO"
