Oracle学习笔记

修改用户密码：
alter user 用户名 identified by 新密码;


忘记sys和system密码
开始菜单->运行->输入‘CMD’,打开命令提示符窗口，输入如下命令： 
orapwd file=D:/oracle/product/10.2.0/db_1/database/pwdctcsys.ora 
password=newpass 


查看用户表
SELECT * from user_tables;

查询所有用户：
SELECT user_name from dba_users;
查看表中重复数据：
1、利用分组函数查找表中的重复行：按照某个字段分组，找出行数大于1的列，即由重复记录
SELECT column from table_name test group by column having count(column)>1;
2、利用伪列自关联查询:在ORACLE数据库的内部，每一表都有一rowid伪列，行标识惟一标识行,提供对特殊行的快速存取。对该列使用最大(max)或者最小(min)函数可以非常容易地确定重复的行
SELECT column1，column2 from table_name test where rowid<(select max(rowid) from table_name where column1=test.column1 and column2=test.column2);

SELECT column1，column2 from table_name test where rowid>(select min(rowid) from table_name where column1=test.column1 and column2=test.column2);
3、通过定义完整性约束查找重复行


定义一个完整性约束,integrity constraint是一个限制基表中一列或多列值的规则。可通过对表定义UNIQUE约束，指定惟一关键字。为了满足此约束，在惟一关键字列中不能包含相同的值。因此可用EXCEPTIONS INTO子句，将违背激活的完整性约束的记录存储在一个表（EXCEPTIONS）中，此表必须在使用此选项之前先建好。将EXCEPTIONS表和table_name表通过rowid关联起来即可得到表table_name中重复的记录。 具体方法如下：
1)创建表EXCEPTIONS,用来存放重复记录的信息。
SQL>create table exceptions(row_id rowid,   owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
2)为表table_name定义惟一（UNIQUE）约束，如果在定义的关键字中包含相同的值，系统将提示ORA-02299: 不能创建 - 有重复的值，并将重复记录的信息存入EXCEPTIONS表中。
SQL>alter table table_name add constraint unq_column unique(column1,column2,……) exceptions into EXCEPTIONS;
2． 将表table_name与EXCEPTIONS通过伪列(rowid)建立关联，伪列相等的记录就是table_name中的重复记录。
SQL>select column1,column2,……  from table_name a ,EXCEPTIONS b where a.rowid=b.row_id ; 这种方式查询效率较高，而且可以较完全的记录下重复记录的信息，但是步骤较繁琐。

查询及删除重复记录的SQL语句
 
1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断  
select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录  
DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1); 
3、查找表中多余的重复记录（多个字段） 
select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录  
delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录  
select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

查看表中列数：
1、select count(*) from user_tab_columns where table_name=upper('表名')
2、select max(column_id) from user_tab_columns where table_name=upper('表名')

查看表中列属性名：
SELECT column_name from user_tab_columns where table_name = 'TB_CCSTBASICINFO';

oracle数据库导出为csv
set pages
set lines 200
set colsep ','
spool 'e:\test.csv'
select '"'||CUST_ID||'","'||CUST_NAME||'","'||CUST_NAME_ENG||'","'||CUST_CLASS_CD||'","'||CUST_DTL_CLASS_CD||'","'||CUST_MNG_CD||'","'||CUST_NO_TYPE||'","'||CUST_NO||'","'||CRN_ERR_FLAG||'","'||SEX_CD||'","'||CNTRY_CD||'","'||CNTC_TEL_NO||'","'||CNTC_TEL_NO_1||'","'||CNTC_EMAIL_ADDR||'","'||CNTC_ADDR||'","'||SECURITY_FLAG||'","'||LEGACY_CUST_ID||'","'||REGER_EMP_NO||'","'||REGER_EMP_NAME||'","'||REG_OFC_CD||'","'||REG_DATE||'","'||MIG_ID||'","'||MIG_DATE||'","'||CUST_GRD_CD||'","'||END_DATE||'","'||INFO_ASSENT_FLAG||'","'||CHECK_BIT||'","'||BUSI_OWNER_REG_NO||'","'||CUST_REAL_NAME||'","'||CUSID_TYPE_CD||'","'||CUST_IDFY_NO||'"' from TB_CCSTBASICINFO;
spool off;

oracle数据库中dbf文件迁移：当oracle的数据文件所在的磁盘空间不够用了或其他情况需要把dbf文件迁移到另外的位置，下面是操作步骤：
1、sqlplus sys/sys as sysdba
2、shutdown immediate
3、copy dbf文件到新的位置
4、startup mount;
5、alter database rename file 'E:\oracle\oradata\hqbi\EFMWEB03.DBF' to 'G:\oradata\hqbi\EFMWEB03.DBF';
第一个文件为原来表空间的dbf文件，第二个为新的位置
6、alter database open;
7、如果报下面的错：
第 1 行出现错误:
ORA-01113: 文件 9 需要介质恢复
ORA-01110: 数据文件 9: 'G:\ORADATA\HQBI\EFMWEB03.DBF'
则需要执行下面的语句：
recover datafile 'G:\oradata\hqbi\EFMWEB03.DBF';

修改数据库最大进程数：如果遇到登录sqlplus报无法找到
alter system set processes=500 scope = spfile;
查看：show parameter processes
 
修改表空间大小为自动扩展
alter database datafile 'D:\Oracle\oradata\niu\CS.dbf' autoextend on next 100M maxsize unlimited

创建表空间设置表空间大小最大值超过32G时，需要使用bigfile tablespace命令如:
create tablespace CS datafile 'D:\Oracle\oradata\niu\CS.dbf' size 1500M autoextend on next 100M maxsize 100G;

查看表空间大小性能：
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 T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES D WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_NAME;

修改触发器：
alter trigger trigger_name disable--禁用
alter trigger trigger_name enable--启用

删除表空间，同时删除数据文件：
drop tablespace test_data including contents and datafiles;

声明游标：
TYPE i_cursor_type IS REF CURSOR;
my_cursor i_cursor_type;