Oracle的安装及使用流程
Oracle的安装及使用流程
1.Win10安装Oracle10g
1.1 安装与测试
安装版本: OracleXEUniv10.2.1015.exe
步骤参考:oracleXe下载与安装
安装完成后测试是否正常
# 输入命令连接oracle conn sys as sysdba; # 无密码,直接按回车 # 测试连接的sql语句 select 1 from dual;
1.2 查询数据库信息
#在Oracle中 #查询数据库名 select name,dbid from v$database;或者show parameter db_name; #查询实例名 select instance_name from v$instance;或者show parameter instance_name; #查询数据库域名 select value from v$parameter where name='db_domain';或者show parameter domain; #查询数据库服务器 select value from v$parameter where name='service_name';或者show parameter service;或者show parameter names; #在PL/SQL下 Oracle用户查询其他用户的表一定要使用user.table格式才能查询,每次用户名.表名嫌麻烦的话。可以使用同义词,意思将user.table访问格式改成table格式即可 #创建同义词语句格式: create public synonym table_name for user.table_name; public很关键词,访问权限修饰,若想某表的授权用户也能用同义词查询,必须要用public访问修饰符。 #举例说明 原本"表前缀.表名"格式才能访问select * from PAASDATA.paas_city_def; 创建同义词:create public synonym table_name for PAASDATA.paas_city_def; 可直接用表名:select * from paas_city_def;
1.3 自定义用户和表空间
执行创建命令
conn sys/root as sysdba; # 查询实例 select instance_name from v$instance; # 查询数据库 select name from v$datafile; # 创建表空间 create tablespace CK_BUS_TABLE_SPACE datafile 'D:/AsiaInfo/oraclexe/oraclexe/oradata/XE/CK_BUS_TABLE_SPACE.dbf' size 200m autoextend on extent management local segment space management auto; # 查询数据库 select name from v$datafile; # 创建用户并指定表空间 # CREATE USER 用户 IDENTIFIED BY 用户密码 DEFAULT TABLESPACE NOTIFYDB; CREATE USER CK_BUS IDENTIFIED BY ck_bus DEFAULT TABLESPACE CK_BUS_TABLE_SPACE; # 给用户授予dba的权限 grant dba to CK_BUS; # 查看当前数据库连接数 select count(*) from v$process; # 查看数据库允许的最大连接数 select value from v$parameter where name = 'processes'; # 如果数据库连接数超过最大连接数,请修改最大连接数,我这里修改成了1000 alter system set processes = 1000 scope = spfile; # 重启 shutdown immediate; startup;
测试创建结果
# 登录账号 conn CK_BUS/ck_bus@xe; select * from dual;
2.CentOS7安装Oracle11gR2
2.1 安装与测试
#CentOS Linux release 7.6.1810 (Core) redhat-7 yum -y install binutils* compat-libcap1* compat-libstdc++* gcc* gcc-c++* glibc* glibc-devel* ksh* libaio* libaio-devel* libgcc* libstdc++* libstdc++-devel* libXi* libXtst* make* sysstat* elfutils* unixODBC* unzip lrzsz groupadd oinstall groupadd dba useradd -g dba -m oracle passwd oracle id oracle mkdir -p /data/oracle mkdir -p /data/oracle/inventory mkdir -p /data/oracle/src chown -R oracle:oinstall /data/oracle chown -R oracle:oinstall /data/oracle/inventory chown -R oracle:oinstall /data/oracle/src #解压linux.x64_11gR2安装包 unzip linux.x64_11gR2_database_1of2.zip -d /data/oracle/src unzip linux.x64_11gR2_database_2of2.zip -d /data/oracle/src chown -R oracle:oinstall /data/oracle/src ll /data/oracle/src su oracle vim /data/oracle/src/database/response/db_install.rsp /data/oracle/src/database/runInstaller -silent -responseFile /data/oracle/src/database/response/db_install.rsp -ignorePrereq /data/oracle/product/11.2.0/db_1/root.sh vim /data/oracle/src/database/response/netca.rsp /data/oracle/product/11.2.0/db_1/bin/netca /silent /responseFile /data/oracle/src/database/response/netca.rsp vim /data/oracle/src/database/response/dbca.rsp /data/oracle/product/11.2.0/db_1/bin/dbca -silent -responseFile /data/oracle/src/database/response/dbca.rsp ps -ef | grep ora_ | grep -v grep vim /data/oracle/product/11.2.0/db_1/bin/dbstart vim /data/oracle/product/11.2.0/db_1/bin/dbshut # 遇到问题 # 解决linux中oracle的/etc/oratab文件的缺失导致数据库启动失败https://blog.csdn.net/kruie/article/details/88676777 vim /etc/oratab orcl:/data/oracle/product/11.2.0:Y /data/oracle/product/11.2.0/db_1/bin/dbstart chmod +x /etc/rc.d/rc.local vim /etc/rc.d/rc.local su oracle -lc "/data/oracle/product/11.2.0/db_1/bin/lsnrctl start" su oracle -lc "/data/oracle/product/11.2.0/db_1/bin/dbstart" su oracle sqlplus / as sysdba
2.2 创建用户和表空间
- 以超级管理员身份登录
- 创建表空间
- 创建用户
- 给用户授权
- 查询测试
# linux创建数据库Oracle用户,linux下ORACLE数据库中创建新用户 # https://blog.csdn.net/weixin_29325515/article/details/116349855 su - oracle sqlplus /nolog # 通过超级管理员以dba的身份登录 conn sys/root as sysdba; # conn /as sysdba # startup select name from v$datafile; # 查询实例 select instance_name from v$instance; # 创建表空间 CREATE TABLESPACE MY_ORACLE_TABLE_SPACE DATAFILE '/data/oracle/oradata/orcl/my_oracle_table_space.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; select name from v$datafile; # 创建用户并指定表空间 # CREATE USER 用户 IDENTIFIED BY 用户密码 DEFAULT TABLESPACE NOTIFYDB; CREATE USER CK_BUS IDENTIFIED BY ck_bus DEFAULT TABLESPACE MY_ORACLE_TABLE_SPACE; # 给用户授予dba的权限 grant dba to CK_BUS; exit; db_name=ora_ck db_block_size=8192 control_files=(/data/oracle/oradata/check/ora_ck.ctl) undo_tablespace=undotbs1 undo_management=auto sga_max_size=1024M sga_target=1024M pga_aggregate_target=1024M log_buffer=10485760 processes=300 open_cursors=2000 sessions=330 audit_trail ='db' db_domain='' remote_login_passwordfile='EXCLUSIVE' compatible ='11.2.0.1.0' export ORACLE_SID=ora_ck sqlplus / as sysdba mkdir redo mkdir data startup nomount pfile='/data/oracle/oradata/check/ora_ck.ora' CREATE DATABASE ora_ck USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle DATAFILE '/data/oracle/oradata/check/data/system_01.dbf' SIZE 1024m autoextend on SYSAUX DATAFILE '/data/oracle/oradata/check/data/sysaux_01.dbf' SIZE 512m autoextend on UNDO TABLESPACE undotbs1 DATAFILE '/data/oracle/oradata/check/data/undotbs1_01.dbf' SIZE 1024m autoextend on DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/data/oracle/oradata/check/data/temp_01.dbf' SIZE 1024m REUSE autoextend on LOGFILE GROUP 1 ('/data/oracle/oradata/check/redo/redo11.log','/data/oracle/oradata/check/redo/redo12.log') SIZE 50m, GROUP 2 ('/data/oracle/oradata/check/redo/redo21.log','/data/oracle/oradata/check/redo/redo22.log') SIZE 50m, GROUP 3 ('/data/oracle/oradata/check/redo/redo31.log','/data/oracle/oradata/check/redo/redo32.log') SIZE 50m CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET UTF8; create spfile from pfile='/data/oracle/oradata/check/ora_ck.ora'; @?/rdbms/admin/catalog @?/rdbms/admin/catproc lsnrctl status lsnrctl stop lsnrctl start lsnrctl reload hostname more /etc/hosts ps -ef|grep LISTENER kill 4562 # 开机自启动 cat /etc/oratab su - oracle export ORACLE_SID=ora_ck sqlplus / as sysdba # 动态绑定,不需要考虑listener.ora和tnsnames.ora alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.31.214)(PORT=1521))'; alter system register; show parameter local_listener show parameter service shutdown immediate startup # 不使用该语句,则默认连接orcl数据库 export ORACLE_SID=ora_ck sqlplus / as sysdba # 查询实例 select instance_name from v$instance; # 查询数据库 select name from v$datafile; # 创建表空间 CREATE TABLESPACE CK_BUS_TABLE_SPACE DATAFILE '/data/oracle/oradata/check/data/ck_bus_table_space.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; # 查询数据库 select name from v$datafile; # 创建用户并指定表空间 # CREATE USER 用户 IDENTIFIED BY 用户密码 DEFAULT TABLESPACE NOTIFYDB; CREATE USER CK_BUS IDENTIFIED BY ck_bus DEFAULT TABLESPACE CK_BUS_TABLE_SPACE; # 给用户授予dba的权限 grant dba to CK_BUS;
参考网址
CentOS7安装Oracle完整教程(超级详细,亲测完美)
Linux服务器创建Oracle实例,命令行安装详解
Linux下安装ORACLE(一周成果、全是干货!)
CentOS7安装Oracle数据库的全流程
解决数据库连接报错Listener refused the connection with the following error: ORA-12505
linux 启动监听报 TNS-12545、TNS-12560、TNS-00515问题处理办法
Oracle 11g安装使用、备份恢复并与SpringBoot集成
oracle jdbctype null,Oracle数据库之springboot 项目mybatis plus 设置 jdbcTypeForNull
怎么删除oracle用户及表空间
解决ORA-01034: ORACLE not available
如何重启oracle数据库及监听
Oracle创建实例dbca错误
Oracle删除数据的三种方式
使用Druid连接池远程访问阿里云服务器中安装的Oracle库,隔一段时间总是连接超时的解决方案
linux centos7环境下修改oracle19c监听IP并重启
TNS-12542: TNS:address already in use 问题
NS-01201: Listener cannot find executable /u01/oracle/bin/extproc for SID orcl Listener failed to
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
监控多台oracle,ORACLE 一个监听开启多个实例
Oracle processes和sessions参数(进程连接数&会话连接数)
解决‘ORA-12505, TNS:listener does not currently know of SID given in connect descriptor’