郑州网站制作_郑州网页制作_做网站设计_河南网站制作网,湖南网站建设价格,莱芜定制网站建设公司,动画设计素材Oracle的安装及使用流程
1.Win10安装Oracle10g
1.1 安装与测试
安装版本#xff1a; OracleXEUniv10.2.1015.exe 步骤参考#xff1a;oracleXe下载与安装 安装完成后测试是否正常 # 输入命令连接oracle
conn sys as sysdba;
# 无密码#xff0c;直接按回车
# 测试连接的s…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 namedb_domain;或者show parameter domain;
#查询数据库服务器
select value from v$parameter where nameservice_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_busxe;
select * from dual;2.CentOS7安装Oracle11gR2
2.1 安装与测试
#CentOS Linux release 7.6.1810 (Core)
redhat-7yum -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 lrzszgroupadd 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/srcsu 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.rspvim /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.rspps -ef | grep ora_ | grep -v grepvim /data/oracle/product/11.2.0/db_1/bin/dbstartvim /data/oracle/product/11.2.0/db_1/bin/dbshut# 遇到问题
# 解决linux中oracle的/etc/oratab文件的缺失导致数据库启动失败https://blog.csdn.net/kruie/article/details/88676777vim /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/dbstartsu oracle
sqlplus / as sysdba2.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_nameora_ck
db_block_size8192
control_files(/data/oracle/oradata/check/ora_ck.ctl)
undo_tablespaceundotbs1
undo_managementauto
sga_max_size1024M
sga_target1024M
pga_aggregate_target1024M
log_buffer10485760
processes300
open_cursors2000
sessions330
audit_trail db
db_domain
remote_login_passwordfileEXCLUSIVE
compatible 11.2.0.1.0export ORACLE_SIDora_ck
sqlplus / as sysdbamkdir redo
mkdir data
startup nomount pfile/data/oracle/oradata/check/ora_ck.oraCREATE 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/catproclsnrctl status
lsnrctl stop
lsnrctl start
lsnrctl reload
hostname
more /etc/hosts
ps -ef|grep LISTENER
kill 4562
# 开机自启动
cat /etc/oratabsu - oracle
export ORACLE_SIDora_ck
sqlplus / as sysdba
# 动态绑定不需要考虑listener.ora和tnsnames.ora
alter system set LOCAL_LISTENER(ADDRESS(PROTOCOLTCP)(HOST172.22.31.214)(PORT1521));
alter system register;
show parameter local_listener
show parameter service
shutdown immediate
startup# 不使用该语句则默认连接orcl数据库
export ORACLE_SIDora_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’
