Pro Oracle Database 11g Administration (41 page)

Read Pro Oracle Database 11g Administration Online

Authors: Darl Kuhn

Tags: #Oracle DBA

BOOK: Pro Oracle Database 11g Administration
12.91Mb size Format: txt, pdf, ePub

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('','',[length])

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 that contains the SQL statements that select row counts from all tables in the specified schema. If the username you provide to the script is INVUSER, then you can run the generated script as follows.

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;

Other books

Castaway Dreams by Darlene Marshall
The Credulity Nexus by Graham Storrs
That Boy by Jillian Dodd
Sirenas by Amanda Hocking
Until the End of Time by Schuster, Melanie
Shear Murder by Cohen, Nancy J.
Promises by Ellen March
The Floodgate by Cunningham, Elaine
The Suitcase by Sergei Dovlatov