Solved

error on rebuild indexes script

Posted on 2013-12-29
3
543 Views
Last Modified: 2014-01-08
Hello,

When I execute with following scripts, I have this error :
SQL> SQL> SQL> SQL> SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.

export SID=$1
export SCHEMA=$2
export ANA_LOG=/opt/oracle/logs
export ANA_SQL=/opt/oracle/scripts
export HOME=/home/oracle
. ${HOME}/.bash_profile

if [ -f ${ANA_LOG}/rebuild_index_$1_$2.log ] ; then
   rm ${ANA_LOG}/rebuild_index_$1_$2.log
fi

logf=${ANA_LOG}/rebuild_index_$1_$2.log


date >${logf}


export ORACLE_SID=${SID}

sqlplus /nolog  << EOF >>${logf}
connect / as sysdba

set feedback off
set pagesize 0

SPOOL /opt/oracle/scripts/Indexrebuild_${ORACLE_SID}_${SCHEMA}.SQL 

SELECT 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME||' REBUILD  TABLESPACE '||TABLESPACE_NAME||';' FROM DBA_SEGMENTS 
WHERE OWNER='${SCHEMA}' AND SEGMENT_TYPE='INDEX';

SPOOL OFF

@/opt/oracle/scripts/Indexrebuild_${ORACLE_SID}_${SCHEMA}.SQL

EOF

date >>${logf}
exit;

Open in new window


How can I resolve it?

Thanks
0
Comment
Question by:bibi92
  • 2
3 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 39744624
Take a look at the SQL file that is being generated.  I believe you are going to see the SQL statements echoed in the file.

You should add at least these to your settings:

set echo off
set lines 32767
set trimspool on

That should get you closer.

I believe that your resulting script is actually working, those errors are just extraneous messages.

If you could post at least the first few lines of the SQL file, through the first couple of alter statements, then we could help more.  Please make the suggested changes first.
0
 

Author Comment

by:bibi92
ID: 39744702
I modified the settings like :
set feedback off
set pagesize 0
set echo off
set lines 32767
set trimspool on

The following error occurs:
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2  SQL> SQL> SQL> SQL> SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> SPOOL..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Sun Dec 29 15:53:41 CET 2013



SQL> SQL> SQL> SQL> SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 39746990
Then I would also change the "sqlplus /nolog" command to "sqlplus -s /nolog".  That should get rid of most of what you are seeing.

If that does not work, please post the script and the .SQL file that are generated.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Records that don't match 8 50
join 2 views with 5 conditions 3 61
PL/SQL Two changes 7 34
Need SQL Query to Find Foreign-keys Without Indexed Columns 4 25
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question