oracle笔记

Oracle笔记

2016-11-18 14:27:00

Oracle笔记（一） Oracle简介及安装 Oracle笔记（二） SQLPlus命令 Oracle笔记（三） Scott用户的表结构 Oracle笔记（四）简单查询、限定查询、数据的排序 Oracle笔记（五）单行函数 Oracle笔记（六）多表...

来源于：http://www.cnblogs.com/mchina/
Oracle笔记（一） Oracle简介及安装
Oracle笔记（二） SQLPlus命令
Oracle笔记（三） Scott用户的表结构
Oracle笔记（四） 简单查询、限定查询、数据的排序
Oracle笔记（五） 单行函数
Oracle笔记（六） 多表查询
Oracle笔记（七） 数据更新、事务处理、数据伪列
Oracle笔记（八） 复杂查询及总结
Oracle笔记（九） 表的创建及管理
Oracle笔记（十） 约束
Oracle笔记（十一） 建表、更新、查询综合练习
Oracle笔记（十二） 集合、序列
Oracle笔记（十三） 视图、同义词、索引
Oracle笔记（十四） 用户管理
Oracle笔记（十五） 数据库备份
Oracle笔记（十六） 数据库设计范式
 
 
基本知识都涵盖了，可以常看看！

收起

展开全文

oracle笔记

万次阅读 2020-09-04 09:30:38

【4】解决 Oracle11g使用exp导出空表【5】批量增加【6】批量修改【7】MERGE INTO 【8】WM_CONCAT 【9】分区排序取值【10】oracle即使客户端【11】oracle性能【12】ORA-12514 TNS 监听程序当前无法识别连接描述...

【1】cmd命令登录

【2】创建表空间

【3】exp导出数据

【4】解决 Oracle11g使用exp导出空表

【5】批量增加

【6】批量修改

【7】MERGE INTO

【8】WM_CONCAT

【9】分区排序取值

【10】oracle即使客户端

【11】oracle性能

【12】ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务

【13】UNPIVOT操作，列转行

【14】CentOS6.5 安装oracle

【15】函数

【16】同义词

【17】视图

【18】时间轴sql

【19】命令行导入sql文件

【20】oracle连接数

【21】OracleOraDb11g_home1TNSListener服务启动后停止

【22】Oracle创建、查看、修改、赋权、删除directory目录

【23】Oracle字符集

【24】sql命令行运行sql文件乱码

【1】cmd命令登录
    sqlplus /nolog
    connect kettle/123456@DQ106
    connect sys/123456 as sysdba

【2】创建表空间
-- SELECT * FROM dba_data_files ORDER BY tablespace_name; 
CREATE TABLESPACE UPLOADCARD
LOGGING
DATAFILE '/orcldata/UPLOADCARD.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

【3】exp导出数据
#将数据库完全导出,导出到D:\daochu.dmp中
    exp system/manager@DQ106 file=d:\daochu.dmp full=y
#将数据库中kettle用户的表导出
    exp kettle/123456@DQ106 file=d:\kettle.dmp owner=kettle
#将数据库中的表table1、table2导出
    exp uploadcard/123456@DQ10.63.201.74 file=d:\xx.dmp tables=(table1,table2)
#将数据库中的表table1中的字段filed1以"00"打头的数据导出
    exp kettle/123456@DQ106 file=d:\daochu.dmp tables=(table1) query=\"where filed1 like '00%'\"

#将D:\daochu.dmp中的数据导入TEST数据库中。
    imp system/manager@TEST file=d:\daochu.dmp
#将d:\daochu.dmp中的表table1导入
    imp system/manager@TEST file=d:\daochu.dmp tables=(table1)


Oracle中IMP导入数据时提示字符集不一致解决

参考链接

分析：使用exp命令导出的dmp文件的编码会受到两个原因影响，一个是导出端数据库的编码，查询sql:
    SELECT * FROM NLS_DATABASE_PARAMETERS;
这个会影响导出dmp文件的数据内容，另一个是导出端的系统编码，即系统变量中的NLS_LANG参数值影响，如果NLS_LANG为空，那么导出的文件编码会默认为数据库的编码，设置NLS_LANG值方法：
    set NLS_LANG=american_america.AL32UTF8	#windows中，一次性设置，即在导入的窗口中直接设置
    NLS_LANG=american_america.AL32UTF8	#windows中，直接设置环境变量：添加系统的（非某个用户）环境变量
    #windows中，修改注册表：Oracle的NLS_LANG为american_america.AL32UTF8
使用imp导入时，目标dmp文件的编码会影响导入结果，这样就需要设置导入端的系统编码，同上；
而数据库的编码不能够轻易去改，要不会将已有的数据丢失，这里也会有一个问题，不同的数据库编码，会对字符（特指中文）的字段长度定义不一致，导入的时候会出现有的记录因字段过长而不能导入，
这是正常的（解决办法：修改字段长度，重新导出导入）；


1.查询数据库字符集
    select * from nls_database_parameters; #第六行，NLS_CHARACTERSET
2.查看dmp文件的中字符集
    cat /opt/oracle/dpdump/sso_data.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6	#返回 xxx(如0345)（可能查询的不正确）
    select nls_charset_name(to_number('0345','xxxx')) from dual;	#查询0345对应的字符集
3.修改字符集的几种方法
    方法1、修改服务器端中的环境变量中的字符集设置（服务器端导入只要配置服务器端，客户端导入的话还要设置客户端字符集），注：此方法在服务器修改后再及时修改回去
    方法2、修改数据库中字符集
    方法3、修改dmp文件中字符集

###修改dmp文件中字符集
    使用编辑工具，如uedit32（16进制方式）。打开导出的dmp文件，获取2、3字节的内容，如00 01，先把它转换为10进制数，为1，使用函数NLS_CHARSET_NAME即可获得该字符集：
    使用NLS_CHARSET_ID获取目标字符集的编号：select nls_charset_id('zhs16gbk') from dual;	#852
    把852换成16进制数，为354，把2、3字节的00 01换成03 54，即完成了把该dmp文件字符集从us7ascii到zhs16gbk的转化，这样，再把该dmp文件导入到zhs16gbk字符集的数据库就可以了。

【4】解决 Oracle11g使用exp导出空表
#Oracle11g默认对空表不分配segment，故使用exp导出Oracle11g数据库时，空表不会导出。
#设置deferred_segment_creation 参数为FALSE后，无论是空表还是非空表，都分配segment。在sqlplus中，执行如下命令：
alter system set deferred_segment_creation=false;
#查看
show parameter deferred_segment_creation;
#该值设置后只对后面新增的表产生作用，对之前建立的空表不起作用。
#可以使用手工为空表分配Extent的方式，来解决导出之前建立的空表的问题。使用ALLOCATE EXTENT。
#批量输出上述生成的SQL语句，建立C:\createsql.sql，其内容如下：
	set heading off;
	set echo off;
	set feedback off;
	set termout on;
	spool C:\allocate.sql;
	#select 'alter table '||table_name||' allocate extent;' from user_tables where SEGMENT_CREATED='NO';
	Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
	spool off;
