标题:
使用Navicat连接Oracle11gR2
[打印本页]
作者:
admin
时间:
2019-10-26 10:12
标题:
使用Navicat连接Oracle11gR2
笺注:这是在
Oracle Linux6.9安装Oracle11gR2
的基础上进行的。 登录Oracle:(Oracle已启动时) [root@oracle ~]#
su - oracle
[oracle@oracle ~]$
sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 12 06:49:09 2019 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL>
conn /as sysdba;
Connected. SQL> 查看数据库版本信息: SQL> SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'; VERSION --------------------------------------------------------------------------------
11.2.0.1.0
SQL> 查看实例名(SID): SQL> select instance_name from v$instance; INSTANCE_NAME --------------------------------
ORCL
SQL> 查看服务器的主机名: SQL> select host_name from v$instance; HOST_NAME --------------------------------------------------------------------------------
oracle.zhuohua.store
SQL> 查看用户表空间:(以下五个表空间是默认就存在的) SQL> select TABLESPACE_NAME from Dba_Tablespaces; TABLESPACE_NAME ------------------------------------------------------------
SYSTEM SYSAUX UNDOTBS1 TEMP USERS
5 rows selected. SQL> 创建表空间(zhuohua_ts): SQL> CREATE TABLESPACE
zhuohua_ts
DATAFILE '/db/app/oracle/oradata/orcl/zhuohua.dbf' SIZE
200M
AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> 再次查看用户表空间: SQL> select TABLESPACE_NAME from Dba_Tablespaces; TABLESPACE_NAME ------------------------------------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS
ZHUOHUA_TS
6 rows selected. SQL> 查看用户表空间的物理文件的存放位置: SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /db/app/oracle/oradata/orcl/system01.dbf /db/app/oracle/oradata/orcl/sysaux01.dbf /db/app/oracle/oradata/orcl/undotbs01.dbf /db/app/oracle/oradata/orcl/users01.dbf
/db/app/oracle/oradata/orcl/
zhuohua.dbf SQL> 查看所有表空间的物理文件名称及各自的大小:(单位为MB) SQL> SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; TABLESPACE_NAME FILE_ID ------------------------------------------------------------ ---------- FILE_NAME -------------------------------------------------------------------------------- TOTAL_SPACE ----------- SYSAUX 2 /db/app/oracle/oradata/orcl/sysaux01.dbf 480 SYSTEM 1 /db/app/oracle/oradata/orcl/system01.dbf 670 TABLESPACE_NAME FILE_ID ------------------------------------------------------------ ---------- FILE_NAME -------------------------------------------------------------------------------- TOTAL_SPACE ----------- UNDOTBS1 3 /db/app/oracle/oradata/orcl/undotbs01.dbf 75 USERS 4 /db/app/oracle/oradata/orcl/users01.dbf TABLESPACE_NAME FILE_ID ------------------------------------------------------------ ---------- FILE_NAME -------------------------------------------------------------------------------- TOTAL_SPACE ----------- 5
ZHUOHUA_TS
5
/db/app/oracle/oradata/orcl/zhuohua.dbf
200
SQL> ############# ############# 创建数据库用户(happy),密码(mima),并指明此用户的默认表空间(zhuohua_ts): SQL> create user
happy
identified by mima default tablespace
zhuohua_ts
;
User created.
SQL> 给数据库用户(happy)进行赋权: SQL> grant connect, resource,dba to
happy
;
Grant succeeded.
SQL> 查看当前用户: SQL> show user;
USER is "SYS"
SQL> 切换用户: SQL> connect
happy
/mima; Connected. SQL> SQL> show user;
USER is "HAPPY"
SQL> 查看当前用户所拥有的权限: SQL> select * from user_sys_privs; USERNAME ------------------------------------------------------------ PRIVILEGE -------------------------------------------------------------------------------- ADMIN_ ------
HAPPY
UNLIMITED TABLESPACE NO SQL> 查看数据库用户happy所拥有的权限:(用户名要大写才行) SQL> select * from dba_sys_privs where grantee='
HAPPY
'; GRANTEE ------------------------------------------------------------ PRIVILEGE -------------------------------------------------------------------------------- ADMIN_ ------
HAPPY
UNLIMITED TABLESPACE NO SQL> 使用当前用户创建表:(表名、字段名中的英文字母最好都是大写) SQL> Create table
T1
(ID int,NAME varchar2(10)); Table created. SQL> SQL> Create table
TABLE2
(ID int,NAME varchar2(20)); Table created. SQL> 查看当前用户所拥有的表: SQL> Select t.table_name from user_tables t; TABLE_NAME ------------------------------------------------------------
TABLE2 T1
SQL> 查看表
T1
的表结构:(查询时,表名不区分英文字母大小写)
下载
(56.78 KB)
2022-6-26 15:25
查看表
TABLE2
的表结构:(查询时,表名不区分英文字母大小写)
下载
(60.63 KB)
2022-6-26 15:25
删除表TABLE2: SQL> drop table
TABLE2
;
Table dropped.
SQL> SQL> Select t.table_name from user_tables t; TABLE_NAME ------------------------------------------------------------
T1
SQL> 确保服务器的防火墙打开了TCP 1521端口: [root@oracle ~]# cat /etc/sysconfig/iptables # Generated by iptables-save v1.4.7 on Mon Aug 12 08:15:17 2019 *filter :INPUT ACCEPT [2:132] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [2:232]
-A INPUT -p tcp -m tcp --dport 1521 -j ACCEPT
COMMIT # Completed on Mon Aug 12 08:15:17 2019 [root@oracle ~]# ############# ############# Windows客户端使用Navicat连接Oracle11gR2:
下载
(83.96 KB)
2022-6-26 15:27
数据库用户为
happy
,实例名(SID)为
ORCL
:
下载
(80.13 KB)
2022-6-26 15:27
可以看到前面创建的表:
下载
(81.42 KB)
2022-6-26 15:27
设计表:
下载
(43.84 KB)
2022-6-26 15:28
表字段:
下载
(66.2 KB)
2022-6-26 15:28
表空间:
下载
(74.77 KB)
2022-6-26 15:28
设置主键:
下载
(85 KB)
2022-6-26 15:29
记得保存:
下载
(74.63 KB)
2022-6-26 15:29
添加栏位:(即添加字段)
下载
(74.33 KB)
2022-6-26 15:29
记得保存:
下载
(72.15 KB)
2022-6-26 15:29
保存后的效果:
下载
(73.81 KB)
2022-6-26 15:30
新建查询:
下载
(42.59 KB)
2022-6-26 15:30
往表T1一次性插入多条记录: INSERT into t1 values (1,'小明','广东省,guangzhou'); INSERT into T1 values (2,'小李','广东省,foshan'); INSERT into T1 values (3,'Zhuohua','四川省,chengdu');
下载
(74.66 KB)
2022-6-26 15:30
查询记录:(查询时,表名、字段名不区分英文字母大小写) SELECT * from t1;
下载
(63.15 KB)
2022-6-26 15:31
查询记录:(查询时,表名、字段名不区分英文字母大小写) SELECT * from T1 ORDER BY id;
下载
(42.13 KB)
2022-6-26 15:31
查询记录:(查询时,表名、字段名不区分英文字母大小写) SELECT * from t1 ORDER BY id DESC;
下载
(42.3 KB)
2022-6-26 15:31
查询记录:(查询时,表名、字段名不区分英文字母大小写) SELECT * from T1 WHERE name = '小明';
下载
(33.83 KB)
2022-6-26 15:32
查询记录:(查询时,表名、字段名不区分英文字母大小写) SELECT * from T1 WHERE Name = '小明';
下载
(33.98 KB)
2022-6-26 15:32
查询记录:(查询时,关键字区分英文字母大小写) SELECT * from T1 WHERE name = '
zhuohua
';
下载
(30.34 KB)
2022-6-26 15:32
查询记录:(查询时,关键字区分英文字母大小写) SELECT * from T1 WHERE name = '
Zhuohua
';
下载
(33.02 KB)
2022-6-26 15:33
查询记录:(使用模糊查询) SELECT * from T1 WHERE name
LIKE
'Zhuo%';
下载
(33.52 KB)
2022-6-26 15:33
查询记录:(使用模糊查询) SELECT * from T1 WHERE ADDRESS
LIKE
'广东省%';
下载
(42.01 KB)
2022-6-26 15:33
查询记录:(使用模糊查询) SELECT * from T1 WHERE ADDRESS
LIKE
'%du';
下载
(34.32 KB)
2022-6-26 15:34
############# ############# 使用Shell脚本执行Oracle的SQL语句: 例子一: 输出数据库版本信息: [root@oracle ~]# cat /script/xx.sh #!/bin/bash sqlplus -S "
/as sysdba
" <
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'; exit EOF [root@oracle ~]# 设置脚本权限: [root@oracle ~]# chmod a+x /script/xx.sh [root@oracle ~]# ll /script/xx.sh -rwxr-xr-x 1 root root 161 Aug 12 09:56 /script/xx.sh [root@oracle ~]# 使用系统用户oracle运行脚本: [root@oracle ~]# su - oracle [oracle@oracle ~]$ [oracle@oracle ~]$ bash /script/xx.sh
11.2.0.1.0
[oracle@oracle ~]$ 例子二: 输出数据库编码: [root@oracle ~]# cat /script/xx.sh #!/bin/bash sqlplus -S "
/as sysdba
" <
select userenv('language') from dual; exit EOF [root@oracle ~]# 使用系统用户oracle运行脚本: [root@oracle ~]# su - oracle [oracle@oracle ~]$ [oracle@oracle ~]$ bash /script/xx.sh
|tail -1
AMERICAN_AMERICA.AL32UTF8
[oracle@oracle ~]$ 例子三: 输出SQL语句执行后的返回值: [root@oracle ~]# cat /script/xx.sh #!/bin/bash sqlplus -S "
happy/mima@192.168.168.163:1521/orcl
" <
SELECT NAME from T1 WHERE ADDRESS LIKE '%du'; exit EOF [root@oracle ~]# 使用系统用户oracle运行脚本: [root@oracle ~]# su - oracle [oracle@oracle ~]$ [oracle@oracle ~]$ bash /script/xx.sh
|tail -1
Zhuohua
[oracle@oracle ~]$ 例子四: 获取SQL语句执行后的返回值: [root@oracle ~]# cat /script/xx.sh #!/bin/bash sqlplus -S "happy/mima@192.168.168.163:1521/
ORCL
" <
SELECT ID from T1 WHERE NAME = 'Zhuohua'; exit EOF 使用系统用户oracle运行脚本: [root@oracle ~]# su - oracle [oracle@oracle ~]$ [oracle@oracle ~]$ bash /script/xx.sh |tail -1
3
[oracle@oracle ~]$ [oracle@oracle ~]$ Result_1=`bash /script/xx.sh |tail -1` [oracle@oracle ~]$ [oracle@oracle ~]$ echo $Result_1
3
[oracle@oracle ~]$ ############# ############# 修改数据库用户happy的密码: SQL> show user;
USER is "SYS"
SQL> SQL> alter user
happy
identified by
new_mima
; User altered. SQL> 数据库用户happy使用旧密码已经无法登录: SQL> connect happy/
mima
;
ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
SQL> 数据库用户happy使用新密码可以正常登录: SQL> connect happy/
new_mima
;
Connected.
SQL> SQL> show user;
USER is "HAPPY"
SQL> ############# ############# 删除数据库用户happy: SQL> show user;
USER is "HAPPY"
SQL> SQL> drop user
happy
cascade;
drop user happy cascade * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected
SQL> 要先切换用户为管理员:(客户端也要把数据库用户happy退出) SQL>
conn /as sysdba;
Connected. SQL> SQL> show user;
USER is "SYS"
SQL> 成功删除数据库用户happy: SQL> drop user
happy
cascade;
User dropped.
SQL> SQL> select * from dba_sys_privs where grantee='
HAPPY
';
no rows selected
SQL> 相关文章:
Oracle11gR2恢复已删除或更改的数据
Oracle11gR2的备份与还原
Zabbix调用Python3脚本监控Linux下的Oracle(一)
Zabbix调用Python3脚本监控Linux下的Oracle(二)
CentOS8_在Docker中安装Oracle19c
Oracle的SQL语句
Python3脚本管理Oracle11gR2
Win7使用Python3脚本远程管理Oracle11gR2
CentOS8使用Python3脚本远程管理Oracle11gR2
使用Navicat远程管理MySQL
图片附件:
图片1.png
(2022-6-26 15:25, 56.78 KB) / 下载次数 85
http://blog.zhuohua.store/attachment.php?aid=19947&k=17d3579746c1cd4a4f59ddc87c972248&t=1713871604&sid=CDTZT6
图片附件:
图片2.png
(2022-6-26 15:25, 60.63 KB) / 下载次数 80
http://blog.zhuohua.store/attachment.php?aid=19948&k=ef9f8f80c761b70f7449672ae9d40749&t=1713871604&sid=CDTZT6
图片附件:
图片3.png
(2022-6-26 15:27, 83.96 KB) / 下载次数 88
http://blog.zhuohua.store/attachment.php?aid=19949&k=f3854b8823da8cb357789e691158341d&t=1713871604&sid=CDTZT6
图片附件:
图片4.png
(2022-6-26 15:27, 80.13 KB) / 下载次数 98
http://blog.zhuohua.store/attachment.php?aid=19950&k=daea03267d6c24cb6d652a5065b80ad9&t=1713871604&sid=CDTZT6
图片附件:
图片5.png
(2022-6-26 15:27, 81.42 KB) / 下载次数 101
http://blog.zhuohua.store/attachment.php?aid=19951&k=8a5599c6647f5a1f18e91bd731c7e92f&t=1713871604&sid=CDTZT6
图片附件:
图片6.png
(2022-6-26 15:28, 43.84 KB) / 下载次数 92
http://blog.zhuohua.store/attachment.php?aid=19952&k=926a9646b963e0f76322a153864e9253&t=1713871604&sid=CDTZT6
图片附件:
图片7.png
(2022-6-26 15:28, 66.2 KB) / 下载次数 101
http://blog.zhuohua.store/attachment.php?aid=19953&k=2252081c8336d567d16ca9787fb82dfc&t=1713871604&sid=CDTZT6
图片附件:
图片8.png
(2022-6-26 15:28, 74.77 KB) / 下载次数 98
http://blog.zhuohua.store/attachment.php?aid=19954&k=ca10bad8e984bb0021fa8eff6706a926&t=1713871604&sid=CDTZT6
图片附件:
图片9.png
(2022-6-26 15:29, 85 KB) / 下载次数 109
http://blog.zhuohua.store/attachment.php?aid=19955&k=a0b67250a1d5a76af1bb48a8b04decb0&t=1713871604&sid=CDTZT6
图片附件:
图片10.png
(2022-6-26 15:29, 74.63 KB) / 下载次数 106
http://blog.zhuohua.store/attachment.php?aid=19956&k=ec25af2cab4cf7f0e2f01f1f8f8042b4&t=1713871604&sid=CDTZT6
图片附件:
图片11.png
(2022-6-26 15:29, 74.33 KB) / 下载次数 88
http://blog.zhuohua.store/attachment.php?aid=19957&k=57eb7017251a3b2d53074e09fa7fefe1&t=1713871604&sid=CDTZT6
图片附件:
图片12.png
(2022-6-26 15:29, 72.15 KB) / 下载次数 86
http://blog.zhuohua.store/attachment.php?aid=19958&k=c005e9f3ded274811fa586d2c96ec9c5&t=1713871604&sid=CDTZT6
图片附件:
图片13.png
(2022-6-26 15:30, 73.81 KB) / 下载次数 90
http://blog.zhuohua.store/attachment.php?aid=19959&k=f7e3313ef765b19a907e1d9f4c869463&t=1713871604&sid=CDTZT6
图片附件:
图片14.png
(2022-6-26 15:30, 42.59 KB) / 下载次数 92
http://blog.zhuohua.store/attachment.php?aid=19960&k=cee4de500b3460a1eddedd8a606ea28a&t=1713871604&sid=CDTZT6
图片附件:
图片15.png
(2022-6-26 15:30, 74.66 KB) / 下载次数 97
http://blog.zhuohua.store/attachment.php?aid=19961&k=e44e4ef6034651f83b14580c6cff4aff&t=1713871604&sid=CDTZT6
图片附件:
图片16.png
(2022-6-26 15:31, 63.15 KB) / 下载次数 102
http://blog.zhuohua.store/attachment.php?aid=19962&k=5f6070c9be96ba3c2877cb0e7b034b64&t=1713871604&sid=CDTZT6
图片附件:
图片17.png
(2022-6-26 15:31, 42.13 KB) / 下载次数 97
http://blog.zhuohua.store/attachment.php?aid=19963&k=c9d41c8a8986f71cdd3e08ad41568213&t=1713871604&sid=CDTZT6
图片附件:
图片18.png
(2022-6-26 15:31, 42.3 KB) / 下载次数 96
http://blog.zhuohua.store/attachment.php?aid=19964&k=5d7ea2a3f3df0a355cc990634bcd4660&t=1713871604&sid=CDTZT6
图片附件:
图片19.png
(2022-6-26 15:32, 33.83 KB) / 下载次数 91
http://blog.zhuohua.store/attachment.php?aid=19965&k=e07923cfcfd5a8be18b4b01cbcbe35b4&t=1713871604&sid=CDTZT6
图片附件:
图片20.png
(2022-6-26 15:32, 33.98 KB) / 下载次数 83
http://blog.zhuohua.store/attachment.php?aid=19966&k=78c9ca6d43ad2e92baac2e27cf6c51f8&t=1713871604&sid=CDTZT6
图片附件:
图片21.png
(2022-6-26 15:32, 30.34 KB) / 下载次数 42
http://blog.zhuohua.store/attachment.php?aid=19967&k=ae9a616f8523817d10203dc9f2aa22e0&t=1713871604&sid=CDTZT6
图片附件:
图片22.png
(2022-6-26 15:33, 33.02 KB) / 下载次数 46
http://blog.zhuohua.store/attachment.php?aid=19968&k=02eea34b316cbf21ffc7f08102f60012&t=1713871604&sid=CDTZT6
图片附件:
图片23.png
(2022-6-26 15:33, 33.52 KB) / 下载次数 46
http://blog.zhuohua.store/attachment.php?aid=19969&k=a4b4790e56814a16a52689319161fcc8&t=1713871604&sid=CDTZT6
图片附件:
图片24.png
(2022-6-26 15:33, 42.01 KB) / 下载次数 47
http://blog.zhuohua.store/attachment.php?aid=19970&k=46389431d67e12989c3ccffd787ad8fb&t=1713871604&sid=CDTZT6
图片附件:
图片25.png
(2022-6-26 15:34, 34.32 KB) / 下载次数 58
http://blog.zhuohua.store/attachment.php?aid=19971&k=e4b007609a5f474bae0cb464a92394d3&t=1713871604&sid=CDTZT6
欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/)
Powered by Discuz! 7.2