Database Notes: December 2016

[oracle@prod ~]$ ps -ef | grep pmon

oracle 4945 4909 0 09:47 pts/1 00:00:00 grep pmon
[oracle@prod ~]$
[oracle@prod ~]$
[oracle@prod ~]$ export ORACLE_SID=testprd
[oracle@prod ~]$
[oracle@prod ~]$ echo $ORACLE_SID
testprd
[oracle@prod ~]$
[oracle@prod ~]$
[oracle@prod ~]$ cd ../..
[oracle@prod /]$
[oracle@prod /]$ cd /disk1/oradata/testprd/
[oracle@prod testprd]$ mkdir -p log_mine
[oracle@prod testprd]$ ll
total 48
drwxr-xr-x 3 oracle oinstall 4096 May 3 15:20 arch_dest
drwxr-xr-x 2 oracle oinstall 4096 May 11 17:13 backup_data
drwxr-xr-x 2 oracle oinstall 4096 May 11 18:29 data_files
drwxr-xr-x 2 oracle oinstall 4096 May 15 09:47 log_mine
drwxr-xr-x 2 oracle oinstall 4096 May 11 18:30 rman_catalog
[oracle@prod testprd]$
[oracle@prod testprd]$
[oracle@prod testprd]$
[oracle@prod testprd]$ cd
[oracle@prod ~]$
[oracle@prod ~]$ cd /oraeng/app/oracle/product/11.2.0/dbhome_2/dbs/
[oracle@prod dbs]$ ll
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 3014 May 3 15:11 inittestprd.ora
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ vi inittestprd.ora
[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ cat inittestprd.ora

db_name='testprd'
db_unique_name=testprd
sga_max_size=4G
sga_target=3G
processes = 150
audit_file_dest='/oraeng/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/disk1/oradata/testprd/arch_dest/'
db_recovery_file_dest_size=2G
diagnostic_dest='/oraeng/app/oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/disk1/oradata/testprd/data_files/trecont.ctl)
compatible ='11.2.0'
utl_file_dir = '/disk1/oradata/testprd/log_mine/' (to store logminer dictionary) -----*****imp*****

[oracle@prod dbs]$
[oracle@prod dbs]$
[oracle@prod dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 09:53:17 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SYS@testprd >> create spfile from pfile;

File created.

SYS@testprd >> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 1728057176 bytes
Database Buffers 2533359616 bytes
Redo Buffers 12144640 bytes
Database mounted.
Database opened.
SYS@testprd >>
SYS@testprd >>
SYS@testprd >> set time on
09:54:10 SYS@testprd >>
09:54:14 SYS@testprd >> select status from v$instance;

STATUS
------------
OPEN

09:54:21 SYS@testprd >> show parameter utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /disk1/oradata/testprd/log_mine
/

09:54:25 SYS@testprd >>
09:54:37 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5

09:55:00 SYS@testprd >>
09:55:01 SYS@testprd >>
09:55:14 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

09:57:39 SYS@testprd >>
10:02:04 SYS@testprd >> !

[oracle@prod 2016_05_15]$ pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15

[oracle@prod 2016_05_15]$ ll
total 20544
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$
[oracle@prod 2016_05_15]$ echo $ORACLE_SID
testprd
[oracle@prod 2016_05_15]$

10:07:27 @ >> conn scott/scott
Connected.
10:07:33 scott@testprd >> select count(*) from tab;

COUNT(*)
----------
5

Elapsed: 00:00:00.09
10:07:40 scott@testprd >>
10:07:41 scott@testprd >> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE

Elapsed: 00:00:00.01
10:07:57 scott@testprd >>

------------------ Now connect with SYS to enable SUPPLEMENTAL LOG DATA

10:10:24 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

Elapsed: 00:00:00.00
10:10:41 SYS@testprd >>
10:10:42 SYS@testprd >> alter database add supplemental log data;

Database altered.

Elapsed: 00:00:00.02
10:10:58 SYS@testprd >> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES

Elapsed: 00:00:00.00
10:11:12 SYS@testprd >> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
10:11:21 SYS@testprd >>
10:11:22 SYS@testprd >> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:11:27 @ >> conn scott/scott
Connected.
10:11:34 scott@testprd >>
10:11:35 scott@testprd >>
10:11:35 scott@testprd >> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE

Elapsed: 00:00:00.01
10:11:51 scott@testprd >> select count(*) from salgrade;

COUNT(*)
----------
5

Elapsed: 00:00:00.05
10:12:02 scott@testprd >> delete from salgrade;

5 rows deleted.

Elapsed: 00:00:00.00
10:12:09 scott@testprd >> commit;

Commit complete.

Elapsed: 00:00:00.01
10:12:11 scott@testprd >> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:12:19 @ >> conn sys / as sysdba
Enter password:
Connected.
10:12:32 SYS@testprd >>
10:12:32 SYS@testprd >>
10:12:33 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
10:12:39 SYS@testprd >> alter system switch logfile;

System altered.

Elapsed: 00:00:00.01
10:12:46 SYS@testprd >> /

System altered.

Elapsed: 00:00:02.73
10:12:53 SYS@testprd >> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 7
Current log sequence 7
10:12:57 SYS@testprd >> !
[oracle@prod 2016_05_15]$ ll
total 22664
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
-rw-r----- 1 oracle oinstall 2152960 May 15 10:12 o1_mf_1_5_cmhzl65t_.arc
-rw-r----- 1 oracle oinstall 1536 May 15 10:12 o1_mf_1_6_cmhzlf7j_.arc
[oracle@prod 2016_05_15]$ exit
exit

10:14:34 SYS@testprd >>
10:14:35 SYS@testprd >> sho parameter utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /disk1/oradata/testprd/log_mine
/
10:14:41 SYS@testprd >>
10:14:41 SYS@testprd >>
10:15:56 SYS@testprd >> begin
10:16:00 2 dbms_logmnr_d.build('lgwr.dict','/disk1/oradata/testprd/log_mine/');
10:16:22 3 end;
10:16:23 4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.24
10:16:26 SYS@testprd >>
10:20:14 SYS@testprd >> !pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15

10:20:23 SYS@testprd >>
10:20:24 SYS@testprd >>
10:21:53 SYS@testprd >> begin
10:22:21 2 dbms_logmnr.add_logfile('/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc');
10:22:46 3 end;
10:22:48 4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
10:22:48 SYS@testprd >> begin
10:22:55 2 dbms_logmnr.start_logmnr(dictfilename=>'/disk1/oradata/testprd/log_mine/lgwr.dict');
10:23:08 3 end;
10:23:10 4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
10:23:11 SYS@testprd >> spool logmnr.txt
10:23:18 SYS@testprd >> select sql_undo from v$logmnr_contents where seg_name='SALGRADE' and seg_owner='scott';

SQL_UNDO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('1','700','1200');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('2','1201','1400');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('3','1401','2000');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('4','2001','3000');
insert into "scott"."SALGRADE"("GRADE","LOSAL","HISAL") values ('5','3001','9999');

Elapsed: 00:00:00.32
10:23:25 SYS@testprd >> spool off
10:23:34 SYS@testprd >>
10:23:59 SYS@testprd >>
10:24:00 SYS@testprd >> !pwd
/disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15

10:24:04 SYS@testprd >>
10:24:10 SYS@testprd >> !ls -ltr
total 22672
-rw-r----- 1 oracle oinstall 5241344 May 15 09:51 o1_mf_1_1_cmhyc5ob_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_2_cmhycmjx_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_3_cmhyczl9_.arc
-rw-r----- 1 oracle oinstall 5241344 May 15 09:52 o1_mf_1_4_cmhyd5d6_.arc
-rw-r----- 1 oracle oinstall 2152960 May 15 10:12 o1_mf_1_5_cmhzl65t_.arc
-rw-r----- 1 oracle oinstall 1536 May 15 10:12 o1_mf_1_6_cmhzlf7j_.arc
-rw-r--r-- 1 oracle oinstall 2274 May 15 10:23 logmnr.txt

10:24:13 SYS@testprd >>
10:24:14 SYS@testprd >>
10:24:14 SYS@testprd >> @logmnr.txt

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00
10:24:21 SYS@testprd >> commit;

Commit complete.

Elapsed: 00:00:00.00
10:24:28 SYS@testprd >> select count(*) from scott.salgrade;

COUNT(*)
----------
5

Elapsed: 00:00:00.00
10:24:44 SYS@testprd >>
10:24:46 SYS@testprd >>
10:24:46 SYS@testprd >> --------------finish--------------------
10:24:52 SYS@testprd >>

----------- ALERT LOG FILE ----------

Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 2 seq# 6 mem# 0: /disk1/oradata/testprd/data_files/redo2.log
Sun May 15 10:12:46 2016
Archived Log entry 17 added for thread 1 sequence 5 ID 0xdbe17139 dest 1:
Thread 1 cannot allocate new log, sequence 7
Checkpoint not complete
Current log# 2 seq# 6 mem# 0: /disk1/oradata/testprd/data_files/redo2.log
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /disk1/oradata/testprd/data_files/redo1.log
Sun May 15 10:12:53 2016
Archived Log entry 18 added for thread 1 sequence 6 ID 0xdbe17139 dest 1:
Sun May 15 10:12:54 2016
Starting background process SMCO
Sun May 15 10:12:54 2016
SMCO started with pid=27, OS id=5637
Sun May 15 10:16:24 2016
Sun May 15 10:16:24 2016
Logminer Bld: Lockdown Complete. DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 583714
Sun May 15 10:19:29 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:20:08 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:21:38 2016
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
Sun May 15 10:23:11 2016
LOGMINER: summary for session# = 2147483905
LOGMINER: StartScn: 581287 (0x0000.0008dea7)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag 0x0
Sun May 15 10:23:25 2016
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 4, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_4_cmhyd5d6_.arc
LOGMINER: Begin mining logfile for session -2147483391 thread 1 sequence 5, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc
LOGMINER: End mining logfile for session -2147483391 thread 1 sequence 5, /disk1/oradata/testprd/arch_dest/testprd/archivelog/2016_05_15/o1_mf_1_5_cmhzl65t_.arc
Sun May 15 10:35:45 2016

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

1) To find out no of transactions happens on table per hour basis.

SELECT to_date(TRAN_AUTH_ON) DAY,
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'00',1,0)),'99999') "00",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'01',1,0)),'99999') "01",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'02',1,0)),'99999') "02",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'03',1,0)),'99999') "03",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'04',1,0)),'99999') "04",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'05',1,0)),'99999') "05",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'06',1,0)),'99999') "06",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'07',1,0)),'99999') "07",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'08',1,0)),'99999') "08",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'09',1,0)),'99999') "09",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'10',1,0)),'99999') "10",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'11',1,0)),'99999') "11",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'12',1,0)),'99999') "12",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'13',1,0)),'99999') "13",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'14',1,0)),'99999') "14",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'15',1,0)),'99999') "15",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'16',1,0)),'99999') "16",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'17',1,0)),'99999') "17",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'18',1,0)),'99999') "18",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'19',1,0)),'99999') "19",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'20',1,0)),'99999') "20",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'21',1,0)),'99999') "21",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'22',1,0)),'99999') "22",
to_char(sum(decode(to_char(TRAN_AUTH_ON,'HH24'),'23',1,0)),'99999') "23"
from scott.salgrade
where to_date(TRAN_AUTH_ON) > sysdate-4
GROUP by to_char(TRAN_AUTH_ON,'YYYY-MON-DD'),to_date(TRAN_AUTH_ON)
order by to_date(TRAN_AUTH_ON);