#执行C:\createsql.sql
SQL>@ C:\createsql.sql;
# 执行完毕后，得到C:\allocate.sql文件，执行C:\allocate.sql
SQL>@ C:\allocate.sql;

【5】批量增加
	<insert id="addDetall" parameterType="list">
		insert into
		PHC_DISEASE_CARD_COMP_DETAIL(DETAIL_ID,COMP_ID,DIAG_CODE,OPERATOR_ID,OPERATOR_DATE)
		SELECT DETAIL_ID_SEQ.NEXTVAL DETAIL_ID,t.* FROM (
		<foreach collection="list" item="item" index="index"
			separator="UNION ALL">
			select
			#{item.id,jdbcType=NUMERIC} COMP_ID,
			#{item.diagCode,jdbcType=VARCHAR} DIAG_CODE,
			#{item.operatorId,jdbcType=NUMERIC} OPERATOR_ID,
			#{item.operatorDate,jdbcType=NUMERIC} OPERATOR_DATE
			FROM dual
		</foreach>
		) t
	</insert>
--------------------------------------------------------------------------------------------
	<insert id="addSysMensusbutton" parameterType="java.util.List">
		INSERT INTO
		sys_menus_buttons
		(system_id,menu_id,role_id,button_id)
		SELECT *
		FROM (
		<foreach collection="list" item="item" index="index"
			separator="UNION ALL">
			SELECT
			#{item.systemId} system_id,
			#{item.menuId} menu_id,
			#{item.roleId} role_id，
			#{item.buttonId} button_id
			FROM dual
		</foreach>
		)
	</insert>

【6】批量修改
update PHC_INFECTIOUS_DISEASE_CARD t1  set t1.card_type=
(select card_type from PHC_DEATH_CARD t2 where t1.card_id=t2.card_id) where 
exists (select 1 from PHC_DEATH_CARD t2 where t1.card_id=t2.card_id)

update PHC_INFECTIOUS_DISEASE_CARD t1  set(card_type,pid)=
(select card_type,pid from PHC_DEATH_CARD t2 where t1.card_id=t2.card_id) where 
exists (select 1 from PHC_DEATH_CARD t2 where t1.card_id=t2.card_id)

【7】MERGE INTO(oracle)
MERGE INTO --要插入的表 别名  
USING (  
	  --查询的SQL  
	  )别名 ON   
		 --(连接条件)  
WHEN MATCHED THEN --如果符合条件  
	 UPDATE SET   
WHEN NOT MATCHED THEN   
	 INSERT ()VALUES();--不符合就插入 
------------------------------------------eg:
MERGE INTO EMP1 T  
USING (  
SELECT A.ID,A.NAME,A.PASSWORD FROM EMP A  
	  )S ON   
		 (T.ID=S.ID)  
WHEN MATCHED THEN  
	 UPDATE SET   
			T.NAME=S.NAME,  
			T.PASSWORD=S.PASSWORD  
WHEN NOT MATCHED THEN   
	 INSERT (  
			ID,  
			NAME,  
			PASSWORD  
			)VALUES(  
			S.ID,  
			S.NAME,  
			S.PASSWORD  
			);  
----------------------------------------


【8】WM_CONCAT (Oracle)
------------------------------------------------------------------------eg:
select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum  
select operating_apply_id, listagg(OPERATING_NAME,'；') within group (order by  main_operating) as "shoushumingcheng" from  ZYLC.HD_PLAN_OPERATING   group by operating_apply_id
---------------------------------------------------------------------------

【9】分组排序取值
SELECT *  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,test1.* FROM test1) t WHERE t.rn = 1  ;     

【10】oracle即使客户端
设置环境变量：
NLS_lANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
ORACLE_HOME= D:\instantclient_10_2\
TNS_ADMIN= D:\instantclient_10_2\
Path=D:\instantclient_10_2\;%Path%
设置pl/sql首选项：
Oracle_home= D:\instantclient_10_2
OCI Library= D:\instantclient_10_2\oci.dll

【11】oracle性能
select * from v$session where username is not null; --查看会话
select username,count(username) from v$session where username is not null group by username;
Select count(*) from v$session where status='ACTIVE';--并发连接数 
select count(*) from v$process; --当前连接数
select value from v$parameter where name = 'processes';--数据库允许的最大连接数
--查看是否有死锁：
--Username：死锁语句所用的数据库用户；
--Lockwait：死锁的状态，如果有内容表示被死锁。
--Status： 状态，active表示被死锁
--Machine： 死锁语句所在的机器。
--Program： 产生死锁的语句主要来自哪个应用程序。
select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object); 
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object)); --查看死锁的语句
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID; --查看死锁进程

【12】ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务
A:通过重启服务的方式启动数据库，再次连接尝试。
B:如果第一种方法不可行，我们采用第二种方法:
 1>正确添加listener.ora；
		 SID_LIST_LISTENER =
		  (SID_LIST =
		   (SID_DESC =
			(GLOBAL_DBNAME = ORCL)
			(ORACLE_HOME = D:\oracle)
			(SID_NAME = ORCL)
			)
		 )
		 
		LISTENER =
		  (DESCRIPTION =
			(ADDRESS = (PROTOCOL = TCP)(HOST = user9768)(PORT = 1521))
		  )
 2>重新启动了oracle服务器，并检查oracle所有服务是否启动；
 3>oracle客户端Net Cofiguration Assistant正确添加或重新配置实例名

【13】UNPIVOT操作，列转行
UNPIVOT 的语法:
SELECT [columns not unpivoted],[unpivot_column],[value_column]
FROM (<source query>) AS <alias for the source data>
UNPIVOT ( [value_column] FOR [unpivot_column] IN ( <column_list> ) )  AS <alias for unpivot>
示例：
SELECT PersonID, PhoneType, PhoneNumber
FROM (SELECT PersonID, HomePhone, CellPhone, Workphone, FaxNumber FROM PhoneNumbers ) AS Src
UNPIVOT ( PhoneNumber FOR PhoneType IN  (HomePhone, CellPhone, WorkPhone, FaxNumber)) AS UNPVT;

