oracle总结

功能：Oracle数据导入导出imp/exp就相当与oracle数据还原与备份。
大多情况都可以用Oracle数据导入导出完成数据的备份和还原（不会造成数据的丢失）。

Oracle有个好处，虽然你的电脑不是服务器，但是你装了oracle客户端，并建立了连接
（通过net8 assistant中本地-->服务命名 添加正确的服务命名
其实你可以想成是客户端与服务器端修了条路，然后数据就可以被拉过来了）
这样你可以把数据导出到本地，虽然可能服务器离你很远。
你同样可以把dmp文件从本地导入到远处的数据库服务器中。
利用这个功能你可以构建俩个相同的数据库，一个用来测试，一个用来正式使用。

执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行，
DOS中可以执行时由于 在oracle 8i 中 安装目录/ora81/BIN被设置为全局路径，
该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写，我想SQLPLUS.EXE、EXP.EXE、IMP.EXE这俩个文件是被包装后的类文件。
SQLPLUS.EXE调用EXP.EXE、IMP.EXE他们所包裹的类，完成导入导出功能。

下面介绍的是导入导出的实例，向导入导出看实例基本上就可以完成，因为导入导出很简单。
数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:/daochu.dmp中
exp system/manager@TEST file=d:/daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:/daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:/daochu.dmp tables=(table1,table2)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:/daochu.dmp tables=(table1)query=/" where filed1 like '00%'/"

上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。
不过在上面命令后面 加上 compress=y 就可以了

数据的导入
1 将D:/daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:/daochu.dmp
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:/daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:/daochu.dmp tables=(table1)

基本上上面的导入导出够用了。不少情况我是将表彻底删除，然后导入。

注意：
你要有足够的权限，权限不够它会提示你。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

一.数据控制语句 (DML) 部分
1.INSERT (往数据表里插入记录的语句)
INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);
INSERT INTO 表名(字段名1, 字段名2, ……) SELECT (字段名1, 字段名2, ……) FROM 另外的表名;
字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''.
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.
日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒
或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.
年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS
INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,
方法借用ORACLE里自带的DBMS_LOB程序包.
INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL
2.DELETE (删除数据表里记录的语句)
DELETE FROM表名 WHERE 条件;
注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.
如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
TRUNCATE TABLE 表名;
此操作不可回退.
3.UPDATE (修改数据表里记录的语句)
UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;
如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;
值N超过定义的长度会出错, 最好在插入前进行长度校验..
注意事项:
A. 以上SQL语句对表都加上了行级锁,
确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效,
否则改变不一定写入数据库里.
如果想撤回这些操作, 可以用命令 ROLLBACK 复原.

B. 在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围,
应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.
程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成,
其间加上COMMIT 确认事物处理.

二.数据定义 (DDL) 部分
1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)
ORACLE常用的字段类型有
CHAR 固定长度的字符串
VARCHAR2 可变长度的字符串
NUMBER(M,N) 数字型M是位数总长度, N是小数的长度
DATE 日期类型
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
创建表时可以用中文的字段名, 但最好还是用英文的字段名
创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY

2.ALTER (改变表, 索引, 视图等)
改变表的名称
ALTER TABLE 表名1 TO 表名2;
在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名 字段名描述;
修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;
给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;
3.DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)
删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;
4.TRUNCATE (清空表里的所有记录, 保留表的结构)
TRUNCATE 表名;
三.查询语句 (SELECT) 部分
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件;
字段名可以带入函数
例如: COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),
TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')
NVL(EXPR1, EXPR2)函数
解释:
IF EXPR1=NULL
RETURN EXPR2
ELSE
RETURN EXPR1
DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数
解释:
IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
LPAD(char1,n,char2)函数
解释:
字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位
字段名之间可以进行算术运算
例如: (字段名1*字段名1)/3
查询语句可以嵌套
例如: SELECT …… FROM
(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;
两个查询语句的结果可以做集合操作
例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS, 交集INTERSECT
分组查询
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1
[HAVING 条件] ;
两个以上表之间的连接查询
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE
表名1.字段名 = 表名2. 字段名 [ AND ……] ;
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE
表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;
有(+)号的字段位置自动补空值

查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
ORDER BY字段名1, 字段名2 DESC;
字符串模糊比较的方法
INSTR(字段名, ‘字符串’)>0
字段名 LIKE ‘字符串%’ [‘%字符串%’]
每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.
四.ORACLE里常用的数据对象 (SCHEMA)
1.索引 (INDEX)
CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );
ALTER INDEX 索引名 REBUILD;
一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况,
也可以建立多字段的组合索引和基于函数的索引
ORACLE8.1.7字符串可以索引的最大长度为1578 单字节
ORACLE8.0.6字符串可以索引的最大长度为758 单字节
2.视图 (VIEW)
CREATE VIEW 视图名AS SELECT …. FROM …..;
ALTER VIEW视图名 COMPILE;
视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.
3.同义词 (SYNONMY)
CREATE SYNONYM同义词名FOR 表名;
CREATE SYNONYM同义词名FOR 表名@数据库链接名;
4.数据库链接 (DATABASE LINK)
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;

数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.
数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样
数据库全局名称可以用以下命令查出
SELECT * FROM GLOBAL_NAME;
查询远端数据库里的表
SELECT …… FROM 表名@数据库链接名;
五.权限管理 (DCL) 语句
1.GRANT 赋于权限
常用的系统权限集合有以下三个:
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)
常用的数据对象权限有以下五个:
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,
DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名
GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;
2.REVOKE 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;


清除SQL SERVER 的日志

backup log databasename with NO_LOG
backup log databasename with TRUNCATE_ONLY
DBCC SHRINKDATABASE(databasename)
增加一个自动增长的列
SELECT IDENTITY(int, 1,1) AS ID_Num,*
INTO NewTable
FROM OldTable

* ORACLE物理文件大小的限制*
Oracle数据库的物理文件可以设置成自动增长的类型, 这样数据库在需要更多空间时, 物理文件会按设定
的增长量自动增长到指定的最大值. 这样会有一些好处: 保证不会因为分配数据区域(extent)失败而终止应用.
但如果没有设定这个最大值, 它将会是一个非常大的数字. 数据块大小不同, 其最大值也不一样. 因为Oracle
的物理文件最大只允许4194303个数据块(datablock).
下面列表说明不同数据块数据库所能支持的最大物理文件大小:

数据块的大小 物理文件的最大值 M
===============================================
2048 8191 M
4096 16383 M
8192 32767 M
16384 65535 M

不幸的是, 不同操作系统不同Oracle版本对其物理文件的最大值限制是不一样的. 如果物理文件超过了某
个限制大小, ORACLE数据库将会出现内核错误并崩溃.

下面列出了几种常见操作系统的最大物理文件限制, 有的还需要在OS端设置大的文件系统和特定的环境变
量等.

Windows NT Maximum 4Gb files on FAT Theoretical 16Tb on NTFS

Sun Solaris (32 bit Oracle releases)
===========================================================================
OS Limits
~~~~~~~~~
Release Max file-system size Max OS File size
< Solaris 2.6 1Tb (UFS) 2Gb
>= Solaris 2.6 1Tb (40 bits) 1Tb

Oracle Limits
~~~~~~~~~
SOLARIS 2.6 / 2.7 / 2.8

File Exp/ Loader
Oracle System Raw Imp** see ** UTL_FILE
=====================================================
8.1.6.0 >4Gb >=2Gb >=2Gb >=2Gb >=2Gb
8.1.5.0 32Bit >4Gb >=2Gb <2Gb <2Gb <2Gb
8.1.5.0 64Bit >4Gb >=2Gb <2Gb ? ?
8.0.6.0 >4Gb >=2Gb <2Gb ? >2Gb
8.0.5.2 >4Gb >=2Gb <2Gb <2Gb <2Gb
8.0.5.1 <4Gb >=2Gb <2Gb <2Gb <2Gb
8.0.5.0 <2Gb >=2Gb <2Gb <2Gb <2Gb
>=8.0.4.2 <4Gb >=2Gb <2Gb <2Gb <2Gb
< 8.0.4.2 <2Gb >=2Gb <2Gb <2Gb <2Gb
8.0.3.x <2Gb >=2Gb <2Gb <2Gb <2Gb
7.3.4.x <2Gb >=2Gb <2Gb <2Gb <2Gb
7.3.3.x <2Gb >=2Gb <2Gb <2Gb <2Gb

