| Only values a select-result [message #628348] |
Mon, 24 November 2014 07:02  |
 |
chricken
Messages: 13
Registered: November 2014
|
Junior Member |
|
|
Hi,
I am about to make a kind of homemade logging-tool for my oracle-installation(s).
Therefore I want to perform some checks via SQL and store the results in a plain textfile, which is being analysed via Javascript later.
First thing I want to have is the information whether the DB is up and running.
I have an sql-file calles suche.sql, containing the following code:
CONNECT user/password
SET LINESIZE 100
SET PAGESIZE 50
SELECT 'Alive' FROM dual;
EXIT;
And I have a bashscript called suche.sh, containing the following code
sqlplus /nolog @suche.sql > message.log
That works fine so far, except that the result is:
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 24 13:37:32 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connect durchgefuhrt.
'ALIVE'
--------------------------------
Alive
Verbindung zu Oracle Database 11g Release 11.2.0.3.0 - 64bit Production beendet
Unfortunately I only want the value "Alive".
Is there a way to manage that?
I am sorry for stupid questions, I am very new to Oracle and SQL.
Regards
Christian
|
|
|
|
| Re: Only values a select-result [message #628350 is a reply to message #628348] |
Mon, 24 November 2014 07:06  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, you can switch off the column headers with "SET PAGESIZE 0".
But when the database is down, there might be some other error messages that get logged.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Only values a select-result [message #628464 is a reply to message #628362] |
Tue, 25 November 2014 08:38 |
|
chricken
Messages: 13
Registered: November 2014
|
Junior Member |
|
|
Hi,
based on your suggestions I built an SQL-file:
CONNECT user/password@IP/instancename
set pagesize 0
SELECT ROUND( SUM(bytes) /1024 / 1024,1 ) fsize FROM V$DATAFILE;
SELECT 'Alive' FROM dual;
SELECT ROUND ( Space_Limit / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
SELECT ROUND ( Space_Used / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
SELECT ROUND ( Space_Reclaimable / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
EXIT;
This does, what I wanted - almost.
The output looks like this:
SP2-0103: SQL-Puffer enthalt keine auszufuhrenden Befehle.
43895
Alive
Alive
215040
157750,5
33021,6
This is kinda funny. The values are okay, but I can not imagine, where these empty spaces come from.
And the second "Alive"
Any idea?
[Updated on: Tue, 25 November 2014 08:41] Report message to a moderator |
|
|
|
| Re: Only values a select-result [message #628465 is a reply to message #628464] |
Tue, 25 November 2014 08:41 |
 |
Michel Cadot
Messages: 68678
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
What tool do you use?
If SQL*Plus then actually copy and paste your session like:
SQL> SELECT ROUND( SUM(bytes) /1024 / 1024,1 ) fsize FROM V$DATAFILE;
FSIZE
----------
3130
1 row selected.
SQL> SELECT 'Alive' FROM dual;
'ALIV
-----
Alive
1 row selected.
SQL>
SQL> SELECT ROUND ( Space_Limit / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
ROUND(SPACE_LIMIT/1024/1024,1)
------------------------------
550
1 row selected.
SQL> SELECT ROUND ( Space_Used / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
ROUND(SPACE_USED/1024/1024,1)
-----------------------------
663.2
1 row selected.
SQL> SELECT ROUND ( Space_Reclaimable / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
ROUND(SPACE_RECLAIMABLE/1024/1024,1)
------------------------------------
27
1 row selected.
|
|
|
|
|
|
| Re: Only values a select-result [message #628470 is a reply to message #628466] |
Tue, 25 November 2014 09:06 |
|
chricken
Messages: 13
Registered: November 2014
|
Junior Member |
|
|
I do it via sqlplus.
And I found the reason for the whitespace. The headers are not visible, but the space is used:
Call with s-option:
Call without s-option:
ROUND(SPACE_RECLAIMABLE/1024/1024,1)
------------------------------------
33021,6
This seems quite strange, but it can be suppressed, I guess.
[Updated on: Tue, 25 November 2014 09:07] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Only values a select-result [message #628555 is a reply to message #628478] |
Wed, 26 November 2014 04:43 |
|
chricken
Messages: 13
Registered: November 2014
|
Junior Member |
|
|
Sorry for bothering with such basics, but I don't get it.
With "show all" I got the following:
appinfo ist OFF und auf "SQL*Plus" eingestellt
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFFund entspricht den ersten Zeichen der nachsten SELECT-Anweisung
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK ist ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
exitcommit ON
FEEDBACK ON fur 6 oder mehr Zeilen
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; backgroun d:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial, Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {fon t:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16 pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; ma rgin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; m argin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; ma rgin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90 %' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE ist OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1102000300
repfooter OFF und ist NULL
repheader OFF und ist NULL
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 11.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFFund entspricht den ersten Zeichen der nachsten SELECT-Anweisung
underline "-" (hex 2d)
USER ist "SYSTEM"
verify ON
wrap : Zeilen werden umbrochen
xmloptimizationcheck OFF
errorlogging is OFF
So, reagrding to http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch6.htm#i1082570 I added the following to my SQL-script:
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 20
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
That deleted the empty lines, but there is still whitespace in front of the values.
But I guess, they can be ignored later, so it's fine.
To be complete the result of this query:
43895
Alive
Alive
215040
157750,5
33021,6
Regards
Christian
[Updated on: Wed, 26 November 2014 04:47] Report message to a moderator |
|
|
|
| Re: Only values a select-result [message #628556 is a reply to message #628555] |
Wed, 26 November 2014 04:46 |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
|
Senior Member |
|
|
|
Can you show us your amended script and how you are running it in its entirety, please? Copy and paste the entire session. Additionally, you could format your numbers using TO_CHAR function, amongst other things.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Only values a select-result [message #628596 is a reply to message #628555] |
Wed, 26 November 2014 06:47 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
chricken wrote on Wed, 26 November 2014 04:43
That deleted the empty lines, but there is still whitespace in front of the values.
But I guess, they can be ignored later, so it's fine.
To be complete the result of this query:
43895
Alive
Alive
215040
157750,5
33021,6
TRIMSPOOL (which you didn't use) only trims white space from the end of the line. Such that, if you set LINESIZE 128 but a given line only has 50 bytes of data, that line won't get padded out to 128.
Except for the "Alive" text string literal, everything you are selecting is a NUMBER. As such it will be right aligned within the specific column width and left-padded with blanks. Alignment issues could be either/or
1) every number you select was a seperate SELECT, and the nature of the data in a particular select makes for a different column width
2) You are viewing it in an editor/display using a proportional font, so spaces are narrower than other characters and so a different number of leading spaces makes for different right-alignment.
|
|
|
|
|
|
|
|
| Re: Only values a select-result [message #628627 is a reply to message #628625] |
Wed, 26 November 2014 08:27 |
|
chricken
Messages: 13
Registered: November 2014
|
Junior Member |
|
|
BlackSwan wrote on Wed, 26 November 2014 15:23>@ Michel Cadot: I am using a bash-script to run an sql-file and I don't know, how to log that.
set -x
or
man script
OK, but this topic can be closed now.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
