1.
Subject: Whacko SQL*Net errors
Q -- I am a newbie Oracle person who is trying to setup a system called "AutoSys" that uses Oracle to store it's data.
I'm pretty certain I've got the Oracle/SQL*Net stuff working correctly.
TNSPING, SQLPLUS, etc works just fine.
However, when I run one of the AutoSys applications ( gatekeeper ) I get the following:
Fatal OSN connect error 12500, connecting to:
(description=(connect_data=(sid=asys)(server=dedicated)
(CID=(PROGRAM=)(HOST=alnitak)(USER=autosys)))
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=alnitak))
(address=(protocol=tcp)(host=alnitak)(port=1521))))
VERSION INFORMATION:
TNS for SVR4: Version 2.0.15.0.0 - Production
Unix Domain Socket IPC NT Protocol Adapter for SVR4: Version 2.0.15.0.0
- Production
Oracle Bequeath NT Protocol Adapter for SVR4: Version 2.0.15.0.0 - Production
TCP/IP NT Protocol Adapter for SVR4: Version 2.0.15.0.0 - Production
Time: 09-APR-96 15:08:26
Tracing not turned on.
Tns error struct:
nr err code: 12204
TNS-12204: TNS:received data refused from an application
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
I cannot find any error 12204 or 12564 in the SQL*Net manual and certainly not in the main errors and codes manual.
A -- In my documentation, I have a description of your errors:
TNS-12204:"TNS:Received data refused from an application"
// *Case: The application using the Interchange refused the connection
// at the listener.
// *Action: Make sure that the application listener at the destination is
// functioning correctly. If it is and the problem persists, contact
// Worldwide Customer Support.
TNS-12564:"TNS:connection refused"
// *Cause:The connect request was denied by the remote user (or TNS software).
// *Action: Not normally visible to the user. For further details, turn on
// tracing and reexecute the operation.
Information on tracing is in the Oracle Network Products Messages Manual.
2.
Subject: ORA-01555 snapshot too old? / NetWare-Database crashes
March 27, 1996
Q -- I am experiencing a strange problem. One of our databases is a Oracle7 (7.0.16.6.0) running on a Novell Netware (3.11) server. We must use that old release because of some compatibility reasons.
Since some days ago the database export doesn't work, giving instead the message "ORA-01555: snapshot too old (rollback segment too small)".
When I retrieve some tables, I get at some point (some particular users) that message. Some other users are fine.
Very often - when I shut down and restart the database - not only the database but the whole NetWare-Server crashes. Sometimes the database crashes without any user action.
It's a very small database. I enlarged the tablespaces and rollback segments but that didn't change the situation.
In the manual 4 reasons can be found, but only the first two could be responsible in my case:
1. Insufficient Rollback Segments -> but I increased them
2. Corrupted Rollback Segment
Because of the frequent crashes, I think that might be the reason. How can I check that? Is there somebody with experiences with that error message?
A -- What you found in your manual may not fully explain the problems you have been having. The most likely cause IS that your rollback segments are too small. Specify larger INITAL and/or NEXT EXTENT sizes for your rollback segments. Considering that only some users get the error message, it seems that those users attached to that segment get the error, while the users attached to other segments do not get the error. Also be sure to set the OPTIMAL parameter of the rollback segment (if you are using Oracle7).
Another problem could be caused while using Trusted ORACLE. If your interval between checkpoints is too small in a secondary database, you can get this error. Oracle recommends decreasing the value of the LOG_CHECKPOINT_TIMEOUT parameter in your "init.ora" file.
As to your system crashing, I can't help you there. You could have a
legitimate bug.
If you are worried about corrupted rollback segments, then drop and recreate them as soon as possible!
3.
Subject: How to use " in Oracle data
MARCH 21
Q -- We are encountering db values that contain both double quotes and apostrophes. Has anyone found a work-around for this situation?
A -- In order to deal with single quotes for values in your SQL statements,
you need to prepend another single quote. For example,
Insert a record with a single quote at the beginning:
insert into TAB_NAME values ('''This is a test!');
See if this was inserted correctly:
select * from TAB_NAME;
'This is a test!
If you want to search for a single or double quote within a string, use the INSTR function. If there is no quote, then a 0 is returned. Otherwise, the position of the quote is returned. See your SQL Language Reference Manual for more details on this.
You can also use the TRANSLATE function to make all single quotes double quotes, or vice versa. This will change any occurrence of a character to whatever new character you choose within a string.
4.
Subject: Insufficient Privileges with SYS for SQL*DBA
MARCH 20
Q -- We have Personal Oracle installed here. We changed SYSTEM and SYS passwords but not the DB password (default "oracle"). When shutting down the DB (with Database Manager) the DB password is required. However, by now this password is not accepted. As well, it is not possible to shut down the DB using the DBA with sys login, although sys has full DBA privileges;Oracle returns with "insufficient privileges". Has anybody experienced the same problems and knows something to do against it?
A -- Even though you tried shutting the database down with the "sys" account, and sys has "full" DBA privileges, the "sys" account cannot shut down the database. You must be connected INTERNAL to shut down the database. See the Oracle7 Server Administrator's Guide (page 1-4 in my book), and your installation and users guide for details.
Basically, INTERNAL is a special, powerful administrative account to startup and shutdown the database. It is the ONLY account that can do it. The one exception is if your operating system supports operating system roles; if so, you can grant OSOPER or OSDBA roles. Again, this is system-specific, and you should read up on it in your installation manuals before you try to use it.
5.
Subject: Cannot do 'connect internal' to do system shutdown
Q -- I am Sys Admin for a company with a few political problems (mostly the DBA dislikes me because I push to get things done). How this applies is the DBA has hidden all oracle docs and deleted the online ones....nice guy.
I need to get the automated shutdown running correctly (and the startup too). I have the ORACLE_HOME and ORACLE_SID set. I even have the $ORACLE_HOME/bin in my path statement. When I run the auto start script as root I get the message 'ORA-01031: insufficient privileges'. I can get to the oracle dba account as I have to do this manually to shut it down. What permissions/right/privileges do I need to set and how do I set them???
A -- Sorry to hear about your office politics. Anyway, in order to use the SQL*DBA program to shutdown the database and bring up the database, your UNIX userid (I am assuming this is UNIX) must be in the same Unix group as the oracle Unix user. On most systems this means that your Unix userid must have "dba" as its group. If not, you will get the "ORA-01031: insufficient privileges" error you received.
Another alternative: since you have "root" privilege, just issue an "su - oracle", which will set up your environment and log you in as oracle.
6.
Subject: Export a table from Oracle to ASCII File
Q -- Is there anybody who knows how to export a table from ORACLE to an ordinary ASCII file?
I know there is an EXP command in Oracle that writes an archive dump file in a Oracle format but I don't find any way to use this command to export to an ASCII file instead.
Is the only way to use embedded SQL from a C or VB program and write the ASCII file?
A -- There is a much simpler way. Using SQL*Plus, you can create an ASCII file, delimiting all fields with any character you want.
For example, suppose you want to create a comma-delimited file for the
following table:
Table BATTER:
name varchar2(30),
hr varchar2(3),
rbi varchar2(3),
batting_avg varchar2(5)
To make the file, write the following script:
set pagesize 0
set pause off
set header off
spool ASCII_FILE
select name||','||hr||','||rbi||','||batting_avg
from BATTER;
spool off
Note that the commas will serve as the delimiters, and can be easily changed to whatever you need. Also, Oracle adds ".lst" to the name of the spool file unless you specify another extension.
7.
Subject: Need help with simple SQL syntax
APRIL 24
Q -- Please bear with me, I always thought I knew SQL - now I have my doubts. Simple interactive query to access records with a particular VMS date field being at least 14 days in the past. What is the correct syntax?
(Oracle Rdb, former DEC Rdb)
SQL> select * from myTable where timeField < 'today - 14 days'
How do I correctly specify 'today - 14 days'?I get all sorts of error messages.
A -- There is a simple solution for your question. Oracle references today as SYSDATE. So, to reference 'today - 14 days', you can type:
SELECT * FROM myTable WHERE timeField < SYSDATE - 14;
8.
Subject: How do I do a simple directory listing of my available Oracle 7 tables
APRIL 24
Q -- How can I do a simple directory listing of my Oracle7 tables using SQL. I am ODBC connected and can query any one table. I just don't know how to create a list of the tables. I've read all the docs I have and it doesn't flash out at me.
A -- If you have a dba account, type:
SELECT * FROM DBA_TABLES;
If you have a non-dba account, type:
SELECT * FROM ALL_TABLES;
To limit your selection, use the WHERE clause. For instance, to find all tables with the name "PERSON" in it, type:
SELECT * FROM ALL_TABLES
WHERE TABLE_NAME LIKE '%PERSON%';
9.
Subject: Error - ORA-01652
APRIL 24
Q -- I have just started working with Oracle (two days ago). The error ORA-01652: unable to extend temp segment by 5412 in tablespace TEMPJCREW, was occurring before I started, and I am wondering what it means, as I can not find it in any of the manuals.
I am really an application programmer, but in my new position I am currently also acting as the DBA. We are using version 7.1 on Solaris 2.5. The error occurs after doing a rather large select.
We have the following tablespace set up:
create tablespace tempjcrew datafile 'temp01.tab' size 2000m;
alter tablespace tempjcrew add datafile 'temp02.tab' size 1000m;
alter tablespace tempjcrew add datafile 'temp03.tab' size 500m;
alter tablespace tempjcrew add datafile 'temp04.tab' size 500m;
alter tablespace tempjcrew default storage (pctincrease 1 next 10m);
temp01.tab and temp02.tab are on separate disks.
From what I have read, the pctincrease has no affect in version 7.
We are selecting approximately 20 million records. Are we just running out of space. Do we need to create another tablespace?
A -- I want to wish you good luck on your new DBA role. There is much to learn, and two days is not enough to get up to speed. This message should hopefully help get you going.
First of all, make sure that the user has 'TEMPJCREW' as the TEMP tablespace in the user profile. To do this, type:
ALTER USER username TEMPORARY TABLESPACE tempjcrew;
Your problem could be as simple as not having this set, and the processing of the 20 million records is taking place in another smaller tablespace (SYSTEM, perhaps?)
Also, the pctincrease DOES have an effect in Oracle7. You have it set to 1. I would recommend setting it to a more even number, such as 0 or 100. This will depend on how many extents you expect, how large your smallest datafile is (500m in your case), and so on. I would recommend starting at pctincrease 0. If it turns out that you run out of extents, then simply make the default INITIAL extent for the tempjcrew tablespace larger.
To find out if you DO need more space in the "tempjcrew" tablespace, issue the following statement while running the 20 million record query, preferably several times during the execution and right before the ORA-1652 error:
SELECT b.file_name, a.tablespace_name, a.bytes, a.blocks
FROM dba_free_space a, dba_data_files b
WHERE a.file_id = b.file_id
AND a.tablespace_name = 'TEMPJCREW';
The above query will tell you how much contiguous free space is contained on each datafile. If the free space keeps declining during your query, and eventually runs out of space, then you can conclude that you need to add another datafile to the "tempjcrew" tablespace.
Do not add another tablespace as you suggested, because the new tablespace will not be used in the query. Instead, add yet another datafile to your "tempjcrew" tablespace.
10.
Subject: ORA-3113 errors, suddenly
APRIL 18
Q -- After a year of relatively trouble-free Oracle running, first on 7.1.3 and lately on 7.1.6, I started getting ORA-3113 errors today. I don't know if it's relevant, but I recently upgraded my RS/6000 AIX from 3.2.5 to 4.1.4, and then, just a few days ago, moved all applications and data to a new physical server, still running 4.1.4.