oracle技巧.docx
《oracle技巧.docx》由会员分享，可在线阅读，更多相关《oracle技巧.docx（49页珍藏版）》请在冰豆网上搜索。
oracle技巧
----sqlplus命令中启动监听器----
sql>lsnrctlstart--本机服务器监听器是否启动不影响，sqlplus不用"@"符的话,是不需要用监听器
sql>lsnrctlstop
e:
\>lsnrctlstatus--查看监听器的状态
----cmd窗口中启动oracle服务-----
d:
\>netstartoracleserviceorcl--oracleservice后跟oracle服务名称sid
d:
\>netstoporacleserviceorcl
----sqlplus连接数据库注意事项----
c:
\>sqlplus/nolog--不对数据库操作链接，不用参数的话链接到默认数据库
sql>connsystem/system@orclassysdba--指定需要的实例，
sql>selectinstance_namefromv$instance;
sql>host--退出sqlPlus界面
---oracle安装完成后的初始命令
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
----如何分辨某个用户从哪台机器登录oracle的？
---
sql>selectmachine,terminalfromv$session;
----用什么语句查询字段
sql>desctable_name--查询表的结构
sql>select*fromall_tableswheretable_collike'%'
----查看最大会话数----
sql>showparameterprocesses--processes为设置的用户数
sql>selectsessions_highwaterfromv$license--记录曾经的最大会话数
----如何以archivelog方式运行oracle
init.ora
log.archive_start=true
restartdatabase
----怎么获取哪些用户在使用数据库
sql>selectusernamefromv$session
-----怎么样查看数据库的sid---
sql>selectnamefromv$database;--也可以查init.ora文件
----如何在oracle服务器通过sqlplus查看本机ip地址
sql>selectsys_context（'userenv','ip_address'）fromdual;
----如何查询每个用户的权限
sql>select*fromdba_sys_privs;
---如何将表移动表空间
sql>altertabletable_namemovetablespace_name;
----查看数据库字符状况
sql>select*fromnls_database_parameters
sql>select*fromv$nls_parameters
-----查询表空间信息----
sql>select*fromdba_data_files;
----如何使用select语句查询时自动生成序号
sql>selectrownum,字段fromtable;
----如何知道数据库中某个表所在的表空间tablespace,注意大写字母
sql>selectTABLE_NAME,TABLESPACE_NAMEFROMUSER_TABLESWHERETABLE_NAME=''
-----怎么快速做一个和原表一样的备份表
sql>createtablenew_table_nameas（select*fromold_table_name）;
sql>createtablenew_table_nametablespacetablespace_nameas（select*fromold_table_name）;--指定表空间，不指定表空间
按登录用户所在表空间创建。
-----如何查看数据库中有多少个表空间tablespace
sql>select*fromdba_tablespaces;
sql>selecttablespace_name,block_size,statusfromdba_tablespaces;
sql>查看表空间总大小，及其已使用大小
selecta.tablespace_name,a.bytes/1024/1024"SumMB",（a.bytes-b.bytes）/1024/1024"usedMB",b.bytes/1024/1024"freeMB",
round（（（a.bytes-b.bytes）/a.bytes）*100,2）"percent_used"
from
（selecttablespace_name,sum（bytes）bytesfromdba_data_filesgroupbytablespace_name）a,
（selecttablespace_name,sum（bytes）bytes,max（bytes）largestfromdba_free_spacegroupbytablespace_name）b
wherea.tablespace_name=b.tablespace_name
orderby（（a.bytes-b.bytes）/a.bytes）desc;
----如何修改oracle数据库中的用户连接数
修改initSID.ora，将process加大，重新启动数据库
-----怎样查看哪些用户拥有sysdba,sysoper权限？
sql>connsys/change_on_install-其他用户无权限查询
sql>select*fromv_$pwfile_users;
----如何单独备份一个或多个表，用户
c:
\>expuser/passwordtables=tablename
c:
\>expuser/passwordowner=userfile=导出文件
----如何查看数据文件存放的路径
sql>colfile_nameformata50
sql>selecttablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_filesorderbyfile_id;
----oracle常用系统文件有哪些
通过以下视图文件显示文件信息：
v$database,v$datafile,v$logfile,v$controlfile,v$parameter;
----内连接sqlinnerjoin
sql>selecta.*frombsempmsa,bsdptmsbwherea.dpt_no=b.dpt_no
----如何外连接
---如何执行脚本sql文件？
sql>@$path/filename.sql;
----如何快速清空一个大表
sql>truncatetabletable_name;
---查询有多少个数据库实例
sql>select*fromv$instance
----如何查询数据库有多少表
sql>select*fromall_tables;
-----如何测试sql语句执行所用时间
sql>settimingon;
sql>select*fromtablename;
-----chr（）的反函数ASCII（）
sql>selectchr（65）,ascii（'A'）fromdual;
---怎么把select出来的结果导出到一个文本文件中
sql>spoolc:
\name.txt
sql>select*fromtable_name
sql>spooloff;
------查看sql执行的i/o数
sql>select*fromv$filesta;
----如何修改表名
sql>altertableold_namerenametonew_table_name;
-----关闭数据库实例
shutdown--正常关闭（等待所有用户断开连接后才能关闭）
shutdownimmediate--建议使用（迫使用户执行当前SQL后立即断开连接shutdowntransactional--（迫使在当前用户执行完后立即断开连接）
shutdownabort--强制关闭（类db2的db2stopforce）
-----连接sqlplus
命令行下运行cmd进入，如以sys模式登陆，命令如下：
sqlplus"sys/zhb126assysdba"
sqlplus/nologconnectsys/zhb126assysdba
sqlplusscott/tiger
-------启动数据库实例
startup--启动数据库的同时启动控制文件、数据文件
startupmount--启动数据库的同时启动控制文件、不启动数据文件
startupnomount--仅启动数据库实例，用于控制文件丢失情况，此时控制文件无法启
------查看日志归档方式--用
sql>startupmount启动数据库
sql>archiveloglist
sql>ALTERDATABASEarchivelog
sql>ALTERDATABASEOPEN
----如何搜索前N条记录
sql>select*fromtablenamewhererownum----如何知道oracle支持多少并发
sql>showparameterprocesses;
---怎样扩大redolog的大小
建立一个临时的redolog组，然后切换日志，删除以前的日志，建立新的日志。
---tablespace对文件大小没有限制
---获取时间的写法
sql>selectto_char（sysdate,'yyyy'）fromdual;
sql>selectto_char（sysdate,'mm'）fromdual;
sql>selectto_char（sysdate,'dd'）fromdaul;
sql>selectto_char（sysdate,'hh24'）fromdual;
sql>selectto_char（sysdate,'mi'）fromdual;
sql>selectto_char（sysdate,'ss'）fromdual;
sql>selectto_char（sysdate）fromdual;
----返回记录的物理地址
sql>selectrowid,enamefromtablename
----创建一个表空间
sql>createtablespaceATMVdatafile'D:
/oracle/product/10.2.0/oradata/orcl/ATMV.dbf'size100mautoextendonnext50mmaxsizeunlimited;
----查询所有表空间名称-------
sql>selecttablespace_namefromdba_tablespaces;
-----请问如何查询表在哪个表空间中？
sql>SELECTtablespace_nameFROMUSER_TABLESWHEREtable_name='YOUR_TABLENAME'
sql>SELECTtablespace_nameFROMDBA_TABLESWHEREtable_name='YOUR_TABLENAME'andowner='表的OWNER'--用大写字母
----在指定表空间创建表
SQL>createtableorcbase_tablespace_table1（achar（3）,bchar（4））tablespaceor
cbase_tablespace1;
-----ORACLE服务=实例+数据库
实例是一群为你服务的进程，数据库是你真实需要的数据
比如你去饭店吃饭享受服务，厨师+服务员的服务相当于实例，食材、饭菜什么相当于数据库的数据
简单来说有时候这两个名词有时可以互换使用、但是其概念还是不同的。
实例instance=进程+进程所使用的内存（SGA）
数据库database=物理操作系统文件或磁盘（disk）的集合（redo文件+control文件+data文件+临时文件）
数据库实例也称作服务器,是用来访问数据库文件集的存储结构及后台进程的集合.
1、一个数据库可以被多个实例访问（称为真正的应用群集选项）.
2、一个实例在其生存期内只能装载（alterdatabasemount）和打开（alterdatabaseopen）一个数据库
3、Oracle的实例在启动以后，只能load一次数据库，如果想把数据库与Instance断开，然后再重新挂在一个数据库Instance，那么就需要你首先把数据库Instance进程结束，然后重新建立这个instance的一个进程，再load另外一个数据库。
否则肯定要抛除ORA-16169错误，说数据库已经被打开。
因为一个数据库Instance在其生存期中最多只能load和打开一个instance。
.
4、另外实例可以在没有数据文件的情况下单独启动startupnomount,通常没什么意义
---selectinstance_namefromv$instance;
查看自己Oracle的sid。
----Oracle数据库实例支持4种状态，包括打开（OPEN）、关闭（CLOSE）、已装载（MOUNT）和已启动（NOMOUNT）。
打开。
启动实例，装载并打开数据库。
该模式是默认的启动模式，它允许任何有效用户
连接到数据库，并执行典型的数据访问操作。
关闭。
将Oracle实例从允许用户访问数据库的状态转换为休止状态。
关闭操作首先终止用户访问数据库所需的进程，然后释放计算机中供Oracle运行使用的内存。
已装载。
启动实例并装载数据库，但不打开数据库。
该模式用于更改数据库的归档模式
或执行恢复操作，还可以用于数据文件恢复。
因为此状态下没有打开数据库，所以不允许用户访问。
已启动。
启动实例，但不装载数据库。
该模式用于重新创建控制文件，对控制文件进行
恢复或重新创建数据库等。
因为此状态下没有打开数据库，所以不允许用户访问。
该状态也称为“不装载”。
-----SQLPLUS/NOLOG
-----CONNECTSYSASSYSDBA
使用/NOLOG参数表示启动SQLPlus，但不连接到Oracle数据库。
-----SHUTDOWNNORMAL
数据库会一直等待当前连接到数据库的用户都断开连接后，再关闭数据库实例。
因此，通常
执行SHUTDOWNNORMAL命令都需要等待很长时间，一般不采用这种方式来关闭数据库实例。
---SHUTDOWNIMMEDIATE
在执行立即关闭过程中，数据库将不允许建立新的连接，也不允许开始新的事务。
所有未提
交的事务都会被执行回滚操作。
对于比较复杂的事务，回滚操作可能持续很长时间，因此立即关
闭操作的执行时间也许并不像名字中所描述的那么快。
---SHUTDOWNTRANSACTIONAL
在执行事务处理关闭时，数据库将不允许建立新的连接，也不允许开始新的事务。
当所有事
务都处理完成后，仍然连接到当前实例的客户端将被断开。
---SHUTDOWNABORT
在执行中止关闭时，数据库将不允许建立新的连接，也不允许开始新的事务。
所有正在执行
的客户端SQL语句将被立即中止，没有提交的事务也不被回滚，立即切断所有在线用户的连接。
因此，中止关闭是最快速的关闭Oracle数据库的方式。
----STARTUPPFILE=F:
\app\Administrator\product\11.1.0\db_1\dbs\spf_init.ora
有些情况下，需要临时使用非默认的初始化参数文件中的配置信息来启动数据库实例。
使用SYS用户以系统管理员（SYSDBA）身份连接到SQLPlus。
如果数据库实例处于启动状
态，可以使用SHUTDOWNIMMEDIATE命令将其关闭。
然后执行下面的命令：
---STARTUPNOMOUNT
启动数据库实例，但不装载数据库，STARTUPNOMOUNT方式启动数据库实例可以执行重建数据库或数据文件等操作。
----STARTUPMOUNT
以启动数据库实例，同时装载数据库，并不打开数据库。
STARTUPMOUNT方式启动数据库实例可以执行数据库日志归档、数据库介质恢复、重定位数据文件和重做日志文件等操作。
----STARTUPFORCE
强制启动数据库实例，命令相当于先执行SHUTDOWNABORT命令，然后再执行STARTUP
---STARTUPRESTRICT
以限制模式启动数据库实例，只有拥有RESTRICTSESSION权限的用户才可以访问数据库。
----STARTUPRECOVER
在数据库启动时开始介质恢复，执行STARTUPRECOVER命令的效果相当于首先执行RECOVERDATABASE命令，然后再启动数据库实例。
---用户可以使用STARTUPNOMOUNT命令启动数据库实例，执行重建数据库和数据文件等任
。
执行完成后，就需要装载数据库实例。
此时可以执行下面的命令：
ALTERDATABASEMOUNT
----用户可以使用STARTUPMOUNT命令启动数据库实例,打开数据库实例ALTERDATABASEOPEN
为了防止用户事务修改数据库中的数据，可以只读方式打开数据库，代码如下ALTERDATABASEOPENREADONLY
--然后执行下面的SELECT语句查看当前数据库实例的活动状态。
SELECTACTIVE_STATEFROMV$INSTANCE;
ALTERSYSTEMUNQUIESCE;
然后执行下面的SELECT语句查看当前数据库实例的活动状态。
----使用下面的命令可以挂起数据库。
ALTERSYSTEMSUSPEND;
使用下面的命令可以将数据库恢复到正常状态。
ALTERSYSTEMRESUME;
视图V$INSTANCE中DATABASE_STATE列保存数据库的当前状态，它的可能取值如下。
-----查看用户会话信息,过系统视图V$SESSION可以查看每个会话的详细信息。
SELECTUSERNAME,PROCESS,PROGRAM,STATUSFROMV$SESSION;
COLUSERNAMEFORMATA10
COLPROCESSFORMATA10
COLPROGRAMFORMATA20
--查看用户权限信息,通过系统视图DBA_TAB_PRIVS可以查看用户的权限信息
SELECTGRANTEE,TABLE_NAME,PRIVILEGEFROMDBA_TAB_PRIVSWHERErownum<=10;
COLGRANTEEFORMATA15
COLTABLE_NAMEFORMATA15
COLPRIVILEGEFORMATA15
---使用口令文件（也称为密码文件）认证方式可以跟踪所有SYSDBA和SYSOPER用户的口令信息
ORAPWDFILE=F:
\app\Administrator\product\11.1.0\db_1\database\myPwdFile.ora
PASSWORD=syspwdENTRIES=40
--忘记DBA口令的解决办法
---视图DBA_ROLES中可以查询到角色的信息
SELECT*FROMDBA_ROLES;
PASSWORD_REQUIRED--表明角色是否需要使用口令来启用
--使用DBA_PROFILES视图查看概要文件信息
SELECT*FROMDBA_PROFILESWHEREPROFILES='DEFAULT'';
COLPROFILEFORMATA20
COLRESOURCE_NAMEFORMATA20
COLLIMITFORMATA15
--使概要文件生效,只有将初始化参数文件中的RESOURCE_LIMIT参数设置为TRUE后，概要文件才能生效。
SHOWPARAMETERRESOURCE_LIMIT
---使用CREATEPROFILE语句创建概要文件
CREATEPROFILEAdminProfileLIMIT
SESSIONS_PER_USER2
CPU_PER_SESSION10000
CPU_PER_CALLDEFAULT
CONNECT_TIME500
IDLE_TIME90
PASSWORD_LIFE_TIME90
PASSWORD_REUSE_TIME100
PASSWORD_REUSE_MAXUNLIMITED
----使用DBA_PROFILES视图查看概要文件信息
SELECT*FROMDBA_PROFILESWHEREPROFILES='DEFAULT'';
selectPROFILESfromdba_profiles;
---将概要文件授予用户
CREATEUSER<用户名>PROFILE<概要文件>
创建一个用户NEWUSER，同时将其授予AdminProfile概要文件
CREATEUSERNEWUSERPROFILEAdminProfile
IDENTIFIEDBYpwd;
---从逻辑结构上来看，Oracle采用一种层次结构来管理数据，主要由表空间、表、视图、索
引、段、区、数据块和方案（Schema）等组成。
Oracle数据库的逻辑结构包括表空间（tablespace）、方案（schema）对象、段（segment）、区
间（extent）和数据块（datablock）等。
数据库由若干个表空|组成，表空间由表、索引、视图
等逻辑对象组成，表由段组成，段由区组成，区则由数据块组成。
---oracle中方案和表空间的区别在哪里？
方案是针对用户的，一般情况下一个用户对应一个方案，而且用户名和方案名相同，在用户没有包含对象时这个用户没有对应的方案。
方案是一组用户对象的集合，oracle通过方案来管理用户对象。
表空间是oracle数据库的逻辑存储结构中的一部分，oracle的逻辑存储结构如下：
database->tablespaces->segments->ext
升级会员 