[大数据]Oracle常用sql语句

1、查询数据库实例名：?

select * from v$instance;?

2、查询数据库名：?

select * from v$database;?

3、去除字段两端的空格

update EMPLOYEE set BADGE=trim(BADGE);

4、去除字段中所有空格

update EMPLOYEE set BADGE=replace(BADGE,' ','');

5、去除字段中的换行符

SELECT replace(string,chr(13),'')?? from DUAL? ---换行是chr(13)

6、去除字段中的回车符

SELECT replace(string,chr(10),'')?? from DUAL? ---回车是chr(10)

7、取众多查询结果中的第一条

SELECT * FROM (SELECT to_char(zip_date, 'yyyymmddhh24miss') FROM tb_bmp_alarm_zip_result order by zip_date desc) WHERE ROWNUM <= 1;

通过条件WHERE ROWNUM <= 1即可达到只取第1条记录的目的

8、取排序后的结果中的第1条

select * from (select * from nm_task_plan order by created_date,plan_id) where rownum = 1;

不能按照如下方式编写：

select * from nm_task_plan where rownum = 1 order by created_date,plan_id;

这种方式中，是先从结果中获取第1条，然后再对获取后的结果进行排序，而不是先排序再获取第1条

9、查询当前用户创建的所有表

select * from tabs;

10、通过pl/sql导出表空间和表数据

导出表结构:

Tools-->Export User Objects（导出用户对象） -->选择要导出的表(包括Sequence等)-->.sql文件，导出的都为sql文件

导出表数据:

Tools-->Export Tables-->选择表,选择SQL Inserts-->.sql文件

导入表结构:

执行刚刚导出的sql文件，记住要删掉table前的用户名，比如以前这表名为sys.tablename，必须删除sys

导入表数据:

执行刚刚导出的sql文件

11、删除表字段约束constraint

alter table nm_task_plan drop constraint CKC_TASK_TYPE_NM_TASK_

12、创建表字段约束

alter table NM_TASK_PLAN? add constraint CKC_TASK_TYPE_NM_TASK_? check (TASK_TYPE is null or (TASK_TYPE in (1,2,3,4,5,6,7,8)));

alter table NM_TASK_PLAN? add constraint CKC_WEEK_VALUE_NM_TASK_? check (WEEK_VALUE is null or (WEEK_VALUE between 1 and 7));

13、合并字段

select HOUR_VALUE || ':' || lpad(to_char(MIN_VALUE), 2, 0) || ':00' from nm_TASK_PLAN where plan_id = 181;

其中lpad的作用是，判断字段值的长度是否为2，若不是2，则在左侧补0

14、获取日期字段中的年月日

select to_date(to_char(TIMING_DATE, 'yyyymmdd'), 'yyyymmdd') from nm_task_plan;

15、使用已存在的表，创建相同的表

create table abc as select * from NM_COLLECT_FILE;

16、删除表字段

alter table NM_COLLECT_FILE drop column net_id;

17、更改表名

rename NM_COLLECT_FILE to TB_BMP_COLLECT_FILE;

18、修改表字段的长度

alter table TB_BMP_MID_TAB modify(STATE varchar2(30));

alter table TB_BMP_KPI_TMPL modify(KPI_TMPL_CODE varchar2(128),STATE varchar2(30));

19、添加表字段

alter table TB_BMP_ALARM_ITEM add (ALARM_CLEAR_FLAG CHAR default '1',ALARM_CONFIRM_FLAG CHAR default '1',PAGE_ID VARCHAR2(100));

20、表字段重命名

alter table TB_BMP_ALARM_ITEM rename column ALARM_ID to ALARM_ITEM_ID;

21、查看当前用户的所有序列

select * from user_sequences;

22、查看当前永的所有序列、存储过程和触发器

select object_name from all_objects where object_type in ('SEQUENCE', 'PROCEDURE', 'TRIGGER') AND OWNER = 'BMPMID';

其中，“BMPMID”为用户名大写

