useful

Oracle Database Upgrade Path Reference List [ID 730365.1]
---------------------------------------------------------

set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100

column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10

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

=/mnt/dbbackup/bemsvrmbdb02/billsvr11_1_BILLSVR1_S_527_P_1_T_855345927 tag=WEEKLY_BACKUP
channel ch1: restore complete, elapsed time: 01:25:37
Finished restore at 11-OCT-14
recover database;

Starting recover at 11-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA1/billsvr1/datafile/system.285.860647991'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/11/2014 09:40:28
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 2 seq 283705 lowscn 5983102243733 found to restore
RMAN-06025: no backup of log thread 2 seq 283704 lowscn 5983102129256 found to restore
RMAN-06025: no backup of log thread 1 seq 440755 lowscn 5983102293248 found to restore
RMAN-06025: no backup of log thread 1 seq 440754 lowscn 5983102264068 found to restore

SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

---------------------------------------------------------------------------
APEX_VMV:
------------
Apex
http://192.168.0.141:8080/apex

Apex
http://192.168.0.141:8080/apex/f?p=100:LOGIN_DESKTOP:5252404193772

Internal
admin
India12*

http://192.168.0.75:8080/apex/f?p=4550:1:2504728357192:::::
crm
admin

>CRM admin pwd ?
Vmv12*
-------------------------------------------------------------------------------------------------------------
impdp fintemp/fintemp dumpfile=EXPDP_FINANCE1_CRC_080614.DMP logfile=impdp_crc_fintemp_080615.log directory=expdump remap_schema=finance:fintemp TRANSFORM=oid:n:type


impdp boltest/boltest dumpfile=EXPDP_BOLFINANCE_250715.DMP logfile=impdp_boltest_finance_270515.log directory=expdump remap_schema=finance:boltest TRANSFORM=oid:n:type

SELECT username, PASSWORD, 'alter user ' || username || ' identified by values '|| '''' || PASSWORD || '''' ||';' alter_user_sql
FROM dba_users WHERE username = 'SYSMAN'


select 'alter table ' ||table_name|| ' add ||column_name||,table_name,data_type from user_tab_columns where data_type like 'NVARCHAR2';

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

http://www.dba-oracle.com/t_save_reset_oracle_user_password.htm

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

http://oh-bee-eye.blogspot.in/2012/06/oracle-database-arabic-support.html

----------------------------------------------------------------------------------------
Spool charfix.log
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;

-- yes, 2 times startup/shutdown . This is not a typo
SHUTDOWN IMMEDIATE;
STARTUP;
spool off


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


select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='BOLPROD';


get SID



select
a.sid,
a.serial#,
b.sql_text
from
v$session a,
v$sqlarea b
where
a.sql_address=b.address
and
sid=<>;

--------------------------
SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM;

------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
------------------------------
LOCK:

set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column ctime heading "Seconds"
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request,
l.ctime
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/



-----------------
Total Size

select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;



select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' from gv$session where username='ABILLITY_PROD';

DELETE FROM table_name;

or

DELETE * FROM table_name;


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

alter system set audit_trail = db|NONE scope=spfile;

alter system set audit_trail='DB','EXTENDED' scope=spfile;


select value from v$parameter where name = 'audit_file_dest';



SQL> audit all by finance by access;

Audit succeeded.

SQL> audit select table,update table,delect table,insert table by finance by access;
audit select table,update table,delect table,insert table by finance by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option


SQL> audit select table,update table,delete table,insert table by finance by access;

Audit succeeded.

SQL> audit execute procedure by finance by access;

Audit succeeded.



SQL> select count(*) from aud$;
COUNT(*)
----------
344273

SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;
COUNT(*)
----------
53550

SQL> delete from aud$ where ntimestamp# > sysdate - 21;

53550 rows deleted.
----------------------------------

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'VMVERP');
END;



SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

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

