oracle-sqlplus

Windows安装

Instant Client for Microsoft Windows (x64) 64-bit (oracle.com)

下载basic，odbc，sqlplus

将下载的安装包全部解压，放在同一个目录下，比如在D:/sqlplus。

• 添加环境变量

NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK(或AMERICAN_AMERICA.ZHS16GBK)
TNS_ADMIN = D:/sqlplus
LD_LIBRARY_PATH = D:/sqlplus
SQLPATH = D:/sqlplus

• 在d:/sqlplus目录下创建TNSNAME.ORA. ORCL是个连接标示符，可以自己修改，HOST是远程Oracle服务器的地址，PORT是Oracle的服务端口，没有修改过的话，默认是1521。Service_name是远程实例名称。

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

• 命令行下，切换到d:/sqlplus，运行odbc_install.exe，安装ODBC驱动。
• 命令行下执行sqlplus /nolog
• 连接命令

connect <username>/<password>@<连接标识符>

ex： connect root/123456@127.0.0.1:1521/orcl


如果上述方法试验没有成功，用下面的方式最直接了
sqlplus system/manager@'(description=(address_list=(address=(proto=tcp)(host=127.0.0.1)(port=1521)))(connect_data=(service_name=orcl)))’

Linux安装

• 安装步骤

安装解压缩到/usr/local/oracle
mkdir /usr/local/oracle
unzip /usr/local/oracle/instantclient-basic-linux.x64-11.2.0.4.0.zip
unzip /usr/local/oracle/instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
cd /usr/local/oracle/instantclient_11_2
mkdir -p network/admin
cd network/admin
新建tnsnames.ora文件
 
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)  
 
添加环境变量 #永久生效
cd ~
vi .bash_profile
 
export ORACLE_HOME=/usr/local/oracle/instantclient_21_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
##export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$ORACLE_HOME:$PATH
 
执行环境变量
source .bash_profile
 
测试数据库连接
sqlplus root/123456@!

sqlplus语法

# 导入 比如有文件：D:\root_O.sql
@D:\root_O.sql;

Oracle执行sql导入时报错：ERROR:ORA-01756: 引号内的字符串没有正确结束

将原sql文件改为ANSI编码格式

sqlplus /nolog


# 查询本用户的表,视图等
select * from user_tab_comments;
# 查看序列
select * from user_sequences;
# 删除序列
drop sequence seq_sys_dept;

SELECT * FROM ALL_TABLES WHERE OWNER='root'

SELECT DBMS_METADATA.GET_DDL('TABLE','<table-name>') FROM DUAL;
# 查看表结构
DESCRIBE <table-name>;

oracle 导出导入

expdp root/123456@orcl schemas=root dumpfile=root20210624.dmp directory=DATA_PUMP_DIR


# 导出到本地
create public database link dblink connect to root identified by password using '123456';

expdp user/pwd@orcl directory=dd network_link=dblink dumpfile=fileName.dmp

# 导入
SELECT * FROM ALL_TABLES WHERE OWNER='root'

SELECT DBMS_METADATA.GET_DDL('TABLE','admin') FROM DUAL;