Tutorials

MYSQL User Password Modification

2011-03-03T16:57:00.006+05:30

To modify the password of an existing user we use the following syntax:

SET PASSWORD [FOR user] = PASSWORD('some password')

The SET PASSWORD statement assigns a password to an existing MySQL user account.

With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.

With a FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:

SET PASSWORD FOR 'tom'@'localhost' = PASSWORD('newpass');

That is equivalent to the following statements:

UPDATE mysql.user SET Password=PASSWORD('newpass')

WHERE User='bob' AND Host='%.loc.gov';

FLUSH PRIVILEGES;

Note:

You can see which account the server authenticated you as by executing

SELECT CURRENT_USER()

Dropping a User in MYSQL Database

2011-03-03T16:52:00.001+05:30

PROCEDURE OF DROPPING A USER:

Syntax:

DROP USER user [, user] ...

Example:

mysql> drop user gsmc_monitor;
Query OK, 0 rows affected (0.00 sec)

The DROP USER statement removes one or more MySQL accounts. To use it, you must have the DELETE privilege for the mysql database. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the username part of the account name, a hostname part of '%' is used..

To remove a MySQL account completely (including all of its privileges), you should use the following procedure, performing the steps in the order shown:

1.Use SHOW GRANTS to determine what privileges the account has.

2.Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This removes rows for the account from all the grant tables except the user table, and revokes any global privileges listed in the user table.

3.Delete the account by using DROP USER to remove the user table row.

In MySQL 5.0.2 and up, DROP USER removes the account row in the user table and also revokes the privileges held by the account.

Important:

DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail.

MySQL Frequeently Asked Questions -2

2011-03-03T16:34:00.002+05:30

11). How to setup admin user for MYSQL ?

Login as super user 'root' in mysql and execute the following commands.

mysql> use mysql;

mysql> create user 'tom'@'%' identified by 'tom';

mysql> grant all on *.* to 'tom'@'%' with grant option;

mysql> flush privileges;

12). What types of privileges are there in MySQL ?

There are 4 types of privileges.

i .Global privileges like *.* (all hosts connecting to Mysql db server)

Ex: GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

ii. Database privileges like .*

Ex: GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

iii. Table privileges like SELECT, INSERT, UPDATE, DELETE

Ex: GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

iv. Column privileges like

Ex: GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

13). How to find the version of MySQL ?

mysql> select version();

14). How do I limit the number of rows I get out of my database?

SELECT name FROM table LIMIT [, ] ;

if you want to get the rows between 10 and 20 do the following:
SELECT name FROM table LIMIT 10, 20 ;

15). Is it possible to insert multiple rows using single command in MySQL ?

Yes. Please see below example.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9) ;

16). I am getting the following error while logging into “sras” database.

ERROR 1044 (42000): Access denied for user 'srini'@'localhost' to database 'sras'.

Please refer the error to DBA asking for granting the privilege to “sras” database.

mysql > grant all on sras.* to 'user_name' @ 'host_name' ;

17). What is null value in MySQL ?

In MySQL NULL is only equal to NULL, but NULL is not equal to ' ' ( blank value ) or 0(zero)

18). How can I check if a table in MySQL database already exists?

Command : SHOW TABLES LIKE '%';

Mysql > SHOW TABLES LIKE 'mytab%';

19). I am getting below error when application tries to execute any procedure say sras_purge etc.,

Caused by: java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.

Solution : Remove the grants to particular user using following command:

delete from mysql.user where host like '%';

grant ALL on .* to ''@'' identified by '';

grant ALL on mysql.proc to ''@'' identified by '';

Eg:

delete from mysql.user where host like 'sustain-73%';

grant ALL on sras25_qa01.* to 'sras_app'@'sustain-73.central.sun.com' identified by 'sras_app';

grant ALL on mysql.proc to 'sras_app'@'sustain-73.central.sun.com' identified by 'sras_app';

20). Convert datetime from MST (db servers timezone) into GMT returns NULL value, how to solve it?

Solution: DB should be updated with timezone value from os otherwise Mysql will return null.

To load the timezone value from os into db

Command : mysql_tzinfo_to_sql /usr/share/lib/zoneinfo
mysql -u root mysql -p

eg. mysql_tzinfo_to_sql /usr/share/lib/zoneinfo
mysql -u root mysql -pmysql

21). Column alias not displaying for tables column but with function it is displaying for driver mysql-connector-java-5.1.6-bin.jar, how to solve it?

Solution: This is issue with this driver, so please use mysql-connector-java-5.0.7-bin.jar since SRAS DB version is Mysql 5.0.68 enterprise version
22). How to make MySQL database & table names are case in-sensitive?

Solution: Change lower_case_table_names=1 in my.cnf and restart the instance

23). How to make MySQL character data are case in-sensitive?

Solution: Recreated db with COLLATION=utf8_bin option instead of utf8_general_ci

Eg: CREATE DATABASE IF NOT EXISTS

CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL Frequeently Asked Questions -1

2011-03-03T16:17:00.001+05:30

1). What is the minimum hardware requirement for MySQL to be installed ?

The following are the minimum hardware requirement for MySQL installation.

Minimum Disk Space 80GB

Minimum RAM 4G

Server with Multiple CPU processor

2). How do I login in MySQL Database ?

From Mysql DB server:

mysql -u -p

eg : mysql -u root srasdev -pmysql

From Mysql client: (Client hostname should be granted from Mysql DB server)

mysql -u -p -h

eg : mysql -u root srasdev -pmysql -h gssie-4200-4.central

3). How do I startup/shutdown the MySQL database ?

Run the following command in $ prompt.

Startup : $MYSQL_HOME/bin/mysqld_safe --user= --datadir= &

eg: $MYSQL_HOME/bin/mysqld_safe --user=mysql --datadir=/mysql/mysql/data &

Shutdown : $MYSQL_HOME/bin/mysqladmin -u -p shutdown

eg: $MYSQL_HOME/bin/mysqladmin -u root -p shutdown

Note: Mysql(db) root user's password required

4). How do to change user password ?

Run the following command in mysql prompt

UPDATE mysql.user SET Password=PASSWORD('newpass')

WHERE User='bob' AND Host='%.loc.gov';

or

GRANT all ON *.* TO 'user'@'host' IDENTIFIED BY 'password';

or

SET PASSWORD FOR 'user_name'@'host_name' = PASSWORD('newpass');

Note: In some cases above commands may fails, so login as root and delete entry from mysql.user

and grant with new password.

For eg:

delete from mysql.user where host='';

GRANT all ON *.* TO 'user'@'host' IDENTIFIED BY 'password';

5). How do I see MYSQL process ?

Command : SHOW [FULL] PROCESSLIST;

Eg: MYSQL>SHOW PROCESSLIST;

6). How to grant privileges to a user ?

