oracle常用语句总结
来源:互联网 发布:php json null 处理 编辑:程序博客网 时间:2024/06/05 05:45
oracle常用语句总结
表空间扩展,创建表空间。。。
1、oracle查询表中的最新10条数据select * from testdataa1004 t where rownum<=10 order by primarykey desc2、将查询结果放入到新表中create table aa4 as select * from testdataa1004 t where primarykey='2014-09-1210:06:269'3、oracle把数据结果导入到另一个表中insert into testdata select * from aa t4、创建表空间create tablespace heatpump loggingdatafile 'D:\app\Administrator\oradata\orcl\USERS02.DBF' size 50mautoextend onnext 50m maxsize 20480mextent management local;5、查询表空间select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name6、扩展表空间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_name2、创建表空间: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_name4.修改表的空间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 localblocksize 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 blobcreate 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_USERalter 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 tgroup by t.tablespace_name) dbf,(select tt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespace,sum(tt.blocks) freeblocks from dba_free_space ttgroup by tt.tablespace_name) dfswhere 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;
0 0
- oracle常用语句总结
- 总结oracle常用的语句
- ORACLE 常用SQL语句总结
- Oracle 常用查询语句总结
- oracle总结:常用sql语句
- 常用sql语句总结-oracle
- Oracle常用Sql语句进行总结
- oracle常用数据字典和SQL语句总结
- oracle数据库常用的sql语法语句总结
- oracle常用SQL语句
- oracle常用SQL语句
- oracle常用SQL语句
- 常用Oracle语句
- oracle 常用sql语句
- oracle常用sql语句
- Oracle常用语句(不断更新)
- oracle常用SQL语句
- Oracle常用SQL语句
- C# 学习网站整理
- ClassLoder(类加载器)与JDBC的调用
- 2016搜素基础1006
- DL4NLP---第二讲(上)
- java socket 服务器代码 自动接收文件并存储服务器
- oracle常用语句总结
- Java读写properties配置文件
- 11.2 练习题
- STM3216位IO口操作的一些教训
- JDBC的分页查询
- ECSHOP仿淘宝商品评论现金奖励功能
- Prime Path (bfs)POJ
- DL4NLP---第三讲
- DubboKeeper监控平台搭建