I'm not sure I can bring you a lot of value today, but maybe just this one small bit of info will help you discover a wealth of information. After doing Oracle and SQL Server for many years, I am now learning Sybase as well. Here's today's discovery:
What is called an "inline view" in Oracle is called a "derived table" in Sybase. Derived Tables have been available in Sybase since ASE 12.5.2.
I was happily surprised to find that the column aliases can be used to select from the derived table.
Another thing I learned was that the derived table must have a name. Here's a simple example:
select * from
(select sum(advance) from total_sales) dt
"dt" is the name in this example.
Tuesday, June 16, 2009
Wednesday, June 13, 2007
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
Exec sp_cycle_errorlog
Wednesday, May 23, 2007
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
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
Friday, May 18, 2007
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
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
Thursday, May 17, 2007
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
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
Friday, May 11, 2007
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
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$
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
Thursday, May 10, 2007
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
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
Subscribe to:
Posts (Atom)