2) Query to find basic redo log information

SELECT A.GROUP#,A.MEMBER, B.BYTES/1024/1024 "MB", B.ARCHIVED,B.STATUS,B.SEQUENCE#

FROM V$LOGFILE A INNER JOIN  V$LOG B ON A.GROUP# = B.GROUP#

ORDER BY GROUP# ASC;

3) To split alert log file which having size in GB to MB, in order to retrieve info easily.

split -b 100m alert_test.log

4) Candidate datafiles which are applicable for resize throughout the database.

ORA-03297: file contains used data beyond requested RESIZE value

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc;


******(BELOW WILL EXCLUDE UNDO DATAFILES & NON EXTENDABLE DATAFILES)

set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
ó get highest block id from each datafiles ( from x$ktfbue as we donít need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
ó join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
ó join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno) where tablespace_name not like ë%UNDO%í
)
select
case when autoextensible=íNOí and maxbytes>=bytes
then ó we generate resize statements only if autoextensible can grow back to current size
ë/* reclaim ë||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||íM from ë||to_char(ceil(bytes/1024/1024),999999)||íM */ ë
||íalter database datafile îí||file_name||îí resize ë||ceil(hwm_bytes/1024/1024)||íM;í
else ó generate only a comment when autoextensible is off
ë/* reclaim ë||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||íM from ë||to_char(ceil(bytes/1024/1024),999999)||íM */ ë
||íalter database datafile îí||file_name||îí resize ë||ceil(hwm_bytes/1024/1024)||íM;í
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 ó resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

REFERENCE: http://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/ 

Database Notes

Tuesday 13 December 2016

Oracle Log Mining Activity

Wednesday 7 December 2016

Useful scripts for DBA's

1) To find out no of transactions happens on table per hour basis.