Check Oracle Database table growth

In DBA job, sometime we need to check table growth rate. For check table growth we need to enable snapshot of database. This snapshot will keep information of database in capture time. Base on this snapshot, we can calculate table growth rate. 

To view the object (segment) growth in blocks, you can use the views dba_hist_seg_stat and dba_hist_snapshot like below.

select   obj.owner, obj.object_name, 
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.db_block_changes_delta) block_increase
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('01-JAN-2012','DD-MON-RRRR') 
         and to_timestamp('02-FEB-2012','DD-MON-RRRR')
group by obj.owner, obj.object_name, 
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name

This script check only increase database block, if you want change from block to OS size we must know database block size. In default , database block size is 8192 Bytes.


When you summary total increase size  of table and compare with increase size of database, you will see it not equal. Because, this command don't minus delete block of table, and calculate database flashback retention, recycle bin and some table is template table. Template table will automatic drop at end of session, then size of table will return to be zero.

Reference: Doc ID 1395195.1

Clear Oracle Enterprise Manager Alert

Clear alert on EM

1. Make a SELECT * from MGMT_CURRENT_SEVERITY and show for the TARGET_GUID, METRIC_GUID and KEY_VALUE.

2. Connect to db user SYSMAN and execute:
exec EM_SEVERITY.delete_current_severity(TARGET_GUID,METRIC_GUID,KEY_VALUE);

for example:
exec EM_SEVERITY.delete_current_severity('0DEB8E6980695B7548CF98871084AD10','F95BA0D95585002889E1ABF92B2DA7C3','SYS');

3. Wait a few minutes.

Config Raid 0 in Linux

This port config raid 0 to improve Read/Write performance. We need 1 pc with 3 disk, 2 disk had same Disk size.

[root@vm04 mnt]# fdisk -l

Disk /dev/sdb: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00035d1e

Device Boot Start End Blocks Id System

Disk /dev/sda: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0000e9dc

Device Boot Start End Blocks Id System

Disk /dev/sdc: 250.1 GB, 250059350016 bytes
255 heads, 63 sectors/track, 30401 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0009f996

Device Boot Start End Blocks Id System
/dev/sdc1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sdc2 64 30402 243685376 8e Linux LVM

Disk /dev/mapper/vg_vm04-lv_root: 236.9 GB, 236948815872 bytes
255 heads, 63 sectors/track, 28807 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/mapper/vg_vm04-lv_swap: 12.6 GB, 12582912000 bytes
255 heads, 63 sectors/track, 1529 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

[root@vm04 mnt]# cd /
[root@vm04 /]# ls
bin cgroup etc lib lost+found misc net proc sbin srv tmp var
boot dev home lib64 media mnt opt root selinux sys usr
[root@vm04 /]# mkdir /disk1
[root@vm04 /]# mkdir /disk2
[root@vm04 /]# fdisk /dev/sda

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-60801, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-60801, default 60801):
Using default value 60801

Command (m for help): p

Disk /dev/sda: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0000e9dc

Device Boot Start End Blocks Id System
/dev/sda1 1 60801 488384001 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@vm04 /]# fdisk /dev/sdb

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-60801, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-60801, default 60801):
Using default value 60801

Command (m for help): p

Disk /dev/sdb: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00035d1e

Device Boot Start End Blocks Id System
/dev/sdb1 1 60801 488384001 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@vm04 /]# mount /dev/sda1 /disk1
mount: you must specify the filesystem type
[root@vm04 /]# mkfs.ext4
mkfs.ext4 mkfs.ext4dev
[root@vm04 /]# mkfs.ext4 /dev/sda1
mke2fs 1.43-WIP (20-Jun-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
30531584 inodes, 122096000 blocks
6104800 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
3727 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
102400000

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information:
done

[root@vm04 /]#
[root@vm04 /]# mkfs.ext4 /dev/sdb1
mke2fs 1.43-WIP (20-Jun-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
30531584 inodes, 122096000 blocks
6104800 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
3727 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
102400000

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information:
done

[root@vm04 /]#
[root@vm04 /]# mount /dev/sda1 /disk1
[root@vm04 /]# mount /dev/sdb1 /disk2
[root@vm04 /]# fdisk -l

Disk /dev/sdb: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00035d1e

Device Boot Start End Blocks Id System
/dev/sdb1 1 60801 488384001 83 Linux

Disk /dev/sda: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0000e9dc

Device Boot Start End Blocks Id System
/dev/sda1 1 60801 488384001 83 Linux

Disk /dev/sdc: 250.1 GB, 250059350016 bytes
255 heads, 63 sectors/track, 30401 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0009f996

Device Boot Start End Blocks Id System
/dev/sdc1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sdc2 64 30402 243685376 8e Linux LVM

Disk /dev/mapper/vg_vm04-lv_root: 236.9 GB, 236948815872 bytes
255 heads, 63 sectors/track, 28807 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/mapper/vg_vm04-lv_swap: 12.6 GB, 12582912000 bytes
255 heads, 63 sectors/track, 1529 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

[root@vm04 /]# dd if=/dev/zero of=/dev/sda1 bs=1G count=1 oflag=direct
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 9.20848 s, 117 MB/s
[root@vm04 /]# dd if=/dev/zero of=/dev/sdb1 bs=1G count=1 oflag=direct
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 9.65729 s, 111 MB/s
[root@vm04 /]# umount /disk1
[root@vm04 /]# umount /disk2
[root@vm04 /]# mdadm --create --verbose /dev/md0 --level=stripe --raid-devices=2 /dev/sdb1 /dev/sda1
mdadm: chunk size defaults to 512K
mdadm: Defaulting to version 1.2 metadata
mdadm: array /dev/md0 started.
[root@vm04 /]# ls /dev/md
md/ md0
[root@vm04 /]# ls /dev/md0
/dev/md0
[root@vm04 /]# dd if=/dev/zero of=/dev/md0 bs=1G count=1 oflag=direct
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 4.71866 s, 228 MB/s
[root@vm04 /]#