similar SQL/XML queries do not work the same way across oracle versions

Leave a reply

If you just upgraded your Oracle version and you see inconsistency in behavior of your old SQL/XML (SQLX) queries, you might want to check if the actual XML structure is still the same.

Specifically, there is a problem with XMLAgg alias. The fact is that if you run the same code on Oracle 9 or 10 (up to 10.2.0.3) and later versions, the same query returns different results. If you used aliasing to name an aggregated collection you will notice that the alias disappeared in later versions.

For example, if you run the following code on Oracle 9

with t as (SELECT 'Emp 1' name FROM dual UNION ALL SELECT 'Emp 2' name FROM dual UNION ALL SELECT 'Emp 3' name FROM dual) SELECT xmlelement("EMPLOYEES_DATA", (SELECT xmlagg( XMLELEMENT("EMPLOYEE_NAME", t.name) ) FROM t) as "EMPLOYEES").extract('/') from dual

the result will be :

<EMPLOYEES_DATA> <EMPLOYEE_NAME>Emp 1</EMPLOYEE_NAME> <EMPLOYEE_NAME>Emp 2</EMPLOYEE_NAME> <EMPLOYEE_NAME>Emp 3</EMPLOYEE_NAME> </EMPLOYEES_DATA>
Notice that the <EMPLOYEES> tag is missing. Oracle simply ignores the alias.

Clearly, similar queries do not work the same way across oracle versions. The problem is that it is dropping the alias silently. The query is not failing it just produce a different xml.

Unfortunately, to overcome this, a change in the code is required. You can easily add an additional XMLELEMENT.

with t as (SELECT 'Emp 1' name FROM dual UNION ALL SELECT 'Emp 2' name FROM dual UNION ALL SELECT 'Emp 3' name FROM dual) SELECT xmlelement("EMPLOYEES_DATA", xmlelement ("EMPLOYEES", (SELECT xmlagg( XMLELEMENT("EMPLOYEE_NAME", t.name) ) FROM t)) as "WILL_BE_IGNORED_ORACLE_11").extract('/') from dual;

This will produce the pre upgrade results:

<EMPLOYEES_DATA> <EMPLOYEES> <EMPLOYEE_NAME>Emp 1</EMPLOYEE_NAME> <EMPLOYEE_NAME>Emp 2</EMPLOYEE_NAME> <EMPLOYEE_NAME>Emp 3</EMPLOYEE_NAME> </EMPLOYEES> </EMPLOYEES_DATA>
Another way to use SYS_XMLAGG together with XMLFormat but this will require code change anyway so I believe that the best alternative will be to add an additional xmlelement.

How to resolve ORA-24247: network access denied by access control list (ACL)

1 Reply

If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error:

ORA-24247: network access denied by access control list (ACL)

The cause according to oracle is that

“No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.”

This error is common after an upgrade to Oracle 11. before oracle 11, using network resources via packages like utl_tcp, utl_smtp, utl_mail, utl_http, and utl_inaddr exposed the database to a serious security threat because once the user is granted with permission to use those packages there was no other limitation to connect to any computer.

Since Oracle 11, oracle introduced a fine grained access to network services using access control lists (ACL).

This new feature gave the DBA a better control on which user can connect to which computer

In order to solve ORA-24247 you will need to:

1) Create an acl (if it is not already created)

2) Add privileges to the user using the network resources

3) Assign the acl to a specific address

1) run the following query to check if an ACL exists

SELECT *

FROM dba_network_acls;

If the computer you are trying to connect to is not listed under host, you will need to create an acl:

begin

dbms_network_acl_admin.create_acl (


acl         => 'http_permissions.xml',  -- or any other name
description => 'HTTP Access',
principal   => 'SCOTT',   -- the user name trying to access the network resource
is_grant    => TRUE,
privilege   => 'connect',
start_date  => null,
end_date    => null
);
end;
/

commit;

This will create the acl and grant SCOTT the connect privilege.

2) IF the acl exists run the following query to verify the user is granted with the appropriate privilege

SELECT *

FROM dba_network_acl_privileges

where principal='SCOTT';

In order to use UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL the user will need the connect privilege

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',


principal => 'SCOTT',
is_grant  => true,
privilege => 'connect');
end;
/

commit;

If you need to resolve a host name from a host IP you will need the resolve grant as well.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',


principal => 'SCOTT',
is_grant  => true,
privilege => 'resolve');
end;
/
commit;

3) The final step is to assign the acl to a specific target

BEGIN

dbms_network_acl_admin.assign_acl (


acl        => 'http_permissions.xml',
host       => 'NETWORK ADDRESS', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/
lower_port => 80,
upper_port => 80
);
END;

It is important to note that only one ACL can be assigned to any host computer. If you assign a new acl to a target the old acl gets unassigned.

However, the old acl is not dropped. So, this could cause confusion because even if the acl was already assigned, it is possible that a new assignment overrode it.

What to do when Oracle XE is not starting after a reboot

Leave a reply

When you install Oracle Xe on a windows machine (mainly server OS’s like Windows 2003) you may notice that the database is not always starting after a reboot. This ussualy happenes when the Oracle XE was installed under a different user than the one you are currently logged in as.

