Cause:
PL/SQL was unable to allocate additional storage. This message normally appears with an ORA-4030 or ORA-4031 error which gives additional information. Sometimes this error can be caused by runaway programs.
Action:
1) Ensure there are no issues or bugs in your PL/SQL program which are causing excessive amounts of memory use.
2) Programatically cause unused objects to be freed (e.g. by setting them to NULL).
3) Increase the amount of shared or process memory (as appropriate) available to you.
Tuesday, September 30, 2008
Wednesday, September 24, 2008
Create Database Link
Purpose
A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement
Prerequisites
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database. Oracle Net must be installed on both the local and remote Oracle databases.
Keyword and Parameters
SHARED : Specify SHARED to use a single network connection to create a public database link that can be shared among multiple users.
PUBLIC : Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you.
DBLINK : Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database. If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name. The maximum number of database links that can be open in one session or one instance of a Real Application Clusters configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.
Restriction on Creating Database Links
You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.
CONNECT TO Clause : The CONNECT TO clause lets you enable a connection to the remote database.
CURRENT_USER Clause : Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user. When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott. However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invoker-rights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges.
user IDENTIFIED BY password : Specify the username and password used to connect to the remote database using a fixed user database link. If you omit this clause, the database link uses the username and password of each user who is connected to the database. This is called a connected user database link.
dblink_authentication : Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user. You must specify this clause when using the SHARED clause. USING 'connect string' : Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.
Examples
The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different.
Defining a Public Database Link: Example
The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:
CREATE PUBLIC DATABASE LINK remote USING 'remote';
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):
UPDATE employees@remote SET salary=salary*1.1 WHERE last_name = 'Baer';
Defining a Fixed-User Database Link: Example
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:
CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';
Once this database link is created, hr can query tables in the schema hr on the local database in this manner:
SELECT * FROM employees@local;
User hr can also use DML statements to modify data on the local database:
INSERT INTO employees@local (employee_id, last_name, email, hire_date, job_id) VALUES (999, 'Claus', 'sclaus@oracle.com', SYSDATE, 'SH_CLERK');
UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK';
DELETE FROM employees@local WHERE employee_id = 999;
Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:
SELECT * FROM oe.customers@local;
Defining a CURRENT_USER Database Link: Example
The following statement defines a current-user database link to the remote database, using the entire service name as the link name:
CREATE DATABASE LINK remote.us.oracle.com CONNECT TO CURRENT_USER USING 'remote';
The user who issues this statement must be a global user registered with the LDAP directory service. You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:
CREATE SYNONYM emp_table FOR oe.employees@remote.us.oracle.com;
CREATE DATABASE LINK ORA.ORACLE.NET
connect to hr identified by hr
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db2)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=ORA.ORACLE.NET)
(server=DEDICATED)))';
Useful Views:
A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement
Prerequisites
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database. Oracle Net must be installed on both the local and remote Oracle databases.
Keyword and Parameters
SHARED : Specify SHARED to use a single network connection to create a public database link that can be shared among multiple users.
PUBLIC : Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you.
DBLINK : Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database. If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name. The maximum number of database links that can be open in one session or one instance of a Real Application Clusters configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.
Restriction on Creating Database Links
You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.
CONNECT TO Clause : The CONNECT TO clause lets you enable a connection to the remote database.
CURRENT_USER Clause : Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user. When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott. However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invoker-rights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges.
user IDENTIFIED BY password : Specify the username and password used to connect to the remote database using a fixed user database link. If you omit this clause, the database link uses the username and password of each user who is connected to the database. This is called a connected user database link.
dblink_authentication : Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user. You must specify this clause when using the SHARED clause. USING 'connect string' : Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.
Examples
The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different.
Defining a Public Database Link: Example
The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:
CREATE PUBLIC DATABASE LINK remote USING 'remote';
This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):
UPDATE employees@remote SET salary=salary*1.1 WHERE last_name = 'Baer';
Defining a Fixed-User Database Link: Example
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:
CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';
Once this database link is created, hr can query tables in the schema hr on the local database in this manner:
SELECT * FROM employees@local;
User hr can also use DML statements to modify data on the local database:
INSERT INTO employees@local (employee_id, last_name, email, hire_date, job_id) VALUES (999, 'Claus', 'sclaus@oracle.com', SYSDATE, 'SH_CLERK');
UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK';
DELETE FROM employees@local WHERE employee_id = 999;
Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:
SELECT * FROM oe.customers@local;
Defining a CURRENT_USER Database Link: Example
The following statement defines a current-user database link to the remote database, using the entire service name as the link name:
CREATE DATABASE LINK remote.us.oracle.com CONNECT TO CURRENT_USER USING 'remote';
The user who issues this statement must be a global user registered with the LDAP directory service. You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:
CREATE SYNONYM emp_table FOR oe.employees@remote.us.oracle.com;
CREATE DATABASE LINK ORA.ORACLE.NET
connect to hr identified by hr
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db2)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=ORA.ORACLE.NET)
(server=DEDICATED)))';
Useful Views:
- DBA_DB_LINKS.
- ALL_DB_LINKS.
- USER_DB_LINKS
Sunday, September 21, 2008
Data Encryption Decryption
For security purpose, some times we need to hide data in database level. Only selected person can see the actual data. In Oracle there are some different ways to accomplish the objective.
Among those ways, Encoding and Decoding technique is describe in this article :
The idea is quite simple, we first generate a random key which is used to encode a column's data of a table. Store the key in a different table. Finally using the stored key we decode the encoded column and view actual data.
In real word, keys are stored in table and table that contains sensitive data are placed in two different schemas. But for simplicity we place both table in one schema.
Here the following steps :
1. Create an schema with necessary privilege :
SQL> Create user test2 identified by test default tablespace user temporary tablespace temp;
User Created.
SQL> Grant connect,resource to test2;
Grant succeeded.
SQL> Grant execute on dbms_crypto to test2;
Grant succeeded.
SQL> Grant execute on UTL_I18N to test2;
Grant succeeded.
Here dbms_crypto is the package used for encryption decryption and UTL_I18N is used for row to string conversion (vice versa).
2. EMP is the table which we want to protect. we will encrypt the NAME column.
CREATE TABLE EMP ( ID NUMBER (10), NAME VARCHAR2 (40) );
ALTER TABLE EMP ADD CONSTRAINT pk_emp_id PRIMARY KEY (ID);
INSERT INTO EMP VALUES (1,'HASAN');
COMMIT;
3. ENC_INFO is the table where we store the key.
CREATE TABLE ENC_INFO ( TABLE_NAME VARCHAR2 (40),
COLUMN_NAME VARCHAR2 (40), ENC_KEY raw (200) );
ALTER TABLE ENC_INFO ADD CONSTRAINT pk_enc_info_tname_colname
PRIMARY KEY (TABLE_NAME,COLUMN_NAME);
INSERT INTO ENC_INFO (TABLE_NAME,COLUMN_NAME,ENC_KEY)
VALUES ('EMP','NAME',dbms_crypto.randombytes (56));
COMMIT;
Here dbms_crypto.randombytes generate 56 random bytes which we use as encryption decryption key.
4. A simple encryption function
create or replace function enc_val
(
content in varchar2,
enc_key raw
)
return varchar2 is
l_enc_val varchar2 (2000);
l_enc_val_raw raw(2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
l_enc_val_raw := dbms_crypto.encrypt
(
UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1'),
l_mod,
enc_key
);
l_enc_val:= UTL_I18N.RAW_TO_CHAR
(l_enc_val_raw,'WE8ISO8859P1');
return l_enc_val;
end;
5. A simple decryption function
create or replace function dec_val
(
content in varchar2,
enc_key in raw
)
return varchar2
is
l_ret varchar2 (2000);
l_dec_val raw (2000);
content_raw raw (2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
content_raw := UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1');
l_dec_val := dbms_crypto.decrypt
(
content_raw,
l_mod,
enc_key
);
l_ret:= UTL_I18N.RAW_TO_CHAR
(l_dec_val,'WE8ISO8859P1');
return l_ret;
end;
6. The following procedure encrypt EMP table (NAME column)
DECLARE
l_key raw(200);
begin
select enc_key into l_key from ENC_INFO where table_name='EMP' and column_name='NAME';
dbms_output.put_line(l_key);
dbms_output.put_line( enc_val('hasan',l_key)) ;
UPDATE EMP set name=enc_val(name,l_key) where id=1;
commit;
end;
/
7. Now view the table (decrypt using stored key)
select y.id, dec_val(y.name,x.enc_key) name from ENC_INFO x, EMP y;
NB: In above article i just try to explain the mechanism so some important security measure are not shown (actually every one plan his own security measure so you need to plan it your self !)
Among those ways, Encoding and Decoding technique is describe in this article :
The idea is quite simple, we first generate a random key which is used to encode a column's data of a table. Store the key in a different table. Finally using the stored key we decode the encoded column and view actual data.
In real word, keys are stored in table and table that contains sensitive data are placed in two different schemas. But for simplicity we place both table in one schema.
Here the following steps :
1. Create an schema with necessary privilege :
SQL> Create user test2 identified by test default tablespace user temporary tablespace temp;
User Created.
SQL> Grant connect,resource to test2;
Grant succeeded.
SQL> Grant execute on dbms_crypto to test2;
Grant succeeded.
SQL> Grant execute on UTL_I18N to test2;
Grant succeeded.
Here dbms_crypto is the package used for encryption decryption and UTL_I18N is used for row to string conversion (vice versa).
2. EMP is the table which we want to protect. we will encrypt the NAME column.
CREATE TABLE EMP ( ID NUMBER (10), NAME VARCHAR2 (40) );
ALTER TABLE EMP ADD CONSTRAINT pk_emp_id PRIMARY KEY (ID);
INSERT INTO EMP VALUES (1,'HASAN');
COMMIT;
3. ENC_INFO is the table where we store the key.
CREATE TABLE ENC_INFO ( TABLE_NAME VARCHAR2 (40),
COLUMN_NAME VARCHAR2 (40), ENC_KEY raw (200) );
ALTER TABLE ENC_INFO ADD CONSTRAINT pk_enc_info_tname_colname
PRIMARY KEY (TABLE_NAME,COLUMN_NAME);
INSERT INTO ENC_INFO (TABLE_NAME,COLUMN_NAME,ENC_KEY)
VALUES ('EMP','NAME',dbms_crypto.randombytes (56));
COMMIT;
Here dbms_crypto.randombytes generate 56 random bytes which we use as encryption decryption key.
4. A simple encryption function
create or replace function enc_val
(
content in varchar2,
enc_key raw
)
return varchar2 is
l_enc_val varchar2 (2000);
l_enc_val_raw raw(2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
l_enc_val_raw := dbms_crypto.encrypt
(
UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1'),
l_mod,
enc_key
);
l_enc_val:= UTL_I18N.RAW_TO_CHAR
(l_enc_val_raw,'WE8ISO8859P1');
return l_enc_val;
end;
5. A simple decryption function
create or replace function dec_val
(
content in varchar2,
enc_key in raw
)
return varchar2
is
l_ret varchar2 (2000);
l_dec_val raw (2000);
content_raw raw (2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
content_raw := UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1');
l_dec_val := dbms_crypto.decrypt
(
content_raw,
l_mod,
enc_key
);
l_ret:= UTL_I18N.RAW_TO_CHAR
(l_dec_val,'WE8ISO8859P1');
return l_ret;
end;
6. The following procedure encrypt EMP table (NAME column)
DECLARE
l_key raw(200);
begin
select enc_key into l_key from ENC_INFO where table_name='EMP' and column_name='NAME';
dbms_output.put_line(l_key);
dbms_output.put_line( enc_val('hasan',l_key)) ;
UPDATE EMP set name=enc_val(name,l_key) where id=1;
commit;
end;
/
7. Now view the table (decrypt using stored key)
select y.id, dec_val(y.name,x.enc_key) name from ENC_INFO x, EMP y;
NB: In above article i just try to explain the mechanism so some important security measure are not shown (actually every one plan his own security measure so you need to plan it your self !)
Thursday, September 18, 2008
ora-04031-unable to allocate 8196 bytes of shared memory
Message:
ORA-04031: unable to allocate string bytes of shared memory ("string" ,"string" ,"string" ,"string")
Cause:
More shared memory is needed than was allocated in the shared pool.
Action:
If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".
ORA-04031: unable to allocate string bytes of shared memory ("string" ,"string" ,"string" ,"string")
Cause:
More shared memory is needed than was allocated in the shared pool.
Action:
If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".
Oracle Invalid objects : identify and repair
Oracle support complex and interconnected object structure and changing one object can cause some other database objects to become "invalid".
The following sql query will display a list of Oracle invalid objects:
WHERE status != 'VALID' order by owner, object_type ;
You can also recompile Oracle invalid objects to make them valid. Oracle invalid objects sometimes have dependencies, so it may be necessary to run the recompile process according to the dependencies.
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM dba_objects where status = 'INVALID' and owner='SCOTT';
Tuesday, September 16, 2008
Using QUERY parameter in expdp/impdp
In data pump export import we can use SQl query to filter exported/imported data.
The parameter we use for this is 'QUERY'. But parameter QUERY requer proper formating (quotes)
Incorrect usage of single or double quotes for the QUERY parameter can result FOLLOWING errors:
ORA-39001: invalid argument value
ORA-00933: SQL command not properly ended
LRM-00111: no closing quote for value ''
ORA-06502: PL/SQL: numeric or value error
1. USING 'QUERY' in Parameter file
The preferred method of using QUERY parameter is in a parameter file. Put double quotes around the WHERE clause.
parameter file exp_emp.par contains,
TABLES:emp_salary
DUMPFILE=dump_dir
QUERY= emp_salary:"WHERE emp_id IN (SELECT id from emp
where join_date> to_date('2004-01-01','YYYY-MM-DD')
AND dept='SALSE')"
2. USING 'QUERY' parameter on Command line
Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue
$ expdp query=employees:"where salary>10000" tables=employees
This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):
$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp
query=employees:\"where salary\>10000\ order by salary" tables=employees
The parameter we use for this is 'QUERY'. But parameter QUERY requer proper formating (quotes)
Incorrect usage of single or double quotes for the QUERY parameter can result FOLLOWING errors:
ORA-39001: invalid argument value
ORA-00933: SQL command not properly ended
LRM-00111: no closing quote for value ''
ORA-06502: PL/SQL: numeric or value error
1. USING 'QUERY' in Parameter file
The preferred method of using QUERY parameter is in a parameter file. Put double quotes around the WHERE clause.
parameter file exp_emp.par contains,
TABLES:emp_salary
DUMPFILE=dump_dir
QUERY= emp_salary:"WHERE emp_id IN (SELECT id from emp
where join_date> to_date('2004-01-01','YYYY-MM-DD')
AND dept='SALSE')"
2. USING 'QUERY' parameter on Command line
Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue
$ expdp query=employees:"where salary>10000" tables=employees
This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):
$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp
query=employees:\"where salary\>10000\ order by salary" tables=employees
Sunday, September 14, 2008
Create a control file
Syntax:
CREATE CONTROLFILE [REUSE] [SET] DATABASE database
LOGFILE [GROUP int] filespec
[RESETLOGS | NORESETLOGS]
DATAFILE filespec options [CHARACTER SET charset]
Options:
MAXDATAFILES int
MAXLOGFILES int
MAXLOGMEMBERS int
MAXLOGHISTORY int
MAXINSTANCES int
ARCHIVELOG | NOARCHIVELOG
FORCE LOGGING
Several LOGFILE and/or DATAFILEs can be specified at once if separated with commas. Do not include in the DATAFILE clause any datafiles in temporary or read-only tablespaces. You can add these to the database later.
Use this command to re-create a control file only if:
* All copies of your existing control files have been lost through media failure.
* You want to change the name of the database.
* You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.
Oracle recommend that you perform a full backup of all database files before using this statement. (see the Oracle User-Managed Backup and Recovery Guide)
An alternative to CREATE CONTROLFILE is
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
this generates a new SQL script that may be used to re-create the controlfile.
Specify REUSE to indicate that existing control files identified by the initialization parameter CONTROL_FILES can be reused and overwritten.
Use SET DATABASE to change the name of the database.
Use the logfile_clause to specify ALL the redo log files for the database.
Specify RESETLOGS if you want Oracle to ignore the contents of the files listed in the LOGFILE clause. The log files do not have to exist but each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter. Oracle will assign all online redo log file groups to thread 1 and will enable this thread for public use by any instance. You must then open the database using ALTER DATABASE RESETLOGS.
NORESETLOGS will use all files in the LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups. Oracle will reassign the redo log file groups to reenabled threads as previously assigned.
The FORCE LOGGING clause will put the database into FORCE LOGGING mode after control file creation.
Related Commands:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Related Views:
GLOBAL_NAME
DBA_DATA_FILES
V$CONTROLFILE
V$DATABASE
V$DATAFILE
V$LOG
V$LOGFILE
CREATE CONTROLFILE [REUSE] [SET] DATABASE database
LOGFILE [GROUP int] filespec
[RESETLOGS | NORESETLOGS]
DATAFILE filespec options [CHARACTER SET charset]
Options:
MAXDATAFILES int
MAXLOGFILES int
MAXLOGMEMBERS int
MAXLOGHISTORY int
MAXINSTANCES int
ARCHIVELOG | NOARCHIVELOG
FORCE LOGGING
Several LOGFILE and/or DATAFILEs can be specified at once if separated with commas. Do not include in the DATAFILE clause any datafiles in temporary or read-only tablespaces. You can add these to the database later.
Use this command to re-create a control file only if:
* All copies of your existing control files have been lost through media failure.
* You want to change the name of the database.
* You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.
Oracle recommend that you perform a full backup of all database files before using this statement. (see the Oracle User-Managed Backup and Recovery Guide)
An alternative to CREATE CONTROLFILE is
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
this generates a new SQL script that may be used to re-create the controlfile.
Specify REUSE to indicate that existing control files identified by the initialization parameter CONTROL_FILES can be reused and overwritten.
Use SET DATABASE to change the name of the database.
Use the logfile_clause to specify ALL the redo log files for the database.
Specify RESETLOGS if you want Oracle to ignore the contents of the files listed in the LOGFILE clause. The log files do not have to exist but each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter. Oracle will assign all online redo log file groups to thread 1 and will enable this thread for public use by any instance. You must then open the database using ALTER DATABASE RESETLOGS.
NORESETLOGS will use all files in the LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups. Oracle will reassign the redo log file groups to reenabled threads as previously assigned.
The FORCE LOGGING clause will put the database into FORCE LOGGING mode after control file creation.
Related Commands:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Related Views:
GLOBAL_NAME
DBA_DATA_FILES
V$CONTROLFILE
V$DATABASE
V$DATAFILE
V$LOG
V$LOGFILE
Thursday, September 11, 2008
ENABLE AUTOTTRACE
Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.
ora816 SamSQL :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
To Enable autotrace, please do the following:
1.Run plustrce.sql through SYS schema if it did not run before as
ON NT :- @$ORACLE_HOME\sqlplus\admin\plustrce.sql
ON UNIX :- @$ORACLE_HOME/sqlplus/admin/plustrce.sql
2.Grant PLUSTRACE to
Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table ,UTLXPLAN.sql have to be in user’s schema.
ON NT :- @$ORACLE_HOME\rdbms\admin\utlxplan.sql
ON UNIX :- @$ORACLE_HOME/rdbms/admin/utlxplan.sql
User can use the AUTOTRACE options as follows:
A. SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.
B. SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path.
C. SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.
D. SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
E. SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.
ora816 SamSQL :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
To Enable autotrace, please do the following:
1.Run plustrce.sql through SYS schema if it did not run before as
ON NT :- @$ORACLE_HOME\sqlplus\admin\plustrce.sql
ON UNIX :- @$ORACLE_HOME/sqlplus/admin/plustrce.sql
2.Grant PLUSTRACE to
Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table ,UTLXPLAN.sql have to be in user’s schema.
ON NT :- @$ORACLE_HOME\rdbms\admin\utlxplan.sql
ON UNIX :- @$ORACLE_HOME/rdbms/admin/utlxplan.sql
User can use the AUTOTRACE options as follows:
A. SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.
B. SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path.
C. SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.
D. SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
E. SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.
Monday, September 8, 2008
The Clustering Factor Of Index
The clustering factor is a number which represent the degree to which data is randomly distributed in a table. In simple terms it is the number of “block switches” while reading a table using an index.
Bad clustering factor
Suppose the first index entry (from left of index) points to the first data block and second index entry points to second data block. So while making index range scan or full index scan, optimizer have to switch between blocks and have to revisit the same block more than once because rows are scatter. So the number of times optimizer will make these switches is actually termed as “Clustering factor”.
Good clustering factor
In an event of index range scan, optimizer will not have to jump to next data block as most of the index entries points to same data block. This helps significantly in reducing the cost of your SELECT statements. Clustering factor is stored in data dictionary and can be viewed from dba_indexes (or user_indexes)
SQL> create table sac as select * from all_objects;
Table created.
SQL> create index obj_id_indx on sac(object_id);
Index created.
SQL> select clustering_factor from user_indexes where index_name='OBJ_ID_INDX';
CLUSTERING_FACTOR
-----------------
545
SQL> select count(*) from sac;
COUNT(*)
----------
38956
SQL> select blocks from user_segments where segment_name='OBJ_ID_INDX';
BLOCKS
----------
96
The above example shows that index has to jump 545 times to give you the full data had you performed full table scan using the index.
Note:
- A good Clustering Factor is equal (or near) to the values of number of blocks of table.
- A bad Clustering Factor is equal (or near) to the number of rows of table.
Myth:
- Rebuilding of index can improve the CF.
Then how to improve the CF?
- To improve the CF, it’s the table that must be rebuilt (and reordered).
- If table has multiple indexes, careful consideration needs to be given by which index to order table.
Bad clustering factor
Suppose the first index entry (from left of index) points to the first data block and second index entry points to second data block. So while making index range scan or full index scan, optimizer have to switch between blocks and have to revisit the same block more than once because rows are scatter. So the number of times optimizer will make these switches is actually termed as “Clustering factor”.
Good clustering factor
In an event of index range scan, optimizer will not have to jump to next data block as most of the index entries points to same data block. This helps significantly in reducing the cost of your SELECT statements. Clustering factor is stored in data dictionary and can be viewed from dba_indexes (or user_indexes)
SQL> create table sac as select * from all_objects;
Table created.
SQL> create index obj_id_indx on sac(object_id);
Index created.
SQL> select clustering_factor from user_indexes where index_name='OBJ_ID_INDX';
CLUSTERING_FACTOR
-----------------
545
SQL> select count(*) from sac;
COUNT(*)
----------
38956
SQL> select blocks from user_segments where segment_name='OBJ_ID_INDX';
BLOCKS
----------
96
The above example shows that index has to jump 545 times to give you the full data had you performed full table scan using the index.
Note:
- A good Clustering Factor is equal (or near) to the values of number of blocks of table.
- A bad Clustering Factor is equal (or near) to the number of rows of table.
Myth:
- Rebuilding of index can improve the CF.
Then how to improve the CF?
- To improve the CF, it’s the table that must be rebuilt (and reordered).
- If table has multiple indexes, careful consideration needs to be given by which index to order table.
Tuesday, September 2, 2008
Monitor Data Pump Job
Some times it is important to monitor long running datapump job. well, there are several ways to that :
1. Executing Interactive parameter (STATUS) in command line impdp/expdp.
2. Through datapump API
2. alternatively you can run a sql quarry :
select x.job_name,b.state,b.job_mode,b.degree
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs b
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;
1. Executing Interactive parameter (STATUS) in command line impdp/expdp.
2. Through datapump API
2. alternatively you can run a sql quarry :
select x.job_name,b.state,b.job_mode,b.degree
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs b
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;
Subscribe to:
Posts (Atom)