Command: GRANT /ALL ON /*.* TO ' someuser ' @ ' somehost ';

Eg: GRANT ALL ON *.* TO 'srasapp'@'sustain-42.central';

GRANT SELECT ON sras.* TO 'srasapp'@'sustain-42.central';

7). How do I grant permission to a specific host ?

Command: GRANT ALL ON *.* TO 'user_name'@'host_name' IDENTIFIED BY 'password' ;

Eg: GRANT ALL ON *.* TO 'sras_app'@'sustain-75.central' IDENTIFIED BY 'srasapp123';

8). How do I grant permission for all hosts ?

Command : GRANT ALL ON *.* TO ' user_name ' @ ' %' IDENTIFIED BY 'password' ;

Eg: GRANT ALL ON *.* TO 'sras_app'@'%' IDENTIFIED BY 'srasapp123';

9). How do I run SQL command through command line ?

Login in MySQL using the command below and you can run sql queries in mysql prompt.

eg:

mysql -u root -p

mysql > use mysql ;

mysql > select * from user ;

mysql > \. < file name.sql >

10). How do I resolve access denied error ?

Grant access to the user with password

Command: GRANT /ALL ON /*.* TO ' someuser ' @ ' somehost ' IDENTIFIED BY '';

eg: GRANT ALL ON *.* TO 'sras_app'@'sustain-75.central' IDENTIFIED BY 'srasapp321';

MySql Tools & Utilities

2011-03-03T15:57:00.002+05:30

Following is the list of some tools which support MySql:

Tools to Monitor Queries and Transactions:

1). mytop: tool for monitoring queries. It is a Perl program that runs in a terminal and displays information about all connections in a tabular layout, similar to the Unix top program's process display. Columns include the connection ID, the connection's status, and the text of the current query. From this display you can select a query to EXPLAIN, kill a query, and a few other tasks.

2). mtop: (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. Features include 'zooming' in on a process to show the complete query, 'explaining' the query optimizer information for a query and 'killing' queries. In addition, server performance statistics, configuration information, and tuning tips are provided.

3). mkill: (MySQL kill) monitors a MySQL server for long running queries and kills them after a specified time interval. Queries can be selected based on regexes on the user, host, command, database, state and query.

4). innotop: a powerful text-mode MySQL and InnoDB monitoring tool. It has lots of features, is fast and configurable, and it’s easy to use.

5). phpMyTop: It is a web based mytop clone. It is useful when we don't have shell access and can't connect remotely to our database server, but can connect from a web server.

6). ajaxMyTop: it’s function is similar to phpMyTop. It also feels more like a traditional GUI program, the entire page does not constantly refresh itself.

7). phpMyAdmin : It is also a web based tool with features to design tables, run queries, manage users and more. Its focus isn't on monitoring queries and processes, but it has some of the features such as showing a process list.

Tools to Monitor a MySQL Server:

1). mysqlreport: It is a well-designed program that turns MySQL status information into knowledge. It prints out a report of relevant variables, sensibly arranged for an experienced MySQL user.

2). Nagios: Nagios is an open source host, service and network monitoring program. Its task is setting up automated systems to monitor your server and let you know if it's alive

3). mysqlmonitor: It monitors a Mysql database server. It is designed to be used as a monitor for the mon package. As such if any host's Mysql server is dead it returns 1 and outputs the hostnames that failed. If all hosts' servers are alive 0 is returned.

Following is the list of some MySql Utilities:

1). isamchk

isamchk [options]table [table...]

Performs operations on the database table files themselves (called ISAM files for Indexed Sequential Access Method). This utility is used to check and repair the files, as well as report information about them. You must provide the correct path to the ISAM file you wish to examine. The default location for the ISAM files is /usr/local/var/databasename/tablename.ISM.

2). isamlog

isamlog [options] [logfile] [table]

Displays information about ISAM logs. An ISAM log is generated if the MySQL server is started with the -log-isam option. The information in the ISAM log can be used to recover damaged tables using the -r option. However, modifying the data files directly can be dangerous and you should always back up your data before doing so.

3). mysql

mysql [options] [database]

The MySQL command line monitor. This program is the most basic way to communicate with the MySQL server. SQL commands can be typed directly on the command line and the results are displayed on the screen. If database is supplied, it is automatically selected as the current database.

4). mysqlaccess

mysqlaccess [options] [host] user database

Displays and modifies access rights for the MySQL server. You may examine the rights of users for any database and in connection with any host. Unix shell wildcards `*' and `?' may be used to match multiple hosts, users and databases. All actions are performed on a copy of the actual grant tables until a mysqlaccess --commit command is sent.

5). mysqladmin

mysqladmin [options] command [command...]

Performs operations that affect the database server as a whole. This utility is used to shutdown the database server, add and delete entire databases, and other administrative functions.

6). mysqlbug

mysqlbug

Reports a bug in a MySQL program or utility. This program collects information about your MySQL installation and sends a detailed problem report to the MySQL team.

7). mysqld

mysqld [options]

The MySQL server daemon. All other programs interact with the database through this server, so it should be left running at all times (except when down for maintenance). The daemon is usually started from a script called safe_mysqld. This script sets the appropriate environment variables and launches mysqld with the necessary arguments.

8). mysqldump

mysqldump [options] database[table]

Outputs the contents of the given database (or table within a database) as a series of ANSI SQL commands. This command is handy for breaking up a database; use the -1 and -opt options.

9). mysqlimport

mysqlimport [options] database [file]

Reads a file of data in a variety of common formats (such as comma delimited or fixed width) and inserts the data into a database. A table with the same name as the file must exist in the database with enough columns of the appropriate type to store the data.

10). mysqlshow

mysqlshow [options] [database] [table] [field]

Displays the layout of the requested database, table or field. If no argument is given, a list of all of the databases is given. With one argument the layout of the given database is displayed. With two arguments, a table within the database is displayed. If all three arguments are present, the information about a specific field within a table is presented.

Mysql Questions & Answers - 2

2011-03-03T13:32:00.001+05:30

6). How to delete a table?

DROP TABLE command is used to delete a table.

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
[RESTRICT CASCADE]

DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, so be careful with this statement! If any of the tables named in the argument list do not exist, MySQL returns an error indicating by name which non-existing tables it was unable to drop, but it also drops all of the tables in the list that do exist.

Important: When a table is dropped, user privileges on the table are not automatically dropped. See [HELP GRANT].

IF EXISTS is used to prevent an error from occurring if the table does not exist.

Example:
mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)

This deletes the table t1;

7). How to delete a database?

Syntax:

DROP {DATABASE SCHEMA} [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database. DROP SCHEMA is a synonym for DROP DATABASE.

Important: When a database is dropped, user privileges on the database are not automatically dropped. See [HELP GRANT].

IF EXISTS is used to prevent an error from occurring if the database does not exist.

Example:
mysql> drop database sig;
Query OK, 0 rows affected (0.00 sec)

This deletes the database sig.

8). How to display all databases present?

The SHOW DATABASES is used to display all the databases in the system.

mysql> show databases;

+--------------------+
Database
+--------------------+
information_schema
mysql
test
+--------------------+
3 rows in set (0.16 sec)

9).How to create users in a system?

The CREATE USER command is used to create new users on mysql.

Syntax:

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table that has no privileges. An error occurs if the account already exists. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you

specify only the username part of the account name, a hostname part of '%' is used. For additional information about specifying account names, see [HELP GRANT].

The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the PASSWORD keyword. See [HELP GRANT].

10). How to display all users in a system ?

All the users are stored as different rows in the user table in the mysql databse.

mysql> use mysql

Database changed

mysql> select user from user;

+--------+
user
+--------+
testuser1
testuser2
root
sig
+--------+
4 rows in set (0.00 sec)

11).How to Grant permissions to users?

All the permissions to users created on mysql are given using the grant commands. All the grants to a particular user can be viewed in the user table.

Syntax:

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name * *.* db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE
[{SSL
X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]

mysql> grant all on *.* to testuser1 identified by 'testuser1' with grant option;

Query OK, 0 rows affected (0.09 sec)

This will grant all rights to the user testuser1 having password testuser1. We can also grant the specific privileges by appropriately setting the different columns in the user table.

mysql> update user set Create_priv='Y' where user = 'testuser1';
Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

This grants the create privilege to the user testuser1.

ALSOLOOK http://www.devarticles.com/c/a/MySQL/Creating-Users-and-Setting-Permissions-in-MySQL/

12). How to get help in mysql?

The HELP command is used to get help on mysql.

mysql> help contents

You asked for help about help category: "Contents"
For more information, type 'help ', where is one of the following categories:

Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Plugins
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers

Mysql Questions & Answers - 1

2011-03-03T13:16:00.002+05:30

1. How to create a new database?

CREATE DATABASE creates a database with the given name. To use this statement, you need to have the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax:

CREATE {DATABASE SCHEMA} [IF NOT EXISTS] db_name
[create_specification [create_specification] ...] create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

Example:

mysql> create database sig;

Query OK, 1 row affected (0.01 sec)

This creates a new database sig.

2). How to set a database as the active database?

The USE command is used to select a database as the active database.

Syntax: USE db_name

mysql> use sig;

Database changed

This sets the databse sig as the current database,

3). How to create new tables on a database ?

The CREATE TABLE command is used to create a new table.

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_option ...]

[partition_options]

Example:

mysql> create table t1(a int);
Query OK, 0 rows affected (0.08 sec)

This creates the table t1 with a integer column labeled a.

4). How to insert rows into a table?

The INSERT command is used to add rows to a table.

Syntax:

INSERT [LOW_PRIORITY
DELAYED
HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr
DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Example:

mysql> insert t1 set a= 1;
Query OK, 1 row affected (0.03 sec)

This adds a new rown with a = 1 to table t1.

5). How to display the contents of a table?

The SELECT command is used to display the rows of the table.

Syntax:

SELECT
[ALL
DISTINCT
DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE
SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name
expr
position}
[ASC
DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name
expr
position}
[ASC
DESC], ...]
[LIMIT {[offset,] row_count
row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
INTO DUMPFILE 'file_name'
INTO @var_name [, @var_name]]
[FOR UPDATE
LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and n include UNION statements and subqueries. See [HELP UNION], and ttp://dev.mysql.com/doc/refman/5.1/en/subqueries.html.

mysql> select * from t1;

+------+
a
+------+
1
2
3
4
+------+

4 rows in set (0.00 sec)

Note: sel * from t1; wouldn’t work on MYSQL.

Automatic Shared Memory Management in Oracle 10g ( ASMM)

2011-03-02T23:53:00.004+05:30

OVERVIEW:

It used to be a challenge to size the various memory pools that comprise the Oracle system global area (SGA) for optimal performance. In real world most of the Oracle Databases are of Hybrid type. They are OLTP in day time when the accessibility is high and in the night time, many batch jobs run on the same database to extract data or to run big reports. This will be a combination of the OLTP, OLAP, DSS and HYBRID type of database. Since various types of applications require the underlined memory pools differently, and since the workload can vary minute by minute, manually resizing the SGA components could feel like a never-ending task.

If there is not enough free SGA to allocate for a specific pool when needed, the database will give out-of-memory error, such as

ORA-04031: unable to allocate …

The requirement for the database varies from time to time. During OLTP operation (when most of the users accessing the data) the response time should be minimum. During night time the turn around time should be short when response time is not so important.

The combination of these kinds of requirement makes the task of the DBA a little bit tougher. DBA needs to set the SGA in such a way that demands meet in a balance. But this is tough job. To ease such issue, Oracle has come up with Automatic Shared Memory Management feature in Oracle 10g which will adjust the requirement of the memory component as per demand of the system.

Advantage of ASMM:

Oracle Database 10g, ASMM automatically sizes many of the memory pools while the database is running, allocating and de-allocating memory as needed. As the workload composition changes, Oracle Database 10g enlarges the appropriate pools and reduces the sizes of other automatically sized pools accordingly. In short, ASMM can save the DBA a lot of trouble—and improve overall performance as well.

The main advantage ASMM is that Oracle Database server itself will adjust the following SGA parameters as per the requirement of the system.

· DB_CACHE_SIZE

· JAVA_POOL_SIZE

· LARGE_POOL_SIZE

· SHARED_POOL_SIZE

Remaining components of the SGA are not adjustable by this method. They need to be manually adjusted. They are

LOG_BUFFER

DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE (n = 2, 4, 8, 16, 32, and nk is non-standard Data block size.))
STREAMS_POOL_SIZE (In Oracle 10g Release 2. STEAMS_POOL_SIZE can be automatically managed.)

The ASSM feature will lessen the involvement of DBA to set the different SGA components from time to time based on the demand of the workload. It will improve the performance of the database also reduce the cost factor by elimination of adding additional System resource (Memory, etc).

How to setup ASMM:

The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL. That sets the statistics collection level of the database. (The other value of this parameter is BASIC, which will not allow changing the memory pools automatically.)

Oracle 10g has introduced a new parameter by name SGA_TARGET.

DBA has to set up the SGA_TARGET to a non-zero value that will enable the automatic memory allocation. The SGA_TARGET can be configured dynamically by issuing “Alter system” command or by setting that in Parameter file.

DBA needs to set the below mentioned memory parameters to 0 in the parameter file or remove them completely from the parameter file.

DB_CACHE_SIZE = 0

JAVA_POOL_SIZE = 0

LARGE_POOL_SIZE = 0

SHARED_POOL_SIZE =0

DBA can also do resizing of the autotuned pools to 0, as follows:

SQL> alter system set sga_target=160M scope=both;

System altered.

SQL> alter system set db_cache_size=0;

System altered.

SQL> alter system set shared_pool_size=0;

System altered.

……..

The ORACLE database will share that memory into separate component as mentioned below. The individual pools within the SGA will be dynamically configured based on the workload. From time to time, based on the composition of the work load, the pools will expand to accommodate the increase in demand or shrink to accommodate the expansion in another pool. This expansion or contraction occurs automatically without the DBA's intervention.

· DB_CACHE_SIZE

· JAVA_POOL_SIZE

· LARGE_POOL_SIZE

· SHARED_POOL_SIZE

There are few components of SGA that can’t be automatically tuned; those components need to be set manually. Manually sized components consume memory from the target value first, and then the remainder of SGA memory is spread across the various auto sized pools. In other words, if DBA sets parameter values for any of these below mentioned manually tuned pools, Oracle Database 10g subtracts their sizes from SGA_TARGET first, before allocating memory to the automatically allocated pools.

DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE (n = 2, 4, 8, 16, 32 and nk is non-standard Data block size)
LOG_BUFFER
STREAMS_POOL_SIZE (In Oracle 10g Release 2. STEAMS_POOL_SIZE can be automatically managed.)

The SGA_TARGET can’t cross the size of SGA_MAX_SIZE. SGA_MAX_SIZE can’t be resized dynamically. To reset the SGA_MAX_SIZE, the Oracle Instance needs to be renounced.

Setting a Minimum Value for variable pools:

If DBA doesn't set the values for each of the automatically tuned pools to zero after switching to ASMM, whatever value DBA had set for a parameter will function as a lower threshold value for that pool—ASMM won't go below a nonzero value, even if it needs the memory for another automatically tuned pool.

Suppose, DBA has set up the following parameters in parameter file after switching to ASSM feature.

LARGE_POOL_SIZE = 16M

JAVA_POOL_SIZE = 8 M

In that case, LARGE_POOL_SIZE will never go below 16 MB and JAVA_POOL_SIZE will never go below 8MB.

DBA can also do that by dynamically using ALTER SYSTEM as shown below.

alter system set large_pool_size = 16M; alter system set java_pool_size = 8M;


Of course, these pools are still governed by Automatic Shared Memory Management—their sizes will shrink or expand based on demand. The values DBA has specified explicitly put a lower limit on the pool size; they will never sink below this limit.

Oracle DBA FAQs - 2

2011-03-02T23:42:00.001+05:30

Differentiate between TRUNCATE and DELETE.

The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

1000000

Can you use a commit statement within a database trigger?

Yes, if you are using autonomous transactions in the Database triggers.

What is an UTL_FILE? What are different procedures and functions associated with it?

The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).
Subprogram -Description
FOPEN function-Opens a file for input or output with the default line size.
IS_OPEN function -Determines if a file handle refers to an open file.
FCLOSE procedure -Closes a file.
FCLOSE_ALL procedure -Closes all open file handles.
GET_LINE procedure -Reads a line of text from an open file.
PUT procedure-Writes a line to a file. This does not append a line terminator.
NEW_LINE procedure-Writes one or more OS-specific line terminators to a file.
PUT_LINE procedure -Writes a line to a file. This appends an OS-specific line terminator.
PUTF procedure -A PUT procedure with formatting.
FFLUSH procedure-Physically writes all pending output to a file.
FOPEN function -Opens a file with the maximum line size specified.

Difference between database triggers and form triggers?

Database triggers are fired whenever any database action like INSERT, UPATE, DELETE, LOGON LOGOFF etc occurs. Form triggers on the other hand are fired in response to any event that takes place while working with the forms, say like navigating from one field to another or one block to another and so on.

What is OCI. What are its uses?

OCI is Oracle Call Interface. When applications developers demand the most powerful interface to the Oracle Database Server, they call upon the Oracle Call Interface (OCI). OCI provides the most comprehensive access to all of the Oracle Database functionality. The newest performance, scalability, and security features appear first in the OCI API. If you write applications for the Oracle Database, you likely already depend on OCI. Some types of applications that depend upon OCI are:

· PL/SQL applications executing SQL
· C++ applications using OCCI
· Java applications using the OCI-based JDBC driver
· C applications using the ODBC driver
· VB applications using the OLEDB driver
· Pro*C applications
· Distributed SQL

What are ORACLE PRECOMPILERS?

A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java etc.

What is syntax for dropping a procedure and a function? Are these operations possible?

Drop Procedure/Function ; yes, if they are standalone procedures or functions. If they are a part of a package then one have to remove it from the package definition and body and recompile the package.

How to check if Apps 11i System is Autoconfig enabled ?

Under $AD_TOP/bin check for file adcfginfo.sh and if this exists use adcfginfo.sh contextfile= show=enabled

If this file is not there , look for any configuration file under APPL_TOP if system is Autoconfig enabled then you will see entry like

How to check if Oracle Apps 11i System is Rapid Clone enabled ?

For syetem to be Rapid Clone enabled , it should be Autoconfig enabled (Check above How to confirm if Apps 11i is Autoconfig enabled). You should have Rapid Clone Patches applied , Rapid Clone is part of Rapid Install Product whose Family Pack Name is ADX. By default all Apps 11i Instances 11.5.9 and above are Autoconfig and Rapid Clone enabled.

Whats is difference between two env files in .env and APPS.env under $APPL_TOP ?

APPS.env is main environment file which inturn calls other environment files like .env under $APPL_TOP, .env under 806 ORACLE_HOME and custom.env for any Customized environment files.

Whats main concurrent Manager types.

# ICM - Internal Concurrent Manager which manages concurrent Managers
# Standard Managers - Which Manage processesing of requests.
# CRM - Conflict Resolution Managers , resolve conflicts in case of incompatibility.

Whats US directory in $AD_TOP or under various product TOP's .

US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for

France

, AR for arabic, simplifies chinese or spanish.

Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.

Where would i find .rf9 file, and what execatly it dose ?

These files are used during restart of patch in case of patch failure because of some reason.

Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored and why its used ?

This file is defined by environment variable FORMS60_WEB_CONFIG_FILE This is usually in directory $OA_HTML/bin on forms tier.

This file is used by any forms client session. When a user try to access forms , f60webmx picks up this file and based on this configuration file creates a forms session to user/client.

What is Multi Node System ?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database, Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

Can a function take OUT parameters. If not why?

yes, IN, OUT or IN OUT.

Can the default values be assigned to actual parameters?

Yes. In such case you don’t need to specify any value and the actual parameter will take the default value provided in the function definition.

What is difference between a formal and an actual parameter?

The formal parameters are the names that are declared in the parameter list of the header of a module. The actual parameters are the values or expressions placed in the parameter list of the actual call to the module.

What are different modes of parameters used in functions and procedures?

There are three different modes of parameters: IN, OUT, and IN OUT.

IN - The IN parameter allows you to pass values in to the module, but will not pass anything out of the module and back to the calling PL/SQL block. In other words, for the purposes of the program, its IN parameters function like constants. Just like constants, the value of the formal IN parameter cannot be changed within the program. You cannot assign values to the IN parameter or in any other way modify its value.

IN is the default mode for parameters. IN parameters can be given default values in the program header.

OUT - An OUT parameter is the opposite of the IN parameter. Use the OUT parameter to pass a value back from the program to the calling PL/SQL block. An OUT parameter is like the return value for a function, but it appears in the parameter list and you can, of course, have as many OUT parameters as you like.

Inside the program, an OUT parameter acts like a variable that has not been initialised. In fact, the OUT parameter has no value at all until the program terminates successfully (without raising an exception, that is). During the execution of the program, any assignments to an OUT parameter are actually made to an internal copy of the OUT parameter. When the program terminates successfully and returns control to the calling block, the value in that local copy is then transferred to the actual OUT parameter. That value is then available in the calling PL/SQL block.

IN OUT - With an IN OUT parameter, you can pass values into the program and return a value back to the calling program (either the original, unchanged value or a new value set within the program). The IN OUT parameter shares two restrictions with the OUT parameter:

An IN OUT parameter cannot have a default value.

An IN OUT actual parameter or argument must be a variable. It cannot be a constant, literal, or expression, since these formats do not provide a receptacle in which PL/SQL can place the outgoing value.

Locks Script in ORACLE

2011-03-02T21:05:00.001+05:30

LOCKS:

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.

Oracle has several views for showing lock status, some of which show the username:

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS - Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
DBA_LOCKS - Shows all locks or latches held or being requested
DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks

The following script is commonly used by Oracle professionals who need to quickly find out all database objects that are locked within their system.

Most Oracle professionals use of the v$locked_object view in order to gather information about objects that are locked within the Oracle database. The v$locked_object view can also be joined into the v$session view in order to gather session level information (SID, PID, status, machine) , and also joined into the dba_objects view in order to get the owner, the object name, and the type of objects that is currently being locked within the database.

The following script can be used in order quickly identify all lock objects within your Oracle system.

select

c.owner,

c.object_name,

c.object_type,

b.sid,

b.serial#,

b.status,

b.osuser,

b.machine

from

v$locked_object a ,

v$session b,

dba_objects c

where

b.sid = a.session_id

and

a.object_id = c.object_id;

DEADLOCK:

Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock. This releases one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes the rollback.

Below the script is useful for identify – “ Who is blocking to whom ”

select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

below script is useful for “who’s Holder and Who’s Waiter” to query.

SELECT /*+ FIRST_ROWS ORDERED */ DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type

