Oracle docs note this about ORA-01000:
ORA-01000 maximum open cursors exceeded
Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.
Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.
Question:
I keep encountering ORA-01000 and can't even create a table
Answer:
First, you should try increasing your OPEN_CURSORS and take a look at the application to see if/why cursors are staying open. Here is an example from a forum:
ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;
Furthermore, to resolve ORA-01000, try to close whatever cursors are no longer in use, raise the OPEN_CURSORS parameter within your initialization file, and restart Oracle.
MOSC also offers information regarding ORA-01000 when it occurs with inserts in PL/SQL or SQL*Plus. ORA-01000 is thrown because of a maxed limit of open cursors in an execution or user session. Working around ORA-01000 can be done on the database level by setting the init.ora parameter, OPEN_CURSORS, which states the maximum cursors allowed. Note that the OPEN_CURSORS are defaulted at 50 which may not be high enough, causing ORA-01000, and that each operating system determines the appropriate value.
Resolving ORA-01000 try setting this OPEN_CURSORS to a higher number. Keep in mind when resolving ORA-01000 in this way that changing this value in the proper way should not alter the system performance, but will probably require more memory for cursor storing.
Thanks - http://www.dba-oracle.com/sf_ora_01000_maximum_open_cursors_exceeded.htm
Monday, May 30, 2011
Tuesday, May 10, 2011
Data Pump Export (expdp) and Data Pump Import(impdp)
Data Pump Export (expdp) and Data Pump Import(impdp)
Introduction
Oracle introduced the Data Pump in Oracle Database 10g Release 1. This new oracle technology enables very high transfer of data from one database to another. The oracle Data Pump provides two utilities namely:
Data Pump Export which is invoked with the expdp command.
Data Pump Import which is invoked with the impdp command.
The above two utilities have similar look and feel with the pre-Oracle 10g import and export utilities (imp and exp) but are completely separate. This means that dump files generated by the original export utility (exp) cannot be imported by the new data pump import utility (impdp) and vice-versa.
Data Pump Export (expdp) and Data Pump Import (impdp) are server-based rather than client-based as is the case for the original export (exp) and import (imp). Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped to a file system directory be specified in the invocation of the data pump import or export.
It for this reason and for convenience that a directory object be created before using the data pump export or import utilities. For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:
SQL> create directory expdp_dir as '/u01/backup/exports';
then grant read and write permissions to the users who will be performing the data pump export and import.
SQL> grant read,write on directory expdp_dir to system, user1, user2, user3;
Invoking Data Pump Export
You can invoke the data pump export using a command line. Export parameters can be specified directly in the command line.
Full Export Mode
A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role. Example :
$ expdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.og
Schema Export Mode
The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go. Optionally, you can include the system privilege grants as well.
$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe
Table Export Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own schema. You can only specify tables in the same schema.
$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments
Invoking Data Pump Import
The data pump import can be invoked in the command line. The export parameters can be specified directly in the command line.
Full Import Mode The full import mode loads the entire contents of the source (export) dump file to the target database. However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.
$ impdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og
Schema Import Mode
The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.
$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe
Table Import Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.
$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments
Thanks - http://wiki.oracle.com/page/Data+Pump+Export+%28expdp%29+and+Data+Pump+Import%28impdp%29
Introduction
Oracle introduced the Data Pump in Oracle Database 10g Release 1. This new oracle technology enables very high transfer of data from one database to another. The oracle Data Pump provides two utilities namely:
Data Pump Export which is invoked with the expdp command.
Data Pump Import which is invoked with the impdp command.
The above two utilities have similar look and feel with the pre-Oracle 10g import and export utilities (imp and exp) but are completely separate. This means that dump files generated by the original export utility (exp) cannot be imported by the new data pump import utility (impdp) and vice-versa.
Data Pump Export (expdp) and Data Pump Import (impdp) are server-based rather than client-based as is the case for the original export (exp) and import (imp). Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped to a file system directory be specified in the invocation of the data pump import or export.
It for this reason and for convenience that a directory object be created before using the data pump export or import utilities. For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:
SQL> create directory expdp_dir as '/u01/backup/exports';
then grant read and write permissions to the users who will be performing the data pump export and import.
SQL> grant read,write on directory expdp_dir to system, user1, user2, user3;
Invoking Data Pump Export
You can invoke the data pump export using a command line. Export parameters can be specified directly in the command line.
Full Export Mode
A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role. Example :
$ expdp system/
Schema Export Mode
The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go. Optionally, you can include the system privilege grants as well.
$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe
Table Export Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own schema. You can only specify tables in the same schema.
$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments
Invoking Data Pump Import
The data pump import can be invoked in the command line. The export parameters can be specified directly in the command line.
Full Import Mode The full import mode loads the entire contents of the source (export) dump file to the target database. However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.
$ impdp system/
Schema Import Mode
The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.
$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe
Table Import Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.
$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments
Thanks - http://wiki.oracle.com/page/Data+Pump+Export+%28expdp%29+and+Data+Pump+Import%28impdp%29
Wednesday, May 4, 2011
Using AUTO_INCREMENT in MYSQL
Using AUTO_INCREMENT in MYSQL
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.
You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.
Use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.
Note
For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.
If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.
To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
Regards-
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.
You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.
Use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.
Note
For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.
If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.
To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
Regards-
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
ORA-12638: Credential retrieval failed error
ORA-12638: Credential retrieval failed error -
Oracle 11gR2
Cause: The authentication service failed to retrieve the credentials of a user.
Solution:
Please check the sqlnet.ora file (ORACLE_HOME\\NETWORK\ADMIN\). Change NTS to NONE and this should work.
Before – SQLNET.AUTHENTICATION_SERVICES= (NTS)
After - SQLNET.AUTHENTICATION_SERVICES= (NONE)
Thanks
Oracle 11gR2
Cause: The authentication service failed to retrieve the credentials of a user.
Solution:
Please check the sqlnet.ora file (ORACLE_HOME\\NETWORK\ADMIN\). Change NTS to NONE and this should work.
Before – SQLNET.AUTHENTICATION_SERVICES= (NTS)
After - SQLNET.AUTHENTICATION_SERVICES= (NONE)
Thanks
Subscribe to:
Posts (Atom)