Подключиться к базе данных Oracle из командной строки

[Marley]

sqlplus /nolog
conn system/[email protected]<"ip adress">:1521/xe;

где system/manager - логин/пароль
xe - instance к которому нужно подключиться

[Marley]

Можно в tnsnames.ora прописать параметры подключения (впринципе все так и делают) и подключаться передавая меньшее количество параметров в команднеой строке

ORCL12 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl12.localdomain)
    )
  )

C:\>sqlplus /nolog  
SQL> conn system/[email protected]

SERVICE_NAME можно посмотреть, выполнив слебующую команду:

$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2015 08:01:31

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12serv.localdomain)(PORT=1521)))
Services Summary...
Service "orcl12.localdomain" has 1 instance(s).
  Instance "orcl12", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:14 refused:0 state:ready
         LOCAL SERVER
Service "orcl12XDB.localdomain" has 1 instance(s).
  Instance "orcl12", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oracle12serv.localdomain, pid: 4018>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle12serv.localdomain)(PORT=53042))
The command completed successfully

[Marley]

Выполнить скрипт с выводом сообщений в консоль

START.bat

chcp 1251

sqlplus /nolog @connect.sql 
exit;

connect.sql

conn system/[email protected]<"ip adress">:1521/xe;
--conn sys/[email protected]<"ip adress">:1521/xe as sysdba;
@query.sql

query.sql

SET PAGESIZE 1000
SET LINESIZE 150
SET FEEDBACK OFF

select table_name from  user_tables;


PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

[Marley]

Выполнить скрипт с выводом сообщений в файл

START.bat

chcp 1251

sqlplus /nolog @connect.sql  > result.log
exit

connect.sql

conn system/[email protected]<"ip adress">:1521/xe;
--conn sys/[email protected]<"ip adress">:1521/xe as sysdba;
@query.sql



exit;

query.sql

SET PAGESIZE 1000
SET LINESIZE 150
SET FEEDBACK OFF

select table_name from user_tables;


PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

[Marley]

execute.cmd

set CONN_STR=system/<пароль system>

C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus.exe %CONN_STR% @script_for_executing.sql
exit;

@script_for_executing.sql

spool result.log
begin
 
-- Команда, сценарий и т.д.
  commit;
end;
/
spool off;
exit;