软件测试常用的oracle语句,Oracle 一些常用的语句记录

weixin_39616379

于 2021-07-22 17:22:53 发布

阅读量222

点赞数

文章标签：软件测试常用的oracle语句

1、oracle数据库锁表查询：

select db.object_name,sess.sid,sess.serial#,sess.module,sess.action ,

'alter system kill session '''|| sess.sid ||','||sess.serial#||'''',sess.OSUSER from

v$locked_object obj,

dba_objects db,

v$session sess

where obj.object_id=db.object_id

and obj.session_id=sess.sid;

1、查看oracle用户数：

select t.USERNAME,count(0) from v$session t group by t.USERNAME；

2、查看oracle数据库文件路径启用情况：

select file#,name,status from v$datafile;

3、下线oracle数据库文件：

alter tablespace tsp_data offline;

4、上线oracle数据库文件：

alter tablespace tsp_data online;

5、移动数据库文件到其他目录：

alter tablespace tsp_data add datafile '/home/oracle/tablespaces/M2M/tsp_data1.dbf' size 1024M autoextend on;

select(sum(pins-reloads))/sum(pins) from v$librarycache;

select(sum(gets-getmisses))/sum(gets) from v$rowcache;

select * from v$sgastat where name='free memory';

select name,value from v$sysstat where name in ('db block gets','consistent gets','physical reads') ;

select name,value from v$sysstat where name = 'redo log space requests' ;

select round((100*b.value)/decode((a.value+b.value), 0, 1, (a.value+b.value)), 2)from v$sysstat a, v$sysstat b where a.name='sorts (disk)' and b.name='sorts (memory)';

select count(*) from tsp_log_fortify_service;

6、select * from table;

select * from table where xingbie='男' order by desc(asc);

select count(*) from table ;

update table set xingbie='女' where name='lin';

insert into table (no,name) values (1,'林峰');

\\把生日计算出年龄 jssr为日期字段

select months_between(sysdate,to_date(jssr,'yyyymmdd'))/12 as 年龄 from table;

\\把小数取整cj为成绩字段

select trunc(cj) from table ; //等同于round

--学生表大于20岁的学生；与班级好为198804的

select * from xjgl_xsb where (trunc(months_between(sysdate,to_date(xssr,'yyyymmdd'))/12)) >=20 order by 4 asc;

select * from xjgl_xsb where (trunc(months_between(sysdate,to_date(xssr,'yyyymmdd'))/12)) >=20

union select * from xjgl_xsb where bjh='199804' order by 4 asc;

----学生表大于20岁的学生；与班级好为198804的交集

select * from xjgl_xsb where (trunc(months_between (sysdate,to_date(xssr,'yyyymmdd'))/12)) >=20

intersect select * from xjgl_xsb where bjh='199802' order by 4 desc;

7、查看oracle的编码集：

select userenv('language') from dual

create TEMPORARY tablespace MTP_TEMP tempfile '/opt/oracle/oradata/mtpora/mtp_temp.dbf' size 64m autoextend on next 1M maxsize unlimited extent management local;

8、创建表空间和用户

create TEMPORARY tablespace TSP1_TEMP tempfile '/opt/oracle/oradata/QQT_Ora/tsp1_temp.dbf' size 64m autoextend on next 1M maxsize unlimited extent management local;

create tablespace TSP1_DATA datafile '/opt/oracle/oradata/QQT_Ora/tsp1_data.dbf' size 256m autoextend on next 10M maxsize unlimited extent management local;

create USER tsp1 IDENTIFIED BY tsp1 DEFAULT TABLESPACE TSP1_DATA TEMPORARY TABLESPACE TSP1_TEMP;

GRANT CONNECT TO tsp1;

GRANT RESOURCE TO tsp1;

9、导出oracle数据库

导出语句：exp obd/obd file = /home/oracle/obd.dmp;

导入语句：imp system/oracle file=/home/oracle/obd.dmp fromuser=obd touser=obd

注意：导入到新数据需要在新库创建相同的表空间和用户；

--删除表空间语句：

drop user masp cascade;

drop tablespace masp including contents and datafiles;

如果表空间文件误删了恢复文件的方法如下：

SQL> startup mount