Oracle数据整理心得

 

1．  建表与删表

Drop table tablename;

2. 修改表名

不能直接修改，但可以建立副表

Create table tableName as select * from tableName2

3. 加减字段

Alter table tableName add columnName datatype

Alter table tableName drop column columnName

4. 修改字段长度及类型

Alter table tableName modify columnName newdatatype

若修改长度，则长度不能小于现有数据长度

若修改类型，则字段必须为空，若有数据，则可新建字段，然后使用

使用 UPDATE 进行字段数据转换更新，以达到类型改变。

5. 加 NULL

Alter table tableName modify columnName datatype null/not null

6. 加 Primary key

Alter table tableName add primary key(id,pid…)

7. 删除 Primary key

Alter table tableName drop primary key

8. 当 A,B 结构一样时，复制数据如下

Insert into tableName select * from tableName2

9. 查询表中重复数据

Select count(*) col1,col2 from tableName group by col1,col2 having count(*)>1;

注： col1,col2 为主码，条件为 = 号，则查询出没有重复的数据

10. 删除表中重复数据

Create table tmp as (Select count(*) col1,col2 from tableName group by col1,col2 having count(*)>1);

Delete from tableName where col1,col2 in (select col1,col2 from tmp);

11. 删除表中重复数据，且保留表中最后一次录入的数据

Create table tmp as (Select min(rowed) id, col1,col2 from tableName group by col1,col2 having count(*)>1);

Delete from tableName a where a.rowid = (select b.id from tmp b where a.col1=b.col1 and a.col2=b.col2)

当 tmp 有大量数据时，应考虑使用 min(rowid) 或 max(rowed) ，多次执行删除 .

 

 

Oracle 中同时修改多个表，如在所有的表结构上添加 rkrq

1 select ‘alter table ‘||tname||’ add rkrq date null;’ from tab where tname like ‘D%’;

2 上面的 SQL 执行命令显示头标题，用 set head off 可以去掉头

3 spool c:\a.sql  表示输出上一条命令执行的缓存内容。

4 / 表示执行，或 r 表示执行上一次执行的 SQL ， 并在第一行输出 SQL

5 spool off

6 @c:\a.sql  表示执行生成的 SQL

建立数据库链路

可以高效的传输数据，如果数据量大，可以按日期一段段的插入，

现在，在一个 ORACLE 中有一个表 A 有分区，要把这个分区去掉，将 A 中的数据转到另一个 ORACLE 中，且两表结构一样，则可以通过链路将数据直接插入

 

创建数据库链路步骤

1 在本地 oracle/product/10.20/db_1/network/admin 目录中，找到 tnsnames.ora 文件，并加入以下代码：

       CYC=

              (DESCRIPTION =

                     (ADDRESS = (PROTOCOL = TCP)(HOST=10.84.29.103)(PORT=1521))

                     (CONNECT_DATA =

                            (SERVER = DEDICATED)

                            (SERVICE_NAME = ORA10G)

                     )

              )

可以把 ora10g 的代码，即第一个别名的代码复制再修改

修改后直接保存，不用重启 ORACLE

 

2 在 SQLPLUS 中执行

Create database link linkName connect to 用户名 identified by 密码 using ‘ 别名 ’

或 Create public database link linkName connect to 用户名 identified by 密码 using ‘ 别名 ’

注意 : 创建视图需要权限， grant   create   view   to   mt;

连接数据库使用 conn xxx/xxx ，

存储过程调试权限 : GRANT debug any procedure , debug connect session TO jhcygck;

3 测试链路

Select * from daa01@linkName

 

注：当在一个窗口中创建了链路，则只能在此窗口中使用，若再开一个窗口则无效。

 

创建同义词 create or replace synonym DAA03  for DAA03@JHDZSDB;

 

Oracle 中复制表

Create table XX as select * from daa01@linkName

如果被复制表有分区，复制过来是没有分区的

 

Oracle 中查找相同记录

Select * from tableName where rowed in (select max(rowid) from tableName group by columnName having count(*) > 1)

以下语句查出所有重复且有多余的最后的记录数据

Select count(*),jh,cdsd from daa03 group by jh,cdsd having count(*) > 1;

以上语句查出有得复的数据，为 = 号则查出没有重复的数据

 

查看 ORACLE 中所有表和字段

Select table_name from user_table:// 当前用户的表

Select table_name from all_table;// 所有用户的表

Select table_name from dba_table:// 包括系统表

Select table_name from dba_table where owner=’ 用户名 ’

 

ORACLE 授权与取消授权
conn system/password@ORCL as sysdba;
授权
grant create session to tms;

回收授权
revoke create session from tms;

给用户授权DBA
grant dba to user;

改变用户默认role
alter user 用户 default role DBA;