开发环境windows安装oracle 19c并连接数据库的详细图文教程_Oracle

oracle 19c下载地址：https://www.oracle.com/cn/database/technologies/oracle-database-software-downloads.html#license-lightbox

创建链接服务器（远程数据库、db_link），登录 sql plus ，如下：

创建 链接服务器（远程数据库）语句如下：

create database link dblink_yybip
connect to yybip
 identified by afxm1234 using
  '(description=(address=(protocol=tcp)(host=10.80.189.12)
  (port=1521))(connect_data=(service_name=orcl)))';

查看所有数据库连接，如下：

-- 查看所有的数据库链接
select * from dba_db_links;

-- 删除数据库连接
drop database link dblink_yybip;

-- 查看当前用户的数据库链接
select * from user_db_links;

-- 查询远程数据库中的数据
select * from iuap_apdoc_basedoc.bo_orgadmin@dblink_yybip;

-- 查询所有模式名称
select username from dba_users;

-- 查询当前登录用户拥有的模式名称
select username from all_users;

-- 创建用户
create user c##ry_net
  default tablespace users
  temporary tablespace temp
  profile default
  password expire
  quota unlimited on users;
  
-- 给用户授权系统权限
grant create session to c##ry_net;
grant unlimited tablespace to c##ry_net;

-- 给用户授权创建链接数据库的权限
grant create database link to c##ry_net;

-- 创建角色
create role c##remote_yybip_reader;

-- 查看当前用户拥有的角色
select * from user_role_privs;

-- 查看所有角色
select * from dba_roles;

-- 查看某个角色被授予了哪些权限
select * from dba_role_privs where granted_role = 'c##remote_yybip_reader';

-- 查看某个角色授予了哪些对象权限
select * from dba_tab_privs where grantee = 'c##remote_yybip_reader';

-- 查看角色是否已经存在
select * from dba_roles where role = 'c##remote_yybip_reader';

-- 检查角色是否已授予用户
select * from dba_role_privs where grantee = 'c##ry_net' and granted_role = 'c##remote_yybip_reader';

-- 授权角色给用户
grant c##remote_yybip_reader to c##ry_net;

-- 给用户取消角色授权
revoke c##remote_yybip_reader from c##ry_net;

-- 删除角色
drop role c##remote_yybip_reader;

-- 创建新的模式
create user my_schema identified by password;
create user c##newuser identified by password;

-- 授予模式权限
grant select, insert, update, delete on table_name to my_schema;

-- 查看所有表空间
select * from dba_tablespaces; 
select tablespace_name from dba_tablespaces;

-- 查看指定表空间下的所有表
select table_name,tablespace_name from dba_tables where tablespace_name='users';

-- 查看当前用户默认表空间
select * from user_users; 

-- 修改用户默认表空间
alter user c##ry_net default tablespace users; 

-- 查看指定表空间当前配额
select * from dba_ts_quotas where tablespace_name = 'users';

-- 分配表空间配额
alter user c##ry_net quota unlimited on users;

-- 验证权限
select * from dba_sys_privs where grantee = 'c##ry_net';

-- 基本权限
grant create session to c##ry_net; 

-- 分配表空间配额
grant unlimited tablespace to c##ry_net; 

-- 具体表的权限
grant select, insert, update, delete on dm_bed to c##ry_net; 

-- 切换会话
alter session set current_schema = c##ry_net;

-- 列出当前用户的序列
select * from user_sequences;
select * from dba_sequences order by sequence_name;

-- 用序列和触发器生成自增id
create sequence sys_logininfor_seq start with 1 increment by 1;
create or replace trigger sys_logininfor_seq_tr
 before insert on sys_logininfor for each row
 when (new.info_id is null)
begin
 select sys_logininfor_seq.nextval into :new.info_id from dual;
end;

-- 查看序列当前值
select c##ry_net.sys_logininfor_seq.currval from dual;
-- 输出序列的当前值，确保它是正确的

-- 生成下一个值
select c##ry_net.sys_logininfor_seq.nextval from dual;
-- 输出序列的下一个值，确保它是连续的

