oracle数据库（一）_安装与使用

1 安装

• 安装oracle
• 设置服务机ip
• 关闭防火墙
• 修改文件localhost为服务及ip：C:\app\init\product\11.2.0\dbhome_1\NETWORK\ADMIN
• 重启服务即可远程连接

2 远程配置

• 客户端修改tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\init\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

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

• 服务端修改listener.ora

# listener.ora Network Configuration File: C:\app\init\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\init\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\init\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  (SID_DESC = 
    (SID_NAME = orcl)
    (ORACLE_HOME = C:\app\init\product\11.2.0\dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\init

• 重启服务 OracleOraDb11g_home1TNSListener

3 创建用户

-- 远程连接
sqlplus test/z123@10.10.10.10:1521/orcl

-- 首次登陆
sqlplus / nolog
connect / as sysdba

-- 切换账户
conn scott/tiger
conn / as sysdba

-- 创建用户
create user test identified by 123 ACCOUNT UNLOCK;

-- 更改密码
alter user test identified by 234;

-- 删除用户
drop user user1;

-- 授权
grant 
	CONNECT          -- 临时用户，不能建表，可以连接回话
	,resource        -- 正式用户，建表，序列，过程，触发器，索引等
	,dba			 -- 系统权限
	,create view     -- 常见视图权限
	,drop view       -- 删除视图权限
	,execute on dbms_lock  -- 调用过程权限
to scott;

--撤销授权
revoke resource,dba,create view to scott;

4 常用系统操作

-- 设置列宽
set linesize 300;

-- 设置页长
set pagesize 300;


-- 返回当前用户名
show user；

--返回所有表名
select * from tab;

-- 返回列及列类型 
desc tbname;

-- 清屏
host cls

-- 文本编辑语句
ed

-- 执行
/

-- 追加编辑语句
a order by ts_code

-- 文字录屏
spool e:/note.sql
spool off;

-- 查询数据库语言编码
select userenv('language') from dual; 
select * from V$NLS_PARAMETERS; -- 连接1,2,9行

-- 修改日期显示格式
alter  session set NLS_DATE_FORMAT = 'yyy-mm-dd'