Category Archives: Oracle

Can you move a table between schemas ?

Many reasons can raise the need to change a table owner or to move a table from one user (schema) to another . regardless of reason, one would expect that Oracle will provide an easy way to move a table to a different schema.

well, the short answer is NO . there is no direct method to change the table owner.

If you try something like

RENAME TABLE_NAME TO new_schema.TABLE_NAME ;

Oracle Will return ORA-01765: specifying table’s owner name is not allowed
While you can workaround this error by omitting the schema name. However, in our case, this is exactly what we want to do so it is still a problem.

Similarly, the ALTER TABLE command will not do the work as well.

ALTER TABLE source_schema.TABLE_NAME RENAME TO new_schema.TABLE_NAME;

While a different error message (ORA-14047: ALTER TABLE|INDEX RENAME May Not Be Combined With Other Operations. ) is returned when trying to use the alter table command to move the table to a different schema, the result is the same.

Another option to move a table to a different user is to simply copy the table using

CREATE TABLE new_schema.TABLE_NAME AS SELECT  * FROM  source_schema.TABLE_NAME;

but this will generate a lot of I/O , redo logs and might take a lot of time so it can be a solution only for small tables.
Likewise, Using export and import will require downtime.

While there is no direct method to move a table to a different schema, you can use
One of the less known advantages of partitioning to do the task. the ability to exchange partitions between tables across schema can help us achieve the goal.
Following is the process to move a table from one schema to another:
1) Create a partitioned copy of the table – you do this by using

SELECT dbms_metadata.get_ddl('TABLE','TABLE2MOVE') FROM dual;

if you want just the plain DDL without all the storage definition etc. you can run

     BEGIN
       dbms_metadata.set_transform_param dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',FALSE);
      END;

remember to add a partition clause at the end of the ddl

 CREATE TABLE "NEW_SCHEMA"."TABLE2MOVE" 
   (	"ID" NUMBER, 
	"TEXT" VARCHAR2(100)
   ) 
   partition BY hash (id) (partition p1);

2) Exchange the partition between the source and the target table

ALTER TABLE NEW_SCHEMA.table2move exchange partition p1 WITH TABLE SOURCE_SCHEMA.table2move;

3) That’s it! verify it by selecting from the source and target tables

         SELECT * FROM NEW_SCHEMA.table2move;
         SELECT * FROM  SOURCE_SCHEMA.table2move;

One thing to remember is that partitioning is an extra cost option on top of the enterprise edition license. so, if you get ORA-00439: feature not enabled: Partitioning. the option is not enabled. verify it with

SELECT * FROM v$option;

monitor index usage (are my indexes being used at all?)

Indexes are the easiest solution for fast query performance improvement but the gain does not come without a cost. Indexes take up space and make inserts slower. In Addition, many times, the index is added to solve one slow query. If the query is not executed frequently, the performance improvement might not be worth the cost of making almost every insert and update slower. For example, even if the index is useful for a nightly or weekly report, the extra daily overhead might not be worth the few minutes it will save during the night.

therefore, it is important to know the if and how much the index is used. The tool for this job is index monitoring usage.

Basically the process is very simple. The first step is turning on monitoring since index monitoring usage is turned off by default. The next step is to let it run for a while. It is important to choose the monitoring period wisely as it supposed to correctly represent real life usage. After that you can turn off monitoring and query the usage view.

Turing on index monitoring for a specific index:

ALTER INDEX “YOUR-SCHMA-NAME”.”YOUR-INDEX-NAME” MONITORING USAGE;

If you get: ora-00054 resource busy and aquire with NOWAIT specified or timeout expired
This is because altering a table or an index requires an exclusive lock on the table therefore it will not work while DML operations are performed on the table

Turing on index monitoring for all indexes on all indexes for a specific schema:
declare
query_str varchar2(100);

begin

for i in ( select * from dba_indexes d where d.owner ='YOUR_SCHEMA' and index_type!='LOB' --and index_name='ASSIGNMENT_TERR'
) loop

query_str:='ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE' ;
execute immediate query_str ;

end loop;

end;
Without adding the index_type!=’LOB’ you might get ORA-22864 cannot ALTER or DROP LOB indexes which means that you can not operate directly on a system-defined LOB index. You should perform operations on the corresponding LOB column.

Turing OFF index monitoring for a specific index:
ALTER INDEX "YOUR-SCHMA-NAME"."YOUR-INDEX-NAME" NOMONITORING USAGE;

You can analyze the results by running
select * from v$object_usage

several point to take into consideration:
don’t rush to drop the index if you see that it wasn’t used. It is possible that that it is used only once a month but it can still be crucial.

