Oracle常用命令

1 Oracle 常用命令常用命令 etc init d oracle restart etc init d oracle shutdown 1 连接连接 racle sqlplus username password sid as sysdba SQL startup force nomount mount open force restrict SQL shutdown normal transactional immediate abort 2 启动控制台启动控制台 mysqld nt install 压缩的安装成服务 mysqld nt remove 卸载服务 net start stop mysql mysql v 或者 mysql u root p set oracle sid emctl start dbconsole http hnjk oh 1158 em isqlpuls start http hnjk oh 5560 isqlplus lsnrctl start stop 启动 tns dbca 启动配置管理 dbua 升级管理 netca 网络配置管理 3 创建表空间和用户创建表空间和用户 SQL create tablespace wy863college datafile F oracle product 10 2 0 oradata hnjkorg wy863college dbf size 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED SQL drop tablespace tablespacename including contents and datafiles SQL create user wy863college identified by college default tablespace wy863college SQL grant dba to wy863college 4 更改字符集更改字符集 登录到 oracle sqlplus sys as sysdba 是 sys 的密码 查看字符集 SQL select userenv language from dual 停掉服务 2 SQL shutdown immediate 以限制模式打开服务 SQL startup restrict 更改字符集 SQL alter database character set internal use ZHS16GBK 重启 oracle SQL shutdown immediate SQL startup 5 修改归档 修改归档 SQL shutdown immediate SQL startup mount SQL alter database archivelog SQL alter database open SQL archive log start 6 修改进程数 修改进程数 sqlplus nolog SQL conn as sysdba SQL show parameter pfile SQL show parameter process SQL alter system set processes 500 scope spfile SQL startup force 7 创目录创目录 sqlplus username password sid as sysdba I 创建 SQL create or replace directory DUMP DIR as E test 基于 Windows 系统 SQL create or replace directory DUMP DIR as home oracle bfiletest 基于 Linux 系统 SQL commit II 查看 SQL select from dba directories III 删除 SQL drop directory DUMP DIR 8 导数据导数据 DOC 模式下 I expdp sys hnjk as sysdba schemas developer jforum courseforum 3 virtualcourtroom directory DUMP DIR dumpfile db dmp exp user password file exp dmp compress Y grants Y indexes Y tables table1 table2 exp 才可以用 compress Y II impdp sys hnjk as sysdba directory DUMP DIR dumpfile dump file III impdp system SCHEMAS sh REMAP SCHEMA sh sh2 DUMPFILE datadir1 schema1 U dmp datadir2 schema2 U dmp EXCLUDE constraint ref constraint index materialized view TABLE EXISTS ACTION replace logfile datadir1 impschema log 9 查看查看 FLASH RECOVERY AREA 空间使用情况空间使用情况 1 查看数据库 REDOLOG 情况 SQL select from v log 2 手工切换日志 SQL alter system switch logfile 3 查看 FLASH RECOVERY AREA 空间使用情况 root hrmsdb oracle du a k flash recovery area 2353148 flash recovery area 说明 FLASH RECOVERY AREA 空间使用了 2 35GB 4 查看 FLASH RECOVERY AREA 空间中各部分使用情况 SQL select from v recovery file dest SQL select from v flash recovery area usage 5 如果我们想查询跟索引有关的数据字典时 可以用下面这条 SQL 语句 SQL select from dictionary where instr comments index 0 10 使用排序哈希簇来加提高询速度使用排序哈希簇来加提高询速度 create cluster credit cluster card no varchar2 16 transdate date sort hashkeys 10000 hash is ora hash card no size 256 create table credit orders card no varchar2 16 transdate date amount number cluster credit cluster card no transdate 4 alter session set nls date format YYYYMMDDHH24MISS insert into credit orders card no transdate amount values 4111111111111111 20050131000123 57 99 insert into credit orders card no transdate amount values 4111111111111111 20050130071216 16 59 insert into credit orders card no transdate amount values 4111111111111111 20050131111111 39 00 insert into credit orders card no transdate amount values 4111111111111111 20050130081001 25 16 12 常用常用 sql 1 复制整张表可以用 INSERT INTO NewTableName SELECT FROM OldTableName 2 查重复记录 SELECT a1 CONUT a1 FROM TableName GROUP BY a1 HAVING COUNT 5 3 删除重复记录 DELETE FROM TableName tt WHERE tt guid IN SELECT MAX t guid FROM TableName t GROUP BY t STUDENTGUID t COURSEGUID HAVING COUNT t guid 1 SELECT distinct km a xm a xb c fs FROM xs xxa a SELECT xsid avg fs Avg fs FROM xs fsa group by xsid xs fsa c WHERE a xsid c xsid AND Avg fs 1 order by cc COURSECHSNAME 创建一个过程 指定人的工资加 100 create or replace procedure Dp add gz v grid varchar2 as n rec number begin 判断输入的个人 id 是否在表 gr gz 中存在 select count 1 into n rec from dual where exists select 1 from gr gz where grid v grid if n rec 0 then update gr gz set gz gz 100 where grid v grid end if 6 exception when others then null end 例子 create or replace procedure v2 a in out varchar2 as begin a a end 调用 declare v varchar2 100 ab begin insert into temp values v v2 v insert into temp values v exception when others then null end 13 Oracle server 包括包括 Instance 和和 Database A 例程包括 内存数据 SGA 和后台进程集合 SGA 包括数据高速缓存 重做日志缓冲区 共享池 后台进程包括 SMON PMON DBONCKPT LGWR ARCH B 数据库包括 OS 文件集合 数据文件 控制文件 重做日志 14 网络信使网络信使 net send 计算机名 IP 广播 传送内容 注意不能跨网段 net stop messenger 停止信使服务 也可以在面板 服务修改 net start messenger 开始信使服务 15 Oracle 安装信息安装信息 创建数据库脚本 F oracle product 10 2 0 admin hnjkora scripts Database Control URL 为 http WILLAIM 1158 em Enterprise Manager Database Control URL hnjkora 7 http WILLAIM 1158 em 数据库配置文件已经安装到 F oracle product 10 2 0 同时其他选定的安装组件也已经安装到 F oracle product 10 2 0 db 1 iSQL Plus URL 为 http WILLAIM 5560 isqlplus iSQL Plus DBA URL 为 http WILLAIM 5560 isqlplus dba 已通过 Configuration Assistant Oracle Net Configuration Assistant 生成输出 Command F oracle product 10 2 0 db 1 jdk jre bin java exe Dsun java2d noddraw true Duser dir F oracle product 10 2 0 db 1 bin classpath F oracle product 10 2 0 db 1 jdk jre lib rt jar F oracle product 10 2 0 db 1 jlib ldapjclnt10 jar F oracle product 10 2 0 db 1 jlib ewt3 jar F ora cle product 10 2 0 db 1 jlib ewtcompat 3 3 15 jar F oracle product 10 2 0 db 1 network jlib NetCA jar F oracle pr oduct 10 2 0 db 1 network jlib netcam jar F oracle product 10 2 0 db 1 jlib netcfg jar F oracle product 10 2 0 db 1 jlib help4 jar F oracle product 1 0 2 0 db 1 jlib jewt4 jar F oracle product 10 2 0 db 1 jlib oracle ice jar F oracle product 10 2 0 db 1 jlib share jar F oracle product 10 2 0 db 1 j lib swingall 1 1 1 jar F oracle product 10 2 0 db 1 jdk jre lib i18n jar F oracle produ ct 10 2 0 db 1 jlib srvmhas jar F oracle product 10 2 0 db 1 jlib srvm jar F oracle product 10 2 0 db 1 oui jlib OraInstaller jar F oracle product 10 2 0 db 1 lib xmlparserv2 jar F oracle product 10 2 0 db 1 network tools ca NetCA orahome F oracle product 10 2 0 db 1 orahnam OraDb10g home1 instype typical inscomp client oraclenet javavm server ano insprtcl tcp nmp cfg local authadp NO VALUE nodeinfo NO VALUE responseFile F oracle product 10 2 0 db 1 network install netca typ rsp 正在对命令行参数进行语法分析 参数 orahome F oracle product 10 2 0 db 1 参数 orahnam OraDb10g home1 参数 instype typical 参数 inscomp client oraclenet javavm server ano 参数 insprtcl tcp nmp 参数 cfg local 8 参数 authadp NO VALUE 参数 nodeinfo NO VALUE 参数 responsefile F oracle product 10 2 0 db 1 netwo