Pro Oracle Database 11g Administration (44 page)

If you want to compare objects in two schemas in the same database, then you have to modify the scripts to include an OWNER and use the DBA or ALL data-dictionary view (instead of USER).

Listed next is a more complex example of comparing two schemas’ objects. The following script compares several data-dictionary views for differences in metadata:

spo diff.txt

prompt Default or temp tablespace in db1 NOT IN db2

select default_tablespace, temporary_tablespace

from user_users&&conn1

minus

select default_tablespace, temporary_tablespace

from user_users&&conn2;

239

CHAPTER 10 ■ DATA DICTIONARY BASICS

prompt Default or temp tablespace in db2 NOT IN db1

select default_tablespace, temporary_tablespace

from user_users&&conn2

minus

select default_tablespace, temporary_tablespace

from user_users&&conn1;

prompt Tablespace quotas in db1 NOT IN db2

select tablespace_name, max_bytes

from user_ts_quotas&&conn1

minus

select tablespace_name, max_bytes

from user_ts_quotas&&conn2;

prompt Tablespace quotas in db2 NOT IN db1

select tablespace_name, max_bytes

from user_ts_quotas&&conn2

minus

select tablespace_name, max_bytes

from user_ts_quotas&&conn1;

prompt Objects in db1 NOT IN db2

select object_name, object_type

from user_objects&&conn1

minus

select object_name, object_type

from user_objects&&conn2 order by 2;

prompt Objects in db2 NOT IN db1

select object_name, object_type

from user_objects&&conn2

minus

select object_name, object_type

from user_objects&&conn1 order by 2;

prompt Tables in db1 NOT IN db2

select table_name

from user_tables&&conn1

minus

select table_name

from user_tables&&conn2;

prompt Tables in db2 NOT IN db1

select table_name

from user_tables&&conn2

minus

select table_name

from user_tables&&conn1;

prompt Indexes in db2 NOT IN db1

select table_name, index_name, index_type, uniqueness

from user_indexes&&conn2

minus

select table_name, index_name, index_type, uniqueness

240

CHAPTER 10 ■ DATA DICTIONARY BASICS

from user_indexes&&conn1 order by 1, 2;

prompt Table columns db1 NOT IN db2

select table_name, column_name

from user_tab_columns&&conn1

minus

select table_name, column_name

from user_tab_columns&&conn2 order by 1,2;

prompt Table columns in db2 NOT IN db1

select table_name, column_name

from user_tab_columns&&conn2

minus

select table_name, column_name

from user_tab_columns&&conn1 order by 1,2;

spo off;

This script is just a sample of what you can do with data-dictionary views to report on metadata differences between schemas. The script doesn’t include every possible type of check. Rather, it includes enough to give you an example of how to find the most common types of differences that developers and DBAs look for. A full version of this script is available in the source code section of the Apress website (www.apress.com).

If you have access to a tool such as Enterprise Manager Change Management Pack, then you can use it to display differences between two schemas. A quick Google search shows dozens of tools available for comparing schemas. The purpose of the examples in this section isn’t to compete with these tools, but to show that you can quickly create a set of SQL statements that display schema differences. You can easily augment and enhance these statements as required for your environment.

Summary

Sometimes you're handed an old database that has been running for years, and it’s up to you to manage and maintain it. In some scenarios, you aren’t given any documentation regarding the users and objects in the database. Even if you’re provided with documentation, it may not be accurate or up to date. In this case, the data dictionary quickly becomes your source of documentation. You can use it to extract user information, the physical structure of the database, security information, objects and owners, currently connected users, and so forth.

Oracle provides static and dynamic views in the data dictionary. The static views contain information about the objects in the database. You can use these views to determine which tables are consuming the most space, contain the most rows, have the most extents allocated, and so on. The dynamic-performance views provide a real-time window into events currently transacting in the database. These views provide information about currently connected users, SQL executing, where resources are being consumed, and so on. DBAs use these views extensively to monitor and troubleshoot performance issues.

The book now turns its attention toward specialized Oracle features such as large objects, partitioning, Data Pump, and external tables. These topics are covered in the next several chapters.

241

CHAPTER 11 ■ LARGE OBJECTS

replication, or disaster-recovery mechanisms. BFILEs are more appropriate for large binary files that are read-only and don’t change while an application is running. For example, you may have large binary video files that are referenced by a database application. In this scenario, the business determines that you don’t need to create and maintain a 500TB database when all the application really needs is a pointer (stored in the database) to the locations of the large files on disk.

Table 11–1.
Oracle Large Object Data Types

Data Type

Description

Maximum Size

LONG

Don’t create tables with LONG data types This

2GB

data type is supported for backward

compatibility. Use a CLOB or an NCLOB

instead.

LONG RAW

Don’t create tables with LONG RAW columns.