-- 使用 alter sequence 语句将序列的当前值调整到 133
select c##ry_net.sys_logininfor_seq.currval from dual; -- 39
alter sequence c##ry_net.sys_logininfor_seq increment by 94; -- 修改步长
select c##ry_net.sys_logininfor_seq.nextval from dual; -- 39+94=133
select c##ry_net.sys_logininfor_seq.currval from dual; -- 133
alter sequence c##ry_net.sys_logininfor_seq increment by 1; -- 恢复步长

-- 从 oracle 12c 开始，可以利用 generated always as identity 来生成自增列
create table your_table (
  id number generated always as identity primary key,
  other_column varchar2(100)
);
insert into your_table (other_column) values ('some_value');

-- 查询pdb
select name from v$pdbs;

-- 切换到指定pdb
alter session set container = orclpdb;

-- 修改指定pdb状态
alter pluggable database orclpdb open read write;

-- 查询指定pdb状态
select name,open_mode from v$pdbs where name='orclpdb';

-- 查询数据库实例
select instance_name, status from v$instance;

-- 查询数据库服务
select global_name from global_name;

tnsnames.ora 和 listener.ora 配置文件都正确，但 tnslistener 服务无法启动。检查 环境变量 中是否存在 oracle_home ，将其删除，重新启动 tnslistener 服务。

# tnsnames.ora network configuration file: c:\users\administrator\downloads\windows.x64_193000_db_home\network\admin\tnsnames.ora
# generated by oracle configuration tools.

listener_orcl =
  (address = (protocol = tcp)(host = 192.168.3.185)(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 = 192.168.3.185)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orcl.mshome.net)
    )
  )

# listener.ora network configuration file: c:\users\administrator\downloads\windows.x64_193000_db_home\network\admin\listener.ora
# generated by oracle configuration tools.

sid_list_listener =
  (sid_list =
    (sid_desc =
      (sid_name = clrextproc)
      (oracle_home = c:\users\administrator\downloads\windows.x64_193000_db_home)
      (program = extproc)
      (envs = "extproc_dlls=only:c:\users\administrator\downloads\windows.x64_193000_db_home\bin\oraclr19.dll")
    )
  )

listener =
  (description_list =
    (description =
      (address = (protocol = tcp)(host = 192.168.3.185)(port = 1521))
      (address = (protocol = ipc)(key = extproc1521))
    )
  )

命令行启动数据库实例：

microsoft windows [version 10.0.19045.6332]
(c) microsoft corporation. all rights reserved.

c:\users\administrator>sqlplus / as sysdba

sql*plus: release 19.0.0.0.0 - production on sat oct 4 13:06:30 2025
version 19.3.0.0.0

copyright (c) 1982, 2019, oracle.  all rights reserved.


connected to:
oracle database 19c enterprise edition release 19.0.0.0.0 - production
version 19.3.0.0.0

sql> startup
ora-01081: cannot start already-running oracle - shut it down first
sql> select status from v$instance;

status
------------
open

sql>

检查监听器状态：

c:\users\administrator>lsnrctl status

lsnrctl for 64-bit windows: version 19.0.0.0.0 - production on 04-oct-2025 13:09:19

copyright (c) 1991, 2019, oracle.  all rights reserved.

connecting to (description=(address=(protocol=tcp)(host=192.168.3.185)(port=1521)))
status of the listener
------------------------
alias                     listener
version                   tnslsnr for 64-bit windows: version 19.0.0.0.0 - production
start date                04-oct-2025 10:15:11
uptime                    0 days 2 hr. 54 min. 8 sec
trace level               off
security                  on: local os authentication
snmp                      off
listener parameter file   c:\users\administrator\downloads\windows.x64_193000_db_home\network\admin\listener.ora
listener log file         d:\oracle19cbasedic\diag\tnslsnr\desktop-s83f2ih\listener\alert\log.xml
listening endpoints summary...
  (description=(address=(protocol=tcp)(host=192.168.3.185)(port=1521)))
  (description=(address=(protocol=ipc)(pipename=\\.\pipe\extproc1521ipc)))
  (description=(address=(protocol=tcps)(host=desktop-s83f2ih)(port=5500))(security=(my_wallet_directory=d:\oracle19cbasedic\admin\orcl\xdb_wallet))(presentation=http)(session=raw))