23、删除序列

drop sequence SEQ_ALARM_DATETIME_ID;

24、创建序列

create sequence SEQ_ALARM_DATETIME_ID

? ? minvalue 1

? ? maxvalue 999999999999999999999999999

? ? start with 184

? ? increment by 1

? ? cache 20;

25、序列重命名

RENAME SEQ_NM_FIEL_ID TO SEQ_COLLECT_FILE_ID;

26、查看当前用户的所有视图

select * from user_views;

select view_name from user_views;

27、创建视图

法一：

create view v_tb_bmp_collect_alarm as

? ? select b.target_id, a.parent_id from TB_BMP_TASK_PLAN a, TB_BMP_TASK_PLAN_REL b where?

? ? a.task_type = 10 and a.plan_id = b.plan_id and b.rel_type = 5 order by a.plan_id;

法二：

create or replace view v_tb_bmp_collect_alarm as

? ? select b.target_id, a.parent_id from TB_BMP_TASK_PLAN a, TB_BMP_TASK_PLAN_REL b?

? ? where a.task_type = 10 and a.plan_id = b.plan_id and b.rel_type = 5 order by a.plan_id;

注意：使用create or replace表示在创建视图时,如果已存在同名的视图,则重新创建

28、查看当前用户的所有约束

select * from user_constraints

29、创建表分区

-- Create table

create table TB_BMP_KPI_INST_DETAIL_HIS

(

? KPI_INST_DETAIL_HIS_ID NUMBER(12) not null,

? KPI_INST_ID? ? ? ? ? ? NUMBER(12),

? KPI_TMPL_ID? ? ? ? ? ? NUMBER(9),

? KPI_INST_CODE? ? ? ? ? VARCHAR2(128),

? KPI_INST_NAME? ? ? ? ? VARCHAR2(100),

? KPI_ID? ? ? ? ? ? ? ?? NUMBER(9),

? KPI_VALUE? ? ? ? ? ? ? VARCHAR2(50),

? STATE_DATE? ? ? ? ? ?? DATE,

? BEGIN_DATE? ? ? ? ? ?? DATE,

? END_DATE? ? ? ? ? ? ?? DATE,

? BATCH_NO? ? ? ? ? ? ?? VARCHAR2(14),

? KPI_DESC? ? ? ? ? ? ?? VARCHAR2(100)

)

partition by range (STATE_DATE)

