V$BUFFER_POOL_STATISTICS

V$BUFFER_POOL_STATISTICS displays statistics about all buffer pools available for the instance.

Column	Datatype	Description
ID	NUMBER	Buffer pool identifier number
NAME	VARCHAR2(20)	Name of the buffer pool
SET_MSIZE	NUMBER	Buffer pool maximum set size
CNUM_REPL	NUMBER	Number of buffers on replacement list
CNUM_WRITE	NUMBER	Number of buffers on write list
CNUM_SET	NUMBER	Number of buffers in set
BUF_GOT	NUMBER	Number of buffers gotten by the set
SUM_WRITE	NUMBER	Number of buffers written by the set
SUM_SCAN	NUMBER	Number of buffers scanned in the set
FREE_BUFFER_WAIT	NUMBER	Free buffer wait statistic
WRITE_COMPLETE_WAIT	NUMBER	Write complete wait statistic
BUFFER_BUSY_WAIT	NUMBER	Buffer busy wait statistic
FREE_BUFFER_INSPECTED	NUMBER	Free buffer inspected statistic
DIRTY_BUFFERS_INSPECTED	NUMBER	Dirty buffers inspected statistic
DB_BLOCK_CHANGE	NUMBER	Database blocks changed statistic
DB_BLOCK_GETS	NUMBER	Database blocks gotten statistic
CONSISTENT_GETS	NUMBER	Consistent gets statistic
PHYSICAL_READS	NUMBER	Physical reads statistic
PHYSICAL_WRITES	NUMBER	Physical writes statistic

Note:

1. V$BUFFER_POOL_STATISTICS view can be used to determine the hit ratio for the buffer pools. This view maintains statistics for each pool on the number of logical reads and writes. The buffer pool hit ratio can be determined using the following formula:

1 - (physical_reads/(db_block_gets + consistent_gets))

The ratio can be calculated with the following query:

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,

1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS;

2. To determining which segments have many buffers in the pool

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS

FROM DBA_OBJECTS o, V$BH bh

WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'

GROUP BY o.OBJECT_NAME

ORDER BY COUNT(*);

Oracle data dictionary views

Oracle dynamic performance views

how to reorganize segments online

In an Oracle 10g database, for data segments contained in a tablespace which is created with EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO, the storage can be adjusted online:

ALTER TABLE employees ENABLE ROW MOVEMENT;

ALTER TABLE employees SHRINK SPACE;

or

ALTER TABLE employees SHRINK SPACE CASCADE; --CASCADE includes indexes on the table;

To check if the table is a cadicate:

select tablespace_name, extent_management, segment_space_management

from dba_tablespaces

where tablespace_name = 'TS_EXP'

tablespace_name extent_management segment_space_management

TS_EXP LOCAL AUTO

How to Rename or Move Datafiles and Logfiles

Task: A datafile or logfile must be renamed inside Oracle for the following purpose:

- You want to move a database file to a different disk for performance or maintenance reasons.

- You have restored a datafile from backup, but the disk where it should normally be placed has crashed and you need to relocate it to a different disk.

- You have moved or renamed a datafile at operating system level but forgot to rename it within Oracle. At startup, you get ORA-01157 and ORA-01110. If the database is up and you try to shut it down normal or immediate, you get ORA-01116 and ORA-01110.

- You have multiple databases on the same machine and you need to rename certain database files to prevent collision and confusion.

There are three situations:

- RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN

- RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN

- RENAME AND OR MOVE A LOGFILE

How-To:

I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN

Datafiles can be renamed or moved while the database is open. The tablespace must be made READ-ONLY. Making the tablespace read only freezes the file header, preventing updates from being made to the file header.

Steps:

1. Determine how many datafiles are associated with the tablespace.

> SELECT FILE_NAME, STATUS

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME = '';

2. Make sure that all datafiles returned have the status AVAILABLE.

3. Make the tablespace is read only.

> ALTER TABLESPACE READ ONLY;

4. Make sure that the tablespace is defined as read only in the data dictionary.

> SELECT TABLESPACE_NAME, STATUS

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME = '';

5. Copy the datafile(s) to the new location using the operating system copy command. Once the datafile(s) have been copied to the new location compare the sizes of the datafiles. Make sure that the sizes match.

6. Once the datafiles have been copied to the new location alter the tablespace offline, which makes the tablespace inaccessible to users.

> ALTER TABLESPACE OFFLINE;

7. Once the tablespace is offline you will need to rename the datafile(s) to the new location. This updates the entry for the datafile(s) in the controlfile.

> ALTER DATABASE RENAME FILE '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'

TO '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

or use ALTER TABLESPACE ... RENAME DATAFILE;

8. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.

> ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE;

9. After you bring the tablespace back online you can make the tablespace read/write again.

> ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE;

10. You can check the status of the tablespace to make sure it is read/write. You can also verify that the controlfile has been updated by doing the following:

> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Optionally, you can query V$DATAFILE, which gets information from the controlfile as well.

11. Remove the datafile(s) from the old location at the O/S level.

II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN

1. If the database is up, shut it down.

2. Copy the datafile to the new name/location at operating system level.

3. Mount the database to read the control file but will not mount the datafiles.

> STARTUP MOUNT

4. Rename the file inside Oracle.

> ALTER DATABASE RENAME FILE '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'

TO '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

5. Open the database.

> ALTER DATABASE OPEN;

6. Query v$dbfile to confirm that the changes made were correct.

> SELECT * FROM V$DBFILE;

7. Remove the datafile(s) from the old location at the operating system level.

III. RENAME AND OR MOVE A LOGFILE

1. Shutdown the database.

2. Copy the logfile to the new name/location at operating system level.

3. Mount the database.

> STARTUP MOUNT

4. Rename the file.

> ALTER DATABASE RENAME FILE /FULL_PATH_OF_OLD_LOCATION/AND_REDO_LOG_NAME.LOG'

TO '/FULL_PATH_OF_NEW_LOCATION/AND_REDO_LOG_NAME.LOG';

5. Open the database.

> ALTER DATABASE OPEN;

6. Remove the logfile(s) from the old location at the operating system level.