find . -mtime +0 # find files modified greater than 24 hours ago
find . -mtime 0 # find files modified between now and 1 day ago
# (i.e., in the past 24 hours only)
find . -mtime -1 # find files modified less than 1 day ago (SAME AS -mtime 0)
find . -mtime 1 # find files modified between 24 and 48 hours ago
find . -mtime +1 # find files modified more than 48 hours ago

The following may only work on GNU?

find . -mmin +5 -mmin -10 # find files modified between
# 6 and 9 minutes ago
find / -mmin -10 # modified less than 10 minutes ago


find /path of directory -name "*.log" -mtime +90 -exec ls -ltr {} \; it will list the files older than 3 months

find /path of directory -name "*.log" -mtime +90 -exec rm -f {} \; it will remove the files older than 3 months
---------------------------------------------------------------------------------------------
Database link

CREATE [PUBLIC] DATABASE LINK

CONNECT TO

IDENTIFIED BY

USING '';


create public database link CRCATTENDANCE connect to finlink identified by finlink using 'VMVERP';

create public database link CRCINTERFACE connect to crcinterface identified by crcinterface using 'VMVERP';

create public database link CRCINTERFACE connect to sierra identified by sierra using 'VMVERP';

select * from @

CRC-finuat:-

-- Create database link
create database link CRCATTENDANCE
connect to HRMS identified by hrms
using 'CRCATTENDANCE';


