笺注:这是在 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的表结构:(查询时,表名不区分英文字母大小写)
查看表TABLE2的表结构:(查询时,表名不区分英文字母大小写)
删除表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:
数据库用户为happy,实例名(SID)为ORCL:
可以看到前面创建的表:
设计表:
表字段:
表空间:
设置主键:
记得保存:
添加栏位:(即添加字段)
记得保存:
保存后的效果:
新建查询:
往表T1一次性插入多条记录:
INSERT into t1 values (1,'小明','广东省,guangzhou');
INSERT into T1 values (2,'小李','广东省,foshan');
INSERT into T1 values (3,'Zhuohua','四川省,chengdu');
查询记录:(查询时,表名、字段名不区分英文字母大小写)
SELECT * from t1;
查询记录:(查询时,表名、字段名不区分英文字母大小写)
SELECT * from T1 ORDER BY id;
查询记录:(查询时,表名、字段名不区分英文字母大小写)
SELECT * from t1 ORDER BY id DESC;
查询记录:(查询时,表名、字段名不区分英文字母大小写)
SELECT * from T1 WHERE name = '小明';
查询记录:(查询时,表名、字段名不区分英文字母大小写)
SELECT * from T1 WHERE Name = '小明';
查询记录:(查询时,关键字区分英文字母大小写)
SELECT * from T1 WHERE name = 'zhuohua';
查询记录:(查询时,关键字区分英文字母大小写)
SELECT * from T1 WHERE name = 'Zhuohua';
查询记录:(使用模糊查询)
SELECT * from T1 WHERE name LIKE 'Zhuo%';
查询记录:(使用模糊查询)
SELECT * from T1 WHERE ADDRESS LIKE '广东省%';
查询记录:(使用模糊查询)
SELECT * from T1 WHERE ADDRESS LIKE '%du';
#############
#############
使用Shell脚本执行Oracle的SQL语句:
例子一:
输出数据库版本信息:
[root@oracle ~]# cat /script/xx.sh
#!/bin/bash
sqlplus -S "/as sysdba" <<EOF
set head off
set feedback off
set time off
set timing off
set echo off
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" <<EOF
set head off
set feedback off
set time off
set timing off
set echo off
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" <<EOF
set head off
set feedback off
set time off
set timing off
set echo off
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" <<EOF
set head off
set feedback off
set time off
set timing off
set echo off
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 |