SQL - Forcing and Error Log Switch

If your SQL error log is getting too big and you need to switch to a new log file, try this:

Exec sp_cycle_errorlog

ORACLE - No Table Owner in Indexfile

I was trying to use an indexfile generated from Oracle's "imp" command to create the indexes on a database after the table rows were imported but I ran into a problem with the way the table names were specified in the "on" clause of the index create statements. They were in the form tablename instead of owner.tablename. This was giving me:

ORA-00942: table or view does not exist

The default indexfile has "connect username;" statements in it, but these statements prompt a request for a password, so that wouldn't work for me.

In order to solve this problem I set the "current_schema" in the indexfile script to the correct table owner whenever the owner changed for a group of index creation statements.

Here's the statement that I used:

alter session set current_schema = owner;

I had to manually put the statements in the script, but it was a lot faster for me to do it that way than trying to come up with a script to do the same thing.

I'll meet you further on up the road.

Jim

ORACLE - Clearing Out the Recycle Bin in 10g

I was trying to delete a group of tables in a 10.2.0.3 database and I noticed that the table count didn't decrease after the drop statements. Then I remembered the flashback recovery feature and figured I'd better check the names of the existing tables to see if they had been logically moved to a recycle bin that I'd heard about.

Well my next question was, "How do I clear the recycle bin?", and here's what I found:

PURGE RECYCLEBIN;

This clears everything in your recycle bin.

If you want to bypass the recycle bin in the original drop, you can use a statement like:

DROP TABLE RECYCLETEST PURGE;

For more tips about Flashback table and the recycle bin go to:
http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html

I'll meet you further on up the road.

Jim Hill

ORACLE - Splitting Long Lines in an Indexfile

====================================================
vi commands
====================================================

------------------------
Issue
------------------------
I have an indexfile from Oracle that has some really long lines in it that I need to break into several lines. Whenever I see ", PARTITION" in a line, I need to break the line there.

For example:

BEFORE:

characters words, PARTITION characters words, PARTITION characters words, PARTITION

AFTER:

characters words
, PARTITION characters words
, PARTITION characters words
, PARTITION

------------------------------
vi Solution
------------------------------

:%s/, PARTITION/^M, PARTITION/g

(NOTE: I got the ^M by holding down Ctrl-v and pressing 'm'. This will insert the newline.)

---

I'll meet you further on up the road.
Jim

SQL - Finding the SQL Server Version

SELECT SERVERPROPERTY('ProductLevel')


OR

select @@version

(NOTE: The output on the above command was too big to see in my remote terminal session, so I cut and pasted it to notepad to see it all.)

I confirmed that I was on SQL2000 SP3 (or SP3a) when I found version number 8.00.760; but how do I know if I have SP3 or SP3a? Here's how you find out...

Distinguishing between SQL2000 SP3 and SP3a

To figure out whether you have SP3 or SP3a installed, you have to figure out the version number of the Net-Library file, ssnetlib.dll. This file can be found in one of these locations:

Default instance: C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ssnetlib.dll

Named instance: C:\Program Files\Microsoft SQL Server\MSSQL$\Binn\ssnetlib.dll

Once I found the file I was able to just mouse over it and a box that included the version popped up. You might have to right-click on the file, select "properties", and then click on the version tab.

If you see version 2000.80.760.0, you have SP3; but if the version is 2000.80.766.0, you have SP3a.

I have version SP3a, so I'm happy.

Here are some links to some more good info about SQL Server versions:

http://rage.against.org/DeterminingSQLServerVersion

This one has a nice chart of versions for SQL 2005 and SQL 2000:

http://soulsolutions.com.au/Blog/tabid/73/EntryID/18/Default.aspx

I'll meet you further on up the road.

Jim Hill

Oracle - Temporarily Changing a User's Password

Another interesting, undocumented command syntax is the "alter user ... using values ..."

To temporarily change a user's password then set it back, take a note of the PASSWORD field in DBA_USERS. Change the password and then set it back to what it was:

SQL> select username, password from dba_users where username='BUBBA';

USERNAME PASSWORD
------------------------------ ------------------------------
BUBBA 3104BC5BB78B55BE

SQL> alter user bubba identified by bubba;
User altered.

SQL> conn bubba/bubba
Connected.

SQL> conn / as sysdba
Connected.

SQL> select username, password from dba_users where username='BUBBA';

