Solution for ORA - 001925 Maximum No. of enabled roles exceeded

Solution 1

Increase the value of the parameter "max_enabled_roles".

Solution 2

A user has too many roles allocated, and it doesn't need all that roles. When you CREATE ROLE the role is granted by default to the creating user, but the creating user doesn't necessarily need to be granted the role. Perhaps you could just revoke some roles from the user that has more than the maximum?

select grantee, count (*) from dba_role_privs group by grantee having count (*) > (your maximum) ;

Creating Database Link

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.

CREATE [SHARED][PUBLIC] DATABASE LINK link_name [CONNECT TO user IDENTIFIED BY password] [AUTHENTICATED BY user IDENTIFIED BY password] [USING 'connect_string']

oracle 8i Installer Not Starting

Below are the steps which will help you to Start Oracl e8i Universal Installer

1. Search for all copies of the 'symcjit.dll' file
(Start>>Search>>Files and folders)

2. Rename them to symcjit.old'.

3. Go to the install directory on your hard disk and open the 'oraparam.ini' file for editing.(file is write-protected).

4. Change the JRE_MEMORY_OPTIONS parameter to: JRE_MEMORY_OPTIONS=-nojit -ms16m -mx32m

And Now Double Click on your Setup.exe file ... Enjoy!!!

WITH Clause - Oracle SQL

Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:

• The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.

• Formally, the “WITH clause” is called subquery factoring

• The SQL “WITH clause” is used when a subquery is executed multiple times

• Also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.

We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH subquery_nameAS(the aggregation SQL statement)SELECT(query naming
subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH clause” (Note: You may find a faster execution plan by using Global Temporary tables, depending on your release of Oracle):

WITH sum_sales AS ( select /*+ materialize */
sum(quantity) all_sales from stores )number_stores AS ( select /*+
materialize */ count(*) nbr_stores from stores
)sales_by_store AS ( select /*+ materialize */ store_name,
sum(quantity) store_sales from store natural join sales
)SELECT store_nameFROM store,
sum_sales, number_stores,
sales_by_storewhere store_sales > (all_sales / nbr_stores);

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT.

Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH subquery_nameAS(the aggregation SQL statement)SELECT(query
naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

CPU APRIL 2007 Patch

Oracle has released April 2007 CPU Patch which fixes for 37 security flaws across all its products.

The fixes are delivered as part of Oracle’s quarterly patch cycle. Seven of the bugs are serious and could allow a system running the vulnerable Oracle software to be compromised remotely, the company said in a note on its website

Oracle’s “Critical Patch Update” is planned to include 13 fixes for Oracle database products, five for Application Server, 11 for E-Business Suite, and four for PeopleSoft and J.D. Edwards products, according to the company note

Setting Listener Password

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

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

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

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

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

PASSWORDS_LISTENER = imergegroup and then restart the listener.

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

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

LSNRCTL>change_password

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

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

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

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

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

LSNRCTL> set password

LSNRCTL> save_config

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

Interval Partitioning: A new partitioning strategy in Oracle Database 11g

One more features which really look interesting and I will be looking forward to implement it is, A new partitioning strategy in Oracle Database 11g, Interval partitioning extends the capabilities of the range method to define equipartitioned ranges using an interval definition. Rather than specifying individual ranges explicitly, Oracle will create any partition automatically as-needed whenever data for a partition is inserted for the very first time. Interval partitioning greatly improves the manageability of a partitioned table.

For Example:

An interval partitioned table could be defined so that Oracle creates a new partition for every month in a calendar year; a partition is then automatically created for new month as soon as the first record for that month is inserted into the database.