---------------------------------------------------------------------------------------------------
crctest/******** directory=expdump dumpfile=EXPDP_TEST1.DMP logfile=impdp_tables_test1.log remap_schema=crctest:finlink

308195--old
328742--new

TMP_DAILY_TNS


--------------------------------------------------
St courier

9176989263-->

------------------------
------------------------
97910
73561

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

-------------------------------------------------------------
https://www.udacity.com/course/viewer#!/c-ud837/l-4027328704/m-4329550789
https://www.udacity.com/course/developing-android-apps--ud853
https://www.udacity.com/course/advanced-android-app-development--ud855
https://www.udacity.com/course/ux-design-for-mobile-developers--ud849

https://www.udacity.com/course/html5-game-development--cs255

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

Row Locks:


select
sid,
serial#,machine
from
v$session
where
sid in (
select
session_id
from
dba_dml_locks )


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

select
session_id
from
dba_dml_locks
where
name = '';

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

SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD') ;


select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||',@'||b.inst_id||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD')
-----------------------------------------------
-- Utilisation (MB) du FRA
set lines 100
col name format a60

select
name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, used, Reclaimable
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';


-----------------------------------------------------------------------------
set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/

-------------------------------------------------
SQL> select TABLE_NAME from dict where table_name like 'DBA_S%';


DBA_SCHEDULER_PROGRAMS
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_WINDOWS
DBA_SCHEDULER_PROGRAM_ARGS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAIL
DBA_JOBS


--scheduled job run details--------------------
SQL> set linesize 800
SQL> select LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,CPU_USED,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS;
--------------------------------------------------

--scheduled job logs------

SQL> select LOG_ID,LOG_DATE,OWNER, JOB_NAME,STATUS,USER_NAME,CLIENT_ID,GLOBAL_UID,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_LOG;

-------------------------DBA JOBS---------------


SQL> SELECT LOG_USER,SCHEMA_USER,LAST_DATE, LAST_SEC,THIS_DATE,THIS_SEC, NEXT_DATE, NEXT_SEC,TOTAL_TIME,BROKEN,INTERVAL,FAILURES,WHAT,INSTANCE FROM DBA_JOBS;

----------------------------------------------------SCHEDULED JOBS--------------


SQL> SELECT OWNER,CLIENT_ID, PROGRAM_OWNER, PROGRAM_NAME,SCHEDULE_OWNER,START_DATE END_DATE,ENABLED,STATE,RUN_COUNT,LAST_START_DATE,LAST_RUN_DURATION,
NEXT_RUN_DATE, MAX_RUN_DURATION,COMMENTS FROM DBA_SCHEDULER_JOBS;


SELECT job_name,
destination,
TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
run_duration,
status,
error#
FROM user_scheduler_job_run_details

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

http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_4.shtml


--------------------------------------------------------------------------
TABLESPACE_NAME,Autoextend Size
***********************************

select
file_name "Filename",
tablespace_name "Tablespace",
round(bytes/1024/1024 ,2) "Current Size (MB)",
autoextensible "Autoextend",
round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where TABLESPACE_NAME='ABILLITY_TABLES';


---------------------------------------------
/bin/find /opt/DB_ARCH/ -type f -mtime +3 -name "arch*.arc" -print -exec rm {} \;' 2>&1 >>/opt/DB_ARCH/archives.deleted.log


-----------------------------------------------------------------------------------------------
To find LAsT DLL:

select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects order by last_ddl_time DESC;

-------------------------------------------------
read *Rman Backup Fails with ORA-19510 and ORA-27045 : No space left on device [ID 283681.1]*

RMAN-03009: failure of backup command on ch00 channel at 07/15/2015 10:14:59
ORA-19510: failed to set size of 2407114 blocks for file "/mnt/dbbackup/bemsvrmbdb01/billsvr1_1_BILLSVR1_S_3208_P_1_T_885073430" (blocksize=8192)
ORA-27045: unable to close the file
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
-----------------------------------------------------------
Nagios
Username: nagiosadmin
Password: n30lqk

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

Add datafile to tablespace ASM diskgroup DATAGROUP

SQL> alter tablespace USER-TABLESPACE add datafile '+DATAGROUP' size 10G;
Tablespace altered.
SQL>

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


2 server active node

11gr2 rac

linux or windows

---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
9i database:


---------------------------------------------------------------------------------
select * from app_user for update
select * from user_organization_dtl c where c.u_id=0 for update
select * from user_constraints t where t.constraint_name = 'FK_U_ID'
---------------------

iptables -A OUTPUT -d 216.58.220.46 -p tcp --dport 443 -j REJECT

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

Cleanup trace files more than 7 days old

root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/udump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/cdump/*.trc -mtime +7 -exec rm {} \;
Note that the first part of this script (before the –exec) displays all trace files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7


find *.trc -mtime +30 -exec rm {} \;
-------------------------------------------------------

ASM Commands:-

. oraenv
+ASM

asmcmd

lsct

ls

pwd

du

find +DATA *

ls -s

ls -l
-------------------------------
BI:

http://test.vmvsystem.com:9704/analytics
http://192.168.0.110:9704/analytics Administrator/Administrator

ssue 2: When you access the Dashboard from the OBIEE Page, you may encounter 500 Internal Error: - servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details. I resolved the issue like this: 1. Edit ./oc4j_bi/j2ee/home/applications/analytics/analytics/WEB-INF/web.xml Replace the port as 9703 instead of 9710 2. Go to $ORACLE_BI_HOME/setup/ $run-sa.sh stop wait for 10 seconds $run-saw.sh stop wait for 10 seconds $run-sa.sh start $run-saw.sh start. Now click on the application Dashboard. It will work fine.

/u01/app/oracle/product/OracleBI/setup

/u01/app/oracle/product/OracleBI/oc4j_bi/bin
oc4j -start
---------------------------------------------

Common scripts

-- - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - -
-- NAME: DBUPGDIAG.SQL
-- Version: 1.2
-- Executed as SYS as sysdba
-- ------------------------------------------------------------------------
-- AUTHOR:
-- Raja Ganesh and Agrim Pandit - Oracle Support Services - DataServer Group
-- Copyright 2008, Oracle Corporation
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly output to diagonise
-- the status of the database before (or) after upgrade. The script will
-- create a file called db_upg_diag__.log in your local
-- working directory. This does not make any DDL / DML modifications.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
--
--
col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';
spool &log_path/db_upg_diag_&&dbname&&timestamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col 'Initial DB Creation Info' for a35
col 'Total Invalid JAVA objects' for a45
col 'Role' for a30
col 'User Existence' for a27
col "JAVAVM TESTING" for a15
Prompt
Prompt
set feedback off head off
select LPAD('*** Start of LogFile ***',50) from dual;
select LPAD('Oracle Database Upgrade Diagnostic Utility',44)||
LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual;
Prompt
Prompt ===============
Prompt Hostname
Prompt ===============
select host_name from v$instance;
Prompt
Prompt ===============
Prompt Database Name
Prompt ===============
select name from v$database;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time"
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize"
FROM v$process;
Prompt
Prompt ================
Prompt Software Version
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT 'Compatibility is set as '||value Compatible
FROM v$parameter WHERE name ='compatible';
Prompt
Prompt ================
Prompt Archive Log Mode
Prompt ================
Prompt
archive log list
Prompt
Prompt ================
Prompt Auditing Check
Prompt ================
Prompt
set head on
show parameter audit
Prompt
Prompt ================
Prompt Cluster Check
Prompt ================
show parameter cluster_database
Prompt
DOC
################################################################

If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
upgrading the database

################################################################
#
Prompt
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name;
Prompt
Prompt ============================================================================
Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing
Prompt ============================================================================
Prompt
set head off
select count(*) as Records from sys.aud$ where dbid is null;
Prompt
Prompt
Prompt ============================================================================================
Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
Prompt ============================================================================================
set head off
select count(*) from system.aud$ where dbid is null;
Prompt
Prompt
Prompt =============================================================================
Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
Prompt =============================================================================
set head off
select count(*) from sys.fga_log$ where dbid is null;
Prompt
Prompt
prompt
Prompt ==========================================
Prompt Oracle Label Security is installed or not
Prompt ==========================================
set head off
SELECT case count(schema)
WHEN 0 THEN 'Oracle Label Security is NOT installed at database level'
ELSE 'Oracle Label Security is installed '
END "Oracle Label Security Check"
FROM dba_registry
WHERE schema='LBACSYS';
Prompt
Prompt ================
Prompt Number of AQ Records in Message Queue Tables
Prompt ================
Prompt
SET SERVEROUTPUT ON SIZE 100000
declare
V_COUNT NUMBER;
cursor c1 is
select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS');
begin
for c in c1
loop
execute immediate 'select count(1) from ' || c.owner || '.' || c.queue_table into v_count;
dbms_output.put_line(c.owner || ' - ' || c.queue_table || ' - ' || v_count);
end loop;
END;
/
Prompt
Prompt ================
Prompt Time Zone version
Prompt ================
Prompt
SELECT version from v$timezone_file;
Prompt
Prompt ================
Prompt Local Listener
Prompt ================
Prompt
select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener';
Prompt
Prompt ================
Prompt Default and Temporary Tablespaces By User
Prompt ================
Prompt
set head on
COLUMN USERNAME FORMAT A28
COLUMN TEMPORARY_TABLESPACE FORMAT A22
COLUMN DEFAULT_TABLESPACE FORMAT A22
SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS;
Prompt
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE

ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);

BEGIN

SELECT version INTO p_version
FROM registry$ WHERE cid='CATPROC' ;

IF SUBSTR(p_version,1,5) = '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||
RPAD('Status',10) ||RPAD('Version', 15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
RPAD(' ',10,'-') ||RPAD(' ',15,'-'));

FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name,
dr.status Status,SUBSTR(dr.version,1,15) version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) ||
RPAD(SUBSTR(x.comp_name,1,35),35)||
RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;

ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||
RPAD('Status',10) ||RPAD('Version', 15)||
RPAD('Org_Version',15)||RPAD('Prv_Version',15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')||
RPAD(' ',15,'-'));

FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,
SUBSTR(dr.version,1,11) version,org_version,prv_version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) ||
RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));

END LOOP;

END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM');
Prompt
DOC
################################################################

If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner in
('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP');
Prompt
DOC
################################################################

If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ======================================================
Prompt Count of Invalids by Schema
Prompt ======================================================
Prompt
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type order by owner,object_type ;
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC
###########################################################################

Result referencing the string 'B023' ==> Database was created as 32-bit
Result referencing the string 'B047' ==> Database was created as 64-bit
When String results in 'B023' and when upgrading database to 10.2.0.3.0
(64-bit) , For known issue refer below articles

Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
Upgrading Or Patching Databases To 10.2.0.3
Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) "Metadata",
CASE SUBSTR(metadata,109,4)
WHEN 'B023' THEN 'Database was created as 32-bit'
WHEN 'B047' THEN 'Database was created as 64-bit'
ELSE 'Metadata not Matching'
END "Initial DB Creation Info"
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ....
Prompt
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
from dba_objects
where owner = 'SYS'
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH')) ;
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ....
Prompt
SELECT object_name, object_type, subobject_name, object_id
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
FROM dba_objects
WHERE owner = 'SYS'
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH')) ;
Prompt
DOC

################################################################################

If any objects found please follow below article.
Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Read the Exceptions carefully before taking actions.

################################################################################
#
Prompt
Prompt ========================
Prompt Password protected roles
Prompt ========================
Prompt
DOC

################################################################################

In version 11.2 password protected roles are no longer enabled by default so if
an application relies on such roles being enabled by default and no action is
performed to allow the user to enter the password with the set role command, it
is recommended to remove the password from those roles (to allow for existing
privileges to remain available). For more information see:

Note 745407.1 : What Roles Can Be Set as Default for a User?

################################################################################
#
Prompt
Prompt Querying for password protected roles ....
Prompt
break on "Password protected Role"
select r.ROLE "Password protected Role",
p.grantee "Assigned by default to user"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);

Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE

V_CT NUMBER;
P_VERSION VARCHAR2(10);

BEGIN

-- If so, get the version of the JAVAM component
EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM''
AND status <> 99' INTO p_version;

SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE '%JAVA%' AND owner='SYS';

IF SUBSTR(p_version,1,5) = '8.1.7' THEN
IF v_ct>=6787 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN
IF v_ct>=13866 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN
IF v_ct>=14113 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored');

END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC

################################################################################

There should not be any Java Based users for database version 9.0.1 and above.
If any users found, it is faulty JVM.

################################################################################
#

Prompt
SELECT CASE count(username)
WHEN 0 THEN 'No Java Based Users'
ELSE 'There are '||count(*)||' JAVA based users'
END "User Existence"
FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%';
Prompt
DOC

###############################################################

Healthy JVM Should contain Six Roles.
If there are more or less than six role, JVM is inconsistent.

###############################################################
#

Prompt
SELECT CASE count(role)
WHEN 0 THEN 'No JAVA related Roles'
ELSE 'There are '||count(role)||' JAVA related roles'
END "Role"
FROM dba_roles
WHERE role LIKE '%JAVA%';
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE '%JAVA%';
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*)
WHEN 0 THEN 'There are no SYS owned invalid JAVA objects'
ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects'
END "Total Invalid JAVA objects"
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
Prompt
DOC

#################################################################

Check the status of the main JVM interface packages DBMS_JAVA
and INITJVMAUX and make sure it is VALID.

If there are no Invalid objects below will result in zero rows.

#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
set feedback off
Prompt
DOC

#################################################################

If the JAVAVM component is not installed in the database (for
example, after creating the database with custom scripts), the
next query will report the following error:

select dbms_java.longname('foo') "JAVAVM TESTING" from dual
*
ERROR at line 1:
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

If the JAVAVM component is installed, the query should succeed
with 'foo' as result.

#################################################################
#
Prompt
set heading on
select dbms_java.longname('foo') "JAVAVM TESTING" from dual;
set heading off
Prompt
SET FEEDBACK ON HEAD ON serveroutput on
Prompt ===================================
Prompt Oracle Multimedia/InterMedia status
Prompt ===================================
Prompt

DECLARE
v_count NUMBER;
v_version varchar2(200);
v_user_count number;
v_status VARCHAR2(200);
v_xdb_installed NUMBER;
v_xdk_installed NUMBER;
v_javavm_installed NUMBER;
TYPE string_tt IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;
v_user string_tt;

BEGIN

v_count := 0;
v_version := '';
v_user_count := 0;
v_status := '';
v_xdb_installed := 0;
v_xdk_installed := 0;
v_javavm_installed := 0;

SELECT 1,version,status
INTO v_count, v_version, v_status
FROM dba_registry
WHERE comp_id='ORDIM';


IF v_count > 0 then
DBMS_OUTPUT.PUT_LINE ('.');
DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is installed and listed with the following version: '||v_version||' and status: '||v_status);
DBMS_OUTPUT.PUT_LINE ('.');

/* check if all users are installed.*/