【14】CentOS6.5 安装oracle
  1>修改stsctl.conf文件(vim /etc/sysctl.conf),在末尾添加：
	fs.aio-max-nr = 1048576
	fs.file-max = 6815744
	kernel.shmall = 2097152
	kernel.shmmax = 536870912
	kernel.shmmni = 4096
	kernel.sem = 250 32000 100 128
	net.ipv4.ip_local_port_range = 9000 65500
	net.core.rmem_default = 262144
	net.core.rmem_max = 4194304
	net.core.wmem_default = 262144
	net.core.wmem_max = 1048576
	net.ipv4.tcp_wmem = 262144 262144 262144
	net.ipv4.tcp_rmem = 4194304 4194304 4194304
	 注意注释掉：
	 # Disable netfilter on bridges.
	 #net.bridge.bridge-nf-call-ip6tables = 0
	 #net.bridge.bridge-nf-call-iptables = 0
	 #net.bridge.bridge-nf-call-arptables = 0
	输入命令 sysctl -p 使文件立即生效。
  2>修改limits.conf文件(vim /etc/security/limits.conf),在末尾添加：
	oracle soft nproc  2047
	oracle hard nproc  16384
	oracle soft nofile 1024
	oracle hard nofile 65536
	oracle hard stack 10240
  3>修改/etc/pam.d/login 文件(vim /etc/pam.d/login),在在末尾添加：
	session required /lib64/security/pam_limits.so
	session required pam_limits.so
  4>修改 /etc/profile  文件,在在末尾添加：
	if [ $USER = "oracle" ]; then
		if [ $SHELL = "/bin/ksh" ]; then
			ulimit -p 16384
			ulimit -n 65536
		else
			ulimit -u 16384 -n 65536
		fi
	fi
  5>.创建用户和用户组
	groupadd oinstall; 
	groupadd dba;
	useradd -g oinstall -g dba -m oracle;
	passwd oracle;
  6>.创建数据库软件目录和数据文件存放目录，目录的位置，根据自己的情况来定
	mkdir /home/oracle/app
	mkdir /home/oracle/app/oracle
	mkdir /home/oracle/app/oradata
	mkdir /home/oracle/app/oracle/product
	chown -R oracle:oinstall /home/oracle/app
  7>修改文件 .bash_profile(vim /home/oracle/.bash_profile )，在末尾添加：
	export ORACLE_BASE=/home/oracle/app
	export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1
	export ORACLE_SID=orcl
	export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
	export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
  8>重启，以oracle用户登录,并解压安装包
	unzip linux.x64_11gR2_database_1of2.zip
	unzip linux.x64_11gR2_database_2of2.zip
  9>安装依赖
	yum install binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel elfutils-libelf-devel-static gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers kernel-headers ksh libaio libaio-devel libgcc libgomp libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel
  10>安装oracle(桌面安装)
	cd /opt/database
	./runInstaller
  11>启动Oracle
	#检查oracle监听器运行状态
	lsnrctl status
	#启动监听
	lsnrctl start
	#停止监听
	lsnrctl stop
	#启动实例
		sqlplus /nolog
		conn as sysdba
		startup		#启动实例
		shutdown #关闭实例
		quit
	#启动EM控制台
	 emctl start dbconsole 
	 https://localhost:1158/em/
	 emctl stop dbconsole
  12>配置文件存放路径
	/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
	/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
  13>设置oracle数据库和监听开机自动启动
	vim /etc/oratab (N改为Y   orcl:/home/oracle/app/oracle/product/11.2.0/dbhome_1:Y)
	vim /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dbstart(将ORACLE_HOME_LISTNER=$1改为ORACLE_HOME_LISTNER=$ORACLE_HOME)
	vim /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dbshut(将ORACLE_HOME_LISTNER=$1改为ORACLE_HOME_LISTNER=$ORACLE_HOME)
	#以root用户建立脚本 
	vim /etc/rc.d/init.d/oradb
			#!/bin/bash 
			# chkconfig: 2345 90 10 
			export ORACLE_BASE=/home/oracle/app
			export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1 
			export ORACLE_SID=orcl 
			export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin 
			ORCL_OWN="oracle" 
			# if the executables do not exist -- display error 
			if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ] 
			then 
			   echo "Oracle startup: cannot start" 
			   exit 1 
			fi 
			# depending on parameter -- start, stop, restart 
			# of the instance and listener or usage display 
			case "$1" in 
			start) 
			# Oracle listener and instance startup 
			echo -n "Starting Oracle: " 
			su - $ORCL_OWN -c "$ORACLE_HOME/bin/dbstart" 
			touch /var/lock/subsys/oradb 
			su - $ORCL_OWN -c "$ORACLE_HOME/bin/emctl start dbconsole" 
			echo "OK" 
			;; 
			stop) 
			# Oracle listener and instance shutdown 
			echo -n "Shutdown Oracle: " 
			su - $ORCL_OWN -c "$ORACLE_HOME/bin/emctl stop dbconsole" 
			su - $ORCL_OWN -c "$ORACLE_HOME/bin/dbshut" 
			rm -f /var/lock/subsys/oradb 
			echo "OK" 
			;; 
			reload|restart) 
			$0 stop 
			$1 start 
			;; 
			*) 
			echo "Usage: 'basename $0' start|stop|restart|reload" 
			exit 1 
			esac 
			exit 0 
			
	#将该文件添加到开机启动
	chmod 755 /etc/rc.d/init.d/oradb 
	chkconfig --add oradb 
	#测试
	service oradb start
	service oradb stop 

【15】函数
-- 根据日期计算年龄
CREATE OR REPLACE FUNCTION "XX"."F_GETAGE" (ad_birthday in date,ad_datetime in date) return varchar2 is
  ll_days number(10,0);
  ll_months number(10,0);
  ll_age number(3);
begin
  ll_days := trunc(ad_datetime - ad_birthday);
  if ll_days >= 100 then
     ll_months := trunc(ll_days / 30.5);
      IF ll_months <= 24 THEN
    		RETURN to_char(ll_months)||'月';
    	ELSE
        ll_age := trunc(ll_days / 365.25);
    		RETURN to_char(ll_age) ||'岁';
    	END IF;
    else
    	RETURN to_char(ll_days)||'天';
    END IF;
end f_getage;

--根据身份证号计算年龄
CREATE OR REPLACE FUNCTION "XX"."GET_AGE" (workerid in varchar2) return varchar2 is
	agevalue varchar2(10);
begin
  if(length(trim(workerid))=18) then
     select (to_char(sysdate,'yyyy')-to_char(substr(workerid,7,4))) into agevalue from dual;
  else if (length(trim(workerid))=15) then
    select (to_char(sysdate,'yyyy')-to_char('19'||substr(workerid,7,2))) into agevalue from dual;
  else
    agevalue:='0';
  end if;
  end if;
  return agevalue;
end;

【16】同义词
select synonym_name,table_name from dba_synonyms --查询同义词

【17】视图
CREATE OR REPLACE VIEW 视图名称 AS 
select语句
comment on table 视图名称 is '注释';
comment on column 视图名称.列名 is '注释';

#授予用户查询权限
grant select on 表名 to 用户名;
#收回权限
revoke select on 表名 from 用户名称;
#给用户授予select any dictionary 权限，那么用户就能访问所有的视图，不要轻易给普通用户授予该权限
grant select any dictionary to 用户名称;
#给用户授予select any table 权限,如果把该权限赋予给某用户，并且数据库O7_DICTIONARY_ACCESSIBILITY参数值为true，那么用户可以查询数据库里的所有表，视图等
grant select any table to 用户名称;
#查看用户权限
select * from dba_sys_privs where grantee='UPLOADCARD';
select * from dba_tab_privs where grantee='UPLOADCARD';
select * from dba_role_privs where grantee='UPLOADCARD';

