How to enable Operating System authentication on Oracle XE

Leave a reply

OS authentication is not enabled by default in Oracle XE. To allow the use of OPS$ users, open sql plus (All Programs -> Oracle Database 10g Express Edition -> Run SQL Command Line) and run the following command:
alter system set os_authent_prefix=OPS$ scope=spfile;

Restart the DB:
shutdown immediate
Startup

To Create the OS user:

CREATE USER “OPS$DOMAIN\DOMAINUSER” PROFILE

“DEFAULT” IDENTIFIED EXTERNALLY DEFAULT

TABLESPACE “TABLESPACENAME”

TEMPORARY TABLESPACE “TEMPTABLESPACENAME” ACCOUNT

UNLOCK;

You can grant  the appropriate roles just like any other user

GRANT CONNECT, RESOURCE  TO

“OPS$ DOMAIN\DOMAINUSER “;

What to do when ldf file grows

3 Replies

If you noticed that the free space on your hard drive is shrinking and you found that the log LDF file is taking most of it (and growing) don’t panic.
it is possible that the reason is that your recovery mode is is set to full.
to change this:

1) Right click on the db (in SQL server Managment Studio) and click properties.

2) Navigate to the option tab and make Make sure that the recovery model is set to simple.

If a full recovery mode is needed:

Issue regular backups (in the transaction log shipping tab)
and if Auto Shrink is enabled, the file will eventually shrink (though not immediatly)

If you need to shrink the file immediately:

•    Open MS SQL Server Management Studio, connect to Database Engine

•    Select New Query and type:  backup log <db_name> with truncate_only

(this is discontinued in SQL SERVER 2008 – Microsoft recommends using Simple mode instead)
•    Execute this query (press F5)
•    Right click on Database name and navigate to Tasks->Shrink ->Files:

A quick and dirty fix for ORA-01008

Leave a reply

If a “ORA-01008 Not All Variables Bound” error is displayed, this could probably be related to oracle bug 5254759.
This could be solved by upgrading to 10.2.04 or installing the interim patch.
However if you need a quick and diry workaround you can try to eneable closing cursor on the dsn:

1) Open the ODBC administrator (control panel -> administrative tools -> ODBC)
2) Choose your DSN and click “Configure”
3) In the application tab, check the “enable closing cursor” checkbox.

It is important to note that this solution may not always work and it will probably result in a notable performance reduction but it may help until the patchset is applied.

Nothing to see (Yet)

1 Reply

The purpose of DB tricks is to serve as an online repository of Data Base  tips I encounter during my work. Sometimes, it can be very frustrating to search (again) for a solution for a problem you already solved.  No matter how important it may look now, chances are you will not remember anything about it when it will happen again after few months. This is the rational behind DB Tricks. In a way, it will be a place where I could store and share solutions and save myself (and maybe you) some research time in the future.

So, in the following days many things are going to change here.  As they say in techcrunch, ” we are in private alpha”. Fisrt post is coming soon.