_____________________________________________________________________________________

set pages 1000
set lines 200
_____________________________________________________________________________________

GET SYSTEM INFORMATION WITHOUT DD ACCESS
========================================
select sys_context ('USERENV', 'SERVER_HOST') from dual;



MISCELLANEOUS DB INFORMATION
============================
col INSTANCE  for a10
col HOST      for a10
col PROCESSES for a10
col SESSIONS  for a10

select
   a.instance_name "INSTANCE",
   b.host_name "HOST",
   c.value "PROCESSES",
   d.value "SESSIONS",
   round(e.tablespace_size/1024/1024) "TEMP_TS_MB",
   round(f.allocated_space/1024/1024) "TEMP_TS_ALLOC",
   round(g.free_space/1024/1024) "TEMP_TS_FREE"
  from
   v$instance a,
   v$instance b,
   v$parameter c,
   v$parameter d,
   dba_temp_free_space e,
   dba_temp_free_space f,
   dba_temp_free_space g
 where c.name = 'processes'
   and d.name = 'sessions';



ROWID
=====
select last_name
from hr.employees
where rowid=chartorowid('AAASdNAAFAAAADLAAD');



V$VERSION
=========
describe v$version

select banner
from v$version
where banner like 'Oracle Database%';



V$VIEWS (SAMPLE)
================
describe dba_views

select view_name
from dba_views
where owner = 'SYS'
and view_name like 'V_$%'
and view_name like '%LOCK%'
order by 1;



DATAFILES WHICH NEED RECOVERY
=============================
describe v$datafile_header

select  name
from v$datafile_header
where recover = 'YES';



AUDIT SESSION
=============
describe dba_audit_session

select os_username, sername, terminal,
decode(returncode,'0','Connected','1005','FailedNull','1017','Failed',Returncode),
to_char(TIMESTAMP,'DD-MON-YY HH24:MI:SS'),
to_char(LOGOFF_TIME,'DD-MON-YY HH24:MI:SS')
from dba_audit_session;



COLUMN NAME
=========== 
describe dba_tab_columns

select table_name, column_name
from dba_tab_columns
where owner = 'SYS'
and table_name = 'DBA_DATA_FILES'
order by 1;



CONSTRAINTS
===========
describe dba_constraints

col owner for a20
select owner, table_name, constraint_name
from dba_constraints
where owner = 'SYS'
order by 3;



CONTROL FILE
============
describe v$controlfile

column name format a60

select * from v$controlfile;



DATAFILE (TOTAL SIZE)
=====================
describe V$DATAFILE

column "MB" format 99,999,999

select sum(round(BYTES/1024/1024)) MB from v$datafile;



DB AND INSTANCE
===============
describe v$database
describe v$instance

select d.dbid, i.instance_number
from v$instance i, v$database d
where lower(d.name) = lower(i.instance_name);



GET TSNAME, DATAFILE AND SIZE OF DATABASE
=========================================
describe V$DATAFILE
describe V$TABLESPACE

column tsname format a20
column filename format a55

select t.NAME "tsname", f.NAME "filename", round(f.BYTES/1024/1024) "MB"
from V$TABLESPACE t, V$DATAFILE f
where t.TS# = f.TS#
order by 1, 2;



DIRECTORIES
=========== 
describe DBA_DIRECTORIES

column OWNER format a12
column DIRECTORY_PATH format a80

select * from dba_directories;



INDEX
=====
describe DBA_IND_COLUMNS

select TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME
from DBA_IND_COLUMNS
where TABLE_NAME = 'STREAMS$_DEST_OBJ_COLS'
order by 1, 2;


col INDEX_NAME format a30
col COLUMN_NAME format a30

select INDEX_NAME, COLUMN_NAME
from DBA_IND_COLUMNS
where TABLE_NAME = 'STREAMS$_DEST_OBJ_COLS'
and INDEX_NAME like 'STREAMS%'
order by 1;



REDO LOG
========
describe V$LOG
describe V$LOGFILE

column MEMBER format a50
  
select l.GROUP#, l.MEMBERS, l.ARCHIVED, l.STATUS, f.TYPE, f.MEMBER
from V$LOG l, V$LOGFILE f
where l.GROUP# = f.GROUP#
order by 1, 6;



GET MAXLOGFILES WITHOUT TRACING CONTROLFILE
===========================================
describe V$CONTROLFILE_RECORD_SECTION

select RECORDS_TOTAL MAXLOGFILES
from V$CONTROLFILE_RECORD_SECTION
where TYPE = 'REDO LOG';



OBJECT COUNT FOR A SPECIFIC OWNER
=================================
describe DBA_OBJECTS

column OBJECT_NAME format a40

select OBJECT_TYPE, count(*)
from DBA_OBJECTS
where OWNER = 'SYSTEM'
group by OBJECT_TYPE order by 1;



GET LAST_COMPILE_DATE OF AN OBJECT
==================================
describe DBA_OBJECTS

column OWNER format a20
column OBJECT_NAME format a40
  
select
  OWNER,
  OBJECT_NAME,
  to_char(LAST_DDL_TIME,'DD-MON-YY HH24:MI:SS')
from DBA_OBJECTS
where OBJECT_TYPE in ('PROCEDURE', 'FUNCTION', 'TYPE')
  and OWNER = 'SYSTEM'
order by 1, 2;



GET CURRENT SYSTEM CHANGE NUMBER (SCN)
======================================
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
from dual;



GET ARCHIVELOG NAMES OF A SPECIFIC TIME WINDOW
==============================================
describe V$ARCHIVED_LOG

select NAME
from V$ARCHIVED_LOG
where to_char(COMPLETION_TIME,'YYYYMMDDHH24') >= 2008050512
  and to_char(COMPLETION_TIME,'YYYYMMDDHH24') <= 2008050513;