FROM V$LOCK

WHERE (id1, id2, type) IN

(SELECT id1, id2, type FROM V$LOCK WHERE request>0)

ORDER BY id1, request;

Below the scriprt is Useful for identify Database level locks.

select nvl(S.USERNAME,'Internal') username,

nvl(S.TERMINAL,'None') terminal,

L.SID||','||S.SERIAL# Kill, b.spid,

U1.NAME||'.'||substr(T1.NAME,1,20) tab,

decode(L.LMODE,1,'No Lock',

2,'Row Share',

3,'Row Exclusive',

4,'Share',

5,'Share Row Exclusive',

6,'Exclusive',null) lmode,

decode(L.REQUEST,1,'No Lock',

2,'Row Share',

3,'Row Exclusive',

4,'Share',

5,'Share Row Exclusive',

6,'Exclusive',null) request

from V$LOCK L,

V$SESSION S,

SYS.USER$ U1,

SYS.OBJ$ T1 ,

v$process b

where L.SID = S.SID

and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)

and U1.USER# = T1.OWNER#

and S.TYPE != 'BACKGROUND'

and s.paddr = b.addr order by 1,2,5 ;

Oracle DBA FAQs - 1

2011-03-02T21:00:00.002+05:30

What Is Oracle?

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

What Is an Oracle Database?

An Oracle database is a collection of data treated as a big unit in the database server.

What Is an Oracle Instance?

Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.

What Is a Parameter File?

A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:

Server Parameter Files - Binary version. Persistent.
Initialization Parameter Files - Text version. Not persistent.