(

? partition PART_201008 values less than (TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

? ? tablespace JFBMPNM pctfree 10 initrans 1 maxtrans 255

? ? storage

? ? (

? ? ? initial 64K

? ? ? minextents 1

? ? ? maxextents unlimited

? ? ),

? partition PART_201009 values less than (TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

? ? tablespace JFBMPNM pctfree 10 initrans 1 maxtrans 255

? ? storage

? ? (

? ? ? initial 64K

? ? ? minextents 1

? ? ? maxextents unlimited

? ? ),

? partition PART_201010 values less than (TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

? ? tablespace JFBMPNM pctfree 10 initrans 1 maxtrans 255?

? ? storage

? ? (

? ? ? initial 64K

? ? ? minextents 1

? ? ? maxextents unlimited

? ? )

);

-- Add comments to the table?

comment on table TB_BMP_KPI_INST_DETAIL_HIS is '指标明细实例历史表';

-- Add comments to the columns?

comment on column TB_BMP_KPI_INST_DETAIL_HIS.KPI_INST_ID is '指标实例标识';

comment on column TB_BMP_KPI_INST_DETAIL_HIS.KPI_TMPL_ID is '指标模板标识';

comment on column TB_BMP_KPI_INST_DETAIL_HIS.KPI_INST_CODE is '指标实例编码';

comment on column TB_BMP_KPI_INST_DETAIL_HIS.KPI_INST_NAME is '指标实例名称';

-- Create/Recreate primary, unique and foreign key constraints?

alter table TB_BMP_KPI_INST_DETAIL_HIS

? add constraint PK_TB_BMP_KPI_INST_DETAIL_HIS primary key (KPI_INST_DETAIL_HIS_ID)

? using index?

? tablespace JFBMPNM pctfree 10 initrans 2 maxtrans 255

? storage

? (

? ? initial 64K

? ? minextents 1

? ? maxextents unlimited

? );

?

30、添加表分区

alter table tb_bmp_kpi_inst_detail_his add partition PART_201107 values less than (TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

? ? tablespace JFBMPNM pctfree 10 initrans 1 maxtrans 255

? ? storage

? ? (

? ? ? initial 64K

? ? ? minextents 1

? ? ? maxextents unlimited

? ? )

31、sign、decode、ceil函数

select decode(sign(decode(sign(0.95 - 0.96), -1, 100 - ceil((0.96 - 0.95) / 0.01) * 5, 100)),

? ? ? ? ? ? ? -1, 0, decode(sign(0.95 - 0.96), -1, 100 - ceil((0.96 - 0.95) / 0.01) * 5, 100))

? from dual;

32、查询Oracle所有用户下各表里面的记录数

select NUM_ROWS,TABLE_NAME,t.owner,t.tablespace_name from dba_all_tables t order by nvl(t.num_rows,0) desc

33、linux下启动ORACLE数据库

第一步，启动数据库监听

? ? 使用数据库管理员用户登录：

? ? ? ? su - ora10

? ? 查看数据库监听状态：

? ? ? ? lsnrctl

? ? ? ? status

? ? 启动数据库监听

? ? ? ? start

第二步，启动数据库实例

? ? 先退出lsnrctl，然后执行如下命令：

? ? ? ? sqlplus /nolog

? ? ? ? conn sys/ora10 as sysdba

? ? ? ? startup

34、统计表空间的使用率

SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",

? ? ? ?ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)"

? FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS

? ? ? ? ? FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,

? ? ? ?(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE

? ? ? ? ? FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F

?WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL --如果有临时表空间??

SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "USED_SPACE(M)",

? ? ? ?ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"

? FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS

? ? ? ? ? FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,

? ? ? ?(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

? ? ? ? ? ? ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

? ? ? ? ? FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F

?WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

?ORDER BY 1;

35、按照指定字段，获取最新一条数据

select * from (select t.*, row_number() over(partition by trunc(t.collect_id) order by t.task_start_time desc) seq from tb_bmp_data_audit_result t) where seq = 1

上述语句的作用是，获取不同collect_id的最新一条记录

36、统计所有表空间的使用情况

SELECT a.tablespace_name "表空间名", total "表空间大小", free "表空间剩余大小", (total - free) "表空间使用大小",

? ? ? ?round(total / (1024 * 1024 * 1024), 2) "表空间大小(G)", round(free / (1024 * 1024 * 1024), 2) "表空间剩余大小(G)",

? ? ? ?round((total - free) / (1024 * 1024 * 1024), 2) "表空间使用大小(G)", round(round((total - free) / total, 4) * 100, 2) "使用率 %"

? 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;

37、统计指定用户下每个表的使用情况

select a.segment_name, a.segment_type, a.bytes, a.bytes / 1024 / 1024 byte_m, b.created from dba_segments a

?inner join all_objects b on b.object_type = 'TABLE' and a.owner = b.owner and a.segment_name = b.object_name

?where a.owner = 'SH_IMC' and a.segment_type = 'TABLE' /* and a.bytes>50000000*/

?order by a.bytes desc;

38、查看当前用户下的所有序列信息

select * from user_sequences?

39、创建DBLINK

create public database link bmp_imc connect to bmp_imc identified by bmp_imc using 'bmp_imc';

40、创建同义词

create public synonym test_order_info_his for bmp_imc.test_order_info_his;

41、排序后添加自增字段：

select receive_date, row_number() over(order by receive_date) as id from scene6_par_param_settleplan;

说明：如果我们加上排序，发现rownum往往会变的无序