How to use bare devices to expand or create tablespaces in a database

Editor to share with you how to use bare devices in the database to expand the size of tablespaces or create tablespaces. I hope you will gain something after reading this article. Let's discuss it together.

1. View the data file path corresponding to the tablespace

Select f.file#

T.name tablespace

F.name

Trunc (f.bytes / 1048576, 2) size_mb

To_char (f.creation_time, 'yyyy-mm-dd') creation_time

Status

From v$datafile f, v$tablespace t

Where f.ts# = t.ts# and t.nameplate users

Order by f.creation_time

two。 View tablespace usage

Select a.tablespace_name

A.total_G

B.fee_G

(a.total_G-b.fee_G) / a.total_G as "used%"

From (select a.tablespace_name

Sum (a.bytes) / 1024 / 1024 / 1024 As total_G

From dba_data_files a

Group by a.tablespace_name) a

Left join (select a.tablespace_name

Sum (a.bytes) / 1024 / 1024 / 1024 as fee_G

From dba_free_space a

Group by a.tablespace_name) b

On a.tablespaceroomname = b.tablespace_name and a.tablespaceroomname names users'

Order by 4 desc

TABLESPACE_NAME TOTAL_G FEE_G used%

USERS 1.99707031. 02520752. 987377751

3. Check to see if tablespace extension is on

Select FILE_NAME,file_id,AUTOEXTENSIBLE from dba_data_files where file_id=11

FILE_NAME FILE_ID AUT

-

/ dev/raw/raw31 11 YES

4. View bare device size (root users)

Fdisk-l / dev/ [hmins] dXN

Or:

# blockdev-- getsize / dev/raw/raw1

11718750

11718750 indicates how many OS BLIOCK there are.

In general, the size of an OS BLOCK is 512 bytes, so 11718750G512 Universe 1024Universe 1024 = 5722 (m) is the size of the bare device.

Note: a bare device can only add one file, and can only resize tablespace files. If there is not enough space for the bare device, you can only add the bare device to expand the capacity.

Create a bare device under 5.linux:

1)。 Create a partition:

Use fdisk to divide into a zone, such as partition / dev/hdb1

2)。 Mount the partition:

# raw/ dev/raw/raw1 / dev/hdb1

3)。 Modify permissions:

# chown oracle:oinstall / dev/raw/raw1

4)。 Link bare disk:

# ln-s / dev/raw/raw1 / oracle/oradata/dgdemo/temp.dbf

5)。 Create a data file for oracle:

Create tablespace temp datafile'/ oracle/oradata/dgdemo/temp.dbf' size 1000m reuse

Note: if you use LVM to create LV-type bare devices: because the LV created by LVM is a block device, not a character device, only a block device

# raw/ dev/raw/raw1 / dev/lv_db_test_1g

After reading this article, I believe you have a certain understanding of "how to use bare devices in the database to expand the size or create table space". If you want to know more about it, you are welcome to follow the industry information channel. thank you for reading!