What Is a Server Parameter File?

A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.

What Is a Initialization Parameter File?

An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.

What is System Global Area (SGA)?

The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. The SGA is allocated in memory when an Oracle database instance is started, and any change in the value will take effect at the next startup.

What is Program Global Area (PGA)?

A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.

What Is a User Account?

A user account is identified by a user name and defines the user's attributes, including the following:

Password for database authentication
Privileges and roles
Default tablespace for database objects
Default temporary tablespace for query processing work space

What Is the Relation of a User Account and a Schema?

User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.

What Is a User Role?

A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.

What is a Database Schema?

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects.

What Is a Database Table?

A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

What Is a Table Index?

Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

What Is an Oracle Tablespace?

An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

What Is an Oracle Data File?

An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

What Is a Static Data Dictionary?

Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:

An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

What Is a Dynamic Performance View?

Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.

Developing custom portlets using Eclipse --> Creating a dynamic web project in eclipse

2011-03-02T16:57:00.000+05:30

Creating a dynamic web project in eclipse:

1). To create a Dynamic Web Project, go to File > New > Dynamic Web Project if you are in the Web
     Perspective or File > New >Other > Web > Dynamic Web Project in any other Perspective.

2). Fill in the name of the project. Next, click on the New... button in the Target Runtime section to specify a
     new Target Runtime. Click Jboss 4.2 Runtime and press Next.

3). On the next page we should give a name to the runtime. Example:JBoss Portal 2.7 Runtime,and then point
     it to the location of your JBoss AS + Portal installation

4). Click on Finish. It returns the New Dynamic Web Project wizard where it's also necessary to set the
     project Configuration.

5). The last wizard page is for configuring web modules. Here all values are set, so we can leave everything
     as it is.

6). Press Finish.

Adding a Java Portlet to the Project:

1). Call the New Java Portlet wizard by selecting New > Other > JBoss Tools Web > Portlet > JavaPortlet.

2). The wizard fills in The Web Project and Source Folder fields .Then we need to specify the Java package
     and the Class name and click Next .

3). On the other page we will be able to set the display name of the Portlet, a title, a description, the
     supported modes and init parameters.

4). On the next screen we can define which methods from GenericPortlet you want to override.

5). The last screen lets us build JBoss Portal specific descriptors to create an instance during the deployment
      and place it directly on the Portal.

Once Java Portlet is created, new resources have been added to the project structure.It adds a Java Portlet class, defaultobject. xml and portlet-instances.xml files and updates portlet.xml as well.

Thus the Project is ready to be built and deployed.

Developing custom portlets using Eclipse -> Building the setup

2011-03-02T16:31:00.001+05:30

Introduction:

Custom portlets for JBoss portal can be developed using in two ways:

>> By using JBoss Developer Studio

>> By installing JBoss tools in eclipse

JBoss Developer Studio is not freely available.
This presentation showcases developing custom portlets for JBoss portal using Eclipse IDE.
JBoss set of plug-in’s extends JBoss Portal features.
JBoss Tools provide to ease the development with JBoss Portal.

Building the setup:

Installing Eclipse version 3.4.x(Eclipse Ganymede)
Installing JBoss Tools through Eclipse software updates
Manual installation of JBoss Tools

Installing Eclipse version 3.4.x(Eclipse Ganymede):

Before installing JBoss Tools,we’ll need to download and install the Eclipse IDE.
It is recommended to use Eclipse version 3.4.x
The quickest way to download the Eclipse IDE, from http://www.eclipse.org/downloads

Installing JBoss Tools through Eclipse software updates:

The quickest and simplest way to install the JBoss Tools consists of using a facility of Eclipse, named Software Updates.
This is a graphical wizard that will guide us through the search/install process of the latest plug-ins/add-ons for Eclipse.
JBoss Tools 3.0 for Eclipse 3.4 "Ganymede”
To use JBoss Tools with Eclipse 3.4, start Eclipse, then do the following:

1). Launch the Eclipse IDE.

2). From the Help menu, select Software Updates.

3). Switch to the Available Software tab.

4). Click on the Manage Sites button.

5). Add these URLs, or, if they're already listed, make sure they're enabled (checked)

http://download.jboss.org/jbosstools/updates/nightly/trunk/

http://download.eclipse.org/releases/ganymede/

http://download.eclipse.org/birt/update-site/2.3/

6). Click OK.

7).Expand the JBoss Tools Update Site and select any or all of the available features listed there under.

8).Click Install... then Next and/or Finish. Restart when prompted

Installing JBoss Tools Manually:

Instead of using the Update Manager, we can download the archive of the JBoss Tools plugins and incorporate them into our Eclipse installation manually .

Obtaining a JBoss Tools release:

The latest release of JBoss Tools can be found on the JBoss Tools download page.
Once the download completes, we need to decide what type of installation we are going to use before extracting it.
We can either install it directly into your Eclipse installation or we can link the plugins to our Eclipse installation.

Installing the plugins directly into your Eclipse installation:

Install the JBoss Tools plugins directly into the Eclipse installation.
The directory structure inside the archive is as follows:

          Eclipse/
          features/
          plugins/

If we are using the commandline and the Eclipse installation directory is named "eclipse“:

Navigate to the directory above the Eclipse installation and extract the archive using the unzip command.
unzip JBossTools-2.1.2.GA-ALL-linux-gtk.zip

If we prefer to extract using an archive manager or your Eclipse installation directory is not named "eclipse",:

Extract the features and plugins folders in the archive into your Eclipse installation.

Linking the plugins to the Eclipse installation:

1). Extract the JBoss Tools release anywhere outside of your Eclipse installation.

2). It‘s convenient to put this eclipse folder inside of a directory with the name of the plugin (i.e., /home/max/eclipse-addons/jbosstools)

3). Create the file jbosstools.link in the links folder (Eclipse < 3.4) or dropins folder (Eclipse >= 3.4) of your Eclipse installation

4). The contents of jbosstools.link should consist of a single line that defines the path where you extracted the JBoss Tools plugins .

path=/home/max/eclipse-addons/jbosstools

The links or dropins folder can contain any number of .link files that point to Eclipse extension directories.

Refreshing the plugin cache

eclipse –clean

Teradata SQL Assistant

2011-03-01T21:55:00.004+05:30

How SQL Assistant Can Help You:

SQL Assistant is a Web-based query tool that enables you to compose a query,

submit it to the Teradata database, view the results from a Web browser, and

save the data on your PC for analysis.

Using SQL Assistant, you can save the data retrieved from Teradata databases

to create consolidated reports or to analyze the data. SQL Assistant gives you

SQL activity, complete with source identification, timings, row counts, and

your notes. This is useful because you can use the historical record to build SQL

scripts and then modify the scripts to gather more specific results.

Key Features :

Using SQL Assistant, you can:

• Send queries to any Teradata database.

• View and sort the results by column and save them to a file.

• Save queries to a file so you can run them automatically from the same file

in the future.

• Load queries from a file and make changes before submitting them to the

database.

• View historical records of the queries you have submitted with timings and

status information. You can sort the history rows by column, update them

with your notes, and save the records to a file.

• View a hierarchical listing of databases with object names to reduce the

time required to create SQL statements.

• Use the browser’s built-in find features to do a string search on a query, an

answer set, or any information in the historical table visible in the browser

window.

• Abort a query in progress if you change your mind about running a query

or if a query is taking longer than you expected.

Also, to maintain security and help maximize your server’s resources, your

browser’s connection ends after 30 minutes of inactivity.

TeraData Performance Analysis Tips

2011-03-01T20:51:00.001+05:30

Key Points:

Stat Collection
Pack Disk
Data Skew Analysis
Lock monitoring (Locking Logger)
Session tuning (DBS Control parameters)

1. Stat Collection :

Statistics on the loaded tables are very important from the performance perspective. Statistics helps optimizer to generate the accurate & faster query plans. An old statistics in warehouse can lead to wrong query plans which may take time in query processing. Hence it is much required to refresh the statistics periodically at least in the warehousing environment. We can identify the tables which are frequently accessed & modified with inserts, updates, deletes etc.

It is recommended to refresh the stats after every 10% of data change. We can collect the statistics at column level or at index level.

Syntax:

Collect statistics on column (column_name 1,.., column_name n); OR

Collect statistics on index (column_name 1,.., column_name n);

2. Pack Disk :

Pack disk is an utility that free up the cylinder space on the database, this

utility must be run periodically as in the warehouse environment large amount of data inserts, updates are happening which causes the physical memory to disorder due to frequent data manipulation. Pack disk utility allows us to restructure & physically reorder the data, free up space same as defragmentation. Teradata also run mini CYLPACKs automatically, if cylinder space goes below the prescribed limit. Cylinder space is required for the merge operation while the data Insert, Deletes Updates etc.

To run a pack disk we use Ferret utility provided by Teradata can be run through Teradata Manager Tool or through telnet on node session.

The set of commands that starts packdisk utility are given below one can create a kron job to schedule the same & run it periodically.

Commands to run pack defrag & packdisk utilities :

~ferret

defrag

packdisk fsp=8

3. Skew Analysis :

Primary index of a table in Teradata is responsible for the data

distribution on all the AMPs. Proper data distribution is required for the parallel processing in the system. As Teradata system follows shared nothing architecture, all the AMPs works in parallel. If data is evenly distributed amongst the AMPs then the amount of the work done by every AMP would be equal & time required for particular job would obviously be lesser. In contrast to this if only one/two AMPs are flooded with the data i.e. data skew then while running that job the two AMPs would be working & others will be idle. In this case we won’t be utilizing the parallel processing power of the system.

To avoid such data skew need to analyze the primary index of the tables in Teradata database over the period of time it might happen that data is getting accumulate at the few AMPs, which can have a adverse effect on the ETL as well as the system performance.

To analyze the data distribution for the table we can use the inbuilt HASH functions provided by the Teradata. To check the data distribution for a table one can use a query:

SELECT

HASHAMP (HASHBUCKET (HASHROW (Column 1,.., column n))) AS AMP_NUM, count(*)

From Table_Name

Group by 1;

This query will provide the distribution of records on each AMP

we can also analyze the probable PIs with this query which will predict the data distribution on the AMPs

4. Lock monitoring :

Locking Logger is an utility that enables us to monitor the

