No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48675 | | Total active users | 1329 | | Act. users last 24h | 2 | | Act. users last hour | 0 | | Registered user hits last week | 28 | | Registered user hits last month | 451 |
|
Go up
ORA-01791: not a SELECTed expression - select distinct 'alter table' ||
| 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 ' || ...... |
 |
Michel Cadot |
Aug 20, 2015, 07:41 |
| Like mike responded your query is missing from ent...... |
|
raghavendra rao yella |
Aug 20, 2015, 19:57 |
| Hi Michel,
Sorry, I should have posted the full...... |
|
BenBart BartBen |
Aug 25, 2015, 09:45 |
| 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: |
|
| 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: |
|
| 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
|