Oracle远程连接配置

1. 系统连接方式

本地系统验证(sys用户)：

sqlplus / as sysdba

远程连接方法：

netca
listener.ora
sqlplus system/password@192.168.56.100:1521/orcl
sqlplus sys/password@192.168.83.20:1521/orcldg as sysdba
tnsnames.ora
sqlplus system/password@test

1.1 监听器配置

[oracle@rac01 admin]$ more listener.ora
listener.ora Network Configuration File:
/u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db)
(SID_NAME = orcldg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.83.20)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

1.2 监听器管理

启动监听器：

lsnrctl start

停止监听器：

lsnrctl stop

查看状态：

lsnrctl status

监听器配置文件位置：

$ORACLE_HOME/network/admin/listener.ora

1.3 TNS配置

[oracle@rac01 admin]$ more tnsnames.ora
tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
TESTDB_1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.60)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
orcl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.83.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

用户、角色与权限管理

2.1 用户管理

创建用户：

SQL> Create user [username] identified by [passwd] default tablespace users;

查询用户默认表空间、临时表空间：

SQL> select username, default_tablespace, temporary_tablespace from dba_users;

修改密码：

SQL>Alter user test identified by "888888888";

修改用户默认表空间：

SQL> Alter user test default tablespace users;

修改用户临时表空间：

SQL> Alter user test temporary tablespace temp_data;

强制用户修改密码：

SQL> Alter user test password expire;

锁定用户：

SQL> Alter user test account lock;  -- 加锁
SQL> Alter user test account unlock; -- 解锁

删除用户：

SQL> drop user 用户名;                -- 用户没有实际数据
SQL> drop user 用户名 CASCADE;       -- 将用户及其关联数据全部删除

查询用户会话信息：

SQL> select username, sid, serial#, machine from v$session where username='test';
SQL> alter system kill session 'sid, serial#';
SQL> select username, sid, serial#, status from v$session where username not in
('SYS','SYSTEM');
SQL> select user_name, sql_text from v$open_cursor where user_name='test';
SQL> select user_name, sql_text from v$open_cursor where user_name LIKE '%test%';

2.2 权限管理

创建用户并授权：

SQL> Create user user50 identified by user50;
SQL> grant connect, resource,UNLIMITED TABLESPACE to user50;
SQL> Create user user60 identified by user60;
SQL> grant dba to user50;

创建角色：

sql>create role role1;

授权给角色：

sql>grant create any table,create procedure to role1;

授予角色给用户：

sql>grant role1 to user1;

查看角色包含的权限：

sql>select * from role_sys_privs;
sql>select * from dba_role_privs;

回收权限：

Revoke connect, resource from user50;