Oracle常用总结

超、凢脫俗 2022-08-04 15:56 299阅读 0赞

**Oracle常用总结**

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

5.查询当前用户下的所有表

select t.tablespace_name,  'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_tables t;

6.修改表的索引的空间

alter index INDEX_NAME rebuild tablespace TABLESPACENAME

7.查询当前用户下的所有索引

select t.tablespace_name, 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes t;

8.创建表空间

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;

9.创建表

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;

10.删除表空间

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;

11、查询当前系统中正在执行的sql:

SELECT osuser, username, sql_text from v$session a, v$sqltext b 
    where a.sql_address =b.address order by address, piece;

12、oracle 查看已经执行过的sql 这些是存在共享池中的：

select * from v$sqlarea t order by t.LAST_ACTIVE_TIME desc;

13.【获取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;

14.表空间详细信息

select * from dba_data_files order by tablespace_name

15.查看表空间使用情况

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

16.查看表占用空间情况

select segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name;

17.查询表空间的数据文件

SELECT * FROM dba_data_files WHERE tablespace_name = 'USERS';

18.查询出表空间对应的数据文件，在磁盘空间允许的情况下

ALTER DATABASE  DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\USERS_2.DBF' RESIZE 15000M;

19.如果表空间需要自动扩展或者有最大值限制

ALTER DATABASE datafile 'E:\oracle\product\10.2.0\oradata\orcl\USERS_2.DBF' autoextend ON NEXT 100M maxsize 20000M;

20.另外，查看用户对表空间的使用限额：如果maxsize 字段值为 -1，则是无限制，如果有其他值，则该值是最大值

SELECT * FROM user_ts_quotas;

21.查看用户下的所有表

select 'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_all_tables;

22.查询当前用户下的所有索引

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;

23.查看当前的等待事件

select * from v$session_wait where event = 'direct path read temp';
    
    
    select count(*) from sensorconfig;

24.查询oracle表的信息（表，字段，约束，索引）  
通过搜索摸索，总结了一下oracle中查询表的信息，包括表名，字段名，字段类型，主键，外键唯一性约束信息，索引信息查询SQL如下，希望对大家有所帮助：

25.查询出所有的用户表

select   *   from   user_tables

可以查询出所有的用户表

通过表名过滤需要将字母作如下处理

select * from user_tables where table_name = upper('表名')

因为无论你建立表的时候表名名字是大写还是小写的，create语句执行通过之后，对应的user\_tables表中的table\_name字段都会自动变为大写字母，所以必须通过内置函数upper将字符串转化为大写字母进行查询，否则，即使建表语句执行通过之后，通过上面的查询语句仍然查询不到对应的记录。

26.查询出用户所有表的索引

select   *   from   user_indexes

27.查询用户表的索引(非聚集索引):

select   *   from   user_indexes where   uniqueness='NONUNIQUE'

28.查询用户表的主键(聚集索引):

select   *   from   user_indexes where   uniqueness='UNIQUE'

29.查询表的索引

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and
    t.table_name='NODE'

30.查询表的主键

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and
    au.constraint_type = 'P' AND cu.table_name = 'NODE'

31.查找表的唯一性约束（包括名称，构成列）：

select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and
    cu.table_name='NODE'

32.查找表的外键

select * from user_constraints c where c.constraint_type = 'R' and c.table_name='STAFFPOSITION'

查询外键约束的列名：

select * from user_cons_columns cl where cl.constraint_name = 外键名称

查询引用表的键的列名：

select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名

33.查询表的所有列及其属性  
方法一：

select * from user_tab_columns where table_name=upper('表名');

方法二：

select cname,coltype,width from col where tname=upper('表名');

34.Oracle内存大小

show parameter sga;

35.修改SGA  
要修改SGA的大小，应使用以下命令：

alter system set sga_max_size=164M scope=spfile;

这里我修改为164M，你可以改成你想要的大小。由于sga\_max\_size是一个静态参数，不能够在运行时修改，因此这里的scope只能设置为spfile，新的内存大小将在Oracle重启后生效。  
问题1：  
简单解决“ORA-27100: shared memory realm already exists”的问题  
折腾Oracle时，出现了“ORA-27100: shared memory realm already exists”的问题。上网查问题根源，乃是32位机SGA及PGA不能大于1.7G之故。  
SGA的设置在SPFILEORCL.ORA里面，但该文件是一个二进制文件，不好随便编辑。  
网上给的解决办法是用一个“正常的”PFILE启动Oracle，然后重新生成SPFILE，操作颇繁琐。其实SGA过大的问题也不难解决，不必用“PFILE”那样曲折迂回的办法：用一个二进制编辑器打开SPFILEORCL.ORA文件，可以发现其配置信息中有部分是可读的文本，找到“sga\_max\_size=”，将其后的数值改小，保存文件，再重新启动Oracle即可。

36.创建触发器，其中定义变量

CREATE OR REPLACE TRIGGER "BEFOREINSERT_INSSTATUS"
    before insert on INSTRUMENT_STATUS for each row
    declare nrow number;
    begin
    select count(*) into nrow from INSTRUMENT_STATUS t where (t.siteid= :new.siteid) and t.instrumentid= :new.instrumentid;
    if nrow > 0 then
    delete  from INSTRUMENT_STATUS t where (t.siteid= :new.siteid) and t.instrumentid= :new.instrumentid;
    end if;
    end;