Tuesday, June 10, 2008

CHANGING DATABASE CHARACTER SET

This article describes how one can change the character set of a database.
It should be done with extreme caution having noted the following
limitations.

The database character set should only be changed if the characters of the
code page of the originating database correspond to the same characters
of the target database, ie. if the database was created with the
characterset US7ASCII and it is to be updated to WE8ISO8859P1. Since these
have the same encoding scheme for the first 127 bits, changing the
character set from US7ASCII to WE8ISO8859P1 will display all characters up
to 127 bits as the same character before and after. In addition, in this
particular example, if any characters have been entered with the 8th bit
set, then updating the database characterset to 8 bit will allow that 8th
bit to be displayed. You must not change the characterset from one encoding
scheme to another encoding scheme where the code pages do not correspond.
This will completely scramble your database. In addition, if case*designer
diagrams are stored in the database, this method must not be used. Contact
Worldwide Support for further details.

Before attempting to run any of the scripts below, you must take a full
cold backup of your database. In addition, the procedure must be thoroughly
tested before attempting this on a production instance.

Here's a SQL*PLUS script that allows a database's character set to be
changed to a different encoding scheme without having to rebuild the database.
set echo off
set verify off

rem The data dictionary table that records the database
rem character set is sys.props$
rem
rem SQL> describe sys.props$
rem Name Null? Type
rem ------------------------------- -------- ----
rem NAME NOT NULL VARCHAR2(30)
rem VALUE$ VARCHAR2(2000)
rem COMMENT$ VARCHAR2(2000)

rem For example:
rem
rem SQL> column c1 format a30
rem SQL> select name c1, value$ c1 from sys.props$;

rem C1 C1
rem ------------------------------ ------------------------------
rem DICT.BASE 2
rem NLS_LANGUAGE AMERICAN
rem NLS_TERRITORY AMERICA
rem NLS_CURRENCY $
rem NLS_ISO_CURRENCY AMERICA
rem NLS_NUMERIC_CHARACTERS .,
rem NLS_DATE_FORMAT DD-MON-YY
rem NLS_DATE_LANGUAGE AMERICAN
rem NLS_CHARACTERSET WE8DEC
rem NLS_SORT BINARY
rem GLOBAL_DB_NAME NLSV7.WORLD

rem NLS_CHARACTERSET can be changed by updating its value, for example:

rem update sys.props$
rem set value$ = 'WE8ISO8859P1'
rem Where name = 'NLS_CHARACTERSET';

rem The database has to be shutdown and restarted before the change
rem becomes effective.

rem It is very important to specify the character set name correctly.
rem IMPORTANT NOTE
rem =============
rem If NLS_CHARACTERSET is updated to an invalid value, it will not then
rem be possible to restart the database once it has been shutdown.
rem To recover, it will be necessary to re-create the database, since it
rem cannot be restarted to correct the invalid NLS_CHARACTERSET entry.

rem The character set name should be in uppercase.
rem The new value is not effective until the database has been shutdown and
rem restarted.
rem
rem A suggested procedure is as follows, and can be done by running this
rem script from SQL*Plus when logged into the SYSTEM account.
rem
rem USAGE : SQL> start ch_db.sql
rem
rem where is the desired database character set
rem

Prompt First check that the character set name is valid.

set echo on

select convert('a','&1','us7ascii') from dual;

set echo off

prompt If this select statement returns error ORA-01482, then the
prompt specified character set name is not valid for this installation.
prompt Abort the procedure now with Control-c

prompt To continue, press return
accept ans CHAR

Prompt Check the current value of database character set.

column c1 format a30
select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

prompt To continue, press return

Prompt Update to new character set

update sys.props$
set value$ = upper('&1')
where name = 'NLS_CHARACTERSET';

set echo off

prompt To continue, press return
accept ans CHAR

Prompt Check the new value of database character set

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

Prompt If the value is updated as required, press return to continue and
Prompt then manually type COMMIT; to commit the change. Then shutdown and
Prompt restart the database.
Prompt
Prompt If the value is not updated as required, press return to continue and
Prompt than manually type ROLLBACK; to prevent the change.

prompt To continue, press return
accept ans CHAR
Posted by at No comments:

Wednesday, June 4, 2008

