oracle 11.2.0.4静默安装
oracle 11.2.0.4静默安装
1、安装包
1.1、上传安装包
xshell可用rz命令，选择安装包。
mobaxterm可用左侧栏上传功能。
Oracle时，没有orainstRoot.sh文件
$ ./runInstaller -silent -force -noconfig -responseFile /var/app/oracle/database/response/db_install_dtstack.rsp
Starting Oracle Universal Installer...
·
·
·
As a root user, execute the following script(s):
\1. /var/app/oracle/product/11.2.4/db_1/root.sh
Successfully Setup Software.
重新安装 [Oracle](http://lib.csdn.net/base/oracle) 时，没有orainstRoot.sh文件。
重新安装RAC时，到excute configuration scripts时，只提示运行：root.sh这个文件，不提示orainstRoot.sh;这个文件，是为什么？
解决：删除/etc/oraInst.loc文件
原因：没删除/etc/oraInst.loc文件
[oracle@iz2zec57gfl6i9vbtdksl1z database]$ ./runInstaller -silent -force -noconfig -responseFile /var/app/oracle/database/response/db_install_dtstack.rsp
Starting Oracle Universal Installer...
·
·
·
As a root user, execute the following script(s):
\1. /home/u01/app/oraInventory/orainstRoot.sh
\2. /home/u01/app/oracle/product/11.2.4/db_1/root.sh
Successfully Setup Software.
5.5、启动Oracle出现：MEMORY_TARGET not supported
# su - oracle
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 13 19:33:17 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
解决：增加挂载盘大小
# df -h | grep shm
tmpfs 497M 205M 292M 42% /dev/shm
# cat /etc/fstab | grep tmpfs
# mount -o remount,size=4G /dev/shm
# df -h | grep shm
tmpfs 4.0G 205M 3.9G 5% /dev/shm
删除其他用户登录oracle进程
5.6、启动Oracle出现：cannot mount
# su - oracle
# sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 327158824 bytes
Database Buffers 79691776 bytes
Redo Buffers 4268032 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
解决：
# su - oracle
$ cd /var/app/oracle/database
$ ipcs -s
------ Semaphore Arrays --------
key semid owner perms nsems
0xbcf2a624 491520 oracle 640 154
0x50f506b0 884737 oracle 640 154
$ ipcrm -s 491520
$ ipcrm -s 884737
$ ipcs -s
------ Semaphore Arrays --------
key semid owner perms nsems
$ ipcs -s
5.7、内存问题
$ sqlplus monitor/monitor123
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 31 18:51:59 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 26
Additional information: 229378
Additional information: 1619001344
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name:
解决：清理内存并重启数据库
1、首先检查linux服务器的当前内存使用情况：
# free -m
发现已经没有空闲内存
2、查看内核允许的最大共享内存段大小
# /proc/sys/kernel/shmmax
发现设置的足够大
3、使用top 查看当前消耗内存大的进程
发现oracle有好几个进程消耗了大量的资源
4、kill掉这几个进程
# kill -s 9 11111(进程号)
5、清理内存
# echo 1 > /proc/sys/vm/drop_caches
6、重启数据库
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup
5.8、锁账号（原因是密码错误次数过多）
$ sqlplus monitor/monitor123
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 31 18:57:23 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-28000: the account is locked
解决：
# su - oracle
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 31 18:50:39 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter user monitor account unlock;
User altered.
5.9、ORA-01045没有创建会话的权限
$ sqlplus monitor/monitor123
ERROR:
ORA-01045: user CUSTOMERCHAT lacks CREATE SESSION privilege; logon denied
解决：重新赋创建会话的权限
# su - oracle
# sqlplus / as sysdba
SQL> grant create session to monitor;
6、oracle使用
6.1、创建实例
# su - oracle
$ sqlplus / as sysdba
6.1.1、创建表空间
SQL> select name from v$datafile; //查询用户表空间文件的路径
NAME
--------------------------------------------------------------------------------
/var/app/oradata/orcl11g/system01.dbf
/var/app/oradata/orcl11g/sysaux01.dbf
/var/app/oradata/orcl11g/undotbs01.dbf
/var/app/oradata/orcl11g/users01.dbf
SQL> CREATE TABLESPACE test datafile '/var/app/oradata/orcl11g/htest.dbf' size 600M autoextend on next 50m maxsize unlimited; //创建用户表空间
Tablespace created.
SQL> CREATE USER htest IDENTIFIED BY htest DEFAULT TABLESPACE test; //创建用户，指定密码和上边创建的用户表空间
User created.
SQL> grant connect,resource,dba to htest; //赋予权限
Grant succeeded.
常用oracle命令
select username from dba_users; //查询已创建的用户
alter user customerchat identified by 123456; //修改用户密码
alter user customerchat account unlock; //解锁用户
drop user 用户名 cascade; //删除用户
drop tablespace tablespace_name; //删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name including contents; //删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including datafiles; //删除空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles; //删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS; //如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
6.1.2、建表
sqlplus htest/htest //用创建的用户名、密码(usernmae/password)登陆
SELECT * FROM ALL_TABLES WHERE OWNER= 'HTEST'; //查询用户下所有表
CREATE TABLE TEST1031
(
ID VARCHAR(20) NOT NULL,
NAME VARCHAR(20) NOT NULL,
CREATE_TIME DATE NOT NULL,
UPDATE_TIME DATE NOT NULL,
CONSTRAINT TEST1031 PRIMARY KEY(ID) USING INDEX TABLESPACE TEST
) TABLESPACE TEST;
INSERT INTO TEST1031 VALUES ( '1','123',to_date ( '2020-11-01' , 'YYYY-MM-DD'), to_date ( '2020-11-02' , 'YYYY-MM-DD') ) ;
commit
转
select * from all_tab_comments; //查询所有用户的表,视图等
select * from user_tab_comments; //查询本用户的表,视图等
select * from all_col_comments; //查询所有用户的表的列名和注释.
select * from user_col_comments; //查询本用户的表的列名和注释
select * from all_tab_columns; //查询所有用户的表的列名等信息(详细但是没有备注).
select * from user_tab_columns; //查询本用户的表的列名等信息(详细但是没有备注).
登录新建的customerchat用户导入sql文件
sqlplus customerchat/customerchat //--sqlplus登录
导入sql文件：@路径名/文件名
报如下错误：
ORA-01031: insufficient privileges
这是因为没有建表的权限
登录dba用户导入
sqlplus / as sysdba
导入sql文件报如下错误：
ORA-01950: no privileges on tablespace 'CUSTOMERCHAT'
这是由于创建表空间时设置了有限的大小，两种方法：
方法1: 授予用户对该表空间的UNLIMITED配额
ALTER USER CUSTOMERCHAT QUOTA UNLIMITED ON customerchat;
方法2: 重新授权resource角色给用户之后，便可以创建表
GRANT RESOURCE TO CUSTOMERCHAT;