Home » SQL & PL/SQL » SQL & PL/SQL » execute query in sqlplus (Oracle 10g R2, RHEL4)
execute query in sqlplus [message #299528] Tue, 12 February 2008 05:49 Go to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Dear all,

Running the query with ceil through the sqlplus doesn't return result, but query with count works. See the below examples.

SQL> !more /disk6/backup/TSTTS1/tmpsql.3
set pagesize 0 feedback off verify off heading off echo off;
set serveroutput on;
SELECT COUNT(*) FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'TSTTS1';
/

SQL> !more /disk6/backup/TSTTS1/tmpsql.4
set pagesize 0 feedback off verify off heading off echo off;
set serveroutput on;
SELECT CEIL(SUM(sizeinb)/(1024*1024*1024)/8) FROM (
SELECT SUM(blocks)*8192 sizeinb 
FROM USER_TAB_PARTITIONS 
WHERE TABLE_NAME = 'T1001' 
AND PARTITION_NAME >= 'P20070801' 
AND PARTITION_NAME < 'P20070901'
UNION ALL
SELECT SUM(sample_size) sizeinb 
FROM USER_IND_PARTITIONS 
WHERE index_name IN 
(SELECT DISTINCT index_name 
FROM DBA_IND_COLUMNS 
WHERE  table_name='T1001' 
AND PARTITION_NAME >= 'P20070801' 
AND PARTITION_NAME < 'P20070901')
);
/

When execute the above queries one by one.
SQL> @/disk6/backup/NGPPS200708/tmpsql.3
         1
         1

SQL> @/disk6/backup/NGPPS200708/tmpsql.4

In Toad the sql

SELECT CEIL(SUM(sizeinb)/(1024*1024*1024)/8) 
FROM (
SELECT SUM(blocks)*8192 sizeinb 
FROM USER_TAB_PARTITIONS 
WHERE TABLE_NAME = 'T1001' 
AND PARTITION_NAME >= 'P20070801' 
AND PARTITION_NAME < 'P20070901'
UNION ALL
SELECT SUM(sample_size) sizeinb 
FROM USER_IND_PARTITIONS 
WHERE index_name IN 
(SELECT DISTINCT index_name 
FROM DBA_IND_COLUMNS 
WHERE  table_name='T1001' 
AND PARTITION_NAME >= 'P20070801' 
AND PARTITION_NAME < 'P20070901')
);

works & returns result.


Please help me what to do.

[Updated on: Tue, 12 February 2008 06:05] by Moderator

Report message to a moderator

Re: execute query in sqlplus [message #299533 is a reply to message #299528] Tue, 12 February 2008 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "works & result" mean?

Regards
Michel
Re: execute query in sqlplus [message #299534 is a reply to message #299533] Tue, 12 February 2008 06:11 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

It means in TOAD the following sql returns result.

SELECT CEIL(SUM(sizeinb)/(1024*1024*1024)/8) 
FROM (
SELECT SUM(blocks)*8192 sizeinb 
FROM USER_TAB_PARTITIONS 
WHERE TABLE_NAME = 'T1001' 
AND PARTITION_NAME >= 'P20070801' 
AND PARTITION_NAME < 'P20070901'
UNION ALL
SELECT SUM(sample_size) sizeinb 
FROM USER_IND_PARTITIONS 
WHERE index_name IN 
(SELECT DISTINCT index_name 
FROM DBA_IND_COLUMNS 
WHERE  table_name='T1001' 
AND PARTITION_NAME >= 'P20070801' 
AND PARTITION_NAME < 'P20070901')
);
Re: execute query in sqlplus [message #299539 is a reply to message #299528] Tue, 12 February 2008 06:29 Go to previous message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

I found out the reason. I hope this may help the other people who have experienced this type of problem.
In sqlplus I run the query with sysdba privilege. But in TOAD I run the query with testuser privilege. So I replaced the USER_TAB_PARTITIONS to DBA_TAB_PARTITIONS & USER_IND_PARTITIONS to DBA_IND_PARTITIONS.


Thank you for your attention

Smile
Previous Topic: show in one line
Next Topic: DDL Trigger
Goto Forum:
  


Current Time: Thu Dec 08 02:21:27 CST 2016

Total time taken to generate the page: 0.11237 seconds