GET ALL USERS WITH ANY PRIVILEGES
=================================
describe DBA_SYS_PRIVS

select GRANTEE, PRIVILEGE, ADMIN_OPTION
from DBA_SYS_PRIVS
where PRIVILEGE like '%ANY%'
  and GRANTEE not in ('SYS','SYSTEM','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE')
order by 1, 2;



GET TERRITORY, LANGUAGE AND CHARACTERSET
========================================
describe SYS.PROPS$

select A.VALUE$ || '_' || B.VALUE$ || '.' || C.VALUE$ || ' - ' || D.VALUE$ "CHARSET_STRING"
from SYS.PROPS$ A, SYS.PROPS$ B, SYS.PROPS$ C, SYS.PROPS$ D
where A.NAME = 'NLS_TERRITORY'
  and B.NAME = 'NLS_LANGUAGE'
  and C.NAME = 'NLS_CHARACTERSET'
  and D.NAME = 'NLS_NCHAR_CHARACTERSET';
  
--------------------
describe SYS.PROPS$

select * 
from SYS.PROPS$;



GET NLS PARAMETER
=================
describe V$PARAMETER

col NAME for a30
col VALUE for a30
  
select NAME, VALUE
from V$PARAMETER
where NAME like 'nls%';

--------------------
describe NLS_DATABASE_PARAMETERS

select *
from NLS_DATABASE_PARAMETERS;



USE VARIABLES
=============
var v_usr varchar2(15)

exec :v_usr := 'HR'
select object_name from dba_objects where owner = :v_usr;



DETERMINE SYSAUX USAGE (OCCUPANTS)
==================================
describe v$sysaux_occupants

select occupant_name, space_usage_kbytes
from v$sysaux_occupants
order by 1;

select occupant_name, space_usage_kbytes
from v$sysaux_occupants
order by 2 desc;



_____________________________________________________________________________________

DATE/TS SAMPLES
===============
set pages 1000
set lines 200

select to_date('2007-10-10 23:55:55','YYYY-MM-DD HH24:MI:SS') from dual;

select
  to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "DATE",
  to_char(SYSDATE-10/1440,'YYYY-MM-DD HH24:MI:SS') "DATE-10"
from dual;

select to_char(sysdate, 'HH24:MI:SS'), to_char(systimestamp, 'HH24:MI:SS.FF6') from dual; 



INSERT
======
insert into <table_name> (<col_name_1>,..,<col_name_n>)
  values (<value_1>,..,<value_n>);



SNAPSHOTS
=========
describe STATS$SNAPSHOT

set pages 1000
set lines 200

select SNAP_ID-1 "START", SNAP_ID "END",
  to_char(SNAP_TIME,'YYYYMMDDHH24MISS') "DATE"
from STATS$SNAPSHOT
where SNAP_ID = (select max(SNAP_ID) from STATS$SNAPSHOT);

--------------------
set termout on
set heading off

column begin_snap new_value begin_snap noprint;

select '4711' begin_snap from dual;
set termout on

prompt &begin_snap;



ADMINISTRATIONAL COMMANDS
=========================
-- if datafiles have been removed from filesystem before tablespace was dropped...
alter database datafile 'C:\ORACLE\ORADATA\HAJO1\TEST01.DBF' offline drop;


-- stop archive log shipping (data guard)
alter system set log_archive_dest_state_2=DEFER scope=both;


-- set standby database to recovery mode
alter database recover managed standby database cancel;
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;


-- create pfile/spfile
create spfile [= 'SPFILE-NAME'] from  pfile  [= 'PFILE-NAME'];
create  pfile [= 'PFILE-NAME']  from spfile [= 'SPFILE-NAME'];

create pfile='/tmp/initorcl_stdby.ora' from spfile;
create spfile from pfile='/tmp/initorcl_stdby.ora';


INDEX FRAGMENTATION
===================
analyze index <index_name> validate structure;

-- This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.

    An index should be considered for rebuilding under any of the following conditions:

    The percentage of deleted rows exceeds 30% of the total, i.e. if

	- del_lf_rows / lf_rows > 0.3. 
    - If the ‘HEIGHT’ is greater than 4. 
    - If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

http://www.cryer.co.uk/brian/oracle/tuning_iif.htm



CREATE ALERT.LOG ENTRY
======================
Adding lines manually to the alert log is no a guarantee that the alert will be triggered. The best way to test is adding the alert via command.
Connect as sys into the target database and run:

  exec sys.dbms_system.ksdwrt(2, 'ORA-01157: internal error code, nts: xxxxxxx]');
  commit;

The above command adds the entry into the alert log file, you just need to adapt the error that you want to test.



SHOW ALERT LOG CONTENT (IF ALERT LOG FILE IS NOT READABLE FOR THE WORLD)
========================================================================
- 11g: select message_text from X$DBGALERTEXT;
- 12c: select message_text from SYS.V_$DIAG_ALERT_EXT;



MAKE ALL TRACE AND LOG FILES READABLE FOR WORLD
===============================================
ALTER SYSTEM SET “_trace_files_public” = TRUE SCOPE=SPFILE;



BACKUP CONTROLFILE TO TRACE WITH A DEDICATED PATH/FILE NAME
===========================================================
alter database backup controlfile to trace as '/tmp/hajo.ctl' reuse;



REDO LOG BLOCK CORRUPTION
=========================
-- in case of an error like this:

Errors in file /oracle/app/admin/DES3551/bdump/des3551_m000_4070.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 5440014607632 time 09/13/2010 18:29:34
ORA-00334: archived log: '/oradat102/oramir/DES3551/redo02b.log'

-- it might help to clear the logfiles:
alter database clear logfile group 1;
alter database clear logfile group 2;
_____________________________________________________________________________________