@ 注意：模板内容修改如下（目录、用户等没有变化的情况下，可以直接使用）。
$ cp /home/oracle/database/response/db_install.rsp /home/oracle
$ vi db_install.rsp
###################################################################### Copyright(c) Oracle Corporation 1998,2008. All rights reserved.#### #### Specify values for the variables listed below to customize #### your installation. #### #### Each variable is associated with a comment. The comment #### can help to populate the variables with the appropriate #### values. #### #### IMPORTANT NOTE: This file contains plain text passwords and #### should be secured to have read permission only by oracle user #### or db administrator who owns this installation. #### #######################################################################------------------------------------------------------------------------------# Do not change the following system generated value.#------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
#------------------------------------------------------------------------------# Specify the installation option.# It can be one of the following:# 1. INSTALL_DB_SWONLY# 2. INSTALL_DB_AND_CONFIG# 3. UPGRADE_DB#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_AND_CONFIG
#-------------------------------------------------------------------------------# Specify the hostname of the system as set during the install. It can be used# to force the installation to use an alternative hostname rather than using the# first hostname found on the system. (e.g., for systems with multiple hostnames# and network interfaces)#-------------------------------------------------------------------------------
ORACLE_HOSTNAME=localhost
#-------------------------------------------------------------------------------# Specify the Unix group to be set for the inventory directory.#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall
#-------------------------------------------------------------------------------# Specify the location which holds the inventory files.#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/home/oracle/tools/oraInventory
#-------------------------------------------------------------------------------# Specify the languages in which the components will be installed.## en : English ja : Japanese# fr : French ko : Korean# ar : Arabic es : Latin American Spanish# bn : Bengali lv : Latvian# pt_BR: Brazilian Portuguese lt : Lithuanian# bg : Bulgarian ms : Malay# fr_CA: Canadian French es_MX: Mexican Spanish# ca : Catalan no : Norwegian# hr : Croatian pl : Polish# cs : Czech pt : Portuguese# da : Danish ro : Romanian# nl : Dutch ru : Russian# ar_EG: Egyptian zh_CN: Simplified Chinese# en_GB: English (Great Britain) sk : Slovak# et : Estonian sl : Slovenian# fi : Finnish es_ES: Spanish# de : German sv : Swedish# el : Greek th : Thai# iw : Hebrew zh_TW: Traditional Chinese# hu : Hungarian tr : Turkish# is : Icelandic uk : Ukrainian# in : Indonesian vi : Vietnamese# it : Italian## Example : SELECTED_LANGUAGES=en,fr,ja#------------------------------------------------------------------------------
SELECTED_LANGUAGES=en,zh_CN
#------------------------------------------------------------------------------# Specify the complete path of the Oracle Home.#------------------------------------------------------------------------------
ORACLE_HOME=/home/oracle/tools/oracle11g/product/11.2.0/dbhome_1
#------------------------------------------------------------------------------# Specify the complete path of the Oracle Base.#------------------------------------------------------------------------------
ORACLE_BASE=/home/oracle/tools/oracle11g
#------------------------------------------------------------------------------# Specify the installation edition of the component.## The value should contain only one of these choices.# EE : Enterprise Edition# SE : Standard Edition# SEONE : Standard Edition One# PE : Personal Edition (WINDOWS ONLY)#------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE
#------------------------------------------------------------------------------# This variable is used to enable or disable custom install.## true : Components mentioned as part of 'customComponents' property# are considered for install.# false : Value for 'customComponents' is not considered.#------------------------------------------------------------------------------
oracle.install.db.isCustomInstall=false
#------------------------------------------------------------------------------# This variable is considered only if 'IsCustomInstall' is set to true.## Description: List of Enterprise Edition Options you would like to install.## The following choices are available. You may specify any# combination of these choices. The components you choose should# be specified in the form "internal-component-name:version"# Below is a list of components you may specify to install.## oracle.rdbms.partitioning:11.2.0.1.0 - Oracle Partitioning# oracle.rdbms.dm:11.2.0.1.0 - Oracle Data Mining# oracle.rdbms.dv:11.2.0.1.0 - Oracle Database Vault# oracle.rdbms.lbac:11.2.0.1.0 - Oracle Label Security# oracle.rdbms.rat:11.2.0.1.0 - Oracle Real Application Testing# oracle.oraolap:11.2.0.1.0 - Oracle OLAP#------------------------------------------------------------------------------
oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0
################################################################################ ## PRIVILEGED OPERATING SYSTEM GROUPS ## ------------------------------------------ ## Provide values for the OS groups to which OSDBA and OSOPER privileges ## needs to be granted. If the install is being performed as a member of the ## group "dba", then that will be used unless specified otherwise below. ## #################################################################################------------------------------------------------------------------------------# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.#------------------------------------------------------------------------------
oracle.install.db.DBA_GROUP=dba
#------------------------------------------------------------------------------# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.#------------------------------------------------------------------------------
oracle.install.db.OPER_GROUP=oinstall
#------------------------------------------------------------------------------# Specify the cluster node names selected during the installation.#------------------------------------------------------------------------------
oracle.install.db.CLUSTER_NODES=#------------------------------------------------------------------------------# Specify the type of database to create.# It can be one of the following:# - GENERAL_PURPOSE/TRANSACTION_PROCESSING# - DATA_WAREHOUSE#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
#------------------------------------------------------------------------------# Specify the Starter Database Global Database Name.#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.globalDBName=orcl
#------------------------------------------------------------------------------# Specify the Starter Database SID.#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.SID=orcl
#------------------------------------------------------------------------------# Specify the Starter Database character set.## It can be one of the following:# AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,# EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,# BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,# AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,# IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,# KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,# ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.characterSet=AL32UTF8
#------------------------------------------------------------------------------# This variable should be set to true if Automatic Memory Management# in Database is desired.# If Automatic Memory Management is not desired, and memory allocation# is to be done manually, then set it to false.#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryOption=true
#------------------------------------------------------------------------------# Specify the total memory allocation for the database. Value(in MB) should be# at least 256 MB, and should not exceed the total physical memory available# on the system.# Example: oracle.install.db.config.starterdb.memoryLimit=512#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryLimit=512
#------------------------------------------------------------------------------# This variable controls whether to load Example Schemas onto the starter# database or not.#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.installExampleSchemas=false
#------------------------------------------------------------------------------# This variable includes enabling audit settings, configuring password profiles# and revoking some grants to public. These settings are provided by default.# These settings may also be disabled.#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.enableSecuritySettings=true
################################################################################ ## Passwords can be supplied for the following four schemas in the ## starter database: ## SYS ## SYSTEM ## SYSMAN (used by Enterprise Manager) ## DBSNMP (used by Enterprise Manager) ## ## Same password can be used for all accounts (not recommended) ## or different passwords for each account can be provided (recommended) ## #################################################################################------------------------------------------------------------------------------# This variable holds the password that is to be used for all schemas in the# starter database.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.ALL=Oracle#123456#-------------------------------------------------------------------------------# Specify the SYS password for the starter database.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYS=#-------------------------------------------------------------------------------# Specify the SYSTEM password for the starter database.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSTEM=#-------------------------------------------------------------------------------# Specify the SYSMAN password for the starter database.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSMAN=#-------------------------------------------------------------------------------# Specify the DBSNMP password for the starter database.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.DBSNMP=#-------------------------------------------------------------------------------# Specify the management option to be selected for the starter database.# It can be one of the following:# 1. GRID_CONTROL# 2. DB_CONTROL#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.control=DB_CONTROL
#-------------------------------------------------------------------------------# Specify the Management Service to use if Grid Control is selected to manage# the database.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=#-------------------------------------------------------------------------------# This variable indicates whether to receive email notification for critical# alerts when using DB control.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
#-------------------------------------------------------------------------------# Specify the email address to which the notifications are to be sent.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.dbcontrol.emailAddress=lijie@zte.com
#-------------------------------------------------------------------------------# Specify the SMTP server used for email notifications.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.dbcontrol.SMTPServer=################################################################################ ## SPECIFY BACKUP AND RECOVERY OPTIONS ## ------------------------------------ ## Out-of-box backup and recovery options for the database can be mentioned ## using the entries below. ## #################################################################################------------------------------------------------------------------------------# This variable is to be set to false if automated backup is not required. Else# this can be set to true.#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.enable=false
#------------------------------------------------------------------------------# Regardless of the type of storage that is chosen for backup and recovery, if# automated backups are enabled, a job will be scheduled to run daily at# 2:00 AM to backup the database. This job will run as the operating system# user that is specified in this variable.#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.osuid=#-------------------------------------------------------------------------------# Regardless of the type of storage that is chosen for backup and recovery, if# automated backups are enabled, a job will be scheduled to run daily at# 2:00 AM to backup the database. This job will run as the operating system user# specified by the above entry. The following entry stores the password for the# above operating system user.#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.ospwd=#-------------------------------------------------------------------------------# Specify the type of storage to use for the database.# It can be one of the following:# - FILE_SYSTEM_STORAGE# - ASM_STORAGE#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE
#-------------------------------------------------------------------------------# Specify the database file location which is a directory for datafiles, control# files, redo logs.## Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/home/oracle/tools/oracle11g/oradata
#-------------------------------------------------------------------------------# Specify the backup and recovery location.## Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=/home/oracle/tools/oracle11g/fast_recovery_area
#-------------------------------------------------------------------------------# Specify the existing ASM disk groups to be used for storage.## Applicable only when oracle.install.db.config.starterdb.storage=ASM#-------------------------------------------------------------------------------
oracle.install.db.config.asm.diskGroup=#-------------------------------------------------------------------------------# Specify the password for ASMSNMP user of the ASM instance.## Applicable only when oracle.install.db.config.starterdb.storage=ASM_SYSTEM#-------------------------------------------------------------------------------
oracle.install.db.config.asm.ASMSNMPPassword=#------------------------------------------------------------------------------# Specify the My Oracle Support Account Username.## Example : MYORACLESUPPORT_USERNAME=metalink#------------------------------------------------------------------------------
MYORACLESUPPORT_USERNAME=#------------------------------------------------------------------------------# Specify the My Oracle Support Account Username password.## Example : MYORACLESUPPORT_PASSWORD=password#------------------------------------------------------------------------------
MYORACLESUPPORT_PASSWORD=#------------------------------------------------------------------------------# Specify whether to enable the user to set the password for# My Oracle Support credentials. The value can be either true or false.# If left blank it will be assumed to be false.## Example : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true#------------------------------------------------------------------------------
SECURITY_UPDATES_VIA_MYORACLESUPPORT=#------------------------------------------------------------------------------# Specify whether user wants to give any proxy details for connection.# The value can be either true or false. If left blank it will be assumed# to be false.## Example : DECLINE_SECURITY_UPDATES=false#------------------------------------------------------------------------------
DECLINE_SECURITY_UPDATES=true
#------------------------------------------------------------------------------# Specify the Proxy server name. Length should be greater than zero.## Example : PROXY_HOST=proxy.domain.com#------------------------------------------------------------------------------
PROXY_HOST=#------------------------------------------------------------------------------# Specify the proxy port number. Should be Numeric and atleast 2 chars.## Example : PROXY_PORT=25#------------------------------------------------------------------------------
PROXY_PORT=#------------------------------------------------------------------------------# Specify the proxy user name. Leave PROXY_USER and PROXY_PWD# blank if your proxy server requires no authentication.## Example : PROXY_USER=username#------------------------------------------------------------------------------
PROXY_USER=#------------------------------------------------------------------------------# Specify the proxy password. Leave PROXY_USER and PROXY_PWD# blank if your proxy server requires no authentication.## Example : PROXY_PWD=password#------------------------------------------------------------------------------
PROXY_PWD=
静默安装Oracle 11gR2
操作用户：oracle
@ 操作目录：/home/oracle/database
@ 命令：
【方式1】【屏蔽异常信息】
$ ./runInstaller -silent -ignorePrereq -responseFile /home/oracle/db_install.rsp
【方式2】【屏蔽部分异常信息，会有警告，可忽略】
$ ./runInstaller -silent -ignoreSysPrereqs -responseFile /home/oracle/db_install.rsp
>>>>>>
正在启动 Oracle Universal Installer...
检查临时空间: 必须大于 120 MB。 实际为 38740 MB 通过
检查交换空间: 必须大于 150 MB。 实际为 4095 MB 通过
准备从以下地址启动 Oracle Universal Installer /tmp/OraInstall2019-04-16_03-41-22PM. 请稍候...[oracle@jetflow136 database]$ 可以在以下位置找到本次安装会话的日志:
/home/oracle/tools/oraInventory/logs/installActions2019-04-16_03-41-22PM.log
安装结束后会打印如下信息：
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh#Root scripts to run
/home/oracle/tools/oraInventory/orainstRoot.sh
/home/oracle/tools/oracle11g/product/11.2.0/dbhome_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
4. Return to this window and hit "Enter" key to continue
Successfully Setup Software.
<<<<<<
@ 注意：如果需要查询安装过程日志，请重新开个终端，使用命令：tailf /home/oracle/tools/oraInventory/logs/installActions2019-04-16_03-41-22PM.log 打印日志。
@ 告警：只要不会导致程序退出，即可忽略一些警告异常。
@ 重复安装清理操作：
$ rm -rf /etc/oratab
$ rm -rf /home/oracle/tools
$ mkdir -p /home/oracle/tools/oracle11g
$ lsnrctl status
>>>>>>
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2018 12:52:45
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-JAN-2018 12:49:29
Uptime 0 days 0 hr. 3 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/tools/oracle11g/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/tools/oracle11g/diag/tnslsnr/vmco0240/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmco0240)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...The command completed successfully
建表空间
@创建存储目录
$ mkdir -p /home/oracle/oradata/ufgov
@ 连接数据库
$ sqlplus /nolog
SQL> connect / as sysdba
@ 查询表空间
SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;
@ 创建表空间，表空间路径可以在其他盘符
SQL> create tablespace UFGOV datafile '/home/oracle/oradata/ufgov/UFGOV.dbf' size 500m reuse autoextend on next 1m maxsize unlimited;
@ 创建临时表空间，表空间路径可以在其他盘符
SQL> create temporary tablespace UFGOV_TEMP tempfile '/home/oracle/oradata/ufgov/UFGOV_TEMP.dbf' size 500m reuse autoextend on next 1m maxsize unlimited;
@ 创建用户，指定表空间，临时表空间
SQL> create user usrufgov identified by usrufgov default tablespace UFGOV temporary tablespace UFGOV_TEMP;
@ DBA权限，授予用户数据库管理员权限
SQL> grant dba,connect,resource,imp_full_database,exp_full_database to usrufgov;
(或者 grant all privileges to usrufgov;)
@ 授予用户创建权限
SQL> grant create session to usrufgov;
SQL> grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to usrufgov;
SQL> grant execute on SYS.DBMS_EXPORT_EXTENSION to usrufgov;
@ 可选权限sysdba、sysoper
SQL> grant sysdba to usrufgov;
SQL> grant sysoper to usrufgov;
@ 设置在用户 usrufgov 在 UFGOV 表空间配额不受限。
SQL> alter user usrufgov quota unlimited on UFGOV;
@ 提交修改
SQL> commit;
【附录】
drop tablespace UFGOV including contents and datafiles cascade constraints; -----删除表空间
drop tablespace UFGOV_TEMP including contents and datafiles cascade constraints; -----删除临时表空间
drop user usrufgov cascade; ------删除用户
--建表
--student表+注释
create table student(
sno varchar2(3) not null,
sname varchar2(9) not null,
ssex varchar2(3) not null,
sbirthday date,
sclass varchar2(5),
constraint pk_student primary key(sno)
);
comment on column student.sno is '学号（主键）';
comment on column student.sname is '学生姓名';
comment on column student.ssex is '学生性别';
comment on column student.sbirthday is '学生出生年月日';
comment on column student.sclass is '学生所在班级';
--course表+注释
create table course(
cno varchar2(5) not null,
cname varchar2(15) not null,
tno varchar2(3) not null,
constraint pk_course primary key(cno)
);
comment on column course.cno is '课程编号（主键）';
comment on column course.cname is '课程名称';
comment on column course.tno is '教工编号（外键）';
--score表+注释
create table score(
sno varchar2(3) not null,
cno varchar2(5) not null,
degree number(4,1),
constraint pk_score primary key(sno,cno)
);
comment on column score.sno is '学号（主键）';
comment on column score.cno is '课程编号（主键）';
comment on column score.degree is '成绩';
--teacher表+注释
create table teacher(
tno varchar2(3) not null,
tname varchar2(9) not null,
tsex varchar2(3) not null,
tbirthday date,
prof varchar2(9),
depart varchar2(15) not null,
constraint pk_teacher primary key(tno)
);
comment on column teacher.tno is '教工编号（主键）';
comment on column teacher.tname is '教工姓名';
comment on column teacher.tsex is '教工性别';
comment on column teacher.tbirthday is '教工出生年月';
comment on column teacher.prof is '职称';
comment on column teacher.depart is '教工所在单位';
--添加外键
alter table course add constraint fk_tno foreign key(tno) references teacher(tno);
alter table score add constraint fk_sno foreign key(sno) references student(sno);
alter table score add constraint fk_cno foreign key(cno) references course(cno);
--添加数据
--Student表
insert into student(sno,sname,ssex,sbirthday,sclass) values(108,'曾华','男',to_date('1977-09-01','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(105,'匡明','男',to_date('1975-10-02','yyyy-mm-dd'),95031);
insert into student(sno,sname,ssex,sbirthday,sclass) values(107,'王丽','女',to_date('1976-01-23','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(101,'李军','男',to_date('1976-02-20','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(109,'王芳','女',to_date('1975-02-10','yyyy-mm-dd'),95031);
insert into student(sno,sname,ssex,sbirthday,sclass) values(103,'陆君','男',to_date('1974-06-03','yyyy-mm-dd'),95031);
--teacher表
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(804,'李诚','男',to_date('1958/12/02','yyyy-mm-dd'),'副教授','计算机系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(856,'张旭','男',to_date('1969/03/12','yyyy-mm-dd'),'讲师','电子工程系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(825,'王萍','女',to_date('1972/05/05','yyyy-mm-dd'),'助教','计算机系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(831,'刘冰','女',to_date('1977/08/14','yyyy-mm-dd'),'助教','电子工程系');
--course表(添加外键后要先填teacher表中数据去满足外键约束)
insert into course(cno,cname,tno) values('3-105','计算机导论',825);
insert into course(cno,cname,tno) values('3-245','操作系统',804);
insert into course(cno,cname,tno) values('6-166','数字电路',856);
insert into course(cno,cname,tno) values('9-888','高等数学',831);
--score表（添加外键后要先填Student，course表中数据去满足外键约束）
insert into score(sno,cno,degree) values(103,'3-245',86);
insert into score(sno,cno,degree) values(105,'3-245',75);
insert into score(sno,cno,degree) values(109,'3-245',68);
insert into score(sno,cno,degree) values(103,'3-105',92);
insert into score(sno,cno,degree) values(105,'3-105',88);
insert into score(sno,cno,degree) values(109,'3-105',76);
insert into score(sno,cno,degree) values(101,'3-105',64);
insert into score(sno,cno,degree) values(107,'3-105',91);
insert into score(sno,cno,degree) values(108,'3-105',78);
insert into score(sno,cno,degree) values(101,'6-166',85);
insert into score(sno,cno,degree) values(107,'6-166',79);
insert into score(sno,cno,degree) values(108,'6-166',81);
常用操作语句
@ 连接数据库
$ sqlplus /nolog
SQL> connect / as sysdba
@ 创建表
create table t1(id int not null,name varchar(8) not null,tel int not null);
@ 增
insert into t1(id,name,tel) values ('1','linux','13812341234');
@ 改
update t1 set tel='15512345678' where tel='13812341234';
@清空表
truncate table t1;
@ 修改表名
rename t1 to tb1;
@ 增加字段
alter table tb1 add sex char(4);
@ 修改字段名
alter table tb1 rename column tel to tell;
@ 删除字段
alter table tb1 drop column sex;
@ 修改字段类型
alter table tb1 modify sex int;
常用管理语句
@ 连接数据库
$ sqlplus /nolog
SQL> connect / as sysdba
@ 查看当前登录的用户
select user from dual;
show user;
@ 查询所有表
dba_tables : 系统里所有的表的信息，需要DBA权限才能查询
all_tables : 当前用户有权限的表的信息（只要对某个表有任何权限，即可在此视图中看到表的相关信息）
user_tables: 当前用户名下的表的信息
@ 查看表空间文件位置
select * from dba_data_files;
select * from dba_data_files where tablespace_name='UFGOV';
@ 查看当前所用数据库
select instance_name from V$instance;
@ 查看当前用户所有表
select table_name from user_tables;
@ 查询数据库表行数
select t.table_name,t.num_rows from user_tables t;
@ 查看表空间的名称及大小
SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;
@ 查看表空间物理文件的名称及大小
SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;
@ 表空间是否为自动扩展
select tablespace_name,file_name,autoextensible from dba_data_files;
@ 开启自动扩展
alter database datafile '/home/oracle/oradata/ufgov/UFGOV.dbf' autoextend on;
@ 关闭自动扩展
alter database datafile '/home/oracle/oradata/ufgov/UFGOV.dbf' autoextend off;
@ 开启自动扩展，并限制文件最大值
alter database datafile '/home/oracle/oradata/ufgov/UFGOV.dbf' autoextend on NEXT 50M maxsize 10G;
@ 删除表空间
drop tablespace UFGOV including contents and datafiles cascade constraints;
@ 删除用户
drop user usrufgov cascade;
@ Oracle表空间大小及所剩空间大小
方法一：
select f.tablespace_name,
a.total,
u.used,
f.free,
round((u.used / a.total) * 100) "% USED",
round((f.free / a.total) * 100) "% FREE"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) used
from dba_extents
group by tablespace_name) u,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) f
where a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;
方法二：更快
select a.tablespace_name,
a.bytes / 1024 / 1024 "Sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;
@ 查看回滚段名称及大小
SELECT segment_name,tablespace_name,r.status,(initial_extent / 1024) initialextent,(next_extent / 1024) nextextent,max_extents,v.curext curextent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name;
@ 查看控制文件
SELECT NAME FROM v$controlfile;
@ 查看日志文件
SELECT MEMBER FROM v$logfile;
@ 查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
@ 查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
@ 查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
@ 查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
@ 查看表占用表空间的大小，查看各表数据的行数
SELECT t.table_name, t.NUM_ROWS, s.BYTES, t.OWNER, t.TABLESPACE_NAME
FROM dba_tables t, user_segments s
where t.TABLE_NAME = s.segment_name
ORDER BY 3 DESC;
@ 给UFGOV表空间再添加数据文件
ALTER TABLESPACE UFGOV ADD DATAFILE '/home/oracle/oradata/ufgov/UFGOV01.dbf' SIZE 2048M;
@ 删除某个数据文件
alter tablespace UFGOV drop datafile '/home/oracle/oradata/ufgov/UFGOV01.dbf';
@ 修改数据文件大小
alter database datafile '/home/oracle/oradata/ufgov/UFGOV01.dbf' RESIZE 10240M;
@ 移动数据文件，或者数据文件所在的磁盘损坏，需要将这些文件的副本移动到其他的磁盘，然后恢复。
举例：移动 UFGOV.dbf
--1.确定数据文件所在的表空间
select tablespace_name from dba_data_files where fille_name='/home/oracle/oradata/ufgov/UFGOV.dbf';
--2.使表空间脱机,确保数据文件的一致性，将表空间转变为offline的状态。
alter tablespace UFGOV offline;
--3.使用命令移动数据文件到指定的目标位置
host move '/home/oracle/oradata/ufgov/UFGOV.dbf' '/data1/oracle/oradata/ufgov/UFGOV.dbf';
--4.执行alter tablespace命令 --在物理上移动了数据之后，还必须执行alter tablespace命令对数据库文件进行逻辑修改
alter tablespace UFGOV rename datafile '/home/oracle/oradata/ufgov/UFGOV.dbf' to '/data1/oracle/oradata/ufgov/UFGOV.dbf';
--5.使表空间联机
alter tablespace UFGOV online;
扫一扫
225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