Setting Listener Password

One of the biggest loophole that an Oracle database installation can have is Oracle Listener without a password. At the time of client server this fact somehow can be overlooked as we know who our user are, but now in days of web we don’t them so its becomes very important that we secure Oracle Listener.

Setting the password for the listener can be done in following three ways:

1. Editing the listener.ora file and setting the password in it.
2. Using LSNRCTL utility.
3. Through Oracle Graphical tools such as Net Manager, Oracle Enterprise Manager and so on.

In this blog entry I will mainly concentrate on first two.

Under first method we can edit the listener.ora file and add the following line in it.

PASSWORDS_LISTENER = oraclebrains

and then restart the listener. But the drawback with this method is that password is stored in plain text format without encryption.

In Second method, we can run LSNRCTL utility and then can give command as follows:

LSNRCTL>change_password

After it it will prompt your for old password, if it is there you can type in or press enter.

Then it will prompt you for the new password which you want to set and then press enter.

Then it will again prompt you to reenter the new password for confirmation and then press enter.

After this password will be changes for running instance or session of the listener.

If we want it applicable for all the future instance or session we need to save the configuration for future use as follows:

LSNRCTL> set password

LSNRCTL> save_config

One these steps are completed, if we open listener.ora file we will notice that same line as we add in first method is added but password is in encrypted format.

PASSWORDS_LISTENER = 56ETERY34373WWEGW27362
Posted by at No comments:

Understanding Asynchronous COMMIT

Before Oracle 10g Release 2, Life cycle of a transaction was as follows:

- Transaction Starts When User Issues the First DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- While user is issuing DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- Transaction Ends When User Issues a COMMIT (can be explicit or implicit), Oracle immediately writes this buffered redo to disk.

Main point to understand here is that once COMMIT is issued, Oracle does not return the control to the user until the redo entries corresponding to that commited transaction have been physically written to disk.

This way application can be 100% sure that once the transaction is commited, it is protected against instance failure and commited data can be recovered by “rolling forward” log files.

This is another words we can say is synchronous COMMIT.

Now Asynchronous COMMIT:

In Oracle 10g Release 2, keeping in view a very high speed transaction environment, Oracle has introduce two options:

1. One is to return immediately after the COMMIT is issued, rather than waiting for the log activity to complete.
2. Another option batches multiple transactions together in memory before writing to the disk.

The full syntax of the new WRITE clause is:

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]

By default, if no WRITE clause is specified, a normal COMMIT is equivalent to

COMMIT WRITE IMMEDIATE WAIT;

The COMMIT statement has a new clause, WRITE, which indicates these options. The default COMMIT statement is the same as:

COMMIT WRITE IMMEDIATE WAIT;

To specify that the transaction should be written individually but that the application should not wait for it, specify:

COMMIT WRITE IMMEDIATE NOWAIT;

or just

COMMIT WRITE NOWAIT;

Likewise, to specify that Oracle’s log writer process (LGWR) is allowed to batch multiple transactions together before writing, specify:

COMMIT WRITE BATCH NOWAIT;

The option can also be set system-wide by modifying the commit_write instance parameter. Multiple options are separated by commas, as:

ALTER SYSTEM SET commit_write = BATCH, NOWAIT;
Posted by at No comments:

Enabling Faster Incremental Backups in Oracle 10g

In this blog entry let me introduce you to a new capability in Oracle 10g called Block Change Tracking, which can help DBA’s do faster incremental backups via RMAN (Recovery Manager).

Once this new capability in Oracle 10g is enabled it start recording the modified since last backup and stores the log of it in a block change tracking file.

Later while doing backup RMAN uses this file to determine which blocks are needed to be backed up?

Logically as we can see, this process improves the performance as RMAN does not have to scan whole datafile to detect which block in it has changed and needs backup.

This may not show any significant impact on smaller database but definitely will have big impact on bigger databases as now with this new functionality it does not have to scan full datafile but only need to query block change tracking file.

To create the Change Tracking file and enable Change Tracking, make sure the database is either open or mounted. Then issue following SQL:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING
FILE ‘c:\oracle\oradata\rkt\block_track\block_track_file01.log’;

Database altered.

