`

Oracle常用操作

 
阅读更多

 

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

6.1 增加数据文件

Sql代码  收藏代码
  1. ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;   

6.2 手动增加数据文件尺寸

Sql代码  收藏代码
  1. ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME02.dbf' RESIZE 4000M;   

    6.3 设定数据文件自动扩展

Sql代码  收藏代码
  1. ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME02.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;   

    6.4 设定后查看表空间信息

Sql代码  收藏代码
  1. 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 查询表空间大小

Sql代码  收藏代码
  1. SELECTDISTINCT a.tablespace_name 表空间名称,   
  2.                 trunc((free_space / total_space) *100) || '%' 可用率,  
  3.                 to_char(free_space /1024/1024, '9999999990.99') || 'M' 剩余空间,  
  4.                 to_char(total_space /1024/1024, '9999999990.99') || 'M' 总空间,  
  5.                 to_char((total_space - free_space) /1024/1024,  
  6.                          '9999999990.99') || 'M' 已使用空间  
  7.   FROM (SELECT tablespace_name, SUM(bytes) free_space  
  8.            FROM dba_free_space  
  9.           GROUPBY tablespace_name) a,  
  10.        (SELECT tablespace_name, SUM(bytes) total_space  
  11.            FROM dba_data_files  
  12.           GROUPBY tablespace_name) b  
  13.  WHERE a.tablespace_name = b.tablespace_name;  

 

 

 

  • 常用数据字典的查询使用方法。
     1、用户
          查看当前用户的缺省表空间
    Sql代码  收藏代码
    1. SQL>select username,default_tablespace from user_users;  
     查看当前用户的角色
    Sql代码  收藏代码
    1. SQL>select * from user_role_privs;  
           查看当前用户的系统权限和表级权限
    Sql代码  收藏代码
    1. SQL>select * from user_sys_privs;  
    2. SQL>select * from user_tab_privs;  
     创建数据库用户并赋权
    Sql代码  收藏代码
    1. drop user etl72_dev cascade;  
    2. DROP TABLESPACE ETL72_DEV INCLUDING CONTENTS AND DATAFILES;   
    3.   
    4. CREATE TABLESPACE ETL72_DEV DATAFILE 'D:\oracle\product\10.2.0\oradata\osstest\DATA02.dbf' SIZE 1200M UNIFORM SIZE 128k;   
    5. create user etl72_dev  
    6.   identified by smart  
    7.   default tablespace ETL72_DEV  
    8.   temporary tablespace temp  
    9.   profile DEFAULT;  
    10. -- Grant/Revoke role privileges   
    11. grant connect to etl72_dev;  
    12. grant exp_full_database to etl72_dev;  
    13. grant imp_full_database to etl72_dev;  
    14. grant resource to etl72_dev;  
    15. -- Grant/Revoke system privileges   
    16. grant create procedure to etl72_dev;  
    17. grant create trigger to etl72_dev;  
    18. grant execute any procedure to etl72_dev;  
    19. grant grant any privilege to etl72_dev;  
    20. grant restricted session to etl72_dev;  
    21. grant select any table to etl72_dev;  
    22. grant unlimited tablespace to etl72_dev;  
    23. grant create any view to etl72_dev;  
    24.   
    25. grant create session to etl72_dev;  
    26. GRANT DELETE ANY TABLE TO etl72_dev;  
    27. GRANT DROP ANY TABLE TO etl72_dev;  
    28. GRANT INSERT ANY TABLE TO etl72_dev;  
    29. GRANT SELECT ANY TABLE TO etl72_dev;  
    30. GRANT UNLIMITED TABLESPACE TO etl72_dev;  
    31. GRANT UPDATE ANY TABLE TO etl72_dev;  
    32.   
    33.    
     
         

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

Sql代码  收藏代码
  1. SQL>select * from user_tables;  

        查看名称包含log字符的表

Sql代码  收藏代码
  1. SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0;  

       查看某表的创建时间

Sql代码  收藏代码
  1. SQL>select object_name,created from user_objects where object_name=upper('&table_name');  

查看某表的大小

Sql代码  收藏代码
  1. SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');  

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

Sql代码  收藏代码
  1. SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;  

 
            批量删除:

Sql代码  收藏代码
  1. select 'drop table '||table_name||';' as sqlscript from user_tables;  

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

Sql代码  收藏代码
  1. SQL>select index_name,index_type,table_name from user_indexes order by table_name;  

 
            查看索引被索引的字段

Sql代码  收藏代码
  1. SQL>select * from user_ind_columns where index_name=upper('&index_name');  

 
            查看索引的大小
     

Sql代码  收藏代码
  1. SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');  

 

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

Sql代码  收藏代码
  1. SQL>select * from user_sequences;  

 
       5、视图
      查看视图的名称

Sql代码  收藏代码
  1. SQL>select view_name from user_views;  

 
            查看创建视图的select语句

Sql代码  收藏代码
  1. SQL>set view_name,text_length from user_views;  
  2. SQL>set long 2000;                说明:可以根据视图的text_length值设定set long 的大小  
  3. SQL>select text from user_views where view_name=upper('&view_name');  

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

Sql代码  收藏代码
  1. SQL>select * from user_synonyms;  

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

Sql代码  收藏代码
  1. SQL>select constraint_name, constraint_type,search_condition, r_constraint_name  
  2.            from user_constraints where table_name = upper('&table_name');  
  3.   
  4. SQL>select c.constraint_name,c.constraint_type,cc.column_name  
  5.           from user_constraints c,user_cons_columns cc  
  6.           where c.owner = upper('&table_owner'and c.table_name = upper('&table_name')  
  7.           and c.owner = cc.owner and c.constraint_name = cc.constraint_name  
  8.           order by cc.position;  

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

Sql代码  收藏代码
  1. select t.table_name from user_constraints t  
  2. where t.constraint_type='R' and t.r_constraint_name  
  3. in(  
  4. select s.constraint_name from user_constraints s  
  5. where s.table_name='主表名' and s.constraint_type='P')   

  
     查看外键所关联的数据表

Sql代码  收藏代码
  1. SELECT * FROM USER_CONSTRAINTS C WHERE C.CONSTRAINT_TYPE = 'R' AND C.CONSTRAINT_NAME LIKE '%ETL_TASK'  

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

Sql代码  收藏代码
  1. SQL>select object_name,status from user_objects where object_type='FUNCTION';  
  2. SQL>select object_name,status from user_objects where object_type='PROCEDURE';  

 
            查看函数和过程的源代码

Sql代码  收藏代码
  1. SQL>select text from all_source where owner=user and name=upper('&plsql_name');  

 
      9、DB Link:

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

DB Link 使用示例

Sql代码  收藏代码
  1. INSERT INTO T_DEPARTMENT_DEFINE            
  2.    (DEPARTMENT_CODE, INTERNAL_DEPARTMENT_CODE,            
  3.     DEPARTMENT_CHINESE_NAME, DEPARTMENT_ABBR_NAME, DEPARTMENT_LEVEL,            
  4.     FOUND_DATE, CHINESE_ADDRESS, POSTCODE, TELEPHONE,            
  5.     UPPER_DEPARTMENT_CODE, LINK_MAN_CODE, TELE_AREA_CODE,            
  6.     LEVEL_DEP_CODE, FCD, FCU, LCD, LCU)            
  7.   (SELECT DEPARTMENT_CODE, INTERNAL_DEPARTMENT_CODE, DEPARTMENT_CHINESE_NAME,            
  8.   DEPARTMENT_ABBR_NAME, DEPARTMENT_LEVEL, FOUND_DATE,            
  9.   CHINESE_ADDRESS, POSTCODE, TELEPHONE, UPPER_DEPARTMENT_CODE,            
  10.   LINK_MAN_CODE, TELE_AREA_CODE, LEVEL_DEP_CODE, FCD, FCU, LCD, LCU            
  11.      FROM T_DEPARTMENT_DEFINE@elinkDB_copy);            
  12. commit;            
  13. 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;

分享到:
评论

相关推荐

    oracle 常用命令大全

    oracle 常用命令大全 oracle dba 常用命令 1 运行 SQLPLUS 工具 sqlplus 2 以 OS 的默认身份连接 / as sysdba 3 显示当前用户名 show user 4 直接进入 SQLPLUS 命令提示符 sqlplus /nolog 5 在命令提示符以 OS 身份...

    oracle常用命令文档

    ### Oracle 常用命令与操作指南 #### 一、Oracle 安装与卸载注意事项 **1.1 Oracle 的安装** - **安装步骤:** 对于 Oracle 的安装,网络上有很多详细的指导教程。在安装过程中,需要注意的是,管理口令部分确保...

    Linux下Oracle常用操作

    ### Linux下Oracle常用操作知识点详解 #### 一、概述 ...以上就是关于Linux环境下Oracle常用操作命令的知识点详解。通过这些命令,我们可以更好地管理和优化Oracle数据库,在日常工作中起到关键的作用。

    ORACLE常用命令

    ### ORACLE常用命令详解 #### 一、ORACLE的启动和关闭 在ORACLE数据库管理中,正确地启动和关闭数据库是非常重要的操作之一。这些操作不仅涉及到系统的稳定性,还关系到数据的安全性。 ##### 1、在单机环境下 ...

    oracle常用命令

    以下将详细解析基于提供的文件信息中的Oracle常用命令,涵盖日志管理和表空间管理两大核心领域,以帮助数据库管理员更好地理解和运用这些关键指令。 ### 日志管理 #### 强制日志切换 `ALTERSYSTEMSWITCHLOGFILE;` ...

    natstat,oracle常用命令常用命令

    natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令

    Oracle常用命令大全

    ### Oracle常用命令详解 #### 一、Oracle的启动与关闭 **1. 在单机环境下的启动与关闭** - **启动Oracle系统** - **切换用户:** 首先需要切换到Oracle用户环境。 ```bash su - oracle ``` - **使用`svrmgrl`...

    linux和Oracle常用命令linux环境配置ORACLE常用命令

    linux,linux和Oracle常用命令linux环境配置ORACLE常用命令

    oracle 常用命令 TXT 最全的

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

    Oracle常用命令速查手册中文PDF版最新版本

    《Oracle常用命令速查手册》将介绍一些常用的Oracle命令和其用法,比如数据导入导出命令、存储过程和触发器的管理以及性能监控等方面的常用命令,通过简单明了的示例,展示了每个命令的用法和效果,以帮助读者更好地...

    oracle常用命令 收集

    oracle常用命令收集 oracle常用命令

    ORACLE常用命令大全.

    以下是一些关于Oracle常用命令的详细说明,分为日志管理和表空间管理两大部分。 ### 日志管理 #### 1. 强制日志切换 ```sql alter system switch logfile; ``` 这个命令用于在当前重做日志文件填满前强制进行日志...

    Oracle常用命令大汇总

    第一章:日志管理 1.forcing log switches sql&gt; alter system switch logfile; 2.forcing checkpoints sql&gt; alter system checkpoint; 3.adding online redo log groups sql&gt; alter database add log...

    ORACLE常用命令举例

    ORACLE常用命令举例 ORACLE常用命令举例 ORACLE常用命令举例 ORACLE常用命令举例 ORACLE常用命令举例

    Oracle常用命令大汇总.doc

    Oracle常用命令大汇总 Oracle是世界上最流行的关系数据库管理系统之一,由Oracle Corporation开发和维护。作为一个功能强大的数据库管理系统,Oracle提供了许多实用的命令来帮助数据库管理员和开发者更好地管理和...

    oracle常用命令操作集合

    1. **Oracle常用操作命令**: - `sqlplus`:Oracle的命令行工具,用于执行SQL语句和PL/SQL块。 - `connect`:连接到Oracle数据库,语法:`connect username/password@database`。 - `desc`:描述表结构,如`desc ...

    Oracle常用命令

    Oracle创建删除用户、角色、表空间、导入导出数据库命令行方式总结.pdf

Global site tag (gtag.js) - Google Analytics