ORACLE常用Script

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