oracle常用命令查询、建表、建用户、分区、表空间、函数

最新推荐文章于 2024-08-17 22:34:52 发布

zhouhongyin

最新推荐文章于 2024-08-17 22:34:52 发布

阅读量1.4k

点赞数

分类专栏：数据库技术文章标签： oracle 数据库 database

本文链接：https://blog.csdn.net/zhouhongyin/article/details/11000207

版权

数据库技术专栏收录该内容

1 篇文章 0 订阅

订阅专栏

常用SQL

1.distinct某个字段：select count(distinct(DEV_MOD_CODE)) from T_DM_USER_DEV_INF

2.表清空：TRUNCATE TABLE table_name

3.插入：INSERT INTO EMPLOYEES VALUES ('Bunyan','Paul','1970-07-04','Boston',12,70000);

4.更新：UPDATE EMPLOYEES SET GRADE = 16, SALARY = 40000　WHERE FIRST_NAME = 'Indiana'　 AND LAST_NAME = 'Jones';

5.删除：DELETE FROM EMPLOYEES WHERE BRANCH_OFFICE = 'Los Angeles';

6.查看数据库版本：select @@version

7.CREATE TABLE tab01(name varchar(50),datetime default now())

8.DROP TABLE 数据表名称 (永久性删除一个数据表)

9.指定索引查询：SELECT /*+index(MTR_AWS_STATUS_EVAL,PK_MTR_AWS_STS_EVAL_STN)*/

10.制作报表 SELECT /*+FIRST_ROWS*/ 显示部分数据
11. exec dbms_mview.refresh('V_MTR_STATIONEQU'); 执行物化视图

按时间查询，包含缺测值

select to_char(standard_time,'HH24:MI') hour, c.airtemperature from
( select to_date(a.obsdate||b.eva_time,'yyyymmddhh24miss') standard_time
from    t_obsdate a, t_eval_time b
where   b.ws_typ='02' and a.obsdate||b.eva_time
          between '20110416000000' and '20110416230000' ) a
left join t_aws_obsdata c on c.obsdate=a.standard_time and c.stationnum='54102'
order   by standard_time

obsdate:年月日
eva_time:时分秒
t_aws_obsdata:数据表
b.ws_typ：时分秒类型(正点，半小时等)

时间类型转换

效率比较：
1. select count(*)
     from T_DM_USER_DEV_HIS
     where to_char(VALID_DATE,'YYYYMM')>= '200901' and to_char(VALID_DATE,'YYYYMM')< '200910';
2. select count(*)
     from T_DM_USER_DEV_HIS
     where VALID_DATE>= to_date('200901','YYYY-MM') and VALID_DATE>= to_date('200910','YYYY-MM');

字段的字符截取
select count(*) as UTKDATACOUNTHIS
from T_DM_USER_DEV_HIS where substr(reg_parm,22,2) = 'UA' and prov_code >= '010'

连接数据库脚本

#!/usr/bin/bash
#日志路径
cd $HOME
. .profile
sPath="/opt/neudm/zhouhy/execpro.log"

echo "调用数据库脚本" >$sPath
sqlplus dmtest_owner_user/dmtest_owner_user<<EOF>>$sPath
set time on
set timing on
select * from T_DM_TRAN_OTA_MSG_ACTIVE_BAK
commit;
quit
EOF
echo "数据库脚本执行完毕" >>$sPath

copy from 模板：

set copycommit 20
set arraysize 5000
copy from bill_back/bss_bill_xxp23@hbacct append unitele.aa using select USER_ID,ACCOUNT_ID, -
CUSTOMER_ID,BUNDLE_ID,REGION_CODE,FEE_DATE,CITY_CODE,SERVICE_KIND,FEE_KIND,SPECIAL_BILL,IS_PAY, -
FEE1,FEE2,FEE3,FEE4,FEE11,FEE12,FEE13,FEE14,PAY_DATE,WRITE_OFF,to_number(null) FLOW_NUMBER -
from unitele.BF_BILL_PAY_T where 1=2

例子：
create table T_DM_USER_DEV_HIS_038 tablespace dm_dev_his_dat as select * from T_DM_USER_DEV_HIS where 1=2;

set copycommit 20
set arraysize 5000
copy from dm_owner_user/neusoft_man@n_man append T_DM_USER_DEV_HIS_038 using select HIS_ID,MDN, -
PROV_CODE,CITY_CODE,IMSI,ESN,DEV_MOD_ID,DEV_MOD_CODE,DEV_MAN_ID,DEV_MAN_CODE,DEV_VERSION , -
VALID_DATE ,INVALID_DATE,MSG_ID,REG_TIME ,REG_MODEL,REG_PARM,REG_HASH -
from T_DM_USER_DEV_HIS where prov_code='038';

