Pro Oracle Database 11g Administration (41 page)
CHAPTER 10 ■ DATA DICTIONARY BASICS
Table 10–1 describes some of the views used to report on database physical-space management.
This isn’t an exhaustive list; rather, this table contains the most commonly used views for monitoring database space.
Table 10–1.
Overview of Database Space-Management Views
Data-Dictionary View
Purpose
V$DATABASE
Information about the database in the control file
DBA/ALL/USER_USERS
User account information
DBA/USER_TABLESPACES
Tablespace descriptions
DBA_DATA_FILES
Database datafile descriptions
DBA/USER_FREE_SPACE
Free extents in tablespaces
V$DATAFILE
Datafile information from the control file
V$DATAFILE_HEADER
Datafile information derived from the datafile header
DBA/ALL/USER_TABLES
Table attribute descriptions
DBA/ALL/USER_INDEXES
Index attribute descriptions
DBA/USER_SEGMENTS
Storage data for segments
DBA/ALL/USER_PART_TABLES
Partitioned table data
DBA/ALL/USER_PART_INDEXES
Partitioned index data
DBA/ALL/USER_TAB_PARTITIONS
Storage information for partitioned tables
DBA/ALL/USER_IND_PARTITIONS
Storage information for partitioned indexes
DBA/USER_EXTENTS
Extent information of each segment
V$CONTROLFILE
Names and size of control files
V$LOG
Online redo-log file information in the control file
V$LOG_HISTORY
Online redo-log file history information in control file
V$ARCHIVED_LOG
Archive log-file information in the control file
This chapter doesn’t contain an exhaustive set of SQL scripts for querying data-dictionary objects.
Rather, it covers basic techniques for querying the data dictionary about topics covered up to this point 219
CHAPTER 10 ■ DATA DICTIONARY BASICS
in the book (for example, users, tables, indexes, and so on). You should be able to build on the concepts in this chapter to fulfill any requirement you have for viewing data-dictionary metadata.
Displaying User Information
You may find yourself in an environment that contains hundreds of databases located on dozens of different servers. In such a scenario, you want to ensure that you don’t run the wrong commands in the incorrect database. When performing DBA tasks, it’s prudent to verify that you’re connected as the appropriate account and to the correct database.
Currently Connected User
You can run the following types of SQL commands to verify the currently connected user and database information:
SQL> show user;
SQL> select * from user_users;
SQL> select name from v$database;
SQL> select instance_name, host_name from v$instance;
As shown in Chapter 3, an efficient way of staying aware of your environment is to set your SQL*Plus prompt automatically via the login.sql script to display user and instance information. This example manually sets the SQL prompt:
SQL> set sqlprompt '&_USER.@&_CONNECT_IDENTIFIER.> '
Here’s what the SQL prompt now looks like:
SYS@O11R2>
You can also use the SYS_CONTEXT built-in SQL function to display a wide variety of details about your currently connected session. The general syntax for this function is as follows: SYS_CONTEXT('
This example displays the user, authentication method, host, and instance: select
sys_context('USERENV','CURRENT_USER') usr
,sys_context('USERENV','AUTHENTICATION_METHOD') auth_mth
,sys_context('USERENV','HOST') host
,sys_context('USERENV','INSTANCE_NAME') inst
from dual;
USERENV is a built-in Oracle namespace. More than 50 parameters are available when you use the USERENV namespace with the SYS_CONTEXT function. Table 10–2 describes some of the more useful parameters. Refer to the Oracle SQL Reference guide for a complete list of parameters available.
220
CHAPTER 10 ■ DATA DICTIONARY BASICS
Table 10–2.
Useful USERENV Parameters Available with SYS_CONTEXT
Parameter Name
Description
AUTHENTICATED_IDENTITY
Identity used in authentication
AUTHENTICATION_METHOD
Method of authentication
CURRENT_USER
Username for the currently active session
DB_NAME
Name specified by the DB_NAME initialization parameter
DB_UNIQUE_NAME
Name specified by the DB_UNIQUE_NAME initialization parameter
HOST
Hostname for the machine where the client initiated the database
connection
INSTANCE_NAME
Instance name
IP_ADDRESS
IP address of the machine where the client initiated the database
connection
ISDBA TRUE if the user authenticated with DBA privileges through the operating system or password file
NLS_DATE_FORMAT
Date format for the session
OS_USER
Operating-system user from the machine where the client initiated the database connection
SERVER_HOST
Hostname of the machine where the database instance is running
SERVICE_NAME
Service name for the connection
SID
Session identifier
TERMINAL
Operating-system identifier for the client terminal
Users Currently Logged In
When you’re debugging performance or connectivity issues, it’s useful to view which users are connected to the database and the number of connections per user. If you want to view dynamic information such as users currently logged on to your database, as a user assigned the SELECT_CATALOG_ROLE, execute the following query:
221
CHAPTER 10 ■ DATA DICTIONARY BASICS
select
count(*)
,username
from v$session
group by username;
In Oracle Database 11
g
, the V$SESSION view has nearly 100 columns. Other columns that are commonly queried are OSUSER, SQL_ID, PROCESS, MACHINE, PORT, TERMINAL, and PROGRAM. See the
Oracle Database Reference
guide (available on Oracle’s OTN website) for a complete list of columns and their descriptions.
Currently Executing SQL
If you want to view SQL statements that currently connected users are running, issue this query: select
a.sid
,a.username
,b.sql_text
from v$session a
,v$sqltext_with_newlines b
where a.sql_id = b.sql_id
order by
a.username
,a.sid
,b.piece;
If you’re using an Oracle Database 9
i
or earlier, the previous query won’t work because the SQL_ID
column isn’t available. Here’s a query that works for older versions of Oracle: select
a.sid
,a.username
,b.sql_text
from v$session a
,v$sqltext_with_newlines b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
order by
a.username
,a.sid
,b.piece;
■
Tip
V$SQLTEXT_WITH_NEWLINES is identical to V$SQLTEXT with the exception that V$SQLTEXT_WITH_NEWLINES
doesn’t replace tabs and newlines with spaces.
222
CHAPTER 10 ■ DATA DICTIONARY BASICS
Viewing Accessible Tables
Sometimes, when you’re troubleshooting table-accessibility issues, the first thing to check is which tables you have access to. You can query the USER_TABLES view to display tables owned by the currently connected user:
select
a.table_name
,b.created
,b.last_ddl_time
,a.last_analyzed
from user_tables a, user_objects b
where a.table_name = b.object_name;
To view all tables to which your currently connected user has access—for example, via GRANT
statements issued by other owners—use the ALL_TABLES view:
select
table_name
,tablespace_name
from all_tables;
Querying the USER_TABLES view is a quick way to determine which tables exist in your current account, whereas the ALL_TABLES view contains every table to which you have any type of DML (SELECT, INSERT, UPDATE, and/or DELETE) access. If you have access to the DBA_TABLES view, you can also query the tables a user has access to via the following query:
select
table_name
from dba_tables
where owner = upper('&owner');
When you’re troubleshooting, you can check columns like CREATED and LAST_DDL_TIME, which tell when the structure of a table was last modified. Use the following query to view this information: select
a.table_name
,b.created
,b.last_ddl_time
,a.last_analyzed
from dba_tables a
,dba_objects b
where a.table_name = b.object_name
and a.owner = upper('&owner');
Displaying Object Disk-Space Usage
When you’re diagnosing database space issues, it’s handy to view how much space a user’s tables and indexes are consuming. The next query is useful when you want to view the space consumption of objects for a user:
UNDEFINE owner
COL summer FORM 999,999.999
SET LINES 132 TRIMSPOOL ON PAGES 100
SPO space.txt
224
CHAPTER 10 ■ DATA DICTIONARY BASICS
SELECT
segment_name
,partition_name
,tablespace_name
,segment_type
,SUM(bytes)/1024/1024 summer
FROM dba_extents
WHERE owner = UPPER('&&owner')
GROUP BY segment_name,partition_name,tablespace_name,segment_type
ORDER BY segment_name,partition_name;
SPO OFF;
This script prompts you for an object owner. If the table has partitions, the space per partition is displayed. You need access to DBA-level views to run the script. You can modify the script to point at the ALL or USER-level views to report on objects for the currently connected user account. This query also uses SQL*Plus-specific commands, such as setting the line size and column formatting, which are necessary to make the output readable.
Displaying Table Row Counts
When you’re investigating performance or space issues, it’s useful to display each table’s row count. Run the following SQL code as a DBA-privileged schema. Notice that this script contains SQL*Plus-specific commands such as UNDEFINE and SPOOL. The script prompts you each time for a username: UNDEFINE user
SPOOL tabcount_&&user..sql
SET LINESIZE 132 PAGESIZE 0 TRIMSPO OFF VERIFY OFF FEED OFF TERM OFF
SELECT
'SELECT RPAD(' || '''' || table_name || '''' ||',30)'
|| ',' || ' COUNT(*) FROM &&user..' || table_name || ';'
FROM dba_tables
WHERE owner = UPPER('&&user')
ORDER BY 1;
SPO OFF;
SET TERM ON
@@tabcount_&&user..sql
SET VERIFY ON FEED ON
This code generates a file named tabcount_
SQL> @tabcount_invuser.sql
Keep in mind that if the table row counts are high, then this script can take a long time to run (several minutes).
Developers and DBAs often use SQL to generate SQL statements. This is a useful technique when you need to apply the same SQL process (repetitively) to many different objects, such as all tables in a schema..If you don’t have access to DBA-level views, you can query the USER_TABLES view. For example: SPO tabcount.sql
SET LINESIZE 132 PAGESIZE 0 TRIMSPO OFF VERIFY OFF FEED OFF TERM OFF
SELECT
'SELECT RPAD(' || '''' || table_name || '''' ||',30)'
|| ',' || ' COUNT(*) FROM ' || table_name || ';'
225
CHAPTER 10 ■ DATA DICTIONARY BASICS
||' PARTITION ( '||r1.partition_name ||' )';
EXECUTE IMMEDIATE sql_stmt INTO counter;