HP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HPUX Unix Limits
~~~~~~~~~~~~~~~~~
Max file system size:
<= HP-UX 10.10 4Gb
>= HP-UX 10.20 128Gb
>= HP-UX 11.00 1Tb
Max OS file size:
<= HP-UX 10.10 2Gb
>= HP-UX 10.20 128Gb
>= HP-UX 11.00 1Tb
Oracle on HPUX Limits
~~~~~~~~~~~~~~~~~~~~~~
Max Datafile Size
File Raw Async Export/ SQL*Loader
Release System Device I/O Import** (see **)
===========================================================================
8.1.7.x 64Gb 64Gb raw only >2Gb >2Gb
8.1.6.x 64Gb 64Gb raw only >2Gb >2Gb
8.0.6.x 64Gb 64Gb raw only See*1 See*2
8.0.5.x 64Gb 64Gb raw only See*1 <2Gb
8.0.4.x 64Gb 64Gb raw only SIL HPUX10.20 > 2Gb
HPUX-11 <2Gb
8.0.3.x <2Gb <2Gb raw only 2Gb 2Gb
7.3.4.x 64Gb 64Gb raw only SIL 2Gb
>=7.3.3.4.1 64Gb 64Gb raw only SIL 2Gb
7.3.2.3 <2Gb <2Gb raw only 2Gb 2Gb
7.1.6 <2Gb <2Gb raw only 2Gb 2Gb

下面的SQL语句可以查看数据库物理文件是否是自动增长的及相关参数.

select tablespace_name,file_name,round(bytes/(1024*1024),0) total_space,
autoextentsible,increment_by,maxbytes from dba_data_files order by tablespace_name;

如果你管理的数据库物理文件是自动增长的并有可能超过操作系统或Oracle版本文件最大限制时, 请尽快
改变它的属性, 把它设定成非自动增长的或者限定它最大的值.
假如物理文件的物理位置是 D:/ORACLE/ORADATA/ORACLE8/APP01.DBF 用下面的SQL语句可以实现这一操
作:
alter database datafile 'D:/ORACLE/ORADATA/ORACLE8/APP01.DBF' autoextend off;

alter database datafile 'D:/ORACLE/ORADATA/ORACLE8/APP01.DBF' autoextend on next 50M
maxsize 1500M;

如果有良好的数据库存储规划, 物理文件最好还是设置成非自动增长的类型.


如何將表移動表空間?
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;

如何將索引移動表空間?
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;

查询用户拥有的表
select * from all_tables where owner='user'

如果修改表名?
alter table old_table_name rename to new_table_name;

* 紧急关闭数据库 *

alter system checkpoint
shutdown abort
受限启动startup
正常关闭shutdown
建立表空间
CREATE TABLESPACE "WZMIS_INDEX"
LOGGING
DATAFILE 'd:/ORACLE_wzmis/WZMIS_INDEX.ora' SIZE 100M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DROP TABLESPACE WZMIS_temp INCLUDING CONTENTS AND DATAFILES;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--违章系统常规表空间以及用户的建立
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
CREATE TABLESPACE "WZMIS"
LOGGING
DATAFILE 'D:/oracle/oradata/wzmis/WZMIS.ora' SIZE 500M EXTENT
MANAGEMENT LOCAL