2GB

This data type is supported for backward

compatibility. Use a BLOB instead.

CLOB

Character large object for storing character

(4GB – 1)* blocksize

documents such as big text files, log files,

XML files, and so on.

NCLOB

National character large object. Stores data

(4GB – 1) * blocksize

in national character set format. Supports

characters with varying width.

BLOB

Binary large object for storing unstructured

(4GB – 1) * blocksize

bitstream data (images, video, and so on).

BFILE

Binary file large object stored on the

2^64 – 1 bytes (operating system

filesystem outside of the database. BFILEs

may impose a size limit that is less

are read-only.

than this)

Illustrating LOB Locators, Indexes, and Chunks

Internal LOBs (CLOB, NCLOB, and BLOB) store data in pieces called
chunks
. A chunk is the smallest unit of allocation for a LOB and is made up of one or more database blocks. A LOB
locator
is stored in a row that contains a LOB column. The LOB locator points to a LOB
index
. The LOB index stores information regarding the LOB chunks. When a table is queried, the database uses the LOB locator and associated LOB index to locate the appropriate LOB chunks. Figure 11–1 shows the relationship between a table, a row, a LOB locator, and its associated index and chunks.

244

CHAPTER 11 ■ LARGE OBJECTS

■
Note
The DBMS_LOB package performs operations on LOBs through the LOB locator.

Distinguishing Between BasicFiles and SecureFiles

Several significant improvements were made to LOBs in Oracle Database 11
g
. Oracle now distinguishes between two different types of underlying LOB architectures:

• BasicFile

• SecureFile

SecureFile is a new LOB architecture introduced in Oracle Database 11
g
. The SecureFile architecture has many new enhancements that improve the manageability and performance of LOBs. If you’re using Oracle Database 11
g
or higher, then you should create your LOB columns with the SECUREFILE clause. Be aware that the SecureFile feature itself doesn’t require an additional license.

However, some of the SecureFile advanced features do require additional licenses (encryption, deduplication, and compression).

If you’re not using Oracle Database 11
g
, then your only option is to use the BasicFile architecture.

This is the default type of LOB created, and it’s been available since Oracle version 8.

BasicFile

BasicFile is the name Oracle gives to the LOB architecture available prior to Oracle Database 11
g
. It’s still important to understand the BasicFile LOBs because many shops use Oracle versions that don’t support SecureFiles. You don’t need to do anything special to enable the use of BasicFile LOBs; this is the default LOB architecture that is implemented when you create a table with LOB columns.

SecureFile

If you’re using Oracle Database 11
g
or higher, then you have the option of using the SecureFile LOB

architecture. It includes the following enhancements (over BasicFile LOBs):

•

•

•

SecureFile encryption lets you transparently encrypt LOB data (just like other data types). The compression feature allows for significant space savings. The deduplication feature eliminates duplicate LOBs that otherwise would be stored multiple times.

Prerequisites for SecureFiles

You need to do a small amount of planning before using SecureFiles. Specifically, use of SecureFiles requires the following:

246

CHAPTER 11 ■ LARGE OBJECTS

• A SecureFile LOB must be stored in a tablespace using the automated segment space management feature (ASSM).

• The DB_SECUREFILE initialization setting must be either PERMITTED or ALWAYS.

A SecureFile LOB must be created within a tablespace using ASSM. To create an ASSM-enabled tablespace, specify the SEGMENT SPACE MANAGEMENT AUTO clause. For example: create tablespace inv_mgmt_data

datafile '/ora01/dbfile/O11R2/inv_mgmt_data01.dbf'

size 1000m

extent management local

uniform size 1m

segment space management auto;

If you have existing tablespaces, you can verify the use of ASSM by querying the DBA_TABLESPACES

view. The SEGMENT_SPACE_MANAGEMENT column should have a value of AUTO for any tablespaces that you want to use with SecureFiles:

select

tablespace_name

,segment_space_management

from dba_tablespaces;

Here’s a snippet of the output indicating that the USER1 tablespace is using ASSM: TABLESPACE_NAME SEGMEN

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

USER1 AUTO

Also, SecureFiles require that the DB_SECUREFILE initialization setting is either PERMITTED or ALWAYS.

The default value is PERMITTED. You can verify the value as follows:

SQL> show parameter db_securefile

NAME TYPE VALUE

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

db_securefile string PERMITTED

You can use either ALTER SYSTEM or ALTER SESSION to modify the value of DB_SECUREFILE. Table 11–2

describes the valid values for DB_SECUREFILE.

Table 11–2.
Description of DB_SECUREFILE Settings

DB_SECUREFILE
Setting

Description

NEVER

The LOB is created as a BasicFile regardless of whether the SECUREFILE

option is specified.

PERMITTED

SecureFile LOBs can be created.