`
吃猫的鱼
  • 浏览: 45499 次
  • 性别: Icon_minigender_1
  • 来自: 长沙
社区版块
存档分类
最新评论

Oracle 常用操作(实用)

 
阅读更多
  • SQL>select text from all_source where owner=user and name=upper('&plsql_name');
    
     SQL>select * from user_ind_columns where index_name=upper('&index_name');
     将表记录恢复到指定时间段以前的状态:
  1. 查看指定时间段以前的表记录,时间单位:分钟:
    SELECT * FROM TFM_SERV_EXT  AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '30'MINUTE)
      
  2. 用指定时间前的表记录创建临时表:
    CREATE TABLE TFM_SERV_EXT_1106 AS SELECT * FROM TFM_SERV_EXT  AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '30'MINUTE) ;
     
  3. Delete掉错误表的所有记录
  4. 将临时表记录插入错误表:
    INSERT INTO TFM_SERV_EXT  SELECT * FROM TFM_SERV_EXT_1106;
  • 查看DB锁表信息:
    SELECT SESS.SID,
       SESS.SERIAL#,
       LO.ORACLE_USERNAME,
       LO.OS_USER_NAME,
       AO.OBJECT_NAME,
       LO.LOCKED_MODE
       FROM V$LOCKED_OBJECT LO,
       DBA_OBJECTS AO,
       V$SESSION SESS
    WHERE AO.OBJECT_ID = LO.OBJECT_ID AND LO.SESSION_ID = SESS.SID;
     
  • Kill锁表Session:
    如有記錄則表示有lock,記錄下SID和serial# ,將記錄的ID替換下面的738,1429,即可解除LOCK
    alter system kill session '738,1429';
     
  • 查询session未释放的SQL:
    SELECT T.*, S.SQL_TEXT
      FROM V$SQL S,
           (SELECT COUNT(*), V.PREV_SQL_ADDR, V.PREV_HASH_VALUE
              FROM V$SESSION V
             WHERE V.USERNAME = 'ETL72_DEV'
             GROUP BY V.PREV_SQL_ADDR, V.PREV_HASH_VALUE
             ORDER BY COUNT(*) DESC) T
     WHERE S.ADDRESS = T.PREV_SQL_ADDR
       AND S.HASH_VALUE = PREV_HASH_VALUE ';
    
     
  • 表空间操作:
  1. 建立表空间
    CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定,区尺寸默认为64k 
    
     
  2. 删除表空间
    DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 
    
     
  3. 建立UNDO表空间 
    CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M #注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间: ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
     
  4. 建立临时表空间
    CREATE TEMPORARY TABLESPACE temp_data TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M 
    
     
  5. 改变表空间状态
    1.使表空间脱机 ALTER TABLESPACE game OFFLINE; 如果是意外删除了数据文件,则必须带有RECOVER选项 ALTER TABLESPACE game OFFLINE FOR RECOVER; 
    2.使表空间联机 ALTER TABLESPACE game ONLINE; 
    3.使数据文件脱机 ALTER DATABASE DATAFILE 3 OFFLINE; 
    4.使数据文件联机 ALTER DATABASE DATAFILE 3 ONLINE; 
    5.使表空间只读 ALTER TABLESPACE game READ ONLY; 
    6.使表空间可读写 ALTER TABLESPACE game READ WRITE; 
    
     
  6. 扩展表空间 首先查看表空间的名字和所属文件
    SELECT UPPER(F.TABLESPACE_NAME) "表空间名", 
      D.TOT_GROOTTE_MB "表空间大小(M)", 
      D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", 
      TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 
      2), 
      '990.99') "使用比", 
      F.TOTAL_BYTES "空闲空间(M)", 
      F.MAX_BYTES "最大块(M)" 
      FROM (SELECT TABLESPACE_NAME, 
      ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 
      ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 
      FROM SYS.DBA_FREE_SPACE 
      GROUP BY TABLESPACE_NAME) F, 
      (SELECT DD.TABLESPACE_NAME, 
      ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 
      FROM SYS.DBA_DATA_FILES DD 
      GROUP BY DD.TABLESPACE_NAME) D 
      WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 
      ORDER BY 4 DESC; 
    
     

6.1 增加数据文件

ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M; 

6.2 手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME02.dbf' RESIZE 4000M; 

    6.3 设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME02.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M; 

    6.4 设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

 

6.5 查询表空间大小

SELECTDISTINCT a.tablespace_name 表空间名称, 
                trunc((free_space / total_space) *100) || '%' 可用率,
                to_char(free_space /1024/1024, '9999999990.99') || 'M' 剩余空间,
                to_char(total_space /1024/1024, '9999999990.99') || 'M' 总空间,
                to_char((total_space - free_space) /1024/1024,
                         '9999999990.99') || 'M' 已使用空间
  FROM (SELECT tablespace_name, SUM(bytes) free_space
           FROM dba_free_space
          GROUPBY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total_space
           FROM dba_data_files
          GROUPBY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name;

 

 

 

  • 常用数据字典的查询使用方法。
     1、用户
          查看当前用户的缺省表空间
    SQL>select username,default_tablespace from user_users;
    
     查看当前用户的角色
     SQL>select * from user_role_privs;
    
           查看当前用户的系统权限和表级权限
    SQL>select * from user_sys_privs;
    SQL>select * from user_tab_privs;
    
     创建数据库用户并赋权
    drop user etl72_dev cascade;
    DROP TABLESPACE ETL72_DEV INCLUDING CONTENTS AND DATAFILES; 
    
    CREATE TABLESPACE ETL72_DEV DATAFILE 'D:\oracle\product\10.2.0\oradata\osstest\DATA02.dbf' SIZE 1200M UNIFORM SIZE 128k; 
    create user etl72_dev
      identified by smart
      default tablespace ETL72_DEV
      temporary tablespace temp
      profile DEFAULT;
    -- Grant/Revoke role privileges 
    grant connect to etl72_dev;
    grant exp_full_database to etl72_dev;
    grant imp_full_database to etl72_dev;
    grant resource to etl72_dev;
    -- Grant/Revoke system privileges 
    grant create procedure to etl72_dev;
    grant create trigger to etl72_dev;
    grant execute any procedure to etl72_dev;
    grant grant any privilege to etl72_dev;
    grant restricted session to etl72_dev;
    grant select any table to etl72_dev;
    grant unlimited tablespace to etl72_dev;
    grant create any view to etl72_dev;
    
    grant create session to etl72_dev;
    GRANT DELETE ANY TABLE TO etl72_dev;
    GRANT DROP ANY TABLE TO etl72_dev;
    GRANT INSERT ANY TABLE TO etl72_dev;
    GRANT SELECT ANY TABLE TO etl72_dev;
    GRANT UNLIMITED TABLESPACE TO etl72_dev;
    GRANT UPDATE ANY TABLE TO etl72_dev;
    
     
    
     
         

2、表
     查看用户下所有的表

SQL>select * from user_tables;

        查看名称包含log字符的表

SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0;

       查看某表的创建时间

SQL>select object_name,created from user_objects where object_name=upper('&table_name');

查看某表的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');

 查看放在ORACLE的内存区里的表

SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

 
            批量删除:

select 'drop table '||table_name||';' as sqlscript from user_tables;

 
 3、索引
      查看索引个数和类别

SQL>select index_name,index_type,table_name from user_indexes order by table_name;

 
            查看索引被索引的字段

SQL>select * from user_ind_columns where index_name=upper('&index_name');

 
            查看索引的大小
     

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');

 

 4、序列号
      查看序列号,last_number是当前值

SQL>select * from user_sequences;

 
       5、视图
      查看视图的名称

SQL>select view_name from user_views;

 
            查看创建视图的select语句

SQL>set view_name,text_length from user_views;
SQL>set long 2000;                说明:可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');

 
       6、同义词
            查看同义词的名称

SQL>select * from user_synonyms;

 
             7、约束条件
      查看某表的约束条件

SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
           from user_constraints where table_name = upper('&table_name');

SQL>select c.constraint_name,c.constraint_type,cc.column_name
          from user_constraints c,user_cons_columns cc
          where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
          and c.owner = cc.owner and c.constraint_name = cc.constraint_name
          order by cc.position;

 
         查看有几个表引用了其中某个特定表的主键做为其外键的

select t.table_name from user_constraints t
where t.constraint_type='R' and t.r_constraint_name
in(
select s.constraint_name from user_constraints s
where s.table_name='主表名' and s.constraint_type='P') 

  
     查看外键所关联的数据表

SELECT * FROM USER_CONSTRAINTS C WHERE C.CONSTRAINT_TYPE = 'R' AND C.CONSTRAINT_NAME LIKE '%ETL_TASK'

 
 8、存储函数和过程
      查看函数和过程的状态

SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';

 
            查看函数和过程的源代码

SQL>select text from all_source where owner=user and name=upper('&plsql_name');

 
      9、DB Link:

DROP DATABASE LINK elinkDB_copy;          
create public database link <DBLink名称> connect to <被连接库的用户名> identified by <被连接库的密码> using '<Oracle客户端工具建立的指向被连接库服务名>';  

DB Link 使用示例

INSERT INTO T_DEPARTMENT_DEFINE          
   (DEPARTMENT_CODE, INTERNAL_DEPARTMENT_CODE,          
    DEPARTMENT_CHINESE_NAME, DEPARTMENT_ABBR_NAME, DEPARTMENT_LEVEL,          
    FOUND_DATE, CHINESE_ADDRESS, POSTCODE, TELEPHONE,          
    UPPER_DEPARTMENT_CODE, LINK_MAN_CODE, TELE_AREA_CODE,          
    LEVEL_DEP_CODE, FCD, FCU, LCD, LCU)          
  (SELECT DEPARTMENT_CODE, INTERNAL_DEPARTMENT_CODE, DEPARTMENT_CHINESE_NAME,          
  DEPARTMENT_ABBR_NAME, DEPARTMENT_LEVEL, FOUND_DATE,          
  CHINESE_ADDRESS, POSTCODE, TELEPHONE, UPPER_DEPARTMENT_CODE,          
  LINK_MAN_CODE, TELE_AREA_CODE, LEVEL_DEP_CODE, FCD, FCU, LCD, LCU          
     FROM T_DEPARTMENT_DEFINE@elinkDB_copy);          
commit;          
ALTER SESSION CLOSE DATABASE LINK elinkDB_copy;

  

 

 

ORACLE常用的字段类型有
CHAR                        固定长度的字符串
VARCHAR2               可变长度的字符串
NUMBER(M,N)           数字型M是位数总长度, N是小数的长度
DATE                        日期类型

 

创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
创建表时可以用中文的字段名, 但最好还是用英文的字段名
创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY

    5.ALTER        (改变表, 索引, 视图等)

改变表的名称
ALTER TABLE 表名1  RENAME TO 表名2;
在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名 字段名描述;
修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;
给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;

    6.DROP(删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)

删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;

    7.TRUNCATE (清空表里的所有记录, 保留表的结构)

TRUNCATE 表名;

    8.查看当前数据库谁在运行什么语句:

SELECT OSUSER, USERNAME, SQL_TEXT
  FROM V$SESSION A, V$SQLTEXT B  
 WHERE A.SQL_ADDRESS = B.ADDRESS
 ORDER BY ADDRESS, PIECE;

 

15
9
分享到:
评论
2 楼 有一手DYH 2015-06-26  
高级查询有涉及吗?
1 楼 ronon 2015-06-25  
good.

相关推荐

    C# oracle 常用操作类

    自己常用的Oracle 操作类,写写小程序很好用。简单实用。

    oracle 常用命令 TXT 最全的

    根据提供的文件信息,我们可以整理出一系列关于Oracle数据库管理和操作的重要知识点。下面将详细解析这些知识点,并尽可能地提供更多的背景信息和实用建议。 ### 1. 启动与停止Oracle服务 - **启动Oracle服务**: ...

    Oracle的一些常用操作

    根据提供的标题、描述以及部分内文,我们可以整理出关于Oracle数据库的一些常用操作及查询语句。这些内容对于初学者来说非常实用,可以帮助他们快速掌握如何管理和查询Oracle数据库中的信息。 ### Oracle的一些常用...

    oracle常用sql整理

    本文将基于"oracle常用sql整理"的主题,深入探讨Oracle SQL的一些核心概念、语句及其实用技巧,适合初级到中级水平的学习者。 一、SQL基础 SQL是标准化的查询语言,分为DDL(Data Definition Language)、DML(Data...

    oracle 常用应用操作总结

    以下是对Oracle常用应用操作的详细总结: 1. **变量赋值与判断语句**: - 在Oracle SQL中,给变量赋值时不能使用等号(=),而是使用冒号等于(:=)操作符。例如:`variable_name := value;` - Oracle的条件判断...

    Oracle常用语句-总结文档汇总

    "Oracle常用语句-总结文档汇总"提供了丰富的资源,涵盖了SQL语句的基础到进阶应用,包括列行转换、SQL性能优化等多个关键知识点。 首先,列行转换是数据处理中常见的需求,Oracle提供了多种方法来实现这一操作。...

    Oracle 常用脚本.zip

    "Oracle 常用脚本.zip"这个压缩包很可能包含了这些实用的工具。 首先,让我们探讨一下Oracle数据库中的脚本基本概念。在Oracle中,SQL脚本(通常以.sql为扩展名)是一系列SQL语句的集合,它们可以一起执行以完成...

    oracle常用函数功能演示

    本篇文章将详细解析Oracle的一些常用函数,并通过实例演示它们的功能。 首先,Oracle的数值函数包括三角函数、幂和方根运算以及数值处理函数。例如,`SIN`, `COS`, `TAN`用于三角计算,`POWER`用于求幂,`SQRT`用于...

    oracle常用

    ### Oracle 常用知识点详解 #### 一、Oracle 基础操作 ...以上列举了 Oracle 数据库中的一些常用操作及函数,这些函数和操作对于日常开发和管理都非常实用。熟悉它们能够帮助你更高效地管理和查询数据。

    oracle10g 安装操作实用手册

    总之,这份Oracle 10g安装操作实用手册详细地介绍了Oracle 10g的安装过程,常用数据库操作命令,以及安全管理等方面的知识。手册不仅是数据库管理员安装和使用Oracle 10g时的宝贵指南,也适合那些希望了解Oracle 10g...

    oracle脚本-oracle常用表及数据

    "oracle脚本-oracle常用表及数据"这个资源显然是为了帮助初学者或开发者熟悉Oracle数据库操作和常见用例而准备的。以下是对这个主题的详细解读: 1. **Oracle数据库基础知识**:Oracle数据库是一个基于SQL的数据库...

    Oracle常用函数大全

    Oracle常用函数大全是一个非常重要的知识点,涵盖了许多实用的函数,例如日期函数、字符串函数、聚合函数等。这些函数可以帮助开发者更方便地进行数据库操作和数据分析。本文将详细介绍Oracle常用函数大全中的一些...

    oracle常用经典sql查询

    本资源“oracle常用经典sql查询”汇集了Oracle环境中的一些常见且实用的SQL查询语句,帮助用户提高数据库操作效率。以下是对这些经典查询语句的详细解读: 1. **基本查询操作** - `SELECT * FROM table_name;`:这...

    oracle 常用视图

    在文档《oracle常用视图.docx》中,可能会详细列出更多关于如何使用这些视图的实际示例和技巧,帮助读者深入理解并应用到实际工作中。通过不断学习和实践,我们可以充分利用Oracle的视图功能,提升数据库管理的效率...

    Oracle常用傻瓜问题1000问(txt)

    Oracle数据库是全球广泛使用的...总的来说,“Oracle常用傻瓜问题1000问”应该是一个全面且实用的指南,帮助读者解决在Oracle数据库使用过程中可能遇到的各种问题,无论你是新手还是有一定经验的DBA,都能从中受益。

    Oracle 11g 实用教程

    《Oracle 11g 实用教程》是一本深入讲解Oracle 11g数据库管理与应用的书籍,旨在为读者提供全面、系统的学习资源,帮助读者掌握Oracle 11g的核心功能与高级特性,提升数据库管理和开发技能。本书不仅适合初学者入门...

    oracle常用函数大全

    ### Oracle常用函数详解 Oracle数据库作为一款广泛使用的数据库系统,在数据管理、查询及处理方面提供了丰富的功能支持。本文将详细介绍部分Oracle数据库中的常用函数及其应用场景,帮助用户更好地理解和使用这些...

    Oracle 数据库实用操作技巧

    本文将基于"Oracle 数据库实用操作技巧"这一主题,详细阐述一些关键的Oracle数据库管理和优化技术,帮助资深DBA更好地理解和掌握Oracle数据库的日常运维。 1. **SQL优化**:在Oracle中,SQL查询性能直接影响数据库...

Global site tag (gtag.js) - Google Analytics