Once you issue above SQL, Oracle creates a new background process which is responsible for writing data to the block change tracking file, which is called the block change writer CTRW.

To verify the status of block change tracking, use following SQL:

SQL> SELECT filename, status, bytes FROM v$block_change_tracking;

FILENAME

——————————————————

STATUS BYTE

————— ————–

C:\ORACLE\ORADATA\RKT\BLOCK_TRACK\BLOCK_TRACK_FILE01.LOG

ENABLED 1024

To disable block change tracking, use following SQL:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Posted in Articles:: Oracle Backup, Blog::Oracle Backup | No Comments »

creating UNIQUE index on a table with existing non unique values.
Monday, May 7th, 2007
Today I came up with this challenge of creating UNIQUE index on a table with exiting non unique value. After lot of r&d I came up with following very interested solution:

I am using scott schema to show my solution.

SQL> create table test as select deptno from emp;

Table created.

SQL> select deptno from test;

DEPTNO
———-
20
30
30
20
30
30
10
20
10
30
20

DEPTNO
———-
30
20
10

14 rows selected.

SQL> alter table test add constraint unique_index unique(deptno) disable;

Table altered.

SQL> create index unique_index on test(deptno);

Index created.

SQL> alter table test enable novalidate constraint unique_index;

Table altered.

SQL> insert into test values (20);
insert into test values (20)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UNIQUE_INDEX) violated
Posted by at No comments:

Oracle Histograms

About Oracle Histograms

Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order.

Most Oracle experts only recommend scheduled re-analysis for highly dynamic databases, and most shops save one very-deep sample (with histograms), storing the statistic with the dbms_stats.export_schema_stats procedure. The only exceptions are highly-volatile systems (i.e. lab research systems) where a table is huge one-day and small the next.

For periodic re-analysis, many shops us the table "monitoring" option and also method_opt "auto" after they are confident that all histograms are in-place.

Oracle histograms statistics can be created when you have a highly skewed index, where some values have a disproportional number of rows. In the real world, this is quite rare, and one of the most common mistakes with the CBO is the unnecessary introduction of histograms in the CBO statistics. As a general rule, histograms are used when a column's values warrant a change to the execution plan.

If you need to reanalyze your statistics, the reanalyze task will be less resource intensive with the repeat option. Using the repeat option will only reanalyze indexes with existing histograms, and will not search for other histograms opportunities. This is the way that you will reanalyze you statistics on a regular basis.

--**************************************************************
-- REPEAT OPTION - Only re-analyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only re-analyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
/

Find histograms for foreign key columns - Many DBAs forget that the CBO must have foreign-key histograms in order to determine the optimal table join order (i.e. the ORDERED hint).

Fix the cause, not the symptom - For example, whenever I see a sub-optimal order for table joins, I resist the temptation to add the ORDERED hint, and instead create histograms on the foreign keys of the join to force the CBO to make the best decision.

For new features, explore the Oracle10g automatic histograms collection mechanism that interrogates v$sql_plan to see where the foreign keys are used. It claims to generate histograms when appropriate, all automatically.

This is one reason that the ORDERED hint is so popular, but it has been shown that having liberal column histograms on the table columns can often aid the optimizer in making better execution plans.

In sum, histograms are not just for non-unique column values that are unevenly distributed (skewed), and several noted DBA’s have suggested that more liberal use of histograms will aid the CBO is making better decisions. The dbms_stats “auto” feature detects and builds column histograms, but it has the shortcoming of being too conservative in some cases.

Savvy DBA’s are now experimenting with broad-brush histograms, for all indexes columns. I first heard of this technique from Jeff Maresh (noted data warehouse consultant), who told me that he has taken to creating 10-bucket histograms for all data warehouse table columns. I heard this advice again at the IOUG conference from Arup Nanda (noted author and DBA of the year) and from Mike Ault.

They are abandoning the use of the “auto” option and manually creating 20-bucket histograms across-the-board, and they claim that it can make a huge difference for databases with lots of multi-table joins in he SQL.

I’ve not tried this technique yet, but when three experts make the assertion, I believe that there may be something to the new technique. The only downside, of course, is the time required to gather the column histograms and a small amount of additional storage in the data dictionary.