v_user(1) := 'ORDSYS';
v_user(2) := 'ORDPLUGINS';
v_user(3) := 'MDSYS';
v_user(4) := 'SI_INFORMTN_SCHEMA';
v_user(5) := 'ORDDATA';

DBMS_OUTPUT.PUT_LINE('Checking for installed Database Schemas...');

FOR i IN v_user.first .. v_user.last LOOP
SELECT COUNT(username)
INTO v_user_count
FROM dba_users
WHERE username = v_user(I);

/* ORDDATA user only exists starting 11.2 so no test if v_version is different */

IF v_user(i) = 'ORDDATA' AND SUBSTR(V_VERSION,1,6) NOT IN ('11.2.0','12.1.0') THEN v_user_count :=2;
END IF;

/* SI_INFORMTN_SCHEMA user only exists starting 11.2 so no test if v_version is different */

IF v_user(i) = 'SI_INFORMTN_SCHEMA' AND SUBSTR(V_VERSION,1,2) NOT IN ('10','11','12') THEN v_user_count :=2;
END IF;

CASE v_user_count
WHEN 0 THEN DBMS_OUTPUT.PUT_LINE (v_user(I)||' user does not exist.');
WHEN 2 THEN NULL; -- user does not exist in that version
ELSE DBMS_OUTPUT.PUT_LINE (v_user(I)||' user exists.');
END CASE;
END LOOP;

