How do you know the database name from normal user(connected) in the database:-
How to find the user objects created by him/her in his/her schema:-
SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc
Also query v$transaction and v$session to find out the session that is generating lot of undo information.
Startup time
select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/
How large is the database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
Distribution of objects and data
Which schemas are taking up all of the space
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
Is java installed in the database?
This will return 9000'ish if it is...
select count(*)
from all_objects
where object_type like '%JAVA%'
and owner = 'SYS'
/
Display character set information
select * from nls_database_parameters
/
Show all used features
select name
, detected_usages
from dba_feature_usage_statistics
where detected_usages > 0
/
SQL> conn ram/ram;
Connected.
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show parameter db_name
ORA-00942: table or view does not exist
SQL> select sys_context('userenv','db_name') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
arvind
SQL> select ora_database_name from dual;
ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ARVIND.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ARVIND.REGRESS.RDBMS.DEV.US.ORACLE.COM
How to find the user objects created by him/her in his/her schema:-
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- TEST TABLE AA TABLE SQL> select * from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- TEST TABLE AA TABLE SQL> select * from user_catalog; TABLE_NAME TABLE_TYPE ------------------------------ ----------- TEST TABLE AA TABLE SQL> select table_name from user_tables; TABLE_NAME ------------------------------ TEST AA SQL> select table_name from tabs; TABLE_NAME ------------------------------ TEST AA SQL> select object_name,object_type from user_objects; OBJECT_NAME OBJECT_TYP --------------- ---------- TEST TABLE SYS_C005421 INDEX AA TABLE SQL> select segment_name from user_segments; SEGMENT_NAME ------------------------------------------------------------------- AA SYS_C005421 TEST SQL> select segment_name from user_extents; SEGMENT_NAME ------------------------------------------------------------------- TEST AA SYS_C005421
showing the table structure
SQL> select dbms_metadata.get_ddl('TABLE','DEMO','RAJESH') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','DEMO','RAJESH')
--------------------------------------------------------------------------------
CREATE TABLE "RAJESH"."DEMO"
( "NAME" VARCHAR2(9),
"DBID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
The following sql which show you the allocated, Free and Used space in each of the datafiles;
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes ORDER BY file_name; FILE_NAME ALLOCATED_MB USED_MB FREE_SPACE_MB ---------------------------------------- ------------ ---------- ------------- +DATA/rock/datafile/sysaux.262.725656507 250 240.25 9.75 +DATA/rock/datafile/system.271.725656495 480 471 9 +DATA/rock/datafile/undotbs1.268.7256565 25 5.9375 19.0625 +DATA/rock/datafile/undotbs2.266.7256572 25 8.3125 16.6875 +DATA/rock/datafile/users.263.725656523 5 .5 4.5
Monitor long running operations using v$session_longops
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1;Note: the same query can be used to monitor RMAN backup status
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR != TOTALWORK
;
SID SERIAL# OPNAME SOFAR TOTALWORK COMPLETE
---------- ---------- -------------------- ---------- ---------- ----------
604 13371 Table Scan 6311 24498 25.76
685 1586 Table Scan 6333 24498 25.85
how to find a session with high archive logs
You can query v$sess_io and v$session to findout the the session which is generating lot of redo. i.e. lot of block changes occuring.SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc
Also query v$transaction and v$session to find out the session that is generating lot of undo information.
oracle processes
SQL> select nm, max(description) descript
2 from
3 (select regexp_replace( name,'[0-9a-z]','#') nm, description
4 from v$bgprocess)
5 group by nm
6 order by nm;
NM DESCRIPT
-------- ----------------------------------------------------------------
ARB# ASM Rebalance 9
ARBA ASM Rebalance 10
ARC# Archival Process 9
ASMB ASM Background
CJQ# Job Queue Coordinator
CKPT checkpoint
CTWR Change Tracking Writer
DBW# db writer process 9
DIAG diagnosibility process
DMON DG Broker Monitor Process
EMN# Event Monitor Process 0
NM DESCRIPT
-------- ----------------------------------------------------------------
FMON File Mapping Monitor Process
GMON diskgroup monitor
INSV Data Guard Broker INstance SlaVe Process
LCK# Lock Process 0
LGWR Redo etc.
LMD# global enqueue service daemon 0
LMON global enqueue service monitor
LMS# global cache service process 9
LNS# Network Server 9
LSP# Set Guard Standby Information for Logical Standby
MMAN Memory Manager
NM DESCRIPT
-------- ----------------------------------------------------------------
MMNL Manageability Monitor Process 2
MMON Manageability Monitor Process
MRP# Managed Standby Recovery
NSV# Data Guard Broker NetSlave Process 9
PMON process cleanup
PSP# process spawner 0
QMNC AQ Coordinator
RBAL ASM Rebalance master
RECO distributed recovery
RSM# Data Guard Broker Resource Guard Process 1
RVWR Recovery Writer
NM DESCRIPT
-------- ----------------------------------------------------------------
SMON System Monitor Process
34 rows selected.
Query to find backupset sizes
SQL> select ctime "Date" 2 , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type 3 , bsize "Size MB" 4 from (select trunc(bp.completion_time) ctime 5 , backup_type 6 , round(sum(bp.bytes/1024/1024),2) bsize 7 from v$backup_set bs, v$backup_piece bp 8 where bs.set_stamp = bp.set_stamp 9 and bs.set_count = bp.set_count 10 and bp.status = 'A' 11 group by trunc(bp.completion_time), backup_type) 12 order by 1, 2; Date BACKUP_TYPE Size MB --------- ----------- ---------- 03-JUL-10 Archive Log 7.31 03-JUL-10 Full 29.81 03-JUL-10 Incremental 2853.85 04-JUL-10 Archive Log 3.59 04-JUL-10 Full 7.45 04-JUL-10 Incremental 3.05
Startup time
select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/
How large is the database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
Distribution of objects and data
Which schemas are taking up all of the space
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
Is java installed in the database?
This will return 9000'ish if it is...
select count(*)
from all_objects
where object_type like '%JAVA%'
and owner = 'SYS'
/
Display character set information
select * from nls_database_parameters
/
Show all used features
select name
, detected_usages
from dba_feature_usage_statistics
where detected_usages > 0
/
Tablespace usage
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/ 
