Database Size query

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 /

Adding Redo Log members to Standby database

Checking Redo log files

col member for a90

set linesize 200

select * from v$logfile order by 1;

Adding Redo Logs

alter system set standby_file_management=manual;

ALTER DATABASE ADD LOGFILE MEMBER '&arq' to group &x;

check v$logfile if redo status INVALID

check which redo is not current

select * from v$log;

alter database clear logfile group &X;

If  you want to drop a member

ALTER DATABASE DROP LOGFILE MEMBER '&member';

If broker get failed, disable and enable configuration

Adding Standby Redo Logs

alter system set standby_file_management=manual;

to check if standby redo logs are being used check:

select * from v$standby_log;

alter system set standby_file_management=manual;

alter database recover managed standby database cancel;

To add a member:

ALTER DATABASE ADD STANDBY LOGFILE MEMBER '&arq' to group &x;

To Drop a member

alter database clear logfile group &X;

ALTER DATABASE DROP LOGFILE MEMBER '&arq';

Or to add standby redo log groups

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 101

('/oracle/db1/XXXX/DATA/standby101a_XXXX.log', '/oracle/db2/XXXXX/DATA/standby101b_XXXX.log') SIZE 50M REUSE;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 102

('/oracle/db1/XXXX/DATA/standby102a_XXXX.log', '/oracle/db2/XXXXX/DATA/standby102b_XXXX.log') SIZE 50M REUSE;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 103

('/oracle/db2/XXXX/DATA/standby103b_XXXX.log', '/oracle/db1/XXXX/DATA/standby103a_XXXX.log') SIZE 50M REUSE;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 104

('/oracle/db2/XXXX/DATA/standby104b_XXXX.log', '/oracle/db1/XXXX/DATA/standby104a_XXXX.log') SIZE 50M REUSE;

To drop a group

ALTER DATABASE DROP LOGFILE GROUP &x;

alter system set standby_file_management=auto;

alter database recover managed standby database   through all switchover disconnect nodelay;

If broker get failed, disable and enable configuration

to check if standby redo logs are being used check:
select * from v$standby_log;