orcale 常用语句汇总

根据查询结果创建新表

create table 新表名 as select ...

清除字段中的字符问题

UPDATE accident_nc t SET  t.SUMMARY=REPLACE(t.SUMMARY,'"','');--去掉"
UPDATE accident_nc t SET  t.SUMMARY=REPLACE(t.SUMMARY,CHR(9),'');--去掉制表符
UPDATE accident_nc t SET  t.SUMMARY=REPLACE(t.SUMMARY,chr(10),'');--去除换行
UPDATE accident_nc t SET  t.SUMMARY=REPLACE(t.SUMMARY,chr(13),'');--去掉回车
update accident_nc t set  t.SUMMARY=trim(t.SUMMARY);--去掉空格

将TableB中的no字段赋给TableA的no字段

update TableA 
set TableA.no= (select TableB.no from TableB 
where TableA.id=TableB.id)

修复误删数据
未开启闪回：

select * from 表名 AS OF TIMESTAMP TO_TIMESTAMP('2021-02-2 19:00:00','yyyy-mm-dd hh24:mi:ss');--指定时间
select * from 表名 as of timestamp sysdate - 20/1440;--20分钟前

已开启闪回：

flashback table 表名 to timestamp(systimestamp-interval '3' minute);

删除重复数据（存储过程法）

create or replace procedure deleteEquals
as
begin 
  loop
    delete from 表名 where rowid in (select min(rowid) from 表名 group by 字段1,to_char(字段2) having count(*) > 1);
    dbms_output.put_line('删除了'||to_char(sql%rowcount)||'行');
    exit when sql%rowcount = 0;
  end loop;
  commit;
end;

exec deleteEquals();

用户名过期处理

将密码有效期由默认的180天修改成“无限制”：
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
已经被提示的帐户必须再改一次密码，举例如下：
alter user 用户名 identified by 密码;

清除用户及表空间
使用拥有dba权限的账户登录，登录后，打开Command Window窗口，使用drop命令进行删除操作。

Select * From dba_users;
Drop user xxx cascade;--删除xxx用户及级联关系也删除掉
Select * From dba_data_files;
Drop tablespace xxx including contents and datafiles cascade constraint;

https://blog.youkuaiyun.com/me5572/article/details/80754993

数据导入导出
对于orcale_10g以上的服务器，使用exp通常不能导出0行数据的空表，而此时必须使用expdp导出（数据泵）；

创建用户时要设置默认表空间，所以先建表空间，再建用户；

导出的dmp包含了表空间，所以导入时依然会导入到和dmp中表空间名相同的表空间；若要导入到新建的表空间，需要进行调整

查询所有表空间：

select username,default_tablespace from dba_users;

查询某一表空间下的所有表：

SELECT *   FROM DBA_TABLES WHERE TABLESPACE_NAME = 'USERS';

查看表空间及大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

数据泵导入

impdp NAME_2/密码@ORCL full=Y  directory=NAME_dir dumpfile=NAME.dmp REMAP_SCHEMA=NAME_1:SCHOOL REMAP_TABLESPACE=NAME_1:NAME_2

查询数据库版本select * from v$version
本机数据库版本version=11.2.0.1.0

导出操作（https://blog.youkuaiyun.com/mr_awei/article/details/81089259）
（1）运行cmd；
（2）登录数据库，输入命令：sqlplus as sysdba；
（3）创建目录路径，create directory data_dir as ‘E:\ora\data’ ;
1、data_dir为路径名称，可自命名，E:\ora\data为数据库导出文件存放路径（路径必须存在）；
2、使用命令：select * from dba_directories可查询用户创建目录。
（4）为oracle用户授予访问数据目录的权限，输入命令：Grant read,write on directory data_dir to dbuser;
dbuser为数据库用户名（与第5步中相同）。
（5）导入导出操作授权，输入命令：grant exp_full_database,imp_full_database to dbuser; ；
（6）退出，输入命令：exit;；
（7）数据导出，执行命令：expdp dbuser/123456@orcl schemas=dbuser dumpfile=expdp.dmp directory=data_dir logfile=expdp.log
高版本导到低版本，结尾加version=11.2.0.1.0

expdp [为用户名]/[密码]@[服务名]
schemas=[为用户名]
dumpfile=[导出数据库文件（可自命名）]
directory=[目录名]
logfile=[日志文件文件名（可自命名）]
注意：命令结束不需要加“;”！

导入操作
（1）cmd
（2）登录数据库，输入命令：sqlplus / as sysdba；
（3）创建目录路径：输入命令：create directory data_dir as ‘E:\ora\data’ ;
（4）为oracle用户授予访问数据目录的权限，输入命令：Grant read,write on directory data_dir to dbuser;；
（5）导入导出操作授权，输入命令：grant exp_full_database,imp_full_database to dbuser;
（6）将从A电脑中E:\ora\data目录下的.dmp数据泵导出文件拷贝至B电脑创建的目录（E:\ora\data）中；
（7）退出，输入命令：exit;
（8）数据导入，执行命令：impdp user/123456@orcl REMAP_SCHEMA = dbuser:user table_exists_action = replace directory=data_dir dumpfile=expdp.dmp logfile=expdp.log
impdp [用户名]/[密码]@[服务名]
REMAP_SCHEMA=[源用户名1]:[目标用户名2]
table_exists_action=replace /存在的表动作(覆盖)/
directory=[目录名]
dumpfile=[.dmp文件名]
logfile=[.log文件名]
注意：命令结束不需要加“;”

创建表空间

create tablespace NAME
datafile
'D:\oradata\orcl\NAME01.dbf' size 100m autoextend on next 50m maxsize Unlimited logging extent management local;
create temporary tablespace NAMETemp
tempfile
'D:\oradata\orcl\NAMEtemp01.dbf' size 100m autoextend on next 50m maxsize Unlimited,
'D:\oradata\orcl\NAMEtemp02.dbf' size 100m autoextend on next 50m maxsize Unlimited

创建用户

create user NAME identified by NAME  default tablespace NAME  temporary tablespace NAMETemp;
grant connect,resource to NAME;

grant create any sequence to NAME;
grant create any table to NAME;
grant delete any table to NAME; 

grant insert any table to NAME; 
grant select any table to NAME; 
grant execute any procedure to NAME; 

grant update any table to NAME; 
grant create any view to NAME; 

grant dba to NAME;
commit;