####ORACLE系统提供三种权限：Object 对象级、System 系统级、Role 角色级。这些权限可以授予给用户、特殊用户public或角色，如果授予一个权限给特殊用户"Public"（用户public是oracle预定义的，每个用户享有这个用户享有的权限）,那么就意味作将该权限授予了该数据库的所有用户。

【18】时间轴sql
select TO_CHAR( SYSDATE + ROWNUM - 8, 'yyyy-MM-dd') as zytime from DUAL CONNECT BY ROWNUM <=7
select TO_CHAR(to_date(#{endDateText},'yyyy-mm-dd HH24:mi:ss') - ROWNUM*#{cycleTime,jdbcType=NUMERIC}/60/24 , 'MM-DD HH24:MI') as shijian from DUAL
CONNECT BY ROWNUM<=(to_date(#{endDateText,jdbcType=VARCHAR},'yyyy-mm-dd HH24:mi:ss')-to_date(#{startDateText,jdbcType=VARCHAR},'yyyy-mm-dd HH24:mi:ss'))*24*60/#{cycleTime}

【19】命令行导入sql文件
P1:
	sqlplus user/password@dbname @create.sql  > logs.log 
P2:
#登录
	sqlplus empi_st/empi_st@192.168.1.110/orcl
#导入	
	@E:\workspace\empi\empi.sql 
	
#####中文乱码解决办法：
A1:	这种情况是由于环境变量NLS_LANG的值和数据库字符集不一致，查看数据库的字符集：
		select userenv ('language') from dual;
	查看系统的NLS_LANG环境变量，在系统控制台中执行：
		echo %NLS_LANG%
	若两者不一致修改环境变量即可。
A2: 将sql文件改为ANSI编码格式

【20】oracle连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数
alter system set processes = 2000 scope = spfile; --修改最大连接数
#重启数据库:
shutdown immediate
startup
-----------------------------------------------------------------
#查看processes和sessions参数
 show parameter processes
#修改processes和sessions值
 alter system set processes=300 scope=spfile;
 alter system set sessions=335 scope=spfile;
#查询数据库当前进程的连接数：
 select count(*) from v$process;
#查看数据库当前会话的连接数：
 select count(*) from v$session;
#查看数据库的并发连接数：
 select count(*) from v$session where status='ACTIVE';
#查看当前数据库建立的会话情况：
 select sid,serial#,username,program,machine,status from v$session;
#查看当前有哪些用户正在使用数据
select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
from v$session a, v$sqlarea b
where a.sql_address =b.address
order by cpu_time/executions desc;  


【21】OracleOraDb11g_home1TNSListener服务启动后停止
1> 配置Oracle的listener.ora文件
 LISTENER = 
   (DESCRIPTION_LIST = 
     (DESCRIPTION = 
	   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	   (ADDRESS = (PROTOCOL = TCP)(HOST = 计算机全名)(PORT = 1521))
	  ) 
	)
2> 修改注册表  regedit
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/services/OracleOraDb11g_home1TNSListener中的ImagePath改为：
F:/oracle/product/11.2.0/db_1/BIN/TNSLSNR.EXE（之前后面的.EXE没有）
3> 配置环境变量
ORACLE_HOME：F:/oracle/product/11.2.0/db_1
4> 重启OracleServiceORCL服务，再启动OracleOraDb11g_home1TNSListener服务即可运行。

【22】Oracle创建、查看、修改、赋权、删除directory目录
# 创建directory语法：
	CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
	例如：create or replace directory dumpdir as '/home/oracle/datatmp'
#查看directory路径
	select * from dba_directories;
#修改directory
	create or replace directory dumpdir as '/home/dumpfiles';
#directory赋权
	grant read,write on directory dumpdir to username;
#directory删除
	drop directory DIRENAME;

【23】Oracle字符集
Oracle字符集是一个字节数据的解释的符号集合,有大小之分,有相互的包容关系。
ORACLE 支持国家语言的体系结构允许你使用本地化语言来存储，处理，检索数据。
它使数据库工具，错误消息，排序次序，日期，时间，货币，数字，和日历自动适应本地化语言和平台。

影响Oracle数据库字符集最重要的参数是NLS_LANG参数。它的格式如下: NLS_LANG = language_territory.charset
它有三个组成部分(语言、地域和字符集)，每个成分控制了NLS子集的特性。其中:
    Language： 指定服务器消息的语言， 影响提示信息是中文还是英文
    Territory： 指定服务器的日期和数字格式，
    Charset：  指定字符集。
如:AMERICAN_AMERICA.ZHS16GBK
从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。
所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。

#查看数据库版本
    select * from v$version;
#查看数据库字符集
    select * from nls_database_parameters;	# select userenv('language') from dual;	
#客户端字符集环境
    select * from nls_instance_parameters;
#会话字符集环境 
    select * from nls_session_parameters	#表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
#查询dmp文件的字符集
    cat /opt/oracle/dpdump/sso_data.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6	#返回 xxx(如0345)
    select nls_charset_name(to_number('0345','xxxx')) from dual;	#查询0345对应的字符集

【24】sql命令行运行sql文件乱码
select userenv('language') from dual;#查看服务端字符集
export NLS_LANG=xxx #设置客户端环境变量与服务端一致

收起

展开全文

linux 数据库

oracle 笔记

2015-02-11 14:10:35

归档日志满： ora-00257:archivererror.Connct intenal only.until freed ...[root@tfcsmap2 ~]#su - oracle --切换到oracle用户 [oracle@tfcsmap2 ~]$lsnrctl start --启动监听 [oracle@tfcsmap2 ~]$sqlp





归档日志满：
ora-00257:archivererror.Connct intenal only.until freed




 
 
[root@tfcsmap2 ~]#su - oracle        --切换到oracle用户
[oracle@tfcsmap2 ~]$lsnrctl start    --启动监听
[oracle@tfcsmap2 ~]$sqlplus /nolog  

SQL> conn / assysdba;
Connected to an idleinstance.
然后输入
Sql> rman
Rman>connect target /
在命令窗口里面执行
DELETEARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
crosscheckarchivelog all;
deleteexpired archivelog all;

收起

展开全文

ORACLE笔记

千次阅读 2006-12-21 09:02:00

ORACLE笔记Oracle SQL(Oracle 9i 9.2.0.1.0) SQL(结构化查询语言)，是操作关系型数据库中的对象。DDL（数据定义语言），用于建表或删表操作，以及对表约束进行修改。DML（数据操作语言），向表中插入纪录，修改纪录...

 
ORACLE笔记
Oracle SQL(Oracle 9i 9.2.0.1.0)
 
SQL(结构化查询语言)，是操作关系型数据库中的对象。
DDL（数据定义语言），用于建表或删表操作，以及对表约束进行修改。
DML（数据操作语言），向表中插入纪录，修改纪录。
事务控制语言，commit; rollback;
授权语句
 
select（数据的查询），投影，过滤（选择）查寻，关联查寻（表连接）。
 
sqlplus 访问数据库命令（本地访问/远程访问），和数据库建立连接的命令，是数据库操作的环境
sqlplus 用户名/密码
 
show user 显示当前用户的用户名
 
在sqlplus中可以使用 ! 可以在shell和sqlplus间切换，!shell命令 可以在sqlplus中使用shell命令。实际上是sqlplus开了子进程来执行shell命令。
 
Oracle数据库中的表分两类：用户表（用户使用操作的表），系统表（数据库系统维护的表，数据字典）
 
select查询语句
select table_name from user_tables;（查询系统表）
以上的查询语句就是查询本用户下所拥有的所有表的表名。
 
desc [表名] 这是一条sqlplus命令，注意他不是sql语句，这条命令用于查看表的结构。
[字段名] [字段的类型]，这是使用完desc命令后显示的表结构。
 
投影操作，只查看选择的字段的信息。
选择操作，查看字段中的特定某些信息。
多表查询，通过表间连接，查寻出多表中的信息
 
!oerr ora [错误号] ，系统可以显示错误的原因和如何修改。
如果命令错误输入可以使用edit或ed来修改输入错误。实际上是在编辑缓存文件中的最后一条sql语句。也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。
 
select [表的字段名1],[表的字段名2], ... from 表名;
select * from 表名; 查寻表中所有字段的信息
 
关键字不等拆分，sql语句，以及表名，字段名是大小写不敏感的。
sql语句要以"；"结尾，来表示sql语句结束，如果不加"；"系统不会执行此条sql语句，并提示。
sqlplus的buffer中会缓存最后一条sql语句，可以使用"/"来执行这最后一条sql语句，也可以使用
edit命令来编辑最后一条sql语句。l命令（list）（sqlplus命令）可以显示buffer中最后一条命令。
 
sqlplus设置
set pause on 回车响应，分屏显示，只在本会话中有效
set pause off 关闭分屏显示。
set pause '...' 设置分屏显示的提示信息。
set pause on 先输出提示信息，回车响应，分屏显示
set head off 提头输出关闭
set feed off 结尾输出关闭
set echo off 回写关闭
spool 文件名.sql 写入指定文件
spool off 关闭写入。
 
清屏命令 !clear 或 clear screen
 
在Oracle中字符显示是左对齐，数值右对齐。
 
在select 语句中可以使用数学表达式。
 
select [表达式（必须包含本表字段名）],[...],.... from 表名；
运算的优先级的先乘除后加减，同级自左向右运算，括号改变优先级。
 
select [字段名或表达式] ["别名"]，[...] ["..."],.... from 表名；
 
可以通过在字段名或表达式后加空格"别名"，可以给列，或者表达式结果其别名。
字符串拼接使用||符号
 
select 目标字段名||" "||目标字段名 from 表名；
注意：在Oracle中的字符串要用'..'包含
别名中需要使用空格，或是大小写敏感时需要用".."包含。
 
sql脚本，也就是在文件中写有sql语句的文件，可以在sqlplus中运行,推荐为.sql。
引入sql脚本: sqlplus 用户名/密码 @sql脚本(注意@前一定要有空格,否则含义就变了)
Oracle中的空值 空值会当无穷大处理。
Oracle中控制处理函数 NVL(字段名，值)，这个字段中的空值替换为指定值，如果不为空，则会返回其原值。
例：select (salary*12)*(NVL(commission_pct,0)/100+1) salary,first_name from s_emp;
 
distinct关键字，去掉重复行（这个关键字会处发排序操作）
例： select distinct dept_id,title from s_emp;
注意：distinct，关键字之后会对from之前的字段进行排重操作。
column命令（这是个sqlplus命令）
column命令 列格式的定义
 
column 目标列名 查看这个类是否定义了格式
 
column 目标列名 format a.. 设置列宽。
column last_name heading 'Employee|Name'（设置题头） FORMAT A15
这其中的'|'是换行符
 
column salary justify left format $99,990.00（定义数字显示格式）
注意：如果不满足显示的格式，就会把数据显示为"#"
 
column 列名 clear （清除列格式定义）
 
注意：只有sqlplus命令才有简写，并且在使用sqlplus命令时结尾也不能加分号。
 
选择操作
 
order by 排序子句 ASC（默认，升序） DESC（降序）
 
order by 目标列名（别名） 排序顺序（不写排序顺序，会默认为升序排序）
 
例：select first_name from s_emp order by first_name;
    select first_name from s_emp order by first_name desc;
 
注意：升序空值在结果的末尾，降序空值在结果的最前面。
 
where子句
 
where子句使用在 select ... from ... 后面，用来选择所需（符合条件的）的记录
 
where后面跟的是表达式 也就是 XXX=XXX， XXX between X and X ，XXX in（X，X，X）
like '...' 通配查询
 
between ... and ... ,表示结果在这之间，between and是一个闭区间。
!=，<>，^=，这三个都标识不等于，<=，>=，=，这些运算符都可以使用。
... in (va1,val2,...) 判断结果是否在这个枚举中存在 
like '...' 字符串通配查询，'%'表示多个字符，'_'，表示一个字符。
... and ... 表示只有两个条件同时满足
... or ... 表示条件只要满足其中只一就可以
all ... 是要求都满足条件。
not .....，则是可以与以上的条件产生反效果。
... is null 使用来判断值是不是空。
注意：Oracle中的字符串是严格区分大小写的。 
 
1.注意数据类型
2.选择合适的运算符
 
Oracle数据库函数（单行函数）
 
Oracle中的函数和C中的函数差不多，也是有函数名，参数表，和返回值类型组成的
 
1,字符函数
   字符是大小写敏感的
   转小写 lower(...)
   转大写 upper(...)
   首字母大写 initcap(...)
   dual表，是专门用于函数测试和运算的，他只有一条记录      
   字符串拼接 concat(...,....)
   求指定子串 substr(...,起始位置，取字符个数)
   可以使用"-"表示从右向左取，取的时候可以从左往友取。
    例：select substr(first_name,-2,2) sub from s_emp;（取后两个）
       select substr(first_name,2,2) sub from s_emp;（取前两个）
2,数值函数
   四舍五入 round(数据,保留小数点后几位)
   可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位（保留到十   位）。
   例：select round(15.36,1) from dual;
   截取数字函数 trunc(数据，保留的位数（小数点后位数）) 截取个位之后补0
   例：select trunc(123.456,1) from dual;
3，日期函数
   日期格式，
   全日期格式 世纪信息，年月日，时分秒。
   缺省日期格式，日-月-年 dd-mon-rr
   修改当前会话的日期格式，会按照指定的格式输出日期
   alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
   返回当前日期 sysdate
   例：select sysdate from dual;
   日期是格式敏感的
   求两个日期间相隔了多少个月 months_between(date1,date2)
   加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。
   从下周开始的日期加一天 next_day(date,天数)
   例：select next_day(sysdate,2) from dual;
   返回月末的日期 last_day(date)
   截取日期 trunc(date,'年或月或日或时分秒')
   例：select trunc(add_months(sysdate,1),'month') from dual;
 
4，不同数据类型间转换函数
   将日期转成字符 tochar(date,'日期格式') 
   日期格式要用有效格式，格式大小写敏感 'yyyy mm dd hh24:mi:ss','year'(全拼的年),'mm'(数字表示的月) 'month'(全拼的月)，'day'(星期的全拼)，'ddspth' (日期的全拼) 'yy mm dd'
   例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;
   将字符转换成数字 to_number('...')
   将数字转字符to_char(number，'fmt') fmt是数字格式
   将字符串转成日期 to_date('...','日期格式')
   例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；
5，函数嵌套
例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；
 
表连接（关联查寻）
如果多表查询时不加where子句，也就是过滤条件或者是使用了无效的条件，就会产生两表之间记录的相互逐条匹配（组合），产生很多无效的结果（笛卡尔积）。
注意：在使用表连接时，要注意查询的表间的关系信息，表之间的字段所表示的信息的关系
等值连接
 select [表别名1.字段名1]，[表别名2.字段名2],... 
 from 表1 表别名1 ，表2 表别名2
 where 表别名1.字段名3=表别名2.字段名4;
 表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用
表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。
注意：当为表起了别名，就不能再使用表名.字段名
例：select a.first_name,a.last_name,b.name from s_emp a,s_dept b where a.dept_id=b.id;
 
非等值连接
 select [表别名1.字段名1]，[表别名2.字段名2],... 
 from 表1 表别名1 ，表2 表别名2
 where 表别名1.字段名3 ..... 表别名2.字段名4
 ....可以使比较运算符，也可以使其他的除了'='的运算符
例：select e.ename, d.grade,e.sal from emp e,salgrade d where e.sal between d.losal and d.hisal;
 
自连接 用别名把一张表中的数据分成两部分，然后在使用条件过滤。
 select [表别名1.字段名1]，[表别名2.字段名2],... 
 from 表1 表别名1 ，表1 表别名2
 where 表别名1.字段名3=表别名2.字段名4;
例：select a.first_name ename,b.first_name cname from s_emp a,s_emp b where a.manager_id=b.id;
以上所提到的表连接，都叫做内连接。
 
 
外连接 会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。
例：select a.first_name enamei,a.id,b.first_name cname,b.id from s_emp a,s_emp b where a.manager_id=b.id(+);
注意：要把那一方的记录全部都显示出来，还有注意条件(+)跟在要全部显示的那个表的字段后。
 
组函数
group 组 
group by 分组子句，按指定的分组规则分组 ，这个group by 子句可以跟在 select 语句后或是 having后面。group by子句也会出发排序操作，会按分组字段排序。
 
select [组函数或分组的字段名] ，... from 表名 group by [字段名1],[字段名2],.....；
例：select avg(salary) from s_emp group by dept_id;
 
注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。count()除外，他会把空记录也记录在内。
 
avg(..),求平均值，sum(..),求和 这两个函数的参数只能是number型的。
 
以下所提到的函数可以使用任意类型做参数。
count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。
max(..),min(..)求最大值和最小值，
count(*),统计表中记录数。
例：select max(b.name),avg(a.salary), max(c.name) from s_emp a,s_dept b,s_region c where a.dept_id=b.id and b.region_id=c.id group by b.id;
注意：只要写了group by子句，select后就只能用group by后的字段或者是组函数。
      where子句只能够过滤记录。
having子句可以过滤组函数结果或是分组的信息，且写在group by子句后。
例:
 select max(b.name),avg(a.salary), max(c.name) from s_emp a,s_dept b,s_region c where a.dept_id=b.id and b.region_id=c.id group by b.id having sum(a.salary)>4000;
 
column 也可以定义有别名的列的格式。
column "别名" 格式定义
注意：要先过滤掉不需要的记录，然后再进行分组操作，提高效率。
 
子查询
子查询，就是可以嵌在任何的sql语句中的select语句。
 
在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。
 
注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值）。配合使用子查询返回的结果必须符合运算符的用法。
例:
select first_name,title from s_emp where title=any(select title from s_emp where last_name='Smith') and upper(last_name)!='SMITH';
 
select first_name,title from s_emp where title in (select title from s_emp where last_name='Smith') and upper(last_name)!='SMITH';
 
数据库设计
 
数据库表设计，把业务需求转换成可操作的表。
1，需求分析，了解客户的业务需求（业务技能）。
2，设计，通过ER图（实体关系图）
3，建表
4，测试
5，形成产品
 
ER图
将一类事物的共性抽象处来成为一个实体，并且表现出来实体间的关系。
unique identifier 唯一的值
primary with     '#*' 唯一且非空
indispensable 必要的（也就是要求必须非空）
 
实体关系
one to one 一对一关联，one to many 一对多关联，many to many 多对多关联
反射关联，自身的属性之间的关联
 
ER图转换成表
 
 
第一范式，所有的属性都必须是单值，也就是属性只表示单一的意义。（记录可以重复，没有任何限制）
第二范式，属性要求唯一且非空，（记录不可重复，但是数据可能会出现冗余）。
第三范式，非主属性只能依赖于主属性，不能依赖于其他非主属性。（解决数据冗余问题）
 
约束
约束是针对表中的字段进行定义的。
 
primary key （主键约束 PK）保证实体的完整性，保证记录的唯一
主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键。
 
foreign key （外建约束 FK）保证引用的完整性，
外键约束，外键的取值是受另外一张表中的主键或唯一值得约束，不能够取其他值，只能够引用主键会唯一键的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。
 
unuque key（唯一键），值为唯一
 
index（索引）是数据库特有的一类对象，view（视图）
典型的一对多 class 对应多个学生。
student table                      class table 
 ______________________________     _________________________
| id | name | address| class_id|   | id |class_desc|class_num|
|(PK)|______|________|___(FK)__|   |(pk)|__________|_________|
|    |      |        |         |   |    |          |         |
 
一对一
 
student tabel             shenfenzheng table
 ____________________     _________________________________
| id | name | address|   | s_id |shenfen_desc|shenfen_num|
|(PK)|______|________|   |(PK，FK)|____________|___________|
|    |      |        |   |        |            |           |
 
多对多
 
student tabel             zhongjian table                      kecheng table
 ____________________     _________________________________    __________________
| id | name | address|   | s_id |shenfen_desc|shenfen_num| | kid | kechengname|
|(PK)|______|________|   |(FK，FK)|____________|___________| | (PK)|____________|
|    |      |        |   |联合主键|            |           | |     |            |
 
引用对方表的主键,当作本身的主键,所以这个表的主键,既是主键又是外建
 
建表和其他相关操作
 
DDL语句
 
创建表：
   create    table 表名   (    字段名1    类型(数据长度)(default ...)   约束条件，   字段名2    类型(数据长度)    约束条件 );
 
Oracle数据库中的数据类型
varchar(长度)，可变长字符串，char(长度) 定长
number(..,..),number 表示浮点数，或者是整数
long 大对象，clog 字符的大对象，相当于文本文件在表中只存放一个相当于只针对值 
             blog 二进制的大对象，也是以相当于指针的形式存放的。
primary key约束：
主键约束的定义：
第一种定义形式：
create table   test(c number primary key );     列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table   test( c1 number constraints   pkc1 primary key );   此约束有名字: pkc1
create table   test(c number , c2 number , primary key (c ,c1) ); 用表级约束可以实现联合主键
 
foregin key   (fk)   外键约束:
(先定义父表，再定义子表）
carete   table     parent(c1 number primary key );
create   table    child (c number primary key ,   c2 number references parent(c1));
或表级约束定义:
create   table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
 
约束
 
非空约束（not null）这是一个列级约束
在建表时,在数据类型的后面加上 not null ，也就是在插入时不允许插入空值。
 
例：create table student(id number primary key,name varchar2(32) not null,address varchar2(32));
 
unique 唯一约束
 
唯一约束，是会忽略空值的，唯一约束，要求插入的记录中的值是为一的。
 
例：create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address));
 
check约束
 
检查约束，可以按照指定条件，检查记录的插入。check中不能使用尾列，不能使用函数，不能引用其他字段。
例：create table sal (a1 number , check(a1>1000));
 
创建脚本   例：见课本相应章节
 
 
 
一对一建表语句
 
student table
 
create table student(
       id number,
       name varchar2(32),
       address varchar2(32)
       primary key(id)
);
 
shenfenzheng table
 
create tabel shenfenzheng( 
       sid number primary key, 
       num number unique not null,
       foreign key (sid) references student(id)
);
 
一对多
 
class table
一
create table class(
        cid number,
        class_num number,
        desc varchar2(32),
        primary key(cid)
);
 
student table
多
create table student(
       id number,
       name varchar2(32),
       address varchar2(32)
       class_id number,
       primary key(id),foreign key (class_id) references class(cid)
);
 
 
 
 
 
数据字典
 
数据字典是由系统维护的，包含的数据库的信息
数据字典视图
user_XXXXX 用户视图
all_XXXXX 所有视图
dba_XXXXX 数据库中所有视图
v$_XXXXX   动态性能视图
 
dist或 distionary 表示数据字典的数据字典。
 
user_constraints 用户的表中约束的表
其中有constraints_name字段存放的是约束名，r_constraints_name字段表示外键引用子何处
这两个字段之间有自连接的关系，也就是约束名和外键约束名之间的自连接。
 
user_cons_column表，是用户的列级约束表
 
DML操作
 
insert操作，插入记录
 
insert into 表名 values(值1，值2，......);
注意这种方法插入记录时，要对所有字段进行插入，没有非空约束时，又不想插入值时，要用空值替代，并且要按照字段的顺序插值（要清楚表结构），且要注意数据类型一致。
 
insert into 表名(字段名1，字段名2，.....) values(值1，值2，......);
这种方法可以对指定的字段进行插入，不想插值的就可以不写，前提是该字段没有非空约束。
 
例：insert into student values(1,'xxx','xxx');
    insert into student(id,name,address) values(1,'xxx','xxx');
 
update修改操作
 
update table 表名 set 字段名1=数据1或表达式1， 字段名2=数据2或表达式2 
[where ....=....];
例：update shenfenzhen set num=99 where sid=2;
 
delete删除操作
 
delete from 表名 [where ...=...];
例：update shenfenzhen set num=99 where sid=2;
用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。
delete操作不会释放表所占用的空间，delete不是和删除记录多的大表。delete操作会占用大量的系统资源。
事务transaction
 
OLTP（联机事务处理 OnLine Transaction Process）
原子操作，也就是不可分割的操作，必须一起成功一起失败。
要是实现一个原子操作，就要把这个原子操作（操作数据库数据(DML操作)）放在事务中。
事务的结束动作 就是commit;语句 rollback;语句，DDL，DCL语句执行会自动提交commit;。
sqlplus正常退出是会做提交动作的commit;，当系统异常推出是，会执行回滚操作rollback;。
事务的开始，一个事务的开始就是上一个事务的结束。
一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据，只有本会话才能看见。
readcommited，只可以读取已经作提交操作的数据，本会话可以看到自己的所作的没有提交的操作。
在活动事务中，当多个用户同时对同一张表进行操作时，会对表加上表级共享锁，当用户对操作该表某一条记录进行操作时会对该条记录加上行级排它锁，只允许一个用户对该条记录进行DML操作，只有提交操作commit;或回滚操作rollback;时，才可让其他用户操作对该记录进行DML操作，也就是释放了该条记录的行级排它锁。如果没有提交操作或回滚操作，那么该用户就不能对该条记录加锁，该用户的DML操作就会进入等待状态，但是在对表作drop操作（DDL操作）时，如果还有用户在操作该表，也就是没有释放表级共享锁，就会直接报错。
 
事务越大，就会消耗更多的资源，并长时间持有事务会造成无法进行其他的操作，事物提交太频繁的话，会对I/O造成很大的负担，所以要合理确定事务的大小。
 
commit;提交操作，事物的结束
 
rollback;回滚操作，会将先前的活动事务中的操作（DML操作）的结果进行回滚，撤销全部操作，恢复成事务开始时的数据，也就是恢复成事务开始时的状态。
 
alter table命令
alter table 命令用于修改表的结构(这些命令不会经常用)：
 
增加字段：
alter table 表名　add(字段字，字段类型)
 
删除字段：
alter tbale 表名 drop column 字段; (8i 以后才支持)
 
给列改名:9.2.0才支持
alter table 表名 rename column 旧字段名 to 新字段名;
 
修改字段
alter table 表名 modify( 字段，类型)
(此时应注意的问题，更改时要看具体值情况之间的转达换，　改为字符类型时，必须要为空)
not null约束是使用alter table .. modify (..,not null)，来加上的。
增加约束：
alter table 表名 add constraint [约束名] 约束(字段);
只能够增加表级约束。
 
解除约束：(删除约束)
 
alter table 表名 drop 约束;
（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
 
alter table father drop primary key cascade; 
(如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
 
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：
a、人为的违反约束规定根据错误信息获取!
b、查询视图获取约束名!)
 
使约束失效或者生效
 
alter table 表名 disable from primary key; (相当于把一个表的主键禁用)
 
alter table 表名 enable primary key;
（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
 
更改表名
rename 旧表名 to 新表名;
 
删除表：
trucate table 表名;
(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表)
 
关于oralce中产生序列(sequence)
create sequence 序列名;
 
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值)
 
 
 
sequence 的参数：
increment by n   起始值
start with n     递增量 
maxvalue n       最大值 
minvalue n       最小值
cycle|no cycle   轮回 
cache n          缓存(第一次取时会一次取多少个id存起来)
 
查看sequence 视图：
desc    user_sequences ;
select   sequence_name , cache_size , last_number from user_sequences   where   sequence_name like 's_';
select 序列名.currval from   dual    查看当前的序列数
select 序列名.nextval  from   dual    查看下一个序列数，它会自动给当前的序列加１
为列：nextval          currval
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)
 
清空当前会话的内存：
alter system flush   shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
 
修改序列：(此命令不常用，只需了解就行不必深究)
alter sequence 序列名 修改项；
删除序列sequence
drop sequence 序列名;
 
视图
 
创建视图： 
creating views 视图名;
 
视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句,视图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新视图）
MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题，物化视图中的数据存在延迟问题，主要应用在数据仓库中用要用于聚合表)
 