查分区大小

select substr(partition_name,1,8),sum(bytes)/1024/1024/1024 from dba_segments
where owner='DM_OWNER_USER' AND SEGMENT_NAME ='T_DM_USER_DEV_HIS'
group by substr(partition_name,1,8) order by sum(bytes) desc

Linux 循环处理，监控，起进程

#!/bin/sh

MAX_PROC=10

for part_name in `cat ./part.txt`
do
PROV=`echo $part_name | awk -F, '{print $1}'`
PART=`echo $part_name | awk -F, '{print $2}'`

done

关于Oracle ORA-01555快照过旧的错误

假设有一张6000万行数据的testdb表，预计testdb全表扫描1次需要2个小时，参考过程如下：
1、在1点钟，用户A发出了select * from testdb;此时不管将来testdb怎么变化，正确的结果应该是用户A会看到在1点钟这个时刻的内容。
2、在1点30分，用户B执行了update命令，更新了testdb表中的第4100万行的这条记录，这时，用户A的全表扫描还没有到达第4100万条。毫无疑问，这个时候，第4100万行的这条记录是被写入了回滚段，假设是回滚段UNDOTS1，如果用户A的全表扫描到达了第4100万行，是应该会正确的从回滚段UNDOTS1中读取出1点钟时刻的内容的。
3、这时，用户B将他刚才做的操作提交了，但是这时，系统仍然可以给用户A提供正确的数据，因为那第4100万行记录的内容仍然还在回滚段UNDOTS1里，系统可以根据SCN到回滚段里找到正确的数据，但要注意到，这时记录在UNDOTS1里的第4100万行记录已经发生了重大的改变：就是第4100万行在回滚段UNDOTS1里的数据有可能随时被覆盖掉，因为这条记录已经被提交了！
4、由于用户A的查询时间漫长，而业务在一直不断的进行，UNDOTS1回滚段在被多个不同的transaction使用着，这个回滚段里的extent循环到了第4100万行数据所在的extent，由于这条记录已经被标记提交了，所以这个extent是可以被其他transaction覆盖掉的！
5、到了1点45分，用户A的查询终于到了第4100万行，而这时已经出现了第4条说的情况，需要到回滚段UNDOTS1去找数据，但是已经被覆盖掉了，这时就出现了ORA-01555错误。

建用户名

创建表空间自增
CREATE TABLESPACE "REOM"
    LOGGING
    DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\TS_REOM.ORA' SIZE 3000M
    autoextend on
    next 100m maxsize 20480m ;

添加数据文件自增长
alter tablespace mocds add datafile 'D:\oracle\product\10.2.0\oradata\orcl\TS_mocds2.ORA' SIZE 3000M
autoextend on
next 100m maxsize 20480m ;

删除用户及其对象
drop user username cascade;

创建用赋权限
-- Create the user
create user LESWEB
identified by lesweb
default tablespace LES_TABLE
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to LESWEB;
grant dba to LESWEB;
grant resource to LESWEB;
-- Grant/Revoke system privileges
grant alter any procedure to LESWEB;
grant alter any table to LESWEB;
grant create any index to LESWEB;
grant create any procedure to LESWEB;
grant create any table to LESWEB;
grant create any view to LESWEB;
grant create session to LESWEB;
grant delete any table to LESWEB;
grant drop any index to LESWEB;
grant drop any procedure to LESWEB;
grant drop any table to LESWEB;
grant drop any view to LESWEB;
grant insert any table to LESWEB;
grant select any table to LESWEB;
grant unlimited tablespace to LESWEB;
grant update any table to LESWEB;

--默认表空间
revoke unlimited tablespace from LESWEB;
alter user LESWEB quota 0 on system;
alter user LESWEB quota unlimited on LES_TABLE;

设置用户默认表空间
SQL> revoke unlimited tablespace from user01;//撤销此权限
SQL> alter user user01 quota 0 on system;//将用户在System表空间的配额置为0
SQL> alter user user01 quota unlimited on ts01;//设置在用户在myhuang表空间配额不受限

按时间分组查询