DBMS_OUTPUT.PUT_LINE('.');

/* Prerequisites Check*/
DBMS_OUTPUT.PUT_LINE ('Checking for Prerequisite Components...');

/* for versions >= 10.2 we will verify, if XDB and XDK are installed and valid */

SELECT COUNT(1)
INTO v_javavm_installed
FROM dba_registry
WHERE comp_id='JAVAVM';

IF v_javavm_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('JAVAVM is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM installed and listed as valid');
END IF;

IF SUBSTR(V_VERSION,1,2) IN ('11','12') OR
SUBSTR(V_VERSION,1,6) = ('10.2.0') THEN

SELECT COUNT(1)
INTO v_xdk_installed
FROM dba_registry
WHERE comp_id='XML';

IF v_xdk_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDK is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE('XDK installed and listed as valid');
END IF;

SELECT COUNT(1)
INTO v_xdb_installed
FROM dba_registry
WHERE comp_id='XDB';

IF v_xdb_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDB is not installed or not valid');
ELSE
DBMS_OUTPUT.PUT_LINE ('XDB installed and listed as valid');
END IF;

END IF;

/* for versions >= 11 we run validate_ordim */

DBMS_OUTPUT.PUT_LINE ('Validating Oracle Multimedia/interMedia...(no output if component status is valid)');

