`
feidi
  • 浏览: 120383 次
社区版块
存档分类
最新评论

ORACLE常用Script

    博客分类:
  • db
阅读更多

1、查看当前所有对象

SQL> select * from tab;

2、建一个和a表结构一样的空表

SQL> create table b as select * from a where 1=2;

SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

3、察看数据库的大小,和空间使用情况

SQL> col tablespace format a20
SQL>

select b.file_id  文件ID,
b.tablespace_name  表空间,
b.file_name     物理文件名,
b.bytes       总字节数,
(b.bytes-sum(nvl(a.bytes,0)))   已使用,
sum(nvl(a.bytes,0))        剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
/
dba_free_space --表空间剩余空间状况
dba_data_files --数据文件空间占用情况


4、查看现有回滚段及其状态

SQL> col segment format a30
SQL>

SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

5、查看数据文件放置的路径

SQL> col file_name format a50
SQL>

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

6、显示当前连接用户

SQL>

show user

7、把SQL*Plus当计算器

SQL> select 100*20 from dual;

8、连接字符串

SQL> select 列1||列2 from 表1;
SQL> select concat(列1,列2) from 表1;

9、查询当前日期

SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

10、用户间复制数据

SQL> copy from user1 to user2 create table2 using select * from table1;

11、视图中不能使用order by,但可用group by代替来达到排序目的

SQL> create view a as select b1,b2 from b group by b1,b2;

12、通过授权的方式来创建用户

SQL> grant connect,resource to test identified by test;

SQL> conn test/test

 

13、

相当有用的一个存储过程

--禁止或启用所有约束和触发器

 

CREATE OR REPLACE PROCEDURE P_ALTERCONS(

  AS_ALTER VARCHAR2)

  AS

  v_CursorID INTEGER;

  v_StrCon VARCHAR2(300);

  v_StrTri VARCHAR2(300);

  v_FkNum NUMBER :=0;

  v_TriNum NUMBER :=0;

  v_sqlcode NUMBER;

  v_sqlerrm VARCHAR2(600);

  CURSOR C_CON IS

  SELECT * FROM USER_CONSTRAINTS

  WHERE CONSTRAINT_TYPE='R';

  R_CON C_CON%ROWTYPE;

  CURSOR c_trigger IS

  SELECT TRIGGER_NAME,STATUS FROM user_triggers;

  notfound BOOLEAN;

  BEGIN

  DBMS_OUTPUT.PUT_LINE('BEGIN TIME: '||to_char(sysdate));

  -- 判断输入参数是否为DISABLE或者是ENABLE,如果是的话,就继续处理,否则退出过程,给出提示

  IF (UPPER(AS_ALTER) = 'DISABLE' OR UPPER(AS_ALTER) = 'ENABLE') THEN

  OPEN C_CON;

  -- 当前用户下外键的处理 ENABLE或者 DISABLE

  v_CursorID := DBMS_SQL.OPEN_CURSOR;

  LOOP

  FETCH C_CON into R_CON;

  notfound:=C_CON%NOTFOUND;

  EXIT WHEN notfound;

  begin

  IF (UPPER(AS_ALTER)='DISABLE' AND R_CON.STATUS='ENABLED' OR

  UPPER(AS_ALTER)='ENABLE' AND R_CON.STATUS='DISABLED') THEN

  v_StrCon := 'ALTER TABLE '||R_CON.owner||'.'||R_CON.table_name||' '||

  UPPER(as_alter) || ' CONSTRAINT '||R_CON.constraint_name;

  DBMS_SQL.PARSE( v_CursorID, v_StrCon, DBMS_SQL.V7);

  v_FkNum :=v_FkNum+1;

  END IF;

  EXCEPTION

  WHEN OTHERS THEN

  v_sqlcode := SQLCODE;

  v_sqlerrm := SUBSTR(SQLERRM,1,600);

  DBMS_OUTPUT.PUT_LINE('ERROR: '||' '||V_SQLERRM);

  -- 找出错误原因

  IF (v_sqlcode = -2298) THEN

  p_con_err(R_CON.CONSTRAINT_NAME);

  END IF;

  END;

  END LOOP;

  CLOSE C_CON;

  DBMS_OUTPUT.PUT_LINE('====== Foreign Keys were '||as_alter||', total '||to_char(v_FkNum)||' =====');

  -- 当前用户下触发器的处理 ENABLE或者 DISABLE

  FOR T_TRIGGER IN C_TRIGGER LOOP

  BEGIN

  IF (UPPER(AS_ALTER)='DISABLE' AND T_TRIGGER.STATUS='ENABLED' OR

  UPPER(AS_ALTER)='ENABLE' AND T_TRIGGER.STATUS='DISABLED') THEN

  v_StrTri := 'ALTER TRIGGER '||T_TRIGGER.TRIGGER_name ||' '||UPPER(as_alter);

  DBMS_SQL.PARSE( v_CursorID, v_StrTri, DBMS_SQL.V7);

  v_TriNum :=v_TriNum+1;

  END IF;

  EXCEPTION

  WHEN OTHERS THEN

  v_sqlcode := SQLCODE;

  v_sqlerrm := SUBSTR(SQLERRM,1,600);

  DBMS_OUTPUT.PUT_LINE('ERROR: '||V_SQLCODE||' '||V_SQLERRM);

  END;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('====== Triggers were '||as_alter||', total '||to_char(v_TriNum)||' =====');

  DBMS_SQL.CLOSE_CURSOR(v_CursorID);

  ELSE -- 输入参数不正确

  DBMS_OUTPUT.PUT_LINE('ERROR:输入参数不正确,参数为ENABLE或者DISABLE!');

  END IF;

  DBMS_OUTPUT.PUT_LINE('END TIME: '||to_char(sysdate));

  END;

  /

--显示oracle的错误

CREATE OR REPLACE PROCEDURE P_CON_ERR(as_constraint_name varchar2)
  AS
  v_CursorID INTEGER;
  V_CONSNAME VARCHAR2(30);
  V_TABLE_NAME VARCHAR2(30);
  V_RTABLE_NAME VARCHAR2(30);
  V_COLUMN VARCHAR2(100);
  v_Str VARCHAR2(600);
  TYPE t_col_value IS TABLE OF VARCHAR2(30)
  INDEX BY BINARY_INTEGER;
  v_Col_Val t_col_value;
  v_RET NUMBER;
  v_NUM NUMBER;
  i BINARY_INTEGER;
  V_WHERE VARCHAR2(600);
  V_CAUSE VARCHAR2(200);
  CURSOR C_COL_NAME(V_CON_NAME VARCHAR2) IS
  SELECT * FROM USER_CONS_COLUMNS
  WHERE CONSTRAINT_NAME=V_CON_NAME;
  BEGIN
  V_CONSNAME := TRIM(UPPER(as_constraint_name)); -- 约束名称
  v_num :=0;
  FOR T_COL_NAME IN C_COL_NAME(V_CONSNAME) LOOP
  IF (V_NUM = 0) THEN
  V_COLUMN :=T_COL_NAME.COLUMN_NAME;
  V_WHERE :='A.'||T_COL_NAME.COLUMN_NAME||'= B.'||T_COL_NAME.COLUMN_NAME;
  ELSE
  V_COLUMN :=V_COLUMN||','||T_COL_NAME.COLUMN_NAME;
  V_WHERE :=V_WHERE||' AND '||'A.'||T_COL_NAME.COLUMN_NAME||'= B.'||T_COL_NAME.COLUMN_NAME;
  END IF;
  V_NUM :=V_NUM+1;
  END LOOP;
  FOR I IN 1..V_NUM LOOP
  V_COL_VAL(I) :='';
  END LOOP;
  SELECT DISTINCT TABLE_NAME INTO V_TABLE_NAME FROM USER_CONS_COLUMNS
  WHERE CONSTRAINT_NAME = V_CONSNAME;
  -- 找到被引用的表名称
  SELECT TABLE_NAME INTO V_RTABLE_NAME FROM USER_CONSTRAINTS
  WHERE CONSTRAINT_NAME IN (SELECT R_CONSTRAINT_NAME FROM USER_CONSTRAINTS
  WHERE CONSTRAINT_NAME = V_CONSNAME);
  DBMS_OUTPUT.PUT_LINE('外键错误情况');
  DBMS_OUTPUT.PUT_LINE('============');
  DBMS_OUTPUT.PUT_LINE(v_column);
  DBMS_OUTPUT.PUT_LINE(RPAD('-',LENGTH(V_COLUMN),'-'));
  v_CursorID := DBMS_SQL.OPEN_CURSOR;
  V_STR := 'SELECT DISTINCT '||V_COLUMN||' FROM '||V_TABLE_NAME||' A WHERE NOT EXISTS ( SELECT   NULL FROM '
  ||V_RTABLE_NAME||' B WHERE '||V_WHERE||')';
  DBMS_SQL.PARSE( v_CursorID, v_Str, DBMS_SQL.V7);
  FOR I IN 1..V_NUM LOOP
  DBMS_SQL.DEFINE_COLUMN(v_CursorID,I,v_COL_VAL(I),30);
  END LOOP;
  v_ret := DBMS_SQL.EXECUTE(v_CursorID);
  WHILE DBMS_SQL.FETCH_ROWS(v_CursorID) > 0 LOOP
  V_CAUSE :='';
  FOR I IN 1..V_NUM LOOP
  DBMS_SQL.COLUMN_VALUE(v_CursorID,I,V_COL_VAL(I));
  IF (I = 1) THEN
  V_CAUSE :=V_COL_VAL(I);
  ELSE
  V_CAUSE :=V_CAUSE||', '||V_COL_VAL(I);
  END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(V_CAUSE);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);
  END;
  /

 

用法:

exec p_altercons(‘DISABLE');

exec p_altercons('ENABLE');

分享到:
评论

相关推荐

    Oracle常用script

    ### Oracle常用Script详解 #### 一、查看当前所有对象 **Script内容:** ```sql SQL> select * from tab; ``` **知识点说明:** 在Oracle数据库中,`tab`是一个特殊的视图,用于列出当前用户所拥有的所有对象。...

    Oracle dba_script

    Oracle DBA 常用 Script,我看了你写的如何快速成为一名优秀的oracle DBA>那篇文章,很有感触。我也一直像你说的那样,努力学习oracle。上大学开始就对oracle有很浓厚的兴趣,但是,工作后我做了两年的开发, 更想...

    Oracle数据库学习指南

    12. Oracle常用Script 13. Oracle常用数据字典 14. ORACLE回滚段管理(上) 15. ORACLE回滚段管理(下) 16. Oracle基于Client-Server的性能调整 17. Oracle数据库的安全策略 18. ORACLE数据库简介 19. ...

    最全的oracle常用命令大全.txt

    ORACLE常用命令 一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup ...

    oracle 常用函数 sqlplus基本命令

    本文将深入探讨"Oracle常用函数"以及"SQL*Plus基本命令"这两个关键领域,旨在帮助你提升在数据库操作中的效率。 一、Oracle常用函数 1. 长度函数`LENGTH` `LENGTH`函数用于计算字符串的字符数,不区分字符类型(如...

    oracle 常用命令脚本大全

    本文将详细介绍Oracle的一些常用命令脚本,帮助你更好地管理和操作Oracle数据库。 1. 数据库连接与断开 - `sqlplus username/password@instance`:连接到Oracle数据库,如`sqlplus sys/manager@orcl`。 - `\q` 或...

    oracle常用的sql语句

    ### Oracle常用的SQL语句知识点详解 #### 一、SQL概述 SQL(Structured Query Language),即结构化查询语言,是用于管理关系型数据库的标准语言。Oracle作为一款流行的关系型数据库管理系统,其支持的SQL语句与...

    oracle脚本 常用的数据库sql及pl\sql语言

    1. 查询语句:SELECT是SQL中最常用的命令,用于从数据库中检索数据。例如,`SELECT * FROM 表名` 可以获取表中的所有数据。 2. 插入语句:INSERT用于向表中添加新记录,如 `INSERT INTO 表名 (列1, 列2) VALUES (值1...

    oracle 常用脚本

    "Oracle常用脚本"集合通常包含一系列用于数据库创建、数据备份、性能优化、安全设置等操作的SQL和PL/SQL脚本。 1. 数据库创建与配置: - `create_database.sql`: 这个脚本可能包含了创建新Oracle数据库的完整流程...

    oracle常用脚本

    Oracle常用脚本主要涵盖了一系列用于管理和操作Oracle数据库的命令行工具和SQL脚本。这些脚本对于数据库管理员(DBA)以及开发人员来说是至关重要的,因为它们能提高工作效率,简化复杂的数据库任务。在本文中,我们...

    Oracle DBA 常用ScriptsCHM+.tar)

    "Oracle DBA 常用Scripts CHM+.tar" 提供的资源就是这样的一个集合,包含了DBA在管理Oracle数据库时可能会需要用到的各种实用脚本。这个压缩包包含了一个CHM文件(DBA_SCRIPT.CHM)和一个tar文件(sample20.tar),...

    oracle SQLPlus常用命令

    以下是关于"Oracle SQLPlus常用命令"的一些详细知识点: 1. **连接数据库**:使用`CONNECT`命令登录到Oracle数据库。例如,`CONNECT username/password@database`,其中`username`是你的用户名,`password`是密码,...

    ORACLE获取DDL的几种常用的方法[归纳].pdf

    这篇文章将详细介绍三种常用的Oracle获取DDL的方法。 首先,我们可以通过第三方工具来实现,例如Toad和PL/SQL Developer。Toad是一款功能强大的Oracle数据库管理工具,其DBA菜单中的"Generate Schema Script"选项...

    oracle常用命令

    ### Oracle常用命令详解 #### 一、概述 Oracle是一款广泛使用的数据库管理系统,它提供了丰富的功能来管理和查询数据。本文档将详细介绍Oracle中的常用命令,包括建表、创建工作空间、格式化、系统命令以及函数的...

    oracle sqlplus 常用命令大全.txt

    ### Oracle SQLPlus 常用命令详解 #### 1. 显示系统信息与用户信息 - **`show all`**:此命令用于显示系统级别的设置信息,包括但不限于环境变量、当前会话设置等。 - **`show user`**:显示当前登录数据库的用户名...

    oracle自动安装脚本

    Oracle自动安装脚本是数据库管理员和系统管理员在部署Oracle数据库时常用的一种工具,它通过自动化流程简化了安装过程,减少了人为错误的可能性。Oracle数据库是一款功能强大的关系型数据库管理系统,广泛应用于企业...

    ORACLE-DBA常用脚本.ppt

    以下是一些关于Oracle DBA常用脚本的知识点: 1. 完整性约束检查: - 完整性约束是数据库设计的重要部分,包括主键、外键等,确保数据的一致性和准确性。 - 外键约束对性能可能有影响,因为它们会导致行级锁定,...

    Oracle的一些小经验

    - `@script.sql` - 使用`@`符号可以执行存储在磁盘上的SQL脚本文件,这对于批量执行SQL语句非常有用。 以上就是关于Oracle数据库的一些实用小经验和技术要点,希望对大家在日常使用Oracle数据库时有所帮助。通过...

    Oracle 10g 学习笔记

    │ oracle里常用命令 - Oracle - 51CTO技术论坛_中国领先的IT技术社区.mht │ Sequence相关操作命令 - lvhuiqing的专栏 - CSDN博客.mht │ Sequence相关操作命令 - lvhuiqing的专栏 - CSDN博客.mht.lnk │ 为什么...

Global site tag (gtag.js) - Google Analytics