--天
select to_char(obsdate,'yyyy-mm-dd'), avg(solar_irrad_avg) from T_BSRN_DATA where OBSDATE>=to_date('20091205','yyyymmdd') and OBSDATE<=to_date('20091209','yyyymmdd') group by to_char(obsdate,'yyyy-mm-dd')
--月
select to_char(obsdate,'yyyy-mm'), avg(solar_irrad_avg) from T_BSRN_DATA group by to_char(obsdate,'yyyy-mm')
--季
select to_char(obsdate,'yyyy-Q'), avg(solar_irrad_avg) from T_BSRN_DATA group by to_char(obsdate,'yyyy-Q')
--年
select to_char(obsdate,'yyyy'), avg(solar_irrad_avg) from T_BSRN_DATA group by to_char(obsdate,'yyyy')
--旬
select to_char(obsdate, 'yyyy-mm'), count(*),decode(trunc((to_char(obsdate, 'dd')-1)/10),0, '上旬',1, '中旬','下旬') from T_BSRN_DATA
group by to_char(obsdate, 'yyyy-mm'),decode(trunc((to_char(obsdate, 'dd')-1)/10),0, '上旬',1, '中旬','下旬')
--周
select to_char(obsdate, 'yyyy ww'),count(*) from T_BSRN_DATA where OBSDATE>=to_date('20090101','yyyymmdd') and OBSDATE<=to_date('20090110','yyyymmdd') group by to_char(obsdate, 'yyyy ww')

给表添加字段
alter table T_TEST2 add(FILENAME VARCHAR2 (100 Byte));
使用rename关键字来实现字段名的修改:alter table 表名 rename column旧的字段名 to 新的字段名名;
使用modify关键字来实现对数据类型的修改:alter table 表名 modify 字段名数据类型

表空间操作
CREATE TABLESPACE "LES_TABLE"
    LOGGING
    DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\LES_TABLE.ORA' SIZE 500M
    REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

　有两种方法，一种是为表空间增加数据文件
alter tablespace users add datafile 'c:\oracle\ora81\oradata\sid\user002.dbf' size 100M;
　另一种方法是增加表空间原有数据文件尺寸：
　alter database datafile 'c:\oracle\ora81\oradata\\sid\users.dbf' resize 1000M;

常用函数

一、字符函数

upper() 小写字母变大写 ·select upper('smith') from dual; 必须加上from
·select * from emp where ename =upper('smith');

lower() 大写字母变小写 ·select lower('HELLO WORLD') from dual;

initcap() 开头字母大写 ·select initcap('HELLO WORLD') from dual;
·select initcap(ename) from emp;

*字符串除了可以使用||来连接。还可以使用concat();函数来进行连接
·select concat('hellow','world') from dual;

可以进行字符串截取，求字符串长度。进行指定内容替换
·字符串截取：substr(); substr 的截取点是从0或者是1效果都是一样的（Oracle）
    Oracle 中可以输入负值来倒着截取。
     ·select ename ,substr(ename,-3,3) from emp;
·字符串长度：length();
·内容替换： replace();
_________________范例_______________
select substr('hello',1,3) 截取字符串,
         length('hello') 字符串长度,
         replace('hello','l','x') 字符串替换
   from dual;
________________________________________

二、数值函数
·四舍五入 :round(); 可以指定四舍五入位数select round(789.546,-2) from dual;
负值对整数进行操作。正值是小数
·截断小数位 :trunc();
·取余（取模）:mod
三、日期函数
·日期-数字=日期
·日期+数字=日期
·日期-日期=数字（天数）
·months_between();求出指定日期范围的月数
·add_months();在制定日期加上制定的月数，求出之后的日期
·next_day();下一个的今天是哪一个日期
·last_day();求出给定日期的月最后一天的日期

当前日期 sysdate关键字范例： select sysdate from dual;

四、转换函数
·to_char(): 转换成字符串
·通配符：·年：yyyy
·月：mm
·日：dd
·to_number(): 转换成数字
·to_date(): 转换成日期

五、通用函数
·nvl(字段,0) 如果字段里面的值是空就按照0显示
__________________________范例__________________________
select empno,ename,(nvl(sal,0)+nvl(comm,0))*12 from emp;
________________________________________________________
·decode 类似if(){}else{}
__________________________范例_________________________________
1·select decode(1,1,'内容是1',2,'内容是2',3,'内容是3') from dual;
2·select empno 编号, ename 姓名 , HIREDATE 日期,decode
(
job,'CLERK','业务员','SALESMAN','销售经理',
'MANAGER','经理','ANALYST','分析员',
'PRESIDENT','总裁'
) 职业
from emp;

Oracle数据库 CMD导出/导入
导入：imp edith/edith@orcl file=??.dmp fromuser=reom
imp system/sys@CAM_localhost file=D:\camfiles\cam09-11.dmp log=cam.log fromuser=camuser touser=camuser

