Quest: 2012

Wednesday, October 31, 2012

Windows Authentication prompting for credentials

I created a ASP.Net website with Windows Authentication on IIS7.

When I access the website, IE kept prompting for my network credentials. After some digging around, I found that the issue was coming from Windows Authentication Providers.

1) Launch IIS Manger
2) Select your web application, and double click on Authentication

3) Right Click on Windows Authentication, and select Providers. ( I have Impersonate = "true" set up on my web application, that's why ASP.Net Impersonation is enabled for me)

4) Make sure that the Providers are in the order above. NTLM first and Negotiate second.
5) If the order is reversed, IE will keep prompting you for your network credentials.

Hope this helps.

Thursday, October 25, 2012

How to take a website offline

Quick and easy way is to create a simple html file, and call it app_offline.htm, and paste it to your website root directory. Note that the file has to be named as app_offline.htm

Once IIS finds this file, it stops further processing.

To make your website online, just delete this file or rename this file


<html>
<HEAD><title>Web Site Down for Maintenance</title>
</head>
<body>
<H1>Your custom message blah blah blah</H1>
<BR>

</body>
</html>

Oracle:How to create new Temp Tablespace

If you have your Oracle Temp Tablespace set to Auto Grow, you would have noticed TEMP tablespace becoming quite large

On my database , it was around 20GB and I decided to create a new Temp Tablespace and drop the old one.

Log in as SYS

Create a new Temp tablespace TEMP2

Create Temporary tablespace TEMP2 TEMPFILE 'D:\oracle\product\10.2.0\oradata\suppdb\TEMP.DBF' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

Make the new Temp tablespace as the default

Alter database default temporary tablespace Temp2;

You can verify it by the SQL below

SELECT Distinct temporary_tablespace from dba_users ;

Now time to drop the old one, You need to first verify if any existing sessions is still using your old TEMP tablespace

SELECT u.USERNAME, u.SESSION_NUM, u.SESSION_ADDR, s.sid,s.serial#,s.schemaname,s.osuser,
s.program,'Alter System Kill Session '''||s.sid||','||s.serial#||''';' Kill_Session
FROM V$SORT_USAGE u, v$session s
where u.session_addr = s.saddr;

You can either kill the sessions, are ask the corresponding users to close out their connection. Once all existing connections are closed/killed, you can drop the old temp tablespace.

Drop tablespace Temp including contents and datafiles;

Finally Rename the new tablespace from TEMP2 to TEMP

Alter tablespace TEMP2 rename to TEMP;

You can verify it by the SQL below

SELECT Distinct temporary_tablespace from dba_users ;

Tuesday, October 16, 2012

How to remove login password in Windows 8

Launch command Prompt
type netplwiz
launch the netplwiz app
Uncheck "User must enter username and password to use this computer"
you will be prompted to confirm, with your current password.

Monday, September 10, 2012

ORA-23421: job number xxx is not a job in the job queue

So, This morning I tried to clean up the jobs running in my database. We have a huge support database where there are around 90 schemas installed. Each schema has its own set of jobs running.

Obviously, this was consuming lot of resources, and we decided to remove the jobs that are not necessary.

I logged in as SYS to scan dba_jobs, and I found there are 500 jobs scheduled

select job, log_user, priv_user,schema_user,what, interval, next_date, broken
from dba_jobs ;

When I tried to remove a job, with dbms_job.job =42, using

dbms_job.remove (42), I got the error below

So, there are 2 options

Option 1
1) Find the Owner of the job from dbms_job table, using schema_user column
2) Log in as schema user and then remove the job. Obviosly, this is time consuming to remove 500 jobs

Option 2
1) Oracle provides another package called dbms_ijob. Both Spec an Body are wrapped, so you cannot find the procedures, but it works pretty similar to dbms_job

Remember to COMMIT, in the end. Otherwise, it will still show up in dba_jobs in somebody else's session.

Thursday, August 16, 2012

Rollback a commited transaction

Using Oracle's FLASHBACK command, it is possible to take any table to its previous state (even after commiting some delete statements)

1) You need to enable row movement on the table
2) You can take the table back to its previous state as of any time period

1) alter table table_name enable row movement;

FLASHBACK TABLE table_name TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' minute);
or
FLASHBACK TABLE table_name TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' second);

Thursday, August 2, 2012

ORA-00904: "OBJECT_VALUE": invalid identifier

OBJECT_VALUE is the pseudocolumn for xmltype. Its also called as sys_nc_rowinfo$.

It only works if you create a table of xmltype

Note that, when you use select * from, the colum name is called SYS_NC_ROWINFO$

Alternativley, you can get the column using object_value pseudo column.

If you create the same table in a different way, OBJECT_value will not work