It is easy to start the database by going to Start-> All Programs -> Oracle Database 10g Express Edition – Run SQL Command Line and simply type “STARTUP”.

However, if you want to automate this process you can use the StartDB.bat.

1) Navigate to your Oracle XE directory (typically under C:\oraclexe\app\oracle\product\10.2.0\server\BIN)
2) Edit the StartDB.bat file – If the Oracle Service andthe listener is started you can delete the first two lines (net start OracleXETNSListener and net start OracleServiceXE) and save the file.
3) Go to Start > All Programs > Accessories > System Tools > Scheduled Tasks and Use the Windows Scheduler to execute the batch file “When my computer Starts”.

Oradim messages are not displayed in the command prompt (you can still find the results in oradim.log under C:\oraclexe\app\oracle\product\10.2.0\server\database) so it might be wise to try the command first using the command prompt.

If you get ORA-01031: insufficient privileges when you trying to start the db (you probably will if you are logged on as different user than the user who installed Oracle XE) all you need to do is add the user to the ORA_DBA windows group.

1) Right click on My Computer and click Manage.
2) Navigate to Local Users and Groups and click on Groups.
3) Double click on the ORA_DB group and add the user you are currently logged in as.
4) Re run the oradim -startup -sid XE -starttype inst > nul 2>&1 to verify that it works.

How to grant privileges on all tables to a user

1 Reply

Sometimes you need to grant privileges to one user’s objects to another user. While granting should be done very carefully, when you have a lot of tables it is nice to have an automated script to do it.

It is important to note that the best way to do that is creating a role, granting the privileges to this role and then grant the role to the user. This way, you can fine tune the privileges and the process will be a lot faster next time.

Another important thing to note is that you should avoid the temptation to use the ”SELECT ANY TABLE” privilege. This will indeed allow the user to select any table but (and this is a big but) since it is a system privilege, it will allow him to select any table from any schema. Depends on the setting, it might even allow to user to select from the dictionary tables themselves. There is no reason to grant this privilege to someone who is not the DBA.

The easiest way to grant select on all of one account’s object to another user is to use a PL/SQL loop. This script will grant select on tables but it should be easy to adapt it to any other privilege.

begin

for i in (select * from user_tables)

loop
execute immediate ‘grant select on ‘||i.table_name||’ to a_role_created_for_this’;
end loop;
end;

In case you need a pure SQL solution, the best way to go would be to spool the results of the following query and run the script created in grant-all.txt.

SQL> set lin 150

SQL> spool c:\grant-all.txt

SQL>select ‘grant select to ‘||table_name||’ to “the new user”;’ from all_tables where owner=’the old user;

SQL> spool off

“A service specific error occurred: 2″ when trying to start dbconsole

10 Replies

When logging in to database control after some time you may get a message that some accounts’ passwords need to be changed (sysman, system etc..). A page will be displayed allowing you to change those password but if something (like timeout) happens during the page processing you will not be able to log in again to the database control and change the passwords.

usually, to solve you should restart the dbconsole by opening the command prompt and typing emctl stop dbconsole. However, you may find that the service is no longer running. Trying to start the dbconsole by typing emctl start dbconsole in the command prompt will result in the following message:

The OracleDBConsole[ServiceName] service could not be started.

A service specific error occurred: 2.

More help is available by typing NET HELPMSG 3547.

as expected, typing NET HELPMSG will not provide usefull information.

To solve this:

1) Delete and recreate the Enterprise Manager agent.

In the command prompt type> emca -deconfig dbcontrol db

even if this fails, type> emca -config dbcontrol db

You will be asked to provide the database SID, the listener port, password for SYS, DBSNMP and SYSMAN accounts, and some optional settings.

This process will run for several minutes.

2) restart the dbconsole

> emctl stop dbconsole

> emctl start dbconsole

This should solve the issue, however, if you are still unable to log in or you get the following error:

ORA-28001: the password has expired (DBD ERROR: OCISessionBegin), make sure that the SYSMAN account is unlocked

SQL> conn / as sysdba
Connected.
SQL> alter user sysman identified by [new password]
2 /

User altered.
SQL> alter user sysman account unlock;

User altered.
SQL> exit

> emctl stop dbconsole

> emctl start dbconsole

if you still have problems logging in to database control, refer to the following post

What to do when Enterprise Manager is not able to connect to the database instance.

Net configuration assistant changes are not saved on Windows 7

Leave a reply

If you are trying to use the oracle net configuration assistant in order to create tnsnames.ora entry on windows 7, you may notice that sometimes the net service name is simply not created. Even after you run through the wizard successfully you may get the famous “ORA-12154 – TNS could not resolve service name” when you actually try to use it. Another symptom would be that when you run the net configuration assistant again, the entry you just added will not be there.

This issue is caused by the change in Windows 7 default permissions. Windows 7 has the “Administrator” account disabled by default right out of the box. During the Windows 7 setup, the user is prompted for a username that has administrator rights and is in the administrator group. Running the net configuration assistant as the “administrator” user does not guarantee that this user has the administrator’s rights. This probably prevents the Network Configuration Assistant from editing the tnsnames.ora therefore the changes are not saved.