SQL> create user user01 identified by password default tablespace ts01;

SQL> grant resource,connect to user01;

SQL> grant dba to user01;//赋DBA权限

SQL> revoke unlimited tablespace from user01;//撤销此权限

SQL> alter user user01 quota 0 on system;//将用户在System表空间的配额置为0

SQL> alter user user01 quota unlimited on ts01;//设置在用户在myhuang表空间配额不受限。

导出：

空库
exp scott/scott_2009@orcl rows=n COMPRESS=N file=d:\f\scott.dmp log=d:\f\scott.log

C:\Users\zhy>exp spod/spod@ORCL_172.18.128.15

Export: Release 10.2.0.3.0 - Production on 星期四 7月 21 15:53:36 2011

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
输入数组提取缓冲区大小: 4096 >

导出文件: EXPDAT.DMP > spod20110721.dmp

(1)E(完整的数据库), (2)U(用户) 或 (3)T(表): (2)U >

导出权限 (yes/no): yes >

导出表数据 (yes/no): yes >

压缩区 (yes/no): yes >

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
要导出的用户: (按 RETURN 退出) > spod

要导出的用户: (按 RETURN 退出) >

. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 SPOD 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 SPOD 的对象类型定义
即将导出 SPOD 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 SPOD 的表通过常规路径...
. . 正在导出表                        QHJZHGXS导出了        1440 行
. . 正在导出表                      REFSTATION导出了       46920 行
. . 正在导出表                    STATIONCHINA导出了        2150 行
. . 正在导出表                    T_ALARM_INFO导出了           0 行

select t.province,WMSYS.WM_CONCAT(sta_num) from hunan t
where t.city='长沙' and t.country='浏阳市' and t.check_flag='否'
group by t.province

创建分区表