locking on the tables. Using this utility we can create a table that has the entries for the locks which have been applied to the tables while processing. This utility allows us to analyze the regular ETL process, jobs being blocked at particular time when there is no one to monitor the locking. By analyzing such locking situations we can modify the jobs & avoid the waiting period due to such situations.

To apply this locking loggers First, we need to enable locking logger via the DBS console window or the cnsterm subsystem. The setting does not take effect until the database is restarted.

LockLogger - This Field defines the system default for the locking logger.

This allows the DBA to log the delays caused by database locks to help in identifying lock conflicts. To enable this feature set the field to TRUE. To disable the feature set the field to FALSE.

After a database restart with the LockLogger flag set to true, the Locking Logger will begin to accumulate lock information into a circular memory buffer of 64KB. Depending on how frequently the system encounters lock contention, this buffer will wrap, but it will usually span a several day period. Following a period of lock contention, to analyze the lock activity, you need to run the dumplocklog utility which moves the data from the memory buffer to a database table where it can be accessed.

5. Session Tuning:

Session tuning is done for the running the load utilities in parallel

this requires to analyze some DBScontrol parameters & tune the same to provide the best parallel processing of the load utilities. There are two parameters MaxLoadAWT & MaxLoadTasks that enables the parallel job management a short note on the same:

The MaxLoadAWT internal field serves two purposes:

1) Enabling a higher limit for the MaxLoadTasks field beyond the default limit of 15.

2) Specifying the AMP Worker Task (AWT) limit for concurrent FastLoad and MultiLoad jobs when a higher limit is enabled.

In effect, this field allows more FastLoad, MultiLoad, and FastExport utilities running concurrently while controlling AWT usage and preventing excessive consumption and possible AWT exhaustion.

• The default value is zero: When MaxLoadAWT is zero, concurrency limit operates in the same manner as prior to V2R6.1 MaxLoadTasks specifies the concurrency limit for all three utilities: FastLoad, MultiLoad, and FastExport. The valid range for MaxLoadTasks is from 0 to 15.

• When MaxLoadAWT is non-zero (higher limit enabled): It specifies the maximum number of AWTs that can be used by FastLoads and MultiLoads. Maximum allowable value is 60% of the total AWTs. The valid range for MaxLoadTasks is from 0 to 30.

A new FastLoad/MultiLoad job is allowed to start only if BOTH MaxLoadTasks AND MaxLoadAWT limits are not reached. Therefore, jobs may be rejected before MaxLoadTasks limit is exceeded. MaxLoadTasks specifies the concurrency limit for the combination of only two utilities: FastLoad and MultiLoad. FastExport is managed differently; FastExport is no longer controlled by the MaxLoadTasks field.

A FastExport job is only rejected if the total number of active utility jobs is 60. At least 30 FastExport jobs can run at any time. A FastExport job may be able to run even when FastLoad and MultiLoad jobs are rejected. When a Teradata Dynamic Workload Manager (TDWM) utility throttle rule is enabled, the MaxLoadAWT field is overridden. TDWM will use the highest allowable value which is 60% of total AWTs. Update to MaxLoadAWT becomes effective after the DBS control record has been written. No DBS restart is required. Note that when the total number of AWTs (specified by the internal field MaxAMPWorkerTasks) has been modified but a DBS restart has not occurred, then there may be a discrepancy between the actual number of AWTs and the DBS control record. The system may internally reduce the effective value of MaxLoadAWTs to prevent AWT exhaustion.

• AWT Usage of Load Utilities: All load/unload utilities require and consume AWTs at different rates depending on the execution phase: FastLoad: - Phase 1 (Loading): 3 AWT's - Phase 2 (End Loading): 1 AWT's MultiLoad*: - Acquisition Phase (and before): 2 AWT's. Application Phase (and after): 1 AWT's FastExport: - All.

This description is for the single target table case which is the most common.

The above explained parameters can be analyzed & tuned accordingly to achieve the expected performance on the Teradata system. Also need to have some maintenance/ House keeping activities in place to avoid the performance implications due to some physical data parameters like data skew, less cylinder space etc.

AIX Tips

2011-02-28T12:16:00.001+05:30

Adjusting the AIX kernel parameters:

Unlike many other UNIX operating systems, AIX does not provide the ability to directly configure most kernel parameters. Instead, the AIX kernel dynamically allocates and reallocates resources as they are needed (up to a predefined limit).

For calendar server installations, the only kernel parameter that should be altered is maxuproc (maximum number of processes per user-ID), which can be modified via SMIT (AIX's menu-based system administration utility) or the command line utility chdev.

To see a complete list of the parameters and their current values, run the lsattr command:

% /etc/lsattr -E -l sys0

To see the current values assigned to a particular kernel parameter, use the lsattr command with the parameter name. For example:

% /etc/lsattr -E -l sys0 -a maxuproc

To change the value of the maxuproc parameter, use the chdev command. For example:

% /etc/chdev -l sys0 -a maxuproc = 200

As stated above, the AIX kernel dynamically allocates and reallocates resources as they are needed up to a predefined limit. The known limits of the parameters are as follows:

Table C 8 • AIX kernel parameters

Kernel Parameter Description                                                                              Upper Limit

soft file limit per process                                                                                                 2000

hard file limit per process                                                                                                2000

max. number of concurrent processes per user-id:                                 configurable, cannot exceed 131,072

max. number of concurrent processes on the system                                                      131,072

max. number of open files                                                                                               200000

max. number of message queue identifiers                                                                          4096

max. number of message headers unlimited;                                                    8192 per message queue.

max. number of semaphore identifiers                                                                              4096

max. number of semaphores unlimited;                                                            65535 per semaphore id.

The practical limits of the server, defined as the maximum number of concurrent users on a system, are effectively determined by the upper limits of the maximum number of open files. Assuming there are no other activity or resource requirements on the system, the maximum number of concurrent users (lck_users) under AIX is approximately 10000*20+60=200060.

At least as of version 5.1, it should not be necessary to do any special configuration for such parameters as SHMMAX, as it appears this is configured to allow all memory to be used as shared memory. That is the sort of configuration commonly used for other databases such as DB/2.

It may, however, be necessary to modify the global ulimit information in /etc/security/limits, as the default hard limits for file sizes (fsize) and numbers of files (nofiles) may be too low.

(ii) Reset an Unknown Root Password:

The following procedure describes how to recover access to root privileges when the system's root password is unavailable or unknown. The following procedure requires some system downtime. If possible, schedule your downtime when it least impacts your workload to protect yourself from a possible loss of data or functionality.

1. Insert the product media for the same version and level as the current installation into the appropriate drive.

2. Power on the machine.

3. When the screen of icons appears, or when you hear a double beep, press the F1 key repeatedly until the System Management Services menu appears.

4. Select Multiboot.

5. Select Install From.

6. Select the device that holds the product media and then select Install.

7. Select the AIX version icon.

8. Define your current system as the system console by pressing the F1 key and then press Enter.

9. Select the number of your preferred language and press Enter.

10. Choose Start Maintenance Mode for System Recovery by typing 3 and press Enter.

11. Select Access a Root Volume Group. A message displays explaining that you will not be able to return to the Installation menus without rebooting if you change the root volume group at this point.

12. Type 0 and press Enter.

13. Type the number of the appropriate volume group from the list and press Enter.

14. Select Access this Volume Group and start a shell by typing 1 and press Enter.

15. At the # (number sign) prompt, type the passwd command at the command line prompt to reset the root password. For example:

16. # passwd

17. Changing password for "root"

18. root's New password:

Enter the new password again:

19. To write everything from the buffer to the hard disk and reboot the system, type the following:

sync;sync;sync;reboot

When the login screen appears, the password you set in step 15 should now permit access to root privileges.

(iii) Steps for breaking the mirror and replacing the failed HDD:

unmirrorvg workvg hdisk7

reducevg workvg hdisk7

rmdev -l hdisk7 -d

chpv –c --- if the VG is rootvg to clear boot record before unmirroing

Replace the failed disk drive with a new one, and name it hdisk7 by executing

the following commands:

extendvg workvg hdisk7

mirrorvg workvg

Rootvg mirroring:

When the rootvg mirroring performed:

1. Run the bosboot

The bosboot command

Access Memory)

required to customize

2. Run the bootlist

The bosboot command

does not update

access memory).

To Migrate Particular logical volume to some other location :

migratepv -l loglv00 hdisk1 hdisk0

Command to sync the ODM :

synclvodm -v rootvg

Weblogic Server Health checking Script

2010-09-26T01:09:00.001+05:30

# Modify classpath to as per the weblocic server location in your environment
# The script takes 3 parameters
# The first parameter is the admin url
# The second parameter is the Username of admin server
# The third parameter is the password of admin server

CLASSPATH="/usr/java/lib/tools.jar:/apps/weblogic/server/lib/weblogic_sp.jar:/apps/weblogic/server/lib/weblogic.jar:/apps/weblogic/server/lib/ojdbc14.jar:"
export CLASSPATH
PATH="/apps/weblogic/server/bin:/usr/java/jre/bin:/usr/java/bin:/osmf/mgmt/condir/cft/BT/BTOPUT/bin/SSOL2:/usr/bin:/usr/ucb:/etc::/osmf/mgmt/condir/cft/cft/filexe:/osmf/mgmt/condir/cft/cft/filusr:/osmf/mgmt/condir/cft/cft/filexit:/osmf/mgmt/condir/cft/cft/filapi:/u01/app/oracle/product/8.1.7/bin:/apps/abinitio/bin"
export PATH
java weblogic.Admin -adminurl $1 -username $2 -password $3 GET -pretty -type ServerRuntime
java weblogic.Admin -adminurl $1 -username $2 -password $3 GET -pretty -type JVMRuntime
java weblogic.Admin -adminurl $1 -username $2 -password $3 GET -pretty -type JDBCConnectionPoolRuntime
java weblogic.Admin -adminurl $1 -username $2 -password $3 GET -pretty -type ExecuteQueueRuntime

Script to restart the weblogic server using crontab in unix without any manual intervention

2010-09-26T01:06:00.003+05:30

Problem Description

This script can be used to restart the weblogic server using crontab in unix without any manual intervention.

Solution Description

This script can be used to restart the weblogic server without any manual intervention. It also sends email to the list of people once the restart is done.We can add few more manipulations if you need before restarting the weblogic server. This can be enabled through crontab in the unix.

Code Snippet

#! /bin/ksh

#This script is used for restarting the Weblogic server using CRONTAB

#-------------------------------------------------------------------

# Mail list is used to email the list of users once the restart is done