IF SUBSTR(V_VERSION,1,2) IN ('11','12') THEN
EXECUTE IMMEDIATE 'begin validate_ordim; end;';
ELSIF SUBSTR(V_VERSION,1,2) IN ('8.','9.','10') AND v_status <> 'VALID' THEN
DBMS_OUTPUT.PUT_LINE('Please run $ORACLE_HOME/ord/im/admin/imchk.sql to display details about invalid interMedia installation');
END IF;

END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is NOT installed at database level');
END;
/

set feedback off head off
select LPAD('*** End of LogFile ***',50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select 'Upload db_upg_diag_&&dbname&&timestamp&&suffix from "&log_path" directory'
from dual;
set heading on
set feedback on
Prompt
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - -

tbs_one_dtls_growth_script

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; /

tbs_all_dtls_growth_script

set serverout on set verify off set lines 200 set pages 2000 DECLARE v_ts_id number; not_in_awr EXCEPTION; 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; cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT'; BEGIN FOR v_rec in v_cur LOOP BEGIN SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_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_rec.tablespace_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(v_rec.tablespace_name||' Tablespace'); DBMS_OUTPUT.PUT_LINE('--------------------'); 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 TABLESPACE '||v_rec.tablespace_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; DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\'); EXCEPTION WHEN NOT_IN_AWR THEN DBMS_OUTPUT.PUT_LINE(CHR(10)); DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace'); DBMS_OUTPUT.PUT_LINE('--------------------'); 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('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!'); DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\'); NULL; END; END LOOP; END; /