CREATE USER "WZMIS" PROFILE "DEFAULT" IDENTIFIED BY "wzmis"
DEFAULT
TABLESPACE "WZMIS" TEMPORARY
TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "AQ_ADMINISTRATOR_ROLE" TO "WZMIS";
GRANT "AQ_USER_ROLE" TO "WZMIS";
GRANT "CONNECT" TO "WZMIS";
GRANT "CTXAPP" TO "WZMIS";
GRANT "DBA" TO "WZMIS";
GRANT "DELETE_CATALOG_ROLE" TO "WZMIS";
GRANT "EXECUTE_CATALOG_ROLE" TO "WZMIS";
GRANT "EXP_FULL_DATABASE" TO "WZMIS";
GRANT "HS_ADMIN_ROLE" TO "WZMIS";
GRANT "IMP_FULL_DATABASE" TO "WZMIS";
GRANT "JAVADEBUGPRIV" TO "WZMIS";
GRANT "JAVAIDPRIV" TO "WZMIS";
GRANT "JAVASYSPRIV" TO "WZMIS";
GRANT "JAVAUSERPRIV" TO "WZMIS";
GRANT "JAVA_ADMIN" TO "WZMIS";
GRANT "JAVA_DEPLOY" TO "WZMIS";
GRANT "OEM_MONITOR" TO "WZMIS";
GRANT "RECOVERY_CATALOG_OWNER" TO "WZMIS";
GRANT "RESOURCE" TO "WZMIS";
GRANT "SELECT_CATALOG_ROLE" TO "WZMIS";
GRANT "SNMPAGENT" TO "WZMIS";
GRANT "TIMESERIES_DBA" TO "WZMIS";
GRANT "TIMESERIES_DEVELOPER" TO "WZMIS";


--FOR GONGDA
CREATE TABLESPACE "WZMIS"
LOGGING
DATAFILE 'C:/ORACLE_wzmis/WZMIS.ora' SIZE 1000M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "WZMIS_DATA"
LOGGING
DATAFILE 'd:/ORACLE_wzmis/WZMIS_DATA.ora' SIZE 500M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE "WZMIS_DATA_MAIN"
LOGGING
DATAFILE 'd:/ORACLE_wzmis/WZMIS_DATA_MAIN.ora' SIZE 500M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE "WZMIS_DATA_DICTIONARY"
LOGGING
DATAFILE 'c:/ORACLE_wzmis/WZMIS_DATA_DICTIONARY.ora' SIZE 100M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE "WZMIS_INDEX"
LOGGING
DATAFILE 'E:/ORACLE_wzmis/WZMIS_INDEX.ora' SIZE 100M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE
TEMPORARY TABLESPACE "WZMIS_TEMP" TEMPFILE
'C:/ORACLE_wzmis/WZMIS_TEMP.ora' SIZE 100M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 1M

--create the new table with old data ,while using diffrente tablespace.
create table WZ_ZGWPJLB2 TABLESPACE WZMIS_DATA storage(initial 20m next 5m pctincrease 0) as select * from WZ_ZGWPJLB;
drop table WZ_ZGWPJLB;
alter table WZ_ZGWPJLB2 rename to WZ_ZGWPJLB;

select * from wz_jtwzjlb where wzsj>TO_DATE('2003-06-29','yyyy-mm-dd') and wzsj<TO_DATE('2003-07-26','yyyy-mm-dd');