Maillist=" "

# Mail file is the file which will get emailed to the mail list users

Mailfile= " "

#This is the temporary log file where stopWeblogic.sh result will be stored

> Stop_log

# Stopping the Server

echo "Stopping Weblogic server"

. ./stopWebLogic.sh > Stop_log 2>& 1

sleep 90

echo "Confirming that the Weblogic server stopped"

. ./stopWebLogic.sh >> Stop_log 2>& 1

# Searching for the exception when you shut down the weblogic server for the second time

egrep '[Destination unreachable | "OPUSServer" was shutdown successfully]' Stop_log > /dev/null

if [ $? -eq 0 ]

then

echo "Weblogic server stopped successfully"

# Starting the Server

echo "Trying to start Weblogic server"

#. ./startWebLogic.sh > Start_log 2>& 1 &

. ./startWebLogic.sh > /dev/null

#Getting the process id for startWebLogic.sh process

id=$!

i=0

until [ $i -gt 1 ]

sleep 90

grep "Server started in RUNNING mode" /prod/opus/OPUS_CONS1/logs/wl-domain.log > /dev/null

if [ $? -eq 0 ]

then

echo "Weblogic server started successfully"

#Killing the process startWebLogic.sh using process id

#kill -9 $id

#echo "Killed startweb"

break

i=`expr $i + 1`

done

if [ $i -ne 2 ]

then

echo "Weblogic server restarted successfully"

mailx -s "Weblogic_server name Restarted Succesfully" -r $Maillist < $Mailfile

else

echo "Weblogic server failed to start"

mailx -s "Weblogic_server name failed to start" -r $Maillist

else

echo "Failed to stop Weblogic server"

mailx -s "Failed to stop Weblogic server" -r $Maillist

echo "Completed the script"

exit

SCRIPT TO MONITOR MEMORY USAGE BY DATABASE SESSIONS

2010-09-26T00:59:00.002+05:30

=============================================================================
SCRIPT TO MONITOR MEMORY USAGE BY DATABASE SESSIONS
=============================================================================
REM Created: 21/march/2003
REM Last update: 28/may/2003
REM
REM NAME
REM ====
REM MEMORY.sql
REM
REM
REM DISCLAIMER
REM ==========
REM This script is provided for educational purposes only. It is NOT supported by
REM Oracle World Wide Technical Support. The script has been tested and appears
REM to work as intended. However, you should always test any script before
REM relying on it.
REM
REM PURPOSE
REM =======
REM Retrieves PGA and UGA statistics for users and background processes sessions.
REM
REM EXECUTION ENVIRONMENT
REM =====================
REM SQL*Plus
REM
REM ACCESS PRIVILEGES
REM =================
REM Select on V$SESSTAT, V$SESSION, V$BGPROCESS, V$PROCESS and V$INSTANCE.
REM
REM USAGE
REM =====
REM $ sqlplus "/ as sysdba" @MEMORY
REM
REM INSTRUCTIONS
REM ============
REM Call MEMORY.sql from SQL*Plus, connected as any DBA user.
REM Press whenever you want to refresh information.
REM You can change the ordered column and the statistics shown by choosing from the menu.
REM Spool files named MEMORY_YYYYMMDD_HH24MISS.lst will be generated in the current directory.
REM Every time you refresh screen, a new spool file is created, with a snapshot of the statistics shown.
REM These snapshot files may be uploaded to Oracle Support Services for future reference, if needed.
REM
REM REFERENCES
REM ==========
REM "Oracle Reference" - Online Documentation
REM
REM SAMPLE OUTPUT
REM =============
REM :::::::::::::::::::::::::::::::::: PROGRAM GLOBAL AREA statistics :::::::::::::::::::::::::::::::::
REM
REM SESSION PID/THREAD CURRENT SIZE MAXIMUM SIZE
REM -------------------------------------------------- ---------- ------------------ ------------------
REM 9 - SYS: myworkstation 2258 10.59 MB 10.59 MB
REM 3 - LGWR: testserver 2246 5.71 MB 5.71 MB
REM 2 - DBW0: testserver 2244 2.67 MB 2.67 MB
REM ...
REM
REM :::::::::::::::::::::::::::::::::::: USER GLOBAL AREA statistics ::::::::::::::::::::::::::::::::::
REM
REM SESSION PID/THREAD CURRENT SIZE MAXIMUM SIZE
REM -------------------------------------------------- ---------- ------------------ ------------------
REM 9 - SYS: myworkstation 2258 0.29 MB 0.30 MB
REM 5 - SMON: testserver 2250 0.06 MB 0.06 MB
REM 4 - CKPT: testserver 2248 0.05 MB 0.05 MB
REM ...
REM
REM SCRIPT BODY
REM ===========

spool MEMORY_YYYYMMDD_HH24MISS.log

REM Starting script execution
CLE SCR
PROMPT .
PROMPT . ======== SCRIPT TO MONITOR MEMORY USAGE BY DATABASE SESSIONS ========
PROMPT .

REM Setting environment variables
SET LINESIZE 200
SET PAGESIZE 500
SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
SET TRIMSPOOL ON
COL "SESSION" FORMAT A50
COL "PID/THREAD" FORMAT A10
COL " CURRENT SIZE" FORMAT A18
COL " MAXIMUM SIZE" FORMAT A18

REM Setting user variables values
SET TERMOUT OFF
DEFINE sort_order = 3
DEFINE show_pga = 'ON'
DEFINE show_uga = 'ON'
COL sort_column NEW_VALUE sort_order
COL pga_column NEW_VALUE show_pga
COL uga_column NEW_VALUE show_uga
COL snap_column NEW_VALUE snap_time
SELECT nvl(:sort_choice, 3) "SORT_COLUMN"
FROM dual
/
SELECT nvl(:pga_choice, 'ON') "PGA_COLUMN"
FROM dual
/
SELECT nvl(:uga_choice, 'ON') "UGA_COLUMN"
FROM dual
/
SELECT to_char(sysdate, 'YYYYMMDD_HH24MISS') "SNAP_COLUMN"
FROM dual
/

REM Creating new snapshot spool file
SPOOL MEMORY_&snap_time

REM Showing PGA statistics for each session and background process
SET TERMOUT &show_pga
PROMPT
PROMPT :::::::::::::::::::::::::::::::::: PROGRAM GLOBAL AREA statistics :::::::::::::::::::::::::::::::::
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
ORDER BY &sort_order DESC
/

REM Showing UGA statistics for each session and background process
SET TERMOUT &show_uga
PROMPT
PROMPT :::::::::::::::::::::::::::::::::::: USER GLOBAL AREA statistics ::::::::::::::::::::::::::::::::::
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session uga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session uga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
ORDER BY &sort_order DESC
/

REM Showing sort information
SET TERMOUT ON
PROMPT
BEGIN
IF (&sort_order = 1) THEN
dbms_output.put_line('Ordered by SESSION');
ELSIF (&sort_order = 2) THEN
dbms_output.put_line('Ordered by PID/THREAD');
ELSIF (&sort_order = 3) THEN
dbms_output.put_line('Ordered by CURRENT SIZE');
ELSIF (&sort_order = 4) THEN
dbms_output.put_line('Ordered by MAXIMUM SIZE');
END IF;
END;
/

REM Closing current snapshot spool file
SPOOL OFF

REM Showing the menu and getting sort order and information viewing choice
PROMPT
PROMPT Choose the column you want to sort: == OR == You can choose which information to see:
PROMPT ... 1. Order by SESSION ... 5. PGA and UGA statistics (default)
PROMPT ... 2. Order by PID/THREAD ... 6. PGA statistics only
PROMPT ... 3. Order by CURRENT SIZE (default) ... 7. UGA statistics only
PROMPT ... 4. Order by MAXIMUM SIZE
PROMPT
ACCEPT choice NUMBER PROMPT 'Enter the number of your choice or press to refresh information: '
VAR sort_choice NUMBER
VAR pga_choice CHAR(3)
VAR uga_choice CHAR(3)
BEGIN
IF (&choice = 1 OR &choice = 2 OR &choice = 3 OR &choice = 4) THEN
:sort_choice := &choice;
:pga_choice := '&show_pga';
:uga_choice := '&show_uga';
ELSIF (&choice = 5) THEN
:sort_choice := &sort_order;
:pga_choice := 'ON';
:uga_choice := 'ON';
ELSIF (&choice = 6) THEN
:sort_choice := &sort_order;
:pga_choice := 'ON';
:uga_choice := 'OFF';
ELSIF (&choice = 7) THEN
:sort_choice := &sort_order;
:pga_choice := 'OFF';
:uga_choice := 'ON';
ELSE
:sort_choice := &sort_order;
:pga_choice := '&show_pga';
:uga_choice := '&show_uga';
END IF;
END;
/

REM Finishing script execution
PROMPT Type "@MEMORY" and press
SET FEEDBACK ON
SET VERIFY ON
SET SERVEROUTPUT OFF
SET TRIMSPOOL OFF

spool off

REM =============
REM END OF SCRIPT
REM =============

Database Health Monitoring Scripts in Oracle.

2010-09-26T00:56:00.004+05:30

#################################################################################
---------------------- Database health Monitor ---------------------------------
#################################################################################

break on today
column today noprint new_value tdate
select substr(to_char(sysdate,'fmMonth DD, YYYY HH:MI:SS P.M.'),1,35) today from dual;
column name noprint new_value DB
select name from v$database;

set heading on
set feedback off

spool health.lst
--spool Windows Path or Unix Path\health.lst

prompt **********************************************************
prompt ***** Database Information *****
prompt **********************************************************
ttitle left "DATABASE: "DB" (AS OF: "tdate")"
select name, created, log_mode from v$database;
prompt
prompt **********************************************************
ttitle off

rem -------------------------------------------------------------
rem DB Block Buffer - Hit Ratio
rem -------------------------------------------------------------

clear breaks
clear computes

set heading off
set feedback off
set termout off

create table dbbb (
PR number,
CG number,
dbbg number);

insert into DBBB values (0,0,0);
UPDATE dbbb
set dbbg =
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'db block gets');
UPDATE dbbb
set cg =
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'consistent gets');
UPDATE dbbb
set pr =
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical reads');

set heading on
set termout on

column "Physical Reads" format 99,999,999,999
column "Consistent Gets" format 99,999,999,999
column "DB Block Gets" format 99,999,999,999
column "Percent (Above 70% ?)" format 999.99