One exciting feature of dbms_stats is the ability to automatically look for columns that should have histograms, and create the histograms. Multi-bucket histograms add a huge parsing overhead to SQL statements, and histograms should ONLY be used when the SQL will choose a different execution plan based upon the column value.
Posted by at No comments:

Oracle's V$ Views

v$archive_dest
Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest These values correspond to the init parameter log_archive_dest_n.
v$archive_dest_status
This view allows to find status and errors for each of the defined
v$archived_log
Displays successfully archived redo logs. shows received logs on a primary standby database.
v$archive_gap
Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).
v$archive_processes
This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.
v$controlfile
Displays the location and status of each controlfile in the database.
v$controlfile_record_section
See sections in a controlfile.
v$bh
This dynamic view has an entry for each block in the database buffer cache. The column status can be:
free
This block is not in use
xcur
Block held exclusively by this instance
scur
Block held in cache, shared with other instance
cr
Block for consistent read
read
Block being read from disk
mrec
Block in media recovery mode
irec
Block in instance (crash) recovery mode

v$buffer_pool
See buffer pools. This view's column id can be joined with x$kcbwds.indx See also x$kcbwbpd
v$buffer_pool_statistics
v$database
This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
ADPDB>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
checkpoint_change# records the SCN of the last checkpoint. switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases. Can be:
NOT ALLOWED,
SESSIONS ACTIVE,
SWITCHOVER PENDING,
SWITCHOVER LATENT,
TO PRIMARY,
TO STANDBY or
RECOVERY NEEDED.
See protection modes in data guard for the columns protection_mode and protection_level. database_role determines if a database is a primary or a logical standby database or a physical standby database. force_logging tells if a database is in force logging mode or not.
v$datafile
This view contains an entry for each datafile of the database. This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile
v$datafile_header
Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint was:
select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header
v$dataguard_status
Shows error messages in a data guard environment.
v$db_object_cache
This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.
v$enqueue_stat
If there are a lot of enqueue waits "in" v$session_event or v$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets, waits, failures and the cumulative sum of waited time can be found.
For a list of enqueue types, refer to enqueue types in x$ksqst. The column cum_wait_time stems from x$ksqst.ksqstwtim.
v$eventmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$event_name
Contains a record for each wait event.
v$filemetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$filestat
v$fixed_table
This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables:
ORA81> select count(*) from v where name like 'V$%';

COUNT(*)
----------
185
If you want to know, which x$ tables there are, do a select name from v$fixed_table where name like 'X$%';
v$fixed_view_definition
Contains the defintion in its attribute view_definition for the views of v$fixed_table.
v$flash_recovery_area_usage
See also v$recovery_file_dest
v$instance
instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment. dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance.
v$latch
Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleeps indicates how many times a willing to wait process slept. Waiters_woken tells how often a sleeping process was 'disturbed'.
v$librarycache
v$lock
This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the session holding or aquiring the lock), type, and the lmode/request pair. Important possible values of type are TM (DML or Table Lock), TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction). Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1. The possible values for lmode and request are:
1: null,
2: Row Share (SS),
3: Row Exclusive (SX),
4: Share (S),
5: Share Row Exclusive (SSX) and
6: Exclusive(X)
If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1 A lock type of JI indicates that a materialized view is being refreshed. A more detailed example can be found here See also x$kgllk.
v$locked_object
Who is locking what:
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
v$log
Contains information on each log group. See also online redo log. Comman values for the status column are:
UNUSED:
Oracle8 has never written to this group,
CURRENT:
This is the active group.
ACTIVE:
Oracle has written to this log before, it is needed for instance recovery.
The active log is the one with the current log sequence number
INACTIVE:
Oracle has written to this log before; it is not needed for instance recovery.

v$logfile
This view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2
v$logmnr_contents
See dbms_logmnr.
v$log_history
This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry??? On physical standby databases, this view shows applied logs.
v$logstdby
Can be used to verify that archived redo logs are being applied to standby databases.
v$managed_standby
Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service. see here
select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby;
client_process: the corresponding primary database process. If lgwr log transmission is chosen, one row should have client_process=LGWR. If ARCH transmission is chosen, one row should have ARCH.
v$mystat
This view records statistical data about the session that accesses it. Join statistic# with v$statname.
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates. See also recording statistics with oracle.
v$nls_parameters
The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values.
v$nls_valid_values
This view can be used to obtain valid values for NLS parameters such as
supported character sets
languages
territories
sorting orders