--create index all
--create index index_jszh_JSYJBXXB on WZ_JSYJBXXB(JSZH) tablespace wzmis_index;
create index index_jszh_JSYXXHZB on WZ_JSYXXHZB(JSZH) tablespace wzmis_index;
create index index_jszh_JSYXXHZZFB on WZ_JSYXXHZZFB(JSZH) tablespace wzmis_index;
create index index_jszh_JSYICKZFB on WZ_JSYICKZFB(JSZH) tablespace wzmis_index;
create index index_JYBH_JYJBXXB on WZ_JYJBXXB(JYBH) tablespace wzmis_index;
--机动车基本信息表(WZ_JDCJBXXB)
create index index_HPHM_JDCJBXXB on WZ_JDCJBXXB(HPHM) tablespace wzmis_index;
--交通违章记录表（WZ_JTWZJLB）
drop index index_wjj_JTWZJLB ;
create index index_WZBH_JTWZJLB on WZ_JTWZJLB(WZBH) tablespace wzmis_index;
create index index_JDSBH_JTWZJLB on WZ_JTWZJLB(JDSBH) tablespace wzmis_index;
create index index_JSZH_JTWZJLB on WZ_JTWZJLB(JSZH) tablespace wzmis_index;
--drop index index_WZSJ_JTWZJLB ;
create index index_WZSJ_JTWZJLB on WZ_JTWZJLB(WZSJ) tablespace wzmis_index;
create bitmap index index_BITMAP_JTWZJLB ON WZ_JTWZJLB(ZDBJ,QFBJ,CFJG) tablespace wzmis_index;
--非驾驶员违章记录表(WZ_FJSYWZJLB)
create index index_WZBH_FJSYWZJLB on WZ_FJSYWZJLB(WZBH) tablespace wzmis_index;
--强制措施记录(WZ_QZCSJLB)
create index index_PZBH_JSZH_QZCSJLB on WZ_QZCSJLB(PZBH,JSZH) tablespace wzmis_index;
--事故加分记录表(WZ_SGJFJLB)
create index index_JSZH_SGBH_SGJFJLB on WZ_SGJFJLB(JSZH,SGBH) tablespace wzmis_index;
--撤销记录(WZ_CXJLB)
create index index_JSZH_CXJDSH_CXJLB on WZ_CXJLB(JSZH,CXJDSH) tablespace wzmis_index;
--扣证记录(WZ_KZJLB)
create index index_WZBH_KZJLB on WZ_KZJLB(WZBH) tablespace wzmis_index;
--吊销记录表(WZ_DXJLB)wzbh
create index index_WZBH_DXJLB on WZ_DXJLB(WZBH) tablespace wzmis_index;
--注销记录表：(WZ_ZXJLB)
create index index_zxjdsh_JSZH_ZXJLB on WZ_ZXJLB(zxjdsh,JSZH) tablespace wzmis_index;
--违章未交款记录(WZ_WZWJKJLB)
create index index_WZBH_JSZH_WZWJKJLB on WZ_KZJLB(WZBH,JSZH) tablespace wzmis_index;
--违章已交款记录(WZ_WZYJKJLB)
create index index_WZBH_JSZH_WZYJKJLB on WZ_WZYJKJLB(WZBH,JSZH) tablespace wzmis_index;
-- 行政复议诉讼表(WZ_XZFYSSB) WZSJ
create index index_WZBH_XZFYSSB on WZ_XZFYSSB(WZBH) tablespace wzmis_index;
create index index_JSZH_XZFYSSB on WZ_XZFYSSB(JSZH) tablespace wzmis_index;
create index index_WZSJ_XZFYSSB on WZ_XZFYSSB(WZSJ) tablespace wzmis_index;
--撤销决定复议表(WZ_CXJDFYB)
create index index_JSZH_CXJDFYB on WZ_CXJDFYB(JSZH) tablespace wzmis_index;
--未清分表：(WZ_WQFB)
create index index_JSZH_RKSJ_WQFB on WZ_WQFB(JSZH,RKSJ) tablespace wzmis_index;
--学习报名表(WZ_XXBMB)
create index index_JSZH_XXBMB on WZ_XXBMB(JSZH) tablespace wzmis_index;
--考试报名成绩表(WZ_KSBMCJB)
create index index_JSZH_KSBMCJB on WZ_KSBMCJB(JSZH) tablespace wzmis_index;
--强制催告表(WZ_QZCGB)
create index index_PZBH_JSZH_CGRQ_QZCGB on WZ_QZCGB(JSZH,PZBH,CGRQ) tablespace wzmis_index;
--交款催告表(WZ_JKCGB)
create index index_WZBH_JSZH_CGRQ_JKCGB on WZ_JKCGB(JSZH,WZBH,CGRQ) tablespace wzmis_index;
--考试催告表(WZ_KSCGB)
create index index_JSZH_CGRQ_KSCGB on WZ_KSCGB(JSZH,CGRQ) tablespace wzmis_index;
--黑名单记录表(WZ_HMDJLB)
create index index_JSZH_CSRQ_KSCGB on WZ_HMDJLB(JSZH,CSRQ) tablespace wzmis_index;
--奖励记录表(WZ_JLJLB)
create index index_JSZH_JLJLB on WZ_JLJLB(JSZH) tablespace wzmis_index;


