How to execute .sql script or query from Oracle SQL> prompt in multiple remote instances?

Question

I' trying to execute .sql script or query from SQL> prompt into multiple instances.

But I'm unable to execute it in this way.

SQL> conn username/password@REMOTE @sqlscript.sql
OR
SQL> conn username/password@REMOTE @select name from v$database;

Definitely, I've some other ways to complete my task to execute like below:

M:\> echo select d.name, p.profile, p.limit from dba_profiles p, v$database d where resource_name = 'PASSWORD_VERIFY_FUNCTION' and profile in ('USERS','SERVICEACCOUNTS'); | sqlplus username/password#77@remotestring

But I'm looking in different way, Please find below my execution along with error from SQL> Prompt:

SQL>
SQL> conn username/Password@remotestring @tt.sql
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYS
KM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
    <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL>



SQL> conn username/Password@remotestring @select name from v$database;
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYS
KM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
    <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL>
SQL>

It should give a query output.

@Rajesh How many instances do you need to connect to and how often do you need to run this? If the numbers are small then something like Dmitry's answer should work well. But if you need to frequently query a thousand databases there are better solutions. — Jon Heller
– Jon Heller, Commented Apr 5, 2019 at 5:51

Dmitry Demin · Accepted Answer · 2019-04-04 15:47:16Z

1

create environment variable SQLPATH

for example. Windows

C:\sql>echo %SQLPATH%
c:\sql

create file c:\sql\login.sql

set linesize 10000
select name from v$database;
@@init.sql

create file c:\sql\init.sql

select sysdate from dual;

Test

C:\sql>sqlplus  /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 4 22:41:52 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SP2-0640: Not connected
SP2-0640: Not connected
SQL> conn system/manager
Connected.

NAME
---------
ORCL


SYSDATE
---------
04-APR-19

SQL> conn system/manager@ORCL
Connected.

NAME
---------
ORCL


SYSDATE
---------
04-APR-19

SQL>

answered Apr 4, 2019 at 15:47

Dmitry Demin

2,1232 gold badges19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute .sql script or query from Oracle SQL> prompt in multiple remote instances?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related