使用视图的好处：控制数据访问权限。
 
如何创建一个视图的例子：
create or replace views test_vi as select * from test1 where c1=1;
 
or replace的意义，如果view存在就覆盖，不存在才创建。
force|no force ，基表存在是使用，不存在是则创建该表。
此时往表test1（base table 基表）中插入数据时：表中没能变化，视图中的数据发生改变
从视图中插数据时相对应的表会发生改变:
往视图中插数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句。
 
限制对数据库的访问，简化查询。
简单视图：来自于单表，且select语句中不能包括函数，能进行DML操作。
复杂视图：来源于多张表，不能执行DML操作。
 
视图的约束
with read only 视图只读约束(O)
with check option 不允许插入与where条件不符的记录，类似于check约束的功能(V)
 
在select from 后也可以使用子查寻，这个写法也叫做内嵌视图
例：
select first_name,salary,avgsal from s_emp e,(select dept_id,avg(salary) avgsal from s_emp group by dept_id) s where e.dept_id=s.dept_id and e.salary>s.avgsal;
 
删除视图 drop views 视图名;
 
行号（rownum）
关于rownum：
rownum 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。
rownum常用于分页显示。
rownum只用于读入内存的数据。
 
关于同义词：
 
同义词:相当于别名的作用(***只需了解***）系统自建的同义词:
user_tables
 
create synonym asd_s_emp for asd_0606.s_emp ;
目的就是为了给asd_0606_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public synonym p_s_emp fro asd_0606.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop synonym    同义词名称
 
索引（index）
 
创建索引：Creating indexes(概念很重要对系统的性能影响非常大）
 
建索引的目的就是为了加快查询速度。
 
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。索引是会进行排序。
 
truncate 表时索引结构在，但是数据不存在。
 
full table scan 全表扫描
用索引就是为了快速定位数据：(理解时就以字典的目录为例)
创建索引就是创建key和记录的物理位置（rowid）组成的键值对。索引是有独立的存储空间,但是和表是逻辑关联的,索引和表的关系是依附关系,表被删除了,索引也没有存在的意义也就被删除了
 
在建表时会根据表中的PK或UK自动的建立唯一性索引。
 
查看表的rowid：
select rowid,first_name from s_emp;
rowid 定义的信息有：object block table
 
每条记录都有自己的rowid
 
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的。
 
创建一个索引：
create index 索引名 on 表名 (字段名);
create insex testindex on test(c1, c2);
 
索引分为唯一性索引，联合索引。索引中是不会维护空值的。
 
哪些字段应该建索引：创建索引就是为了减少物理读，索引会减少扫描的时间。
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率就越高.
 
替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间
 
更改交互的提示信息：
accept p_dname prompt ' 提示信息';
定义变量：
define p_dname='abc';
 
set    verify on
set    verify off;
相当于开关变量，用于控制是否显示新旧的sql语句
select id,last_name,salary from s_emp where title='&job_title';

收起

展开全文

table 数据库 sql

Oracle笔记 目录索引
2019-09-21 02:45:00
Oracle笔记 目录索引 ... Oracle笔记 一、oracle的安装、sqlplus的使用 Oracle笔记 二、常用dba命令行 Oracle笔记 三、function 、select Oracle笔记 四、增删改、事务 Oracle笔记 五、创建表、约束、视图、...
收起
Oracle 笔记
千次阅读 2005-01-18 20:54:00
收起
oracle笔记2
2011-01-26 19:27:30
oracle笔记
收起
oracle笔记 oracle问题归纳笔记
2017-05-03 09:46:04
平时工作中遇到的问题归纳笔记
收起
马士兵oracle笔记
2013-05-13 21:45:46
马士兵oracle笔记 经典记录，仅供参考
收起

Oracle笔记

oracle笔记

oracle 笔记

ORACLE笔记

Oracle笔记目录索引

Oracle 笔记

oracle笔记2

oracle笔记 oracle问题归纳笔记

马士兵oracle笔记

oracle笔记

1052 Linked List Sorting (25分)测试点4答案错误

Python语言编程高级精讲课从程序员到架构师的必修课

Python 获取JavaScript变量，JavaScript转Python

ORANGE’S：一个操作系统的实现.pdf

Python入门课，人工智能时代比java还有用的语言

限时训练

WEB安全渗透测试1-Msql基础

Python专题精讲日期和时间的使用

LIO-SLAM测试编译

[BX]和内存单元的描述

【HTTP】HTTP状态码-返回结果-2XX-3XX-4XX-5XX

软件架构场景之—— 全链路日志：这个请求到底经历了什么？

03-2.8LCD显示屏STM32F103RC_SPI例程.rar

用乐鑫国内镜像构建ESP8266_RTOS_SDK开发环境

基于Django的电子商务网站设计---第三章 URL

排序算法（一）——Comparable接口

基于大数据的新型代维现场管理系统设计

微信支付V3版微信小程序支付PYTHON版

UE4游戏逆向与安全+FPS游戏逆向与安全

亚像素级别处理圆心坐标计算程序

Oracle笔记

oracle笔记

oracle 笔记

ORACLE笔记

Oracle笔记 目录索引

Oracle 笔记

oracle笔记2

oracle笔记 oracle问题归纳笔记

马士兵oracle笔记

oracle笔记

1052 Linked List Sorting (25分)测试点4答案错误

Python语言编程高级精讲课 从程序员到架构师的必修课

Python 获取JavaScript变量，JavaScript转Python

ORANGE’S：一个操作系统的实现.pdf

Python入门课，人工智能时代比java还有用的语言

限时训练

WEB安全渗透测试1-Msql基础

Python专题精讲 日期和时间的使用

LIO-SLAM测试编译

[BX]和内存单元的描述

【HTTP】HTTP状态码-返回结果-2XX-3XX-4XX-5XX

软件架构场景之—— 全链路日志：这个请求到底经历了什么？

03-2.8LCD显示屏STM32F103RC_SPI例程.rar

用乐鑫国内镜像构建ESP8266_RTOS_SDK开发环境

基于Django的电子商务网站设计---第三章 URL

排序算法（一）——Comparable接口

基于大数据的新型代维现场管理系统设计

微信支付V3版微信小程序支付PYTHON版

UE4游戏逆向与安全+FPS游戏逆向与安全

亚像素级别处理圆心坐标计算程序

Oracle笔记目录索引

Python语言编程高级精讲课从程序员到架构师的必修课

Python专题精讲日期和时间的使用