--第 三 章 系统表
--行政区划代码表(WZ_SYS_XZQHDMB)
create index index_XZQHDM_SYS_XZQHDMB on WZ_SYS_XZQHDMB(XZQHDM) tablespace wzmis_index;


--end of index create
--催告类型代码表(WZ_SYS_CGLXDMB)
create index index_CGLXDM_SYS_CGLXDMB on WZ_SYS_CGLXDMB(CGLXDM) tablespace wzmis_index_dictionary;
--催告时间代码表(WZ_SYS_CGSJDMB)
create index index_CGLXDM_SYS_CGSJDMB on WZ_SYS_CGSJDMB(CGLXDM) tablespace wzmis_index_dictionary;
--传唤方式代码表(WZ_SYS_CHFSDMB)
create index index_CHFSDM_SYS_CHFSDMB on WZ_SYS_CHFSDMB(CHFSDM) tablespace wzmis_index_dictionary;
--撤消机动车牌照原因代码表(WZ_SYS_CXJDCPZYYDMB)
create index index_CXYYDM_SYS_CXJDCPZYYDMB on WZ_SYS_CXJDCPZYYDMB(CXYYDM) tablespace wzmis_index_dictionary;
--撤消驾驶证原因代码表(WZ_SYS_CXJSZYYDMB)
create index index_CXYYDM_SYS_CXJSZYYDMB on WZ_SYS_CXJSZYYDMB(CXYYDM) tablespace wzmis_index_dictionary;
alter index 用户名.索引名 rebuild
tablespace 表空间名
storage(initial 初始值 next 扩展值)
nologging



--修改表空间数据文件
ALTER TABLESPACE wzmis OFFLINE NORMAL;
--now copy the datafile to the new place
alter tablespace wzmis rename datafile 'E:/oraclespace/oradata/wzmis/WZMIS.ORA' to 'C:/oracle_wzmis/WZMIS.ORA';
ALTER TABLESPACE wzmis ONLINE;



用USING INDEX 子句来指定对UNIQUE 和PRIMARY KEY 约束相关的索引
CREATE TABLE b(
b1 INT,
b2 INT,
CONSTRAINT bu1 UNIQUE (b1, b2)
USING INDEX (create unique index bi on b(b1, b2)),
CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);
已经建好的表可以这样修改：
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
alter tablespace DATA coalesce;
这样可以促使D ATA表空间中的相邻自由盘区合并成更大的自由盘区。


--更改表名
ALTER TABLE employees RENAME TO employee;

--create the spfile from pfile
CREATE spfile='C:/oracle/ora92/database/spfilewzmis.ora'
FROM PFILE='C:/oracle/admin/wzmis/pfile/init.ora.414200323162';



* 怎样把具有相同字段的纪录删除，只留下一条。 例如，表test里有id,name字段 如果有name相同的记录 只留下一条，其余的删除。name的内容不定，相同的记录数不定。有没有这样的sql语句？*
方法
(一)
将重复的记录记入temp1表:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1
2、将不重复的记录记入temp1表:
insert temp1
select [标志字段id],count(*) from [表名]
group by [标志字段id]
having count(*)=1
3、作一个包含所有不重复记录的表：
select * into temp2 from [表名]
where 标志字段id in(select 标志字段id from temp1)
4、删除重复表:
delete [表名]
5、恢复表：
insert [表名]
select * from temp2
6、删除临时表:
drop table temp1
drop table temp2


(二)
首先将test表备份
select distinct id,name into #tmp from test --取test表里id,name不重复的纪录到临时表#tmp里
delete test --清空test里的纪录
insert test(id,name)
select id,name from #tmp --将#tmp里id,name唯一的纪录写入test表