table size

SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type in ('TABLE','TABLE PARTITION') 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') 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 -------------------- select top50.owner, top50.table_name, meg, a.num_rows from dba_tables a, (Select * from ( SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type like 'TABLE%' 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 like 'INDEX%' 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 = 'LOBSEGMENT' 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') GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 /* Ignore small tables */ ORDER BY SUM(bytes) desc ) where rownum < 51) top50 where top50.owner =a.owner and top50.table_name = a.table_name order by meg desc, num_rows desc; ---------------------------------- select TABLE_NAME,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='GSM_PREPAID_CDRS' SELECT table_name, tablespace_name, STATUS FROM dba_tables WHERE table_name LIKE '%T_LOGS_BKP_27032012'

Daily

Rman checking through sql
*****************************

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

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

with backup size:

set serveroutput on
set linesize 150
set pagesize 300
col time_taken_display for a9
col output_bytes_display for a12

select session_key,
input_type,
status,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
order by session_key desc;


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

select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
from dba_objects where STATUS='INVALID';

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

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

delete archivelog all completed before 'sysdate-1';

RMAN>backup database plus archivelog;

starting backup...........

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


RMAN>delete archivelog all;


-----------------------------
select name from v$asm_diskgroup;

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

select name,total_mb,free_mb from v$asm_diskgroup;

-----------------
AWR:

select dbid from v$database;


DBID
----------
3191997282
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;

SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,1,51534, 51538 ));

SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3191997282,2,51535, 51539 ));


output:copy and save as html file

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

select segment_name,segment_type,bytes/1024/1024/1024 from user_segments where tablespace_name='ABILLITY_TABLES'
order by 3 desc

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

select INSTANCE_NUMBER,INSTANCE_NAME from gv$instance;

------------------
select sum(bytes)/1024/1024/1024 from dba_segments;
-------------
select sum(bytes)/1024/1024/1024 from dba_data_files;

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

select opname,time_remaining from v$session_longops where sofar<>totalwork

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

shirnk datafile
==============================================================================
shirnk datafile
===========
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
=======================================

DATA FILE SHIRNK ...

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


column tablespace_name format a10
column file_name format a32
column file_mb format 9999990
column hwm_mb format 9999990
column used_mb format 9999990
column shrnk_mb format 9999990

break on report
compute sum of file_mb on report
compute sum of hwm_mb on report
compute sum of used_mb on report
compute sum of shrnk_mb on report

select a.*
, file_mb-hwm_mb shrnk_mb
from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_mb,
b.hwm*d.block_size/1024/1024 hwm_mb,
b.used*d.block_size/1024/1024 used_mb
from
dba_data_files a,
(select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
from dba_extents
group by file_id) b,
dba_tablespaces d
where a.file_id = b.file_id
and a.tablespace_name = d.tablespace_name
) a
order by a.tablespace_name,a.file_name;

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