TTitle left "***** Database: "db", DB Block Buffers ( As of: "tdate" ) *****" skip 1 - left "Percent = (100*(1-(Physical Reads/(Consistent Gets + DB Block Gets))))" skip 2
SELECT pr "Physical Reads",
cg "Consistent Gets",
dbbg "DB Block Gets",
(100*(1-(PR/(CG+dbbg)))) "Percent (Above 70% ?)"
from dbbb;

set heading off
set termout off
drop table dbbb;
ttitle off
clear breaks
clear computes
set heading on
set termout on

rem -------------------------------------------------------------
rem Shared Pool Size - Gets and Misses
rem -------------------------------------------------------------

set line 150

column "Executions" format 999,999,990
column "Cache Misses Executing" format 999,999,990
column "Data Dictionary Gets" format 999,999,999
column "Get Misses" format 999,999,999

ttitle left skip 1 - left "********** Shared Pool Size (Execution Misses) **********" skip 1

select sum(pins) "Executions",
sum(reloads) "Cache Misses Executing",
(sum(reloads)/sum(pins)*100) "% Ratio (STAY UNDER 1%)"
from v$librarycache;

ttitle left "********** Shared Pool Size (Dictionary Gets) **********" skip 1

select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Get Misses",
100*(sum(getmisses)/sum(gets)) "% Ratio (STAY UNDER 12%)"
from v$rowcache;

ttitle off

rem -------------------------------------------------------------
rem Log Buffer
rem -------------------------------------------------------------

ttitle left "********** Log Buffers **********" skip 1

select substr(name,1,25) Name,
substr(value,1,15) "VALUE (Near 0?)"
from v$sysstat
where name = 'redo log space requests';

ttitle off

rem -------------------------------------------------------------
rem Latch Contention
rem -------------------------------------------------------------

ttitle left "********** Latch Information **********" skip 1

select substr(l.name,1,25) Name,
l.gets, l.misses,
l.immediate_gets, l.immediate_misses
from v$latch l, v$latchname ln
where ln.name in ('redo allocation', 'redo copy')
and ln.latch# = l.latch#;

ttitle off

rem -------------------------------------------------------------
rem Reinstates the xdbname parameter
rem -------------------------------------------------------------

column name noprint new_value xdbname
select name from v$database;

rem -------------------------------------------------------------
rem Tablespace Usage
rem -------------------------------------------------------------

set pagesize 66
set line 132

clear breaks
clear computes

column "Total Bytes" format 9,999,999,999
column "SQL Blocks" format 999,999,999
column "VMS Blocks" format 999,999,999
column "Bytes Free" format 9,999,999,999
column "Bytes Used" format 9,999,999,999
column "% Free" format 9999.999
column "% Used" format 9999.999
break on report
compute sum of "Total Bytes" on report
compute sum of "SQL Blocks" on report
compute sum of "VMS Blocks" on report
compute sum of "Bytes Free" on report
compute sum of "Bytes Used" on report
compute avg of "% Free" on report
compute avg of "% Used" on report

TTitle left "******* Database: "db", Current Tablespace Usage ( As of: "tdate" ) *******" skip 1