v$object_usage
v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage. See On verifying if an index is used.
v$open_cursor
v$option
This view lets you see which options are installed in the server. See also dba_registry.
v$parameter
Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is:
select value from v$parameter where name = 'db_block_size'
The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using alter session or at system level using alter system. A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A parameter is modifiable at system level if issys_modifiable = 'DEFERRED' or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at system level if issys_modifiable = 'DEFERRED' it only affects sessions that are started after chaning the parameter. Additionally, the alter system set ... deferred option must be used. There are also some undocumented (or hidden?) parameters.
v$pgastat
See also pga. Thanks to Oleg who notified me of a typo (v$pgastat instead of v$pga_stat).
v$process
Join v$process's addr with v$session paddr. The column traceid is equal to the value used in alter session set .
v$pwfile_users
Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file.
v$recover_file
Useful to find out which datafiles need recovery. Join with v$datafile to see filenames instead of numbers....
v$recovery_file_dest
See also v$flash_recovery_area_usage
v$reserved_words
This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user). Until 9i (is this correct?), the view only consist of two columns: keyword and length. From 10g onwards (?), it has also the columns reserved, res_type, res_attr, res_semi and duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no) See also reserved words in SQL and reserved words in PL/SQL.
v$resource_limit
v$rollname
The names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field. v$transaction can be used to track undo by session.
v$rollstat
Statistics for rollback segements
v$session
The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used. The fields module and action of v$session can be set with dbms_application_info.set_module. (See v$session_longops for an example. The field client_info can be set with dbms_application_info.set_client_info Join sid with v$sesstat if you want to get some statistical information for a particular sesssion. A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session). A client can set some information in client_info. For example, RMAN related sessions can be found with
.... where client_info like 'rman%';
What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well. See also sessions.
v$sessmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$session_event
This views is similar to v$system_event. However, it breaks it down to currently connected sessions. v$session_event has also the column max_wait that shows the maximum time waited for a wait event.
v$session_longops
Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded. If the following Procedure is run, it will report its progress in v$session_longops. The Procedure will also set the module attribute in v$session which makes it possible to find the sid and serial# of the session.
create table f(g number);

create or replace procedure long_proc as
rindex pls_integer := dbms_application_info.set_session_longops_nohint;
slno pls_integer;
-- Name of task
op_name varchar2(64) := 'long_proc';

target pls_integer := 0; -- ie. The object being worked on
context pls_integer; -- Any info
sofar number; -- how far proceeded
totalwork number := 1000000; -- finished when sofar=totalwork

-- desc of target
target_desc varchar2(32) := 'A long running procedure';

units varchar2(32) := 'inserts'; -- unit of sofar and totalwork
begin

dbms_application_info.set_module('long_proc',null);

dbms_application_info.set_session_longops (
rindex,
slno);

for sofar in 0..totalwork loop

insert into f values (sofar);

if mod(sofar,1000) = 0 then
dbms_application_info.set_session_longops (
rindex,
slno,
op_name,
target,
context,
sofar,
totalwork,
target_desc,
units);

end if;

end loop;
end long_proc;
If the procedure long_proc is run, you can issue the following query to get feedback on its progress:
select time_remaining,sofar,elapsed_seconds
from v$session_longops l, v$session s
where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc'
v$session_wait
This views shows what wait event each session is waiting for, or what the last event was that it waited for.
In contrast, v$session_event lists the cumulative history of events waited for in a session. The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well. Since 10g, Oracle displays the v$session_wait information also in the v$session view.
v$session_wait_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sesstat
This view is similar to v$mystat except that it shows cumulated statistics for all sessions. Join sid with v$session and join statistic# with v$statname. v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
v$sga
Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sga in sql plus with the exeption that the latter also show the total.
v$sgastat
Showing free space in the sga:
select * from v$sgastat where name = 'free memory'
v$sga_dynamic_components
Information about SGA resize operations since startup. This view can also be used to find out the granule size of SGA components.
v$sga_resize_ops
v$sort_usage
See temporary tablespaces
v$sort_segment
See Temporary Tablespaces
v$spparameter
Returns the values for the spfile.
v$sql
v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here.
v$sqlarea
Join v$sqlarea's address with v$session's sql_address. Find the SQL-text of currently running SQL statements:
select sql_text from v$sqlarea where users_executing > 0;
The field version_count indicates how many versions an sql statement has.
v$sqltext
v$sql_plan
variable addr varchar2(20)
variable hash number
variable child number

exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;

select lpad(' ', 2*(level-1))||operation||' '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from v$sql_plan
start with (address = :addr
and hash_value = :hash
and child_number = :child
and id=0 )
connect by prior id = parent_id
and prior address = address
and prior hash_value = hash_value
and prior child_number = child_number
order by id, position ;
In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used:
select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
v$sql_text_with_newlines
This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that:
set serveroutput on size 1000000

declare
v_stmt varchar2(16000);
v_sql_text v$sqltext_with_newlines.sql_text%type;
v_sid v$session.sid%type;
begin
for r in (
select
sql_text,s.sid
from
v$sqltext_with_newlines t,
v$session s
where
s.sql_address=t.address
order by s.sid, piece) loop

v_sid := nvl(v_sid,r.sid);

if v_sid <> r.sid then
dbms_output.put_line(v_sid);
put_line(v_stmt,100);
v_sid := r.sid;
v_stmt := r.sql_text;
else
v_stmt := v_stmt || r.sql_text;
end if;

end loop;
dbms_output.put_line(v_sid);
dbms_output.put_line(v_stmt,100);

end;
/
Thanks to Sarmad Zafar who notified me of an error in this PL/SQL Block. Note: the function put_line is found here and can be used to prevent ORU-10028.
v$sql_bind_data
Join cursor_num with cno of v$sql_cursor.
v$sql_bind_capture
New with Oracle 10g This view captures bind variables for all sessions and is faster than setting 10046 on level 4.
v$sql_cursor
Join parent_handle with address of v$sql or v$sqlarea.
v$sql_workarea
v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number.
v$standby_log
v$statname
Use this view to get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat.
v$sysaux_occupants
v$sysaux_occupants doesn't exist in Oracle versions prior to Oracle 10g. See occupants in the sysaux tablepsaces.
v$sysmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysmetric_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysstat
v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database. For example, it is possible to find out the CPU time (name = 'CPU used by this session') This view is (among others) used to calculate the Hit Ratio.
v$system_event
This view displays the count (total_waits) of all wait events since startup of the instance. If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited. The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second. total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat. v$enqueue_stat can be used to break down waits on the enqueue wait event. While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions.
v$undostat
undo tablespaces
v$tempfile
v$tempseg_usage
v$tempseg_usage is a public synonym for v$sort_usage.
v$tempstat
v$thread
The Oracle SID can be retrieved through select instance from v$thread
v$timer
This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0.
v$transaction
Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with the usn field of v$rollname. This is demonstrated in Transactions generate undo
v$timezone_names
See also timezones for some values of tzabbrev.
v$transportable_platform
Which platforms are supported for cross platform transportable tablespaces.
v$version
Use this view to find out what version you actually work on: select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
v$waitstat
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
Posted by at No comments:

Oracle DBA Checklist

I. Daily Procedures

A. Verify all instances are up
Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this.
Optional implementation: use Oracle Enterprise Manager's 'probe' event.
B. Look for any new alert log entries
• Connect to each managed system.
• Use 'telnet' or comparable program.
• For each managed instance, go to the background dump destination, usually $ORACLE_BASE//bdump. Make sure to look under each managed database's SID.
• At the prompt, use the Unix ‘tail’ command to see the alert_.log, or otherwise examine the most recent entries in the file.
• If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in .
C. Verify DBSNMP is running
1. Log on to each managed machine to check for the 'dbsnmp' process.
For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to "verify that DBSNMP is NOT running".)
D. Verify success of database backup
E. Verify success of database archiving to tape
F. Verify enough resources for acceptable performance
1. Verify free space in tablespaces.
For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of , the minimum free space for : [ < tablespace > is < amount > ]. When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least
Posted by at No comments:
Subscribe to: Posts (Atom)