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.