No question at this time
DBA Top 10
1 M. Cadot 6600
2 B. Vroman 3400
3 J. PĂ©ran 2200
4 A. Kavsek 1400
5 P. Tsongayinwe 900
6 P. Wisse 600
7 T. Boles 400
7 D. Walgude 400
7 D. Johnson 400
10 J. Alcroft 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48675
Total active users1329
Act. users last 24h2
Act. users last hour0
Registered user hits last week28
Registered user hits last month451
Go up

ORA-01791: not a SELECTed expression - select distinct 'alter table' ||
Next thread: username password
Prev thread: Rman backup is running slow.

Message Score Author Date
Hi, Can anyone advise how do I get a distinct l...... BenBart BartBen Aug 20, 2015, 01:02
Hi, <preQ>SQL> select unique 'alter table ' || ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Aug 20, 2015, 07:41
Like mike responded your query is missing from ent...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts raghavendra rao yella Aug 20, 2015, 19:57
Hi Michel, Sorry, I should have posted the full...... BenBart BartBen Aug 25, 2015, 09:45

Follow up by mail Click here


Subject: ORA-01791: not a SELECTed expression - select distinct 'alter table' ||
Author: BenBart BartBen, Philippines
Date: Aug 20, 2015, 01:02, 2508 days ago
Os info: Solaris
Oracle info: Oracle8
Message: Hi,

Can anyone advise how do I get a distinct line only for the SQL below? Still Googling but can't find anyone who is faced with the same 'requirement'

I have tried DISTINCT and also UNIQUE and they both give the same error and am on Oracle8 :(

select unique 'alter table ' || ind.table_name || ' modify partition ' || ind_part.partition_name || ' rebuild unusable local indexes ; '

*
ERROR at line 1:
ORA-01791: not a SELECTed expression



Current workaround I am doing is spooling it to a file and then using sort | uniq :-)

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01791: not a SELECTed expression - select distinct 'alter table' ||
Author: Michel Cadot, France
Date: Aug 20, 2015, 07:41, 2507 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

<preQ>SQL> select unique 'alter table ' || ind.table_name || ' modify partition ' || ind_part.partition_name || ' rebuild unusable local indexes ; '
2 /
select unique 'alter table ' || ind.table_name || ' modify partition ' || ind_part.partition_name || ' rebuild unusable local indexes ; '
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01791: not a SELECTed expression - select distinct 'alter table' ||
Author: raghavendra rao yella, United States
Date: Aug 20, 2015, 19:57, 2507 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Like mike responded your query is missing from entries and where conditions..

But here is what i think you are looking for.. it results in distinct sql stmts that you are trying...

SELECT UNIQUE
'alter table '
|| ind.table_name
|| ' modify partition '
|| ind_part.partition_name
|| ' rebuild unusable local indexes ; '
FROM DBA_TAB_PARTITIONS IND, DBA_IND_PARTITIONS ind_part
WHERE IND.PARTITION_NAME = IND_PART.PARTITION_NAME
AND IND.TABLE_OWNER = IND_PART.INDEX_OWNER
AND TABLE_OWNER IN 'YOUR_SCHEMA';
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01791: not a SELECTed expression - select distinct 'alter table' ||
Author: BenBart BartBen, Philippines
Date: Aug 25, 2015, 09:45, 2502 days ago
Message: Hi Michel,

Sorry, I should have posted the full SQL


select distinct
'alter table '
|| i.table_name
|| ' modify partition '
|| ip.partition_name
|| ' rebuild unusable local indexes ; '
from dba_ind_partitions ip, dba_indexes i
where ip.status = 'UNUSABLE' and ip.index_name = i.index_name
order by i.table_name


This one gives ORA-01791: not a SELECTed expression. If I change order by i.table_name to order by 1 or use a field alias and use that to order by, then it's all good.

Should have paid more attention to what the ORA- error itself is trying to say :$


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here