oracle常用语句总结

oracle常用语句总结
表空间扩展，创建表空间。。。

1、oracle查询表中的最新10条数据
select * from testdataa1004 t where rownum<=10 order by primarykey desc
2、将查询结果放入到新表中
create table aa4 as select * from testdataa1004 t where primarykey='2014-09-1210:06:269'
3、oracle把数据结果导入到另一个表中
insert into testdata select * from aa t
4、创建表空间
create tablespace heatpump     
logging
datafile 'D:\app\Administrator\oradata\orcl\USERS02.DBF'                                                                                
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
5、查询表空间
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name

6、扩展表空间
alter tablespace USERS   
add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF' size 10240M 

AMERICAN_AMERICA.AL32UTF8


程序集：externalWSAssembly = WSHelper.GetWSAssembly(externalWSUrl)

externalWSClassType = externalWSAssembly.GetType(externalWSClassName, True, True)







            externalWSClassName = DataComponent.GetWSClassName(Me.externalWSUrl)
            externalWSAssembly = WSHelper.GetWSAssembly(externalWSUrl)
            externalWSClassType = externalWSAssembly.GetType(externalWSClassName, True, True)
            externalWSClassInstance = Activator.CreateInstance(externalWSClassType)
            externalWSMethodInfo = externalWSClassType.GetMethod("getTestProdInfoItem")
            externalWSMethodResult = externalWSMethodInfo.Invoke(externalWSClassInstance, Nothing)








1、查看表空间详细信息
select * from dba_data_files order by tablespace_name

2、创建表空间：
create tablespace HEATPUMP  
logging  
datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\heatpump01.DBF' 
size 200m  
autoextend on  
next 200m maxsize unlimited  
extent management local;


3、查看当前用户每个表占用空间的大小：
select t.tablespace_name, segment_name,sum(bytes)/1024/1024 from user_extents t group by segment_name,t.tablespace_name



4.修改表的空间
alter table TABLE_NAME move tablespace TABLESPACENAME
查询当前用户下的所有表
select t.tablespace_name,  'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_tables t;
5.修改表的索引的空间
alter index INDEX_NAME rebuild tablespace TABLESPACENAME
查询当前用户下的所有索引
select t.tablespace_name, 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes t;




--创建表空间
create tablespace hr2  
logging  
datafile 'E:\oracle\product\10.2.0\oradata\test\hr2_data1.dbf' 
size 80m  
autoextend on  
next 200m maxsize 20480m  
extent management local
blocksize 8k;--默认8K，可以修改，其作用见"三"中说明; 


--
alter tablespace USERS add datafile 'E:\oracle\product\10.2.0\oradata\USER_data3.dbf' 
size 200m autoextend on next 200m maxsize unlimited;



--创建表
--SENSORVALUE blob
create table TESTDATA2
(
  PRIMARYKEY  CHAR(30) not null,
  HOWLONG     FLOAT not null,
  SENSORVALUE varchar2(4000) 
)
tablespace TBS_USER
  pctfree 10
  initrans 1 
  storage
  (
    initial 20M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints ，注意修改默认表空间
alter table TESTDATA2
  add constraint PK_TESTDATA_2 primary key (PRIMARYKEY, HOWLONG); --tablespace TBS_USER;


--删除表空间
drop tablespace TBS_USER including contents;
drop tablespace TBS_USER including contents;
--DROP TABLESPACE "index01" INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE HR2 INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS; --HR1,HR2,TBS_USER

alter database datafile 'E:\oracle\product\10.2.0\oradata\TBS_USER_DATA1.dbf' offline drop;




--1、查询当前系统中正在执行的sql: 
SELECT osuser, username, sql_text from v$session a, v$sqltext b 
where a.sql_address =b.address order by address, piece;
--4、oracle 查看已经执行过的sql 这些是存在共享池中的：
select * from v$sqlarea t order by t.LAST_ACTIVE_TIME desc;

--5、【获取oracle前10条最耗资源的sql语句】：
SELECT * FROM  (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE, DISK_READS,sql_text  FROM  v$sqlarea
     ORDER BY disk_reads DESC 
 )  WHERE ROWNUM<11; 

 --表空间详细信息
 select * from dba_data_files order by tablespace_name
--查看表空间使用情况
select dbf.tablespace_name,dbf.totalspace "总量(M)",dbf.totalblocks as 总块数,dfs.freespace "剩余总量(M)",dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例" 
from (select t.tablespace_name,sum(t.bytes) / 1024 / 1024 totalspace,sum(t.blocks) totalblocks from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespace,sum(tt.blocks) freeblocks from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

--查看表占用空间情况
select segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name;

----查询表空间的数据文件
SELECT * FROM dba_data_files WHERE tablespace_name = 'USERS';
--查询出表空间对应的数据文件，在磁盘空间允许的情况下
ALTER DATABASE  DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\USERS_2.DBF' RESIZE 15000M; 
--如果表空间需要自动扩展或者有最大值限制的
ALTER DATABASE datafile 'E:\oracle\product\10.2.0\oradata\orcl\USERS_2.DBF' autoextend ON NEXT 100M maxsize 20000M; 
--另外，查看用户对表空间的使用限额：如果maxsize 字段值为 -1，则是无限制，如果有其他值，则该值是最大值
SELECT * FROM user_ts_quotas;
--查看用户下的所有表
select 'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_all_tables; 
--查询当前用户下的所有索引 
select 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes; 



select count(*) from testdata2;
select max(t.howlong) from testdata2 t;
select min(t.howlong) from testdata2 t;
select t.PRIMARYKEY,HOWLONG  from testdata2 t  order by howlong;
select t.PRIMARYKEY,HOWLONG,t.SENSORVALUE  from testdata2 t  where t.howlong<0.0003;

truncate table testdata2;

--select t.PRIMARYKEY,HOWLONG,sensorvalue  from testdata2 t where t.howlong <0.00008 order by howlong;
select * from v$session_wait_history;
select * from v$sqlarea;

--查看当前的等待事件
select * from v$session_wait where event = 'direct path read temp';


select count(*) from sensorconfig;