services summary...
service "52448234712340b69f274bcc790ecfe0.mshome.net" has 1 instance(s).
  instance "orcl", status ready, has 1 handler(s) for this service...
service "clrextproc" has 1 instance(s).
  instance "clrextproc", status unknown, has 1 handler(s) for this service...
service "cb256491eb234c18ae41c8f8bcc40ca5.mshome.net" has 1 instance(s).
  instance "orcl", status ready, has 1 handler(s) for this service...
service "orcl.mshome.net" has 1 instance(s).
  instance "orcl", status ready, has 1 handler(s) for this service...
service "orclxdb.mshome.net" has 1 instance(s).
  instance "orcl", status ready, has 1 handler(s) for this service...
service "orclpdb.mshome.net" has 1 instance(s).
  instance "orcl", status ready, has 1 handler(s) for this service...
the command completed successfully

c:\users\administrator>

配置 orclpdb 数据库，配置完毕重启 services.msc 中的 oracleoradb19home1tnslistener 服务，listener.ora 和 tnsnames.ora 配置如下：

# listener.ora network configuration file: d:\windows.x64_193000_db_home\network\admin\listener.ora
# generated by oracle configuration tools.

sid_list_listener =
  (sid_list =
    (sid_desc =
      (sid_name = clrextproc)
      (oracle_home = d:\windows.x64_193000_db_home)
      (program = extproc)
      (envs = "extproc_dlls=only:d:\windows.x64_193000_db_home\bin\oraclr19.dll")
    )
	(sid_desc =
      (sid_name = orclpdb)
      (oracle_home = d:\windows.x64_193000_db_home)
      (program = extproc)
      (envs = "extproc_dlls=only:d:\windows.x64_193000_db_home\bin\oraclr19.dll")
    )
  )

listener =
  (description_list =
    (description =
      (address = (protocol = tcp)(host = 0.0.0.0)(port = 1521))
      (address = (protocol = ipc)(key = extproc1521))
    )
  )

# tnsnames.ora network configuration file: d:\windows.x64_193000_db_home\network\admin\tnsnames.ora
# generated by oracle configuration tools.

listener_orcl =
  (address = (protocol = tcp)(host = 0.0.0.0)(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 = 0.0.0.0)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orcl)
    )
  )
  
orclpdb =
  (description =
    (address = (protocol = tcp)(host = localhost)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orclpdb)
    )
  )

浏览器访问 https://127.0.0.1:5500/em/shell 可以使用用户名密码登录 oracle 的企业管理器，如下图：

查询是否为 cdb（container databse）容器数据库，如下：

-- 查看用户
select username from dba_users
-- 查看数据库是否为cdb容器数据库
select a.name,a.cdb, a.* from v$database a;
-- v$pdbs 视图，该视图只有在 cdb（容器数据库）中才存在，用于列出所有 pdb
select name, open_mode from v$pdbs;

使用 dbca（database configuration assistant） 创建 pdb（pluggable databse）可插拔数据库，如下：

在本机的 tnsnames.org 文件中添加如下配置：

devservermydb =
  (description =
    (address = (protocol = tcp)(host = 10.80.189.43)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = mydb)
    )
  )

远程服务器 10.80.189.43 上的 tnsnames.org 如下：

# tnsnames.ora network configuration file: d:\windows.x64_193000_db_home\network\admin\tnsnames.ora
# generated by oracle configuration tools.

listener_orcl =
  (address = (protocol = tcp)(host = 0.0.0.0)(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 = 0.0.0.0)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orcl)
    )
  )

orclpdb =
  (description =
    (address = (protocol = tcp)(host = 0.0.0.0)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = orclpdb)
    )
  )
  
mydb =
  (description =
    (address = (protocol = tcp)(host = 0.0.0.0)(port = 1521))
    (connect_data =
      (server = dedicated)
      (service_name = mydb)
    )
  )

在本机使用 plsql developer 连接刚刚创建的 pdb ，如下：