select substr(fs.FILE_ID,1,3) "ID#",
fs.tablespace_name,
df.bytes "Total Bytes",
df.blocks "SQL Blocks",
df.bytes/512 "VMS Blocks",
sum(fs.bytes) "Bytes Free",
(100*((sum(fs.bytes))/df.bytes)) "% Free",
df.bytes-sum(fs.bytes) "Bytes Used",
(100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
from sys.dba_data_files df, sys.dba_free_space fs
where df.file_id(+) = fs.file_id
group by fs.FILE_ID, fs.tablespace_name, df.bytes, df.blocks
order by fs.tablespace_name;

ttitle off

rem -------------------------------------------------------------
rem Disk Activity
rem -------------------------------------------------------------

column "File Total" format 99,999,990

set line 132
set pagesize 33

ttitle " ***** Database: "db", DataFile's Disk Activity (As of:" tdate " ) *****"

select substr(df.file#,1,2) "ID",
rpad(substr(name,1,52),52,'.') "File Name",
rpad(substr(phyrds,1,10),10,'.') "Phy Reads",
rpad(substr(phywrts,1,10),10,'.') "Phy Writes",
rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads",
rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes",
rpad(substr(readtim,1,9),9,'.') "Read Time",
rpad(substr(writetim,1,10),10,'.') "Write Time",
(sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim)) "File Total"
from v$filestat fs, v$datafile df
where fs.file# = df.file#
group by df.file#, df.name, phyrds, phywrts, phyblkrd,
phyblkwrt, readtim, writetim
order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc, df.name;

ttitle off

rem -------------------------------------------------------------
rem Fragmentation Need
rem -------------------------------------------------------------

set heading on
set termout on
set pagesize 66
set line 132

ttitle left " ***** Database: "db", DEFRAGMENTATION NEED, AS OF: " tdate " *****"

select substr(de.owner,1,8) "Owner",
substr(de.segment_type,1,8) "Seg Type",
substr(de.segment_name,1,35) "Table Name (Segment)",
substr(de.tablespace_name,1,20) "Tablespace Name",
count(*) "Frag NEED",
substr(df.name,1,40) "DataFile Name"
from sys.dba_extents de, v$datafile df
where de.owner <> 'SYS'
and de.file_id = df.file#
and de.segment_type = 'TABLE'
group by de.owner, de.segment_name, de.segment_type, de.tablespace_name,
df.name
having count(*) > 1
order by count(*) desc;

ttitle off

rem -------------------------------------------------------------
rem Rollback Information
rem -------------------------------------------------------------

set pagesize 66
set line 132

TTitle left "*** Database: "db", Rollback Information ( As of: " tdate " ) ***" skip 2

select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
substr(sys.dba_segments.OWNER,1,8) "Owner",
substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
substr(sys.dba_segments.SEGMENT_NAME,1,17) "Rollback Name",
substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
sys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;

ttitle off

TTitle left " " skip 2 - left "*** Database: "db", Rollback Status ( As of: " tdate " ) ***" skip 2

select substr(V$rollname.NAME,1,20) "Rollback_Name",
substr(V$rollstat.EXTENTS,1,6) "EXTENT",
v$rollstat.RSSIZE, v$rollstat.WRITES,
substr(v$rollstat.XACTS,1,6) "XACTS",
v$rollstat.GETS,
substr(v$rollstat.WAITS,1,6) "WAITS",
v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
substr(v$rollstat.WRAPS,1,6) "WRAPS",
substr(v$rollstat.EXTENDS,1,6) "EXTEND",
v$rollstat.AVESHRINK,
v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;

ttitle off

TTitle left " " skip 2 - left "*** Database: "db", Rollback Segment Mapping ( As of: " tdate " ) ***" skip 2

select r.name Rollback_Name,
p.pid Oracle_PID,
p.spid VMS_PID,
nvl(p.username,'NO TRANSACTION') Transaction,
p.terminal Terminal
from v$lock l, v$process p, v$rollname r
where l.addr = p.addr(+)
and trunc(l.id1(+)/65536)=r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
order by r.name;

ttitle off

rem -------------------------------------------------------------
rem Current Users
rem -------------------------------------------------------------

set line 132
set pagesize 66

TTitle left "*** Database: "db", Current User Info (As of: "tdate") ***" skip 1

select substr(s.username,1,15) "DB UserName",
s.osuser "OS UserName",
substr(a.object,1,25) Object,
a.type , s.command,
substr(s.machine,1,15) Machine,
substr(s.terminal,1,15) Terminal, s.process, s.status
from v$access a, v$session s
where a.sid = s.sid
order by username;

TTitle left "*** Database: "db", Current Sessions (As of: "tdate") ***" skip 1

select substr(username,1,15) "DB UserName",
substr(osuser,1,15) "OS UserName",
substr(command,1,3) CMD,
substr(machine,1,10) Machine,
terminal, process, status,
substr(program,1,50) "OS Program Name"
from v$session
where type = 'USER'
order by username;

TTitle left "*** Database: "db", Current Access (As of: "tdate") ***" skip 1

select sid,
substr(owner,1,15) Owner,
substr(object,1,25) Object,
type
from v$access
order by owner;

rem -------------------------------------------------------------
rem -------------------------------------------------------------

spool off
set feedback on

PL/SQL Procedure to write records to excel

2010-09-26T00:40:00.001+05:30

-- This PL/SQL procedure will write records to an Excel File.
-- We will use UTL_FILE ( You need an init.ora parameter set for this to work correctly).
-- UTL_FILE allows us to write a file on the server.

create or replace
package spread_sheet as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
sheet_file in utl_file.file_type,
sheet_query in varchar2,
sheet_parm_names in owaSylkArray default owaSylkArray(),
sheet_parm_values in owaSylkArray default owaSylkArray(),
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' );
--
procedure show(
sheet_filein utl_file.file_type,
sheet_cursor in integer,
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' );
--
end spread_sheet;
/
show error

create or replace
package body spread_sheet as
--
g_cvalue varchar2(32767);
g_desc_t dbms_sql.desc_tab;

type vc_arr is table of varchar2(2000) index by binary_integer;
g_lengths vc_arr;
g_sums vc_arr;
--
--

g_file utl_file.file_type;

procedure p( sheet_str in varchar2 )
is
begin
utl_file.put_line( g_file, sheet_str );
exception
when others then null;
end;

function build_cursor(
q in varchar2,
n in owaSylkArray,
v in owaSylkArray ) return integer is
c integer := dbms_sql.open_cursor;
i number := 1;
begin
dbms_sql.parse (c, q, dbms_sql.native);
loop
dbms_sql.bind_variable( c, n(i), v(i) );
i := i + 1;
end loop;
return c;
exception
when others then
return c;
end build_cursor;
--
--
function str_html ( line in varchar2 ) return varchar2 is
x varchar2(32767) := null;
in_html boolean := FALSE;
s varchar2(1);
begin
if line is null then
return line;
end if;
for i in 1 .. length( line ) loop
s := substr( line, i, 1 );
if in_html then
if s = '>' then
in_html := FALSE;
end if;
else
if s = '<' then
in_html := TRUE;
end if;
end if;
if not in_html and s != '>' then
x := x || s;
end if;
end loop;
return x;
end str_html;
--
function ite( b boolean,
t varchar2,
f varchar2 ) return varchar2 is
begin
if b then
return t;
else
return f;
end if;
end ite;
--
procedure print_comment( sheet_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || sheet_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray )
is
l_title varchar2(2000);
begin
p( 'ID;ORACLE' );
print_comment( 'Fonts' );
p( 'P;F' || font || ';M200' );
p( 'P;F' || font || ';M200;SB' );
p( 'P;F' || font || ';M200;SUB' );
--
print_comment( 'Global Formatting' );
p( 'F;C1;FG0R;SM1' ||
ite( upper(grid)='YES', '', ';G' ) ||
ite( upper(col_heading)='YES', '', ';H' ) );
for i in 1 .. g_desc_t.count loop
p( 'F;C' || to_char(i+1) || ';FG0R;SM0' );
end loop;
--
print_comment( 'Title Row' );
p( 'F;R1;FG0C;SM2' );
for i in 1 .. g_desc_t.count loop
g_lengths(i) := g_desc_t(i).col_name_len;
g_sums(i) := 0;
begin
l_title := titles(i);
exception
when others then
l_title := g_desc_t(i).col_name;
end;
if i = 1 then
p( 'C;Y1;X2;K"' || l_title || '"' );
else
p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
end if;
end loop;
end print_heading;
--
function print_rows(
c in integer,
max_rows in number,
sum_columns in owaSylkArray,
show_null_as in varchar2,
strisheet_html in varchar2 ) return number is
row_cnt number := 0;
line varchar2(32767) := null;
n number;
begin
loop
exit when ( row_cnt >= max_rows or
dbms_sql.fetch_rows( c ) <= 0 );
row_cnt := row_cnt + 1;
print_comment( 'Row ' || row_cnt );
--
p( 'C;Y' || to_char(row_cnt+2) );

for i in 1 .. g_desc_t.count loop
dbms_sql.column_value( c, i, g_cvalue );
g_cvalue := translate( g_cvalue,
chr(10)||chr(9)||';', ' ' );
g_cvalue := ite( upper( strisheet_html ) = 'YES',
str_html( g_cvalue ),
g_cvalue );
g_lengths(i) := greatest( nvl(length(g_cvalue),
nvl(length(show_null_as),0)),
g_lengths(i) );
line := 'C;X' || to_char(i+1);
line := line || ';K';
begin
n := to_number( g_cvalue );
if upper( sum_columns(i)) = 'Y' then
g_sums(i) := g_sums(i) + nvl(n,0);
end if;
exception
when others then
n := null;
end;
line := line ||
ite( n is null,
ite( g_cvalue is null,
'"'||show_null_as||
'"', '"'||g_cvalue||'"' ),
n );
p( line );
end loop;
--
end loop;
return row_cnt;
end print_rows;
--
procedure print_sums(
sum_columns in owaSylkArray,
row_cnt in number ) is
begin
if sum_columns.count = 0 then
return;
end if;
--
print_comment( 'Totals Row' );
p( 'C;Y' || to_char(row_cnt + 4) );
p( 'C;X1;K"Totals:"' );
--
for i in 1 .. g_desc_t.count loop
begin
if upper(sum_columns(i)) = 'Y' then
p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' ||
to_char(row_cnt+2) || 'C)' );
end if;
exception
when others then
null;
end;
end loop;
end print_sums;
--
procedure print_widths( widths owaSylkArray ) is
begin
print_comment( 'Format Column Widths' );
p( 'F;W1 1 7' );
for i in 1 .. g_desc_t.count loop
begin
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
to_char(to_number(widths(i))) );
exception
when others then
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
greatest( g_lengths(i), length( g_sums(i) )));
end;
end loop;
p( 'E' );
end print_widths;
procedure show(
sheet_filein utl_file.file_type,
sheet_cursor in integer,
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
g_file := sheet_file;
dbms_sql.describe_columns( sheet_cursor, l_col_cnt, g_desc_t );
--
for i in 1 .. g_desc_t.count loop
dbms_sql.define_column( sheet_cursor, i, g_cvalue, 32765);
end loop;
--
print_heading( sheet_font_name,
sheet_show_grid,
sheet_show_col_headers,
sheet_titles );
l_status := dbms_sql.execute( sheet_cursor );
l_row_cnt := print_rows(
sheet_cursor,
sheet_max_rows,
sheet_sum_column,
sheet_show_null_as,
sheet_strisheet_html );
print_sums( sheet_sum_column, l_row_cnt );
print_widths( sheet_widths );
end show;
--
procedure show(
sheet_filein utl_file.file_type,
sheet_query in varchar2,
sheet_parm_names in owaSylkArray default owaSylkArray(),
sheet_parm_values in owaSylkArray default owaSylkArray(),
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' ) is
begin
show( sheet_file => sheet_file,
sheet_cursor => build_cursor( sheet_query,
sheet_parm_names,
sheet_parm_values ),
sheet_sum_column => sheet_sum_column,
sheet_max_rows => sheet_max_rows,
sheet_show_null_as => sheet_show_null_as,
sheet_show_grid => sheet_show_grid,
sheet_show_col_headers => sheet_show_col_headers,
sheet_font_name => sheet_font_name,
sheet_widths => sheet_widths,
sheet_titles => sheet_titles,
sheet_strisheet_html => sheet_strisheet_html );
end show;
--
end spread_sheet;
/
show error
declare
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\temp\', 'emp2.slk', 'w',32000 );

dbms_sql.parse( l_cursor,
'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = ''MANAGER'' ' ||
'and sal > 2000',
dbms_sql.native );

spread_sheet.show(
sheet_file => output ,
sheet_cursor => l_cursor,
sheet_sum_column =>
spread_sheet.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
sheet_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;

Password Encrypt Query in Oracle

2010-09-26T00:38:00.001+05:30

CREATE OR REPLACE PACKAGE ENCRY_PASS AS
FUNCTION encrypt( Str VARCHAR2 ) RETURN RAW;
FUNCTION decrypt( xCrypt VARCHAR2 ) RETURN VARCHAR2;
END ENCRY_PASS;
/

CREATE OR REPLACE PACKAGE BODY ENCRY_PASS AS
crypt_raw RAW(2000);
crypt_str VARCHAR(2000);

-- Encrypt the string --
FUNCTION encrypt( Str VARCHAR2 ) RETURN RAW AS

l INTEGER := LENGTH(str);
i INTEGER;
padblock RAW(2000);
Cle RAW(8) := UTL_RAW.CAST_TO_RAW('frankzap');

BEGIN
i := 8-MOD(l,8);
padblock := utl_raw.cast_to_raw(str||RPAD(CHR(i),i,CHR(i)));

dbms_obfuscation_toolkit.DESEncrypt(
input => padblock,
KEY => Cle,
encrypted_data => crypt_raw );
RETURN crypt_raw ;
END;

-- Decrypt the string --
FUNCTION decrypt( xCrypt VARCHAR2 ) RETURN VARCHAR2 AS
l NUMBER;
Cle RAW(8) := UTL_RAW.CAST_TO_RAW('frankzap');
crypt_raw RAW(2000) := utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(xCrypt)) ;
BEGIN
dbms_obfuscation_toolkit.DESDecrypt(
input => xCrypt,
KEY => Cle,
decrypted_data => crypt_raw );
crypt_str := utl_raw.cast_to_varchar2(crypt_raw);
l := LENGTH(crypt_str);
crypt_str := RPAD(crypt_str,l-ASCII(SUBSTR(crypt_str,l)));
RETURN crypt_str;
END;
END ENCRY_PASS;
/

Locked Query Script in Oracle 10g

2010-09-26T00:35:00.002+05:30

SELECT OBJECT_NAME, SQL_TEXT,SID, OSUSER, SCHEMANAME, RECURSIVE, LAST_CALL_ET/(60) MINUTES,
(case type when 'TM' then 'DML/TABLE LOCK'
when 'TX' then 'TRANSACTION LOCK'
when 'MR' then 'MEDIA RECOVERY'
when 'ST' then 'DISK SPACE TRANSACTION'
when 'UL' then 'USER SUPPLIED'
else 'LOCKED'
end) LOCK_TYPE,
decode(lmode,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) LOCK_MODE,
decode(REQUEST,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) REQUEST, CTIME/(60) LOCKED_SINCE_MIN
FROM V$TRANSACTION JOIN V$SESSION ON SADDR = SES_ADDR JOIN V$LOCK USING (SID)
JOIN V$SQLAREA ON ADDRESS = PREV_SQL_ADDR AND HASH_VALUE = PREV_HASH_VALUE
JOIN V$LOCKED_OBJECT ON SID=SESSION_ID
JOIN DBA_OBJECTS USING (OBJECT_ID)

Locked Query Script in Oracle 9i

2010-09-26T00:34:00.002+05:30

SELECT OBJECT_NAME, SID, OSUSER, SCHEMANAME, RECURSIVE, LAST_CALL_ET/(60) MINUTES,
(case type when 'TM' then 'DML/TABLE LOCK'
when 'TX' then 'TRANSACTION LOCK'
when 'MR' then 'MEDIA RECOVERY'
when 'ST' then 'DISK SPACE TRANSACTION'
when 'UL' then 'USER SUPPLIED'
else 'LOCKED'
end) LOCK_TYPE,
decode(lmode,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) LOCK_MODE,
decode(REQUEST,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) REQUEST,
CTIME/(60) LOCKED_SINCE_MIN,SQL_TEXT
FROM V$TRANSACTION JOIN V$SESSION ON SADDR = SES_ADDR JOIN V$LOCK USING (SID)
JOIN V$SQLAREA ON HASH_VALUE = SQL_HASH_VALUE AND SQL_ADDRESS = ADDRESS
JOIN V$LOCKED_OBJECT ON SID=SESSION_ID
JOIN DBA_OBJECTS USING (OBJECT_ID)

Detailed Lock Scripts in Oracle

2010-09-26T00:32:00.002+05:30

prompt "Identify the blocking session"
prompt "================================================="

select * from v$lock ;

prompt ""
prompt ""
prompt ""
prompt "Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1."
prompt "Further, you can tell which session is being blocked by comparing the values in ID1 and ID2."
prompt "The blocked session will have the same values in ID1 and ID2 as the blocking session,"
prompt "and, since it is requesting a lock it's unable to get, it will have REQUEST > 0."
prompt ""
prompt ""

prompt "================================================="
prompt "Detailed View"
prompt "================================================="

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

prompt ""
prompt ""
prompt "================================================="
prompt "Readable View"
prompt "================================================="

select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' ) is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;