Oracle11g Rac创建数据库语句与其细节.doc

登录数据库 sqlplus username/password ORACLE_SID(环境变量) 数据库对应的实例的名字，该名字决定了连接哪个具 体的数据库 show user 查看当前用户 desc tablename 查看表结构 desc describe的缩写 查询员工的姓名，和工资 select first_name,salary from s_emp; 查询员工的名字和职位 select first_name,title from s_emp; edit 修改sql语句 l 查看 /运行 select * from s_dept; 列出部门表的所有信息 列出每个员工的年薪 select first_name,salary*12 from s_emp; 列出每个员工的总收入 select first_name,salary*12*(1+commission_pct/100) tol sal from s_emp; 空值会导致算术表达式为空，Oracle认为null为无穷大 select first_name,salary*12*(1+nvl(commission_pct,0)/100) tol_sal, Commission_pct from s_emp nvl(p1,p2) if(p1 is null) then return p2; else return p1; coalesce 和nvl实现的是同样的功能，nvl只能用在Oracle，coalesce可以用在多种数据库,nvl的两个参数的类型必须一致 继续试验： [oracle@oel501 ~]$ cat init.ora |grep -v ^# initRAC1.ora; [oracle@oel501 ~]$ vi initRAC1.ora *.compatible=11.2.0 *.control_files=+DATA/RAC/CONTROLFILE/control00.ctl *.db_block_size=8192 *.db_name=RAC *.open_cursors=300 *.pga_aggregate_target=120M *.processes=150 *.remote_login_passwordfile=EXCLUSIVE *.sga_target=256M [oracle@oel501 ~]$ sqlplus / as sysdba SQL startup nomount; SQL create spfile=+DATA/RAC/SPFILE/spfileRAC.ora from pfile; SQL shutdown abort; [oracle@oel501 ~]$ vi initRAC1.ora spfile=+DATA/RAC/SPFILE/spfileRAC.ora [oracle@oel501 ~]$ sqlplus / as sysdba SQL startup nomount SQL CREATE DATABASE RAC ?2?USER SYS IDENTIFIED BY zzzz ?3?USER SYSTEM IDENTIFIED BY zzzz ?4?LOGFILE ?5牋?GROUP 1 (+DATA/RAC/ONLINELOG/redo1_01.rdo) SIZE 25M BLOCKSIZE 512, ?6牋?GROUP 2 (+DATA/RAC/ONLINELOG/redo1_02.rdo) SIZE 25M BLOCKSIZE 512 ?7?MAXLOGFILES 5 ?8?MAXLOGMEMBERS 5 ?9?MAXLOGHISTORY 1 ?0?MAXDATAFILES 100 ?1?CHARACTER SET AL32UTF8 ?2?NATIONAL CHARACTER SET AL16UTF16 ?3?EXTENT MANAGEMENT LOCAL ?4?DATAFILE +DATA/RAC/DATAFILE/system00.dbf SIZE 700M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED ?5?SYSAUX DATAFILE +DATA/RAC/DATAFILE/sysaux00.dbf SIZE 700M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED ?6?DEFAULT TEMPORARY TABLESPACE tempts1 ?7?TEMPFILE +DATA/RAC/TEMPFILE/temp01.dbf SIZE 20M REUSE ?8?UND