MYSQL User Password Modification

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

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

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

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

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

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

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)

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

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

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

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.
Powered by Blogger