A simple solution to this issue is to run the network configuration assistant as an administrator.

Press and hold Ctrl+Shift while opening the program. Alternatively, you can navigate to the program shortcut (or the actual exe file), right click and then click on Run as administrator.
If prompted by the UAC you should click on Yes to apply permission to allow the program to run with full permission as an Administrator (You may need to provide the administrator password if you are logged in as a standard user).

“Shrink failed for Database” when attempting to shrink a data file.

Leave a reply

Sometimes, when you try to shrink a data file you may get the following message:

TITLE: Microsoft SQL Server Management Studio
——————————
Shrink failed for Database ‘Data base name’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

First, you should know that shrinking data files is not recommended and will hurt performance. Shrinking data files will cause index fragmentation. You can recreate the indexes but it will require the freed space. Shrinking will also cause fragmentation in the server’s file system which will slow it even more. In addition, since every page move is logged to the transaction log, chances are that the transaction log will claim the same space.

However, if in case you decide to shrink the database anyway (for example, after a large and permanent delete, or in case it is a test system) a common reason for this error is lack of space.
Since many times the attempt to shrink a database will come after discovering that the drive is running out of space, it would only make sense that there is not enough space for the shrinking process itself.

To verify, open the sql server error log (usually under Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG) and look for Operating system error 112(There is not enough space on the disk.).

To solve, you should clear some space to allow the process to complete. If you can not free some disk space, Try to shrink the transaction log and tempdb.

What to do when listener.log is growing.

Leave a reply

over time, the Listener.log file can grow to huge size and take a lot of disk space.
If you will try to delete it in windows you may get the following error:

—————————
Error Deleting File or Folder
—————————
Cannot delete listener: It is being used by another person or program.

Close any programs that might be using the file and try again.
—————————
OK
—————————

Linux will allow you to delete the file but it will not create a new one.

The solution to this issue is simple. following are some options:

The easiest solution would be to stop the listener process, delete the file and restart the listener service. However, stopping the listener will prevent new connections to the database and disrupt service.

A better option would be to stop logging to the listener.log file:

In the command prompt type >lsnrctl
This will open the Listener control utility. Type LSNRCTL> set log_status off
now you can navigate to the file location and delete the file using the operating system.
This will prevent the file from growing in the futre.
If you like, you can restart logging LSNRCTL> set log_status on
This will allow you to delete listener.log but nothing will be logged when the log_status is set to off.

If you want to the logging to continue. you can switch to a different logging file:
type LSNRCTL> set log_file listener2.log

The listener will now log to the new file. you can delete or move the old file.

Adding a Transaction Log at runtime to prevent it from running out of space

sometimes, when running a big transaction, the transaction log can become full or simply grow to a huge size . while you can sometimes free some space using “backup log db-name with truncate_only” and trying to shrink the DB using SQL Management studio.

The process is described in the above link but basically it is: right click on the Database name ->tasks->shrink->files and choosing the log file.
This will shrink the db if there is free space in the transaction log but it can only serve as a temporary solution.

If the transaction is still running and you are afraid that the hard drive will run out of disk space, a better solution would be to create another transaction log on a different drive. This will solve the disk space issue and might even improve performance.
To create another transaction log file using the SQL Server Management Studio:

In Some cases, the file name column might be disabled when adding a log file using the Sql Server Mangment Studio while the transaction is still runing.
In this case you can add the file manually using a query.
Open a query editor and type:

USE master

GO

ALTER DATABASE db-name

ADD LOG FILE

( NAME = addedlog,

FILENAME = ‘I:addedlog.ldf’,

SIZE = 1000MB,

– MAXSIZE = 10000MB,

FILEGROWTH = 10MB)

There are better ways to avoid the Transaction log is full situation and this should be prevented in the the first place by the code but if you got no access to it, this might be a good solution.

Possible Causes to ORA-00980 Synonym Transaltion no longer valid

Leave a reply

ORA-00980: synonym translation is no longer valid
This error usually means that the synonym used is based on a table, view, or synonym that no longer exists. in order to fix it you will need to replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym. However, this is not always simple. following are some common causes and suggested solutions.

it is possible that one of the sysnonyms is pointing to an object on a different schema

SELECT * FROM all_synonyms

this component was not installed on the new version or that the component is not in a valid state.
SELECT comp_name, version,status FROM dba_registry

object is there but you don’t have access to it

SELECT * FROM dba_synonyms WHERE owner=’YOUR USER”

add trace=y

DB Tricks

DataBase Tips and Tricks

similar SQL/XML queries do not work the same way across oracle versions

How to resolve ORA-24247: network access denied by access control list (ACL)

What to do when Oracle XE is not starting after a reboot

How to grant privileges on all tables to a user

“A service specific error occurred: 2″ when trying to start dbconsole

Net configuration assistant changes are not saved on Windows 7

“Shrink failed for Database” when attempting to shrink a data file.

What to do when listener.log is growing.

Adding a Transaction Log at runtime to prevent it from running out of space

Possible Causes to ORA-00980 Synonym Transaltion no longer valid