Oracle 常用的sql操作语句

1.表结构的修改

----字段长度的修改

ALTER TABLE 表名 MODIFY(字段  VARCHAR2(300));

----字段的添加

Alter Table 表名 Add (字段名称 字段类型,字段名称 字段类型)；

----字段名称的修改

Alter Table 表名 rename column 旧的字段名称 to 新的字段名称；

----删除字段名

alter table 表名 drop column 字段名

----字段类型的修改，修改为clob类型
alter table 表名 add temp clob;
update 表名 set temp =  字段名;
alter table 表名 drop column 字段名;
alter table 表名 rename column temp to 字段名;

2.可重复执行的建表语句

--创建表Table04
DECLARE num NUMBER ;
BEGIN
  SELECT COUNT (1) INTO num FROM user_tables WHERE TABLE_NAME = 'Table04' ;
  IF num = 1 THEN
    EXECUTE IMMEDIATE 'drop  table  Table04  cascade constraints' ;
  END IF ;
END ;
/
create table Table04
(
   recordid            VARCHAR2(36)                   not null,
   a0401               VARCHAR2(60)                   not null,
   a0402               VARCHAR2(60)                   not null,
   a0403               VARCHAR2(60)                   not null,
   a0404               VARCHAR2(60)                   not null,
   a0405               VARCHAR2(60)                   not null
);

3.创建表空间和用户

---创建名为DEV_DB的表空间 
create tablespace DEV_DB
logging 
datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\DEV_DB.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;

----创建名为DEV_DB，密码为123456 的用户
create user DEV_DB  identified by 123456  default tablespace DEV_DB    temporary tablespace temp;

----将DBA权限授给创建的用户，DBA为超级管理员权限
grant dba to RCGL_MLR;

----查询默认表空间的位置
SELECT * FROM Dba_Data_Files ddf WHERE ddf.tablespace_name = 'USERS';

----查询DEV_DB表空间的位置
SELECT * FROM Dba_Data_Files ddf WHERE ddf.tablespace_name = 'DEV_DB';

----删除表空间
DROP TABLESPACE  DEV_DB（表空间名称）  INCLUDING CONTENTS AND DATAFILES;

----删除用户
DROP user DEV_DB（用户名）  CASCADE;

----查看表空间的使用情况
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

4.查询操作

----将同一列的数据拼接显示
----拼接按逗号隔离
select to_char(wmsys.wm_concat(字段))  别名  from 表名
----拼接按任意指定符合隔离
select listagg(列名,'拼接符') within group(order by 列名) from 表名;

----查询当前节点的所有子节点
select  g.* from tb_doc_catalog g start with g.cat_id=当前id
connect by prior g.cat_id=g.parent_cat_id

----查询当前节点的所有父节点
select  g.* from tb_doc_catalog g start with g.cat_id=当前id
connect by prior g.parent_cat_id=g.cat_id

----查当前数据库字符集
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

5.更新操作

----两个表的联表更新
update  表1  set 表1.name =(select 表2.name from  表2 where  表2.parm=表1.parm)
where  EXISTS (select 1 from 表2  where  表2.parm=表1.parm)

6.删除操作

----删除指定的数据
delete  from   Table_A01；
---删除表
delete  table Table_A01；
---删除表中所有数据
truncate  table Table_A01；
--delete 和truncate 差异
在删除所有数据时truncate执行的速度比delete快，delete执行时先删除索引再删除数据，truncate执行时不考虑索引，直接删除数

 

7.oracle数据库的回滚
----查询指定回滚时间表数据（指定时间没有查询到被删除的数据，修改时间点再此查询）
select * from 表名   as of timestamp to_timestamp('2019-06-24 20:10:38', 'yyyy-mm-dd hh24:mi:ss');
alter table  表名  enable row movement;
----回滚到指定时间
flashback table 表名  to timestamp TO_TIMESTAMP('2019-06-24 20:10:38', 'yyyy-mm-dd hh24:mi:ss');

8.数据的导入导出

----普通操作
exp admin/123456@orcl file='D:/20181012.dmp'
imp admin/123456@orcl file='D:/20181012.dmp' full=y

------创建 自己的逻辑目录
create directory ORACLEDMP  as 'D:\'
--查看逻辑目录
select * from dba_directories
----数据泵导出导入
expdp admin1/123456@orcl directory = ORACLEDMP  dumpfile=20181012.DMP logfile=a.LOG   SCHEMAS=admin1
impdp admin2/123456@orcl directory = ORACLEDMP  dumpfile=20181012.DMP logfile=a.LOG   remap_schema=admin1:admin2  remap_tablespace=admin1的表空间:admin2的表空间