(三)
delete * from table_name
where rowid !=(
select max(rowid) from table_name
group by column1,column2,...
)


ORCLE

实现方法：
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /
表已建立.
SQL> insert into a values('1111','1111');
SQL> insert into a values('1112','1111');
SQL> insert into a values('1113','1111');
SQL> insert into a values('1114','1111');
SQL> insert into a select * from a;
插入4个记录.
SQL> commit;
完全提交.
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
查询到8记录.

查出重复记录
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
ROWID BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
删除重复记录
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
删除4个记录.
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111

SELECT name, LEN(birth_date) AS b, LEN(identity_code) AS i, LEN(first_date) AS f,
LEN(licence_date) AS l, LEN(valid_date) AS v, LEN(last_check_date) AS last,
LEN(this_check_date) AS this
FROM lutian3

UPDATE temp
SET driver_no = '3402' + SUBSTRING(driver_no, 1, 6)

验证日期的位数
SELECT *
FROM ergongsi
WHERE (LEN(driver_no) <> 10) OR
(LEN(birth_date) <> 10) OR
(LEN(first_date) <> 10) OR
(LEN(licence_date) <> 10) OR
(LEN(valid_date) <> 10) OR
(LEN(last_check_date) <> 10) OR
(LEN(this_check_date) <> 10) OR
(LEN(identity_code) =15)

时间字段加0
一 . first_date

1.样式 2003.1.1
UPDATE DATA
SET first_date = SUBSTRING(first_date, 1, 5) + '0' + SUBSTRING(first_date, 6, 2)
+ '0' + SUBSTRING(first_date, 8, 1)
WHERE (SUBSTRING(first_date, 7, 1) = '.') AND (LEN(first_date) = 8)
2.样式 2003.11.1
UPDATE DATA
SET first_date = SUBSTRING(first_date, 1, 8) + '0' + SUBSTRING(first_date, 9,1)
WHERE (SUBSTRING(first_date, 8, 1) = '.') AND (LEN(first_date) = 9)
3.样式 2003.1.11
UPDATE DATA
SET first_date = SUBSTRING(first_date, 1, 5) + '0' + SUBSTRING(first_date, 6,4)
WHERE (SUBSTRING(first_date, 7, 1) = '.') AND (LEN(first_date) = 9)
二. birth_date

1.样式 2003.1.1
UPDATE DATA
SET birth_date = SUBSTRING(birth_date, 1, 5) + '0' + SUBSTRING(birth_date, 6, 2)
+ '0' + SUBSTRING(birth_date, 8, 1)
WHERE (SUBSTRING(birth_date, 7, 1) = '.') AND (LEN(birth_date) = 8)
2.样式 2003.11.1
UPDATE DATA
SET birth_date = SUBSTRING(birth_date, 1, 8) + '0' + SUBSTRING(birth_date, 9,1)
WHERE (SUBSTRING(birth_date, 8, 1) = '.') AND (LEN(birth_date) = 9)
3.样式 2003.1.11
UPDATE DATA
SET birth_date = SUBSTRING(birth_date, 1, 5) + '0' + SUBSTRING(birth_date, 6,4)
WHERE (SUBSTRING(birth_date, 7, 1) = '.') AND (LEN(birth_date) = 9)

三. licence_date
1.样式 2003.1.1
UPDATE DATA
SET licence_date = SUBSTRING(licence_date, 1, 5) + '0' + SUBSTRING(licence_date, 6, 2)
+ '0' + SUBSTRING(licence_date, 8, 1)
WHERE (SUBSTRING(licence_date, 7, 1) = '.') AND (LEN(licence_date) = 8)
2.样式 2003.11.1
UPDATE DATA
SET licence_date = SUBSTRING(licence_date, 1, 8) + '0' + SUBSTRING(licence_date, 9,1)
WHERE (SUBSTRING(licence_date, 8, 1) = '.') AND (LEN(licence_date) = 9)
3.样式 2003.1.11
UPDATE DATA
SET licence_date = SUBSTRING(licence_date, 1, 5) + '0' + SUBSTRING(licence_date, 6,4)
WHERE (SUBSTRING(licence_date, 7, 1) = '.') AND (LEN(licence_date) = 9)
四. valid_date

