Another Way to Reorgnize Table in Oracle

After inserting/deleting from a table for a long time, the table may contain much spared space that hurts the full table scan greatly. Instead of export and import, or recreate it. Move it from one tablespace to another is another way.

Here's a case I did recently. I noticed a table is slow when running full table scan, however it contains only 30K rows, and each row is not that big.

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME        MB_BYTES   BLOCKS     EXTENTS
------------------- ---------- ---------- ----------
BAD_TABLE           220        28160      99

OK, move it to another tablespace:

alter table BAD_TABLE move tablespace tb_another;

Run that check again:

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME           MB_BYTES   BLOCKS     EXTENTS
---------------------- ---------- ---------- ----------
BAD_TABLE              32         4096       47

It's much smaller. Well, you may want to move it back to its original tablespace.

Oracle Database Console Credential Failures on Windows

There is an error message in Oracle Database console:

Connection to host as user USER failed: ERROR: Wrong password for user

Usually you find this error when trying to connect to host, for example, you want to start/shutdown, or backup the database. It had confused me for a long time. Then I found an solution.

1. Provide the 'Log on as a batch job' privilege.

• Go to control panel/administrative tools
• click on "local security policy"
• click on "local policies"
• click on "user rights assignments"
• double click on "log on as a batch job"
• click on "add" and add the user(s) that you're going to use in database console.

2. Set credentials and test

• Go to the Preferences link in the database console page
• click on Preferred Credentials (link on the left menu)
• under "Target Type: Host" click on "set credentials"
• enter the OS user(s) for whom you have set "logon as a batch job" privilege
• click on "Test"

Then you should see the backup is working.

Get Month End in SQL Server

SQL Server doesn't have a function to get the month end. Here shows one I wrote:

 ALTER  function dbo.sp_getmonthend ( @inputDate    DATETIME )
 /*
   This function returns the month end of a specific date.
 */
 RETURNS DATETIME
 BEGIN
     DECLARE @outputDate        DATETIME

     select @outputDate = CAST(YEAR(@inputDate) AS VARCHAR(4)) + '-' +
                        CAST(MONTH(@inputDate) AS VARCHAR(2)) + '-01'
     select @outputDate = dateadd( day, -1, dateadd( month, 1, @outputDate ) )
     return @outputDate
 END

Since there is 1st in every month, we forward 1 month from 1st of the month and backward a day.

Undrop table in Oracle

I don't "undrop" table a lot. So once when I tried to "undrop" a table, I got an error:

sql> undrop table test1;
SP2-0734:unknown command beginning "undrop tab..." - rest of line ignored.

Well, I soon found, it's not "undrop", instead:

SQL> flashback table test1 to before drop rename to test2;
Flashback complete.

You can rename it if the previous name has been used by others.