create table MTR_AWS_OBSDATA_NEW (
   STATIONNUM           VARCHAR2(5)                     not null,
   OBSDATE              DATE                            not null,
   OPERATETIME          DATE,
   WINDDIRECTION2       VARCHAR2(3),
   WINDSPEED2           NUMBER(4,1),
   WINDDIRECTION10      VARCHAR2(3),
   WINDSPEED10          NUMBER(4,1),
   MAXWINDDIRECTION     VARCHAR2(3),
   MAXWINDSPEED         NUMBER(4,1),
   MAXWINDTIME          VARCHAR2(4),
   INSTANTANEOUSWINDDIRECTION VARCHAR2(3),
   INSTANTANEOUSWINDSPEED NUMBER(4,1),
   EXMAXWINDDIRECTION   VARCHAR2(3),
   EXMAXWINDSPEED       NUMBER(4,1),
   EXMAXWINDTIME        VARCHAR2(4),
   PRECIPITATIONAMOUNT NUMBER(5,1),
   AIRTEMPERATURE       NUMBER(5,1),
   MAXAIRTEMPERATURE    NUMBER(5,1),
   MAXAIRTEMPERATURETIME VARCHAR2(4),
   MINAIRTEMPERATURE    NUMBER(5,1),
   MINAIRTEMPERATURETIME VARCHAR2(4),
   RELATIVEHUMIDITY     NUMBER(3),
   MINRELATIVEHUMIDITY NUMBER(3),
   MINRELATIVEHUMIDITYTIME VARCHAR2(4),
   VAPORPRESSURE        NUMBER(4,1),
   DEWPOINT             NUMBER(5,1),
   STATIONPRESSURE      NUMBER(6,1),
   MAXSTATIONPRESSURE   NUMBER(6,1),
   MAXSTATIONPRESSURETIME VARCHAR2(4),
   MINSTATIONPRESSURE   NUMBER(6,1),
   MINSTATIONPRESSURETIME VARCHAR2(4),
   GRASSTEMPERATURE     NUMBER(5,1),
   MAXGRASSTEMPERATURE NUMBER(5,1),
   MAXGRASSTEMPERATURETIME VARCHAR2(4),
   MINGRASSTEMPERATURE NUMBER(5,1),
   MINGRASSTEMPERATURETIME VARCHAR2(4),
   GROUNDSURFACETEMPERATURE NUMBER(5,1),
   MAXGROUNDSURFACETEMPERATURE NUMBER(5,1),
   MAXGROUNDSURFACETEMPERATURET VARCHAR2(4),
   MINGROUNDSURFACETEMPERATURE NUMBER(5,1),
   MINGROUNDSURFACETEMPERATURET VARCHAR2(4),
   GROUNDTEMPERATURE5CM NUMBER(5,1),
   GROUNDTEMPERATURE10CM NUMBER(5,1),
   GROUNDTEMPERATURE15CM NUMBER(5,1),
   GROUNDTEMPERATURE20CM NUMBER(5,1),
   GROUNDTEMPERATURE40CM NUMBER(5,1),
   GROUNDTEMPERATURE80CM NUMBER(5,1),
   GROUNDTEMPERATURE160CM NUMBER(5,1),
   GROUNDTEMPERATURE320CM NUMBER(5,1),
   EVAPORATIONAMOUNT    NUMBER(5,1),
   SEALEVELPRESSURE     NUMBER(6,1),
   VISIBILITY           NUMBER(5),
   MINVISIBILITY        NUMBER(5),
   MINVISIBILITYTIME    VARCHAR2(4),
   CORRECT_FLAG         VARCHAR2(3)
)
PARTITION BY RANGE ( OBSDATE )
(
   partition part_20110401 values less than( to_date('20110402','yyyymmdd') ) tablespace reom,
   partition part_20110402 values less than( to_date('20110403','yyyymmdd') ) tablespace reom,
   partition part_20110403 values less than( to_date('20110404','yyyymmdd') ) tablespace reom,
   partition part_20110404 values less than( to_date('20110405','yyyymmdd') ) tablespace reom,
   partition part_20110405 values less than( to_date('20110406','yyyymmdd') ) tablespace reom,
   partition part_20110406 values less than( to_date('20110407','yyyymmdd') ) tablespace reom,
   partition part_20110407 values less than( to_date('20110408','yyyymmdd') ) tablespace reom,
   partition part_20110408 values less than( to_date('20110409','yyyymmdd') ) tablespace reom,
   partition part_20110409 values less than( to_date('20110410','yyyymmdd') ) tablespace reom,
   partition part_20110410 values less than( to_date('20110411','yyyymmdd') ) tablespace reom,
   partition part_20110411 values less than( to_date('20110412','yyyymmdd') ) tablespace reom,
   partition part_20110412 values less than( to_date('20110413','yyyymmdd') ) tablespace reom,
   partition part_20110413 values less than( to_date('20110414','yyyymmdd') ) tablespace reom,
   partition part_20110414 values less than( to_date('20110415','yyyymmdd') ) tablespace reom,
   partition part_20110415 values less than( to_date('20110416','yyyymmdd') ) tablespace reom,
   partition part_20110416 values less than( to_date('20110417','yyyymmdd') ) tablespace reom,
   partition part_20110417 values less than( to_date('20110418','yyyymmdd') ) tablespace reom,
   partition part_20110418 values less than( to_date('20110419','yyyymmdd') ) tablespace reom,
   partition part_20110419 values less than( to_date('20110420','yyyymmdd') ) tablespace reom,
   partition part_20110420 values less than( to_date('20110421','yyyymmdd') ) tablespace reom,
   partition part_20110421 values less than( to_date('20110422','yyyymmdd') ) tablespace reom,
   partition part_20110422 values less than( to_date('20110423','yyyymmdd') ) tablespace reom,
   partition part_20110423 values less than( to_date('20110424','yyyymmdd') ) tablespace reom,
   partition part_20110424 values less than( to_date('20110425','yyyymmdd') ) tablespace reom,
   partition part_20110425 values less than( to_date('20110426','yyyymmdd') ) tablespace reom,
   partition part_20110426 values less than( to_date('20110427','yyyymmdd') ) tablespace reom,
   partition part_20110427 values less than( to_date('20110428','yyyymmdd') ) tablespace reom,
   partition part_20110428 values less than( to_date('20110429','yyyymmdd') ) tablespace reom,
   partition part_20110429 values less than( to_date('20110430','yyyymmdd') ) tablespace reom,
   partition part_20110430 values less than( to_date('20110501','yyyymmdd') ) tablespace reom
);

表重命名
alter table isgood rename to isnotgood

查看oracle回滚,查看表空间
SELECT USED_UBLK FROM V$TRANSACTION; 回滚时大小逐渐变小

Select Tablespace_Name 表空间,Sum(bytes)/1024/1024 已用表空间M From Dba_Segments Group By Tablespace_Name

SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name

zhouhongyin

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle常用命令查询、建表、建用户、分区、表空间、函数

常用SQL 1.distinct某个字段：select count(distinct(DEV_MOD_CODE)) from T_DM_USER_DEV_INF2.表清空：TRUNCATE TABLE table_name3.插入：INSERT INTO EMPLOYEES VALUES ('Bunyan','Paul','1970-07-04','Boston',12,700
复制链接

扫一扫