1.样式 2003.1.1
UPDATE DATA
SET valid_date = SUBSTRING(valid_date, 1, 5) + '0' + SUBSTRING(valid_date, 6, 2)
+ '0' + SUBSTRING(valid_date, 8, 1)
WHERE (SUBSTRING(valid_date, 7, 1) = '.') AND (LEN(valid_date) = 8)
2.样式 2003.11.1
UPDATE DATA
SET valid_date = SUBSTRING(valid_date, 1, 8) + '0' + SUBSTRING(valid_date, 9,1)
WHERE (SUBSTRING(valid_date, 8, 1) = '.') AND (LEN(valid_date) = 9)
3.样式 2003.1.11
UPDATE DATA
SET valid_date = SUBSTRING(valid_date, 1, 5) + '0' + SUBSTRING(valid_date, 6,4)
WHERE (SUBSTRING(valid_date, 7, 1) = '.') AND (LEN(valid_date) = 9)

birth_date,
first_date,
licence_date,
valid_date

五.更新valid_date日期 为licence_date加6年
UPDATE DATA
SET valid_date = CAST(SUBSTRING(licence_date, 1, 4) + 6 AS char(4))
+ SUBSTRING(licence_date, 5, 6)
WHERE (LEN(valid_date) = 0) AND (LEN(licence_date) = 10)

验证驾驶证有效期
SELECT *
FROM ergongsi
WHERE (valid_date < DATENAME(year, GETDATE()) + '.' + DATENAME(month, GETDATE())
+ '.' + DATENAME(day, GETDATE()))

验证驾驶证重复
select driver_no,count(*) from ergongsi group by driver_no having count(*)>1


//valid_date - licence_date
SELECT DISTINCT
COUNT(*) AS Expr2, CAST(SUBSTRING(valid_date, 1, 4) AS int(4))
- CAST(SUBSTRING(licence_date, 1, 4) AS int(4)) AS Expr1
FROM dd
WHERE (LEN(valid_date) = 10) AND (LEN(licence_date) = 10)
GROUP BY CAST(SUBSTRING(valid_date, 1, 4) AS int(4)) - CAST(SUBSTRING(licence_date,
1, 4) AS int(4))
HAVING (COUNT(*) > 1)

1.驾驶证号为15或18位,最后一位可为X,判断非

SELECT *
FROM DATA
WHERE (LEN(identity_code) NOT IN (15, 18))
2.姓名(name),性别不得为空，判断非
SELECT *
FROM DATA
WHERE (LEN(name) = 0) OR
(LEN(sex) = 0)

3.档案编号(driver_no)为10位数，判断非

SELECT *
FROM DATA
WHERE (LEN(driver_no)<> 10)


4.四个日期birth_date,first_date,lience_date,valid_date要为标准的十位格式，判断非

SELECT *
FROM ergongsi
WHERE (LEN(birth_date) <> 10) OR
(LEN(first_date) <> 10) OR
(LEN(licence_date) <> 10) OR
(LEN(valid_date) <> 10)

5.驾驶证有效期不得为空，不过期
SELECT *
FROM ergongsi
WHERE (valid_date < DATENAME(year, GETDATE()) + '.' + DATENAME(month, GETDATE())
+ '.' + DATENAME(day, GETDATE()))
6.检测驾驶证号identity_code中有不为数字的组成
SELECT *
FROM bangni
WHERE (isnumeric(identity_code) = 0)

7.查询姓的数量
SELECT DISTINCT
SUBSTRING(name, 1, 1) AS xing, COUNT(SUBSTRING(name, 1, 1)) AS COUNT
FROM DATA
WHERE (type = '正式证')
GROUP BY SUBSTRING(name, 1, 1)
HAVING COUNT(SUBSTRING(name, 1, 1)) <> 0