If you turn off monitoring and turning it on again, previous data is deleted. Therefore it might be wise to backup v$object_usage from time to time.

After you drop an index, the indesx data is deleted from v$object_usage

A simple workaround for bug 13477790 – XML/SQL query hangs after Oracle 11

IF you will run the following query on an oracle 11 db

select xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

you might find that the sq/xml query simply hangs for many minutes after the upgrade. Many times, the following message will be displayed ” end-of-file on communication channel” . In other cases, it is possible that your session will be disconnected or might not be able to send a break message because you client will simply freeze.

This is even more problematic if this query was written on earlier versions of oracle like oracle 9. The same query will work without a problem on Oracle 9 or oracle and this can be very challenging if you encounter the problem after the actual upgrade to oracle 11.

Oracle identified this problem as a known issue bug #13477790 and released a patch with the same number. It appears that this patch solves this issue but applying a patch is not always easy on large production systems.
Another option for solving this issue is rewriting the the query using an additional xmlelement instead of the xmlforest but this can be time consuming as well.

A quick workaround for overcoming this bug without applying the 13477790 patch is adding a simple hint to the query. Adding the /*+NO_XML_QUERY_REWRITE*/ hint before the first element in the query. Will solve this issue.

select /*+NO_XML_QUERY_REWRITE*/ xmlelement("root",
(xmlelement("sub_root",
xmlforest('value' as "some_tag",
(select xmlagg(xmlelement("test",
xmlforest('code_value' as
"Code")))
from dual) as "SOME_AGG_CODES"))))

from dual

OCI-21700: object does not exist or is marked for delete (a quick reminder)

While many reasons can cause the OCI-21700: object does not exist or is marked for delete message, many times the OCI-21700 is caused by difficulty to access resources that are checked only at runtime.

The most common reason for this error is granting execute privileges on a function without granting privileges on referenced object like types for example.

Giving the same grants to the referenced objects will solve the problem.

Update: it appears that this error message can be raised when using Weblogic Data Source to run a function as well.
Sometimes, mostly after a network failure/database restart/DB schema changes, the “OCI-21700: object does not exist or is marked for delete” will be raised. The weird thing is that the same function will fail when running it with Weblogic but it can run without a problem when you run it from the sql console using the same credentials.
It appears that some Data Base actions like importing or droping objects or even unexpected network failure can “corrupt the DataSource”. If this is the case, all you have to do to solve the problem is restarting the Data Source.

Update 2: Apparently, there is a bug in Oracle 10 that raises the OCI-21700 error regardless of providing the required permission. A unexplained workaround, if all else fails, is creating the function with the same name in the schema you are connected to and retry. Even weirder, if this solves the problem, it will still work even if you drop the newly created function. Sound like Oracle voodoo but hey, if it solve the problem …

ORA-02020 – Too many database links in use (also a workaround for ORA-28267 )

If many of your queries are done over db links, you will probably encounter the ORA-02020
(too many database links in use)
error. The cause for this error according to the Oracle documentation is ” The current session has exceeded the INIT.ORA open_links maximum” . The default value for this parameter is 4 but this can easily be verified by running the following query

select * from v$parameter where name ='open_links'

In plain English, this means that the current session is using more than 4 db links.

The problem is that Oracle keeps the database link open until the session is closed. This is especially problematic when working with Weblogic data sources pools for example. Since the Weblogic server keeps its session open constantly, all queries are using the same session (or sessions). This cause the number of open links to accumulates with every query from a new remote server. The final result is that when the fourth database link is used the “ora-02020-too many database links in use” is raised. The fact that many times this message is raised when running a query that uses only one database link makes it hard to debug.

According to Oracle the solution is to Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases. However, changing the open_links parameter can not be done online and require downtime. In addition, not every DBA will be happy to increase this parameter.

Using the following query to identify the number of open database links will show that rolling back or committing does not always free up the open links.

select db_link from v$dblink;

The solution is to actively close the db link after every query using the following command.

DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')
This is equivalent to
execute immediate 'alter session close database link DB LINK NAME'

However, it is not always feasible to change existing code. A possible solution might be to create a procedure that will close all open db links and run it after or before queries that uses database links.

create or replace procedure rollback_and_close_db_links
authid current_user is
begin
rollback;
for links in (select db_link from v$dblink) loop

DBMS_SESSION.CLOSE_DATABASE_LINK (links.db_link);
end loop;
end;

UPDATE: Apperantly, this procedure can act as a workaround for the ORA-28267 Invalid NameSpace error that could happen when your SOA suite connects to a database using JDBC and try to invoke an object that connect to a remote DB. For example, weblogic connects to a function on one DB and this function uses a db link. if you add a call to “rollback_and_close_db_links ” before returning the result, you can workaround this error.