USERNAME PASSWORD
------------------------------ ------------------------------
BUBBA 7D2D7383FC9288B7

SQL> alter user bubba identified by values '3104BC5BB78B55BE';
User altered.

SQL> select username, password from dba_users where username='BUBBA';

USERNAME PASSWORD
------------------------------ ------------------------------
BUBBA 3104BC5BB78B55BE

Oracle - Getting “ORA-01031: insufficient privileges” when trying to logon as sysdba

Here's a little background:

I set up 2 new databases and I couldn't logon as sysdba without providing the password. I was trying:

sqlplus "/as sysdba"

It gave me this:

ORA-01031: insufficient privileges

Instead I had to do this:

sqlplus sys/password as sysdba

This problem was also preventing "rman" from working as it should.

Here's a full description of the error:

ORA-01031: insufficient privileges

Connect Internal has been issued
with no password. For local connections the user is NOT in the DBA group
as compiled into the 'oracle' executable.
For remote connections you must always supply a
password.

This error can also
occur after a successful connect internal/password
if there REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE
in
the init.ora file.

The problem that I was having turned out to be related to the fact that the wrong OSDBA group got compiled into the Oracle software.

I found the following instructions on the Oracle Metalink website and the described process fixed my problems.

OSDBA & OSOPER Groups on Unix/Linux

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the same for all databases running from a given ORACLE_HOME directory.The actual groups being used for OSDBA and OSOPER can be checked thus:

cd $ORACLE_HOME/rdbms/lib

cat config.[cs]

The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.

The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.

If you wish to change the OSDBA or OSOPER groups this file needs to be modifiedeither directly or using the installer. Eg: For an OSDBA group of 'mygroup' If your platform has config.c (this is the case for HP-UX, Compaq Tru64 Unixware and Linux):

Change: #define SS_DBA_GRP "dba"

to: #define SS_DBA_GRP "mygroup"

If your platform has config.s:

Due to the way different compilers under different architectures generate assembler code, it's not possible to give a universal rule. Here are some examples:

Sun SPARC Solaris:

------------------

Change both ocurrences of

.ascii "dba\0" to .ascii "mygroup\0"

IBM AIX/Intel Solaris:

----------------------

Change both ocurrences of

.string "dba" to .string "mygroup"

To effect any changes to the groups and to be sure you are using the groupsdefined in this file relink the Oracle executable.Be sure to shutdown all databases before relinking:

Eg: mv config.o config.o.orig

make -f ins_rdbms.mk ioracle

(Note config.o will be re-created by make because of dependencies automatically)

For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:

- Be compiled into the Oracle executable

- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being used)

- It CANNOT be the group called 'daemon'

Note: The commands above are examples and may vary between platforms.

Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER roles using group names of the form 'ORA_sid_OSDBA'. This functionality has not been implemented on Unix.

+++

I'll see you further on up the road.

Jim

Oracle - Dealing with a Full Flash Recovery Area

Finding the current status of the flash_recovery_area:

COL name FORMAT A32 HEADING 'File Name'
COL spc_lmt_mb FORMAT 99999.99 HEADING 'SpaceLimit(MB)'
COL spc_usd_mb FORMAT 99999.99 HEADING 'SpaceUsed(MB)'
COL spc_rcl_mb FORMAT 99999.99 HEADING 'ReclmSpace(MB)'
COL number_of_files FORMAT 99999 HEADING 'Files'

SELECT name ,space_limit /(1024*1024) spc_lmt_mb ,space_used /(1024*1024) spc_usd_mb ,space_reclaimable /(1024*1024) spc_rcl_mb ,number_of_files FROM v$recovery_file_dest;

+++

Increasing the space allocated to the Flash Recovery Area:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = '6G' SCOPE=BOTH SID='*';

+++

Clearing up the archive logs from the flash recovery area:

NOTE: You will need to take a full backup after doing the following...

1) Delete the ones that aren't needed.
2) Connect to RMAN
a) CROSSCHECK ARCHIVELOG ALL;
b) DELETE EXPIRED ARCHIVELOG ALL;

---

I'll meet you further on up the road.

Jim Hill

SQL Server - Clearing/Emptying a Datafile and Removing It

If you have more than one datafile and you'd like to get rid of one, you can clear the data out and remove the file. Here's how:

-----------------------------------------
Moving Data Out of a Datafile
-----------------------------------------

DBCC SHRINKFILE ('[logicalFileName]', EMPTYFILE )

-----------------------------------------
Monitoring the Move of Data
-----------------------------------------

use PADBCC showfilestats

------------------------------
Removing the Datafile
------------------------------

use master
ALTER DATABASE PA REMOVE FILE "[LogicalFileName]"

SQL Server - Space Being Used

---------------------------
For Datafiles
---------------------------

use [databaseName]
DBCC showfilestats

An "Extent" in SQL Server is 64 K. So the amount of space in megabytes allocated to Data file can be calculated by using the following formula:

Space Allocated for Data = TotalExtents * 64.0 / 1024.0

And the amount of space in megabytes used by Data can be calculated using the following formula:

Space Used for Data = UsedExtents * 64.0 / 1024.0

---------------------------

For Logfiles
---------------------------

DBCC sqlperf(logspace)

Transaction Log Space Used = Log Size (MB) * Log Space Used (%).

Creating a Backup Copy of a Table in SQL 2005

I used the following sql to create a backup copy of a table in SQL Server 2005:

select *
into tablename_bkup
from tablename

Wasted

NOTE: If you are completely reloading data warehouse tables in an Oracle database then you should truncate the tables first instead of deleting the rows because just deleting the rows doesn't adjust the high water mark and your table can just keep expanding and expanding.

Here are some suggestions for finding out if a table is wasting space:

The following script came from http://www.dbasupport.com/oracle/scripts/ and it will show you the allocated, used and unused space for a particular object.

set echo off
set serveroutput on
set verify off
accept v_user prompt 'Schema: '
accept v_object prompt 'Object: '
accept v_object_type prompt 'Object Type: '
declare
VAR1 number;
VAR2 number;
VAR3 number;
VAR4 number;
VAR5 number;
VAR6 number;
VAR7 number;
VAR8 number;
vtable varchar2(30);

begin
dbms_space.unused_space(upper('&v_user'),upper('&v_object'),upper('&v_object_type'),VAR1,VAR2,VAR3,VAR4,V

AR5,VAR6,VAR7);
VAR2 := (VAR2)/1024/1024;
VAR4 := (VAR4)/1024/1024;
VAR8 := (VAR2-VAR4);
dbms_output.put_line('OBJECT_NAME = ' upper('&v_object'));
dbms_output.put_line('------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = 'VAR1);
dbms_output.put_line('TOTAL_MBYTES = 'VAR2);
dbms_output.put_line('UNUSED_BLOCKS = 'VAR3);
dbms_output.put_line('UNUSED_MBYTES = 'VAR4);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = 'VAR5);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = 'VAR6);
dbms_output.put_line('LAST_USED_BLOCK = 'VAR7);
dbms_output.put_line('TOTAL_USED_MBYTES = 'VAR8);
end;
/
set verify on
set echo on

+++

If you have enough space in the current tablespace, you can clear up the wasted space like this:

alter table table_name move;

This will invalidate the indexes and you will have to rebuild them like this:

alter index index_name rebuild;

I'll meet you further on up the road.

Jim

Using Special Characters in Oracle Passwords

As you can see from the test SQL below, I was testing the special character in the middle of the password.

======================================
Test SQL
======================================

create user bubba identified by "abc~123";
grant create session to bubba;
connect bubba/abc~123;

drop user bubba;

========================================
These special characters worked
========================================
!
%
^
@ (requires quotes around password when entering password in connect statement)
$
*
(
)
_
+
~
`
-
=
[
{
]
}
\
|
;
:
'
,
<
.
>
/ (requires quotes around password when entering password in connect statement)


=============================================
I could not find a way to make these 2 work
=============================================
&
"



####

SQL> drop user bubba;

User dropped.

SQL> create user bubba identified by "abc'123"
2 ;

User created.

SQL> grant create session to bubba;

Grant succeeded.

SQL> connect bubba/abc'123

Who is Jim Hill?

I'm your everyday DBA working in Birmingham, AL. I've been doing Oracle work since 1989, but it still amazes me how much there is to learn. I think I'm more of a learning machine than an expert DBA. In fact, my mind seems to have learned to forget at a much quicker rate in order to accomodate the new info that is coming in the front door.

I initially started my database career with Sybase so the concepts behind SQL Server aren't new to me, but I've actually only been working with SQL consistently for less than a year. I'm really a newby in that environment.

As I run into useful ideas and techniques for Oracle and SQL, I'll be sharing them here so come back often to look for updates.

I'll see you further on up the road.