ITPub博客

oracle imp[@more@]imp 导入时忽略某些表的办法演示过程1、创建新用户create user sec identified by sec;grant dba to sec;conn sec/seccreate table t1 (x int);create table t2 (x int);create table t3 (x int);insert into t1 values(1);insert into t2 values(2);insert into t3 values(3);commit;exp sec/sec file=sec.dmpdrop table t1 purge;drop table t2 purge;drop table t3 purge;create table t1 (x int);imp sec/sec file=sec.dmp full=yt1表没有被导入数据如果选用参数 ignore=y 那么t1表的数据会被导入。ignore参数默认是

tom kyte login.sql[@more@]define _editor=viset serveroutput on size 1000000set trimspool onset long 5000set linesize 100set pagesize 9999col plan_plus_exp for a80col gloabl_name new_value gnameset termout offdefine gname=idlecol global_name new_value gnameselect lower(user) || '@' || substr (global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name,instr(global_name,'.') dot from global_name);set sqlprompt '&gname>'set termout on

oracle 查询表空间使用率的语句[@more@]SELECT UPPER(F.TABLESPACE_NAME) "SPACE_NAME", D.TOT_GROOTTE_MB "SPACE_SIZE(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "SPACE_USE(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/ D.TOT_GROOTTE_MB * 100, 2), '990.99') "USE_PER", F.TOTAL_BYTES "SPACE_FREE(M)", F.MAX_BYTES "MAX_BLOCK(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2)

emctl[@more@]Oracle10G的EM采用了web方式，并且分成了2个产品，database control和grid control。这里主要介绍如何创建单数据的dbcontrol。Grid control需要下载单独的光盘安装。在用DBCA建库的时候，可以选择是否启用dbcontrol，启用的话需要在数据库中建立一个sysman的schema，用于保存EM的一些数据，这个就是EM的资料库(repository)。使用命令行工具emca可以创建，修改，重建或者删除dbcontrol的配置。SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g E

oracle to_char 函数[@more@]SQL> select * from t 2 ; ID STARTTIME---------- ------------ 0 08-AUG-11 0 07-AUG-11 0 06-AUG-11 0 05-AUG-11SQL> select * from t where to_char(starttime,'yyyymmdd') < 20110807;ID STARTTIME---------- ------------0 06-AUG-110 05-AUG-11

oracle trace[@more@]关于Autotrace几个常用选项的说明:SET AUTOTRACE OFF ------------- 不生成AUTOTRACE 报告，这是缺省模式SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息SET AUTOTRACE ON ---------------包含执行计划和统计信息SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出 set autotrace on explanSP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色SP2-0611: 启用 STATISTICS 报告时出错1. @?/sqlplus/admin/plustrce.sql

oracle drop schema[@more@]1、创建一个删除的脚本set heading offset feedback offset pagesize 0spool c:1.sqlselect 'drop table '||table_name|| ';' from user_tables;select 'drop view '||view_name|| ';' from user_views;select 'drop sequence '||sequence_name|| ';' from user_sequences;select 'drop function '||object_name|| ';' from user_objects where object_type='FUNCTION';select 'drop procedure '||object_name|| ';' from user_objects where object_type='PROCEDURE';select 'drop package '||object_name||