oracle技巧.docx

1、oracle技巧-sqlplus命令中启动监听器-sqllsnrctl start -本机服务器监听器是否启动不影响，sqlplus 不用符的话,是不需要用监听器sqllsnrctl stop e:lsnrctl status -查看监听器的状态-cmd窗口中启动oracle服务-d:net start oracleserviceorcl -oracleservice后跟oracle服务名称sidd:net stop oracleserviceorcl-sqlplus连接数据库注意事项-c:sqlplus /nolog -不对数据库操作链接，不用参数的话链接到默认数据库 sqlconn sys

2、tem/system orcl as sysdba -指定需要的实例，sqlselect instance_name from v$instance;sqlhost -退出sqlPlus界面-oracle安装完成后的初始命令internal/oraclesys/change_on_installsystem/managerscott/tigersysman/oem_temp-如何分辨某个用户从哪台机器登录oracle的？-sqlselect machine,terminal from v$session;-用什么语句查询字段sqldesc table_name -查询表的结构sqlselect

3、 * from all_tables where table_col like %-查看最大会话数-sqlshow parameter processes -processes 为设置的用户数sqlselect sessions_highwater from v$license -记录曾经的最大会话数-如何以archivelog方式运行oracleinit.oralog.archive_start=truerestart database-怎么获取哪些用户在使用数据库sqlselect username from v$session-怎么样查看数据库的sid-sqlselect name fr

4、om v$database; -也可以查init.ora文件-如何在oracle服务器通过sqlplus查看本机ip地址sqlselect sys_context(userenv,ip_address) from dual;-如何查询每个用户的权限sqlselect * from dba_sys_privs;-如何将表移动表空间sqlalter table table_name move tablespace_name;-查看数据库字符状况sqlselect * from nls_database_parameterssqlselect * from v$nls_parameters-查询表空

5、间信息-sqlselect * from dba_data_files;-如何使用select 语句查询时自动生成序号sqlselect rownum,字段 from table;-如何知道数据库中某个表所在的表空间tablespace,注意大写字母sqlselect TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME = -怎么快速做一个和原表一样的备份表sqlcreate table new_table_name as (select * from old_table_name);sqlcreate table new_

6、table_name tablespace tablespace_name as (select * from old_table_name);-指定表空间，不指定表空间按登录用户所在表空间创建。-如何查看数据库中有多少个表空间tablespacesqlselect * from dba_tablespaces;sqlselect tablespace_name,block_size,status from dba_tablespaces;sql查看表空间总大小，及其已使用大小select a.tablespace_name,a.bytes/1024/1024 Sum MB,(a.bytes-

7、b.bytes)/1024/1024 used MB,b.bytes/1024/1024 free MB,round(a.bytes-b.bytes)/a.bytes)*100,2) percent_usedfrom(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_nam

8、e) bwhere a.tablespace_name=b.tablespace_nameorder by (a.bytes-b.bytes)/a.bytes) desc;-如何修改oracle数据库中的用户连接数修改initSID.ora，将process加大，重新启动数据库-怎样查看哪些用户拥有sysdba,sysoper权限？sqlconn sys/change_on_install -其他用户无权限查询sqlselect * from v_$pwfile_users;-如何单独备份一个或多个表，用户c:exp user/password tables=tablenamec:exp us

9、er/password owner=user file=导出文件-如何查看数据文件存放的路径sqlcol file_name format a50sqlselect tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;-oracle常用系统文件有哪些通过以下视图文件显示文件信息：v$database,v$datafile,v$logfile,v$controlfile ,v$parameter;-内连接sql inner joinsqlselect a.* from bse

10、mpms a, bsdptms b where a.dpt_no=b.dpt_no-如何外连接-如何执行脚本sql文件？sql$path/filename.sql;-如何快速清空一个大表sqltruncate table table_name;-查询有多少个数据库实例sqlselect * from v$instance-如何查询数据库有多少表sqlselect * from all_tables;-如何测试sql语句执行所用时间sqlset timing on;sqlselect * from tablename;-chr()的反函数ASCII()sql select chr(65),asc

11、ii(A) from dual;-怎么把select出来的结果导出到一个文本文件中sqlspool c:name.txtsqlselect * from table_name sqlspool off;-查看sql执行的i/o数sqlselect * from v$filesta;-如何修改表名sqlalter table old_name rename to new_table_name;-关闭数据库实例shutdown -正常关闭（等待所有用户断开连接后才能关闭）shutdown immediate-建议使用（迫使用户执行当前SQL后立即断开连接shutdown transactional

12、-（迫使在当前用户执行完后立即断开连接）shutdown abort-强制关闭（类db2的db2 stop force）-连接sqlplus命令行下运行cmd进入，如以sys模式登陆，命令如下：sqlplus sys/zhb126 as sysdbasqlplus/nolog connect sys/zhb126 as sysdbasqlplus scott/tiger-启动数据库实例startup -启动数据库的同时启动控制文件、数据文件startup mount -启动数据库的同时启动控制文件、不启动数据文件startup nomount -仅启动数据库实例，用于控制文件丢失情况，此时控制

13、文件无法启-查看日志归档方式-用sqlstartup mount启动数据库sqlarchive log listsqlALTER DATABASE archivelogsqlALTER DATABASE OPEN -如何搜索前N条记录sqlselect * from tablename where rownumshow parameter processes;-怎样扩大redo log的大小建立一个临时的redolog组，然后切换日志，删除以前的日志，建立新的日志。-tablespace对文件大小没有限制-获取时间的写法sqlselect to_char(sysdate,yyyy) from

14、dual;sqlselect to_char(sysdate,mm) from dual;sqlselect to_char(sysdate,dd) from daul;sqlselect to_char(sysdate,hh24) from dual;sqlselect to_char(sysdate,mi) from dual;sqlselect to_char(sysdate,ss) from dual;sqlselect to_char(sysdate) from dual;-返回记录的物理地址sqlselect rowid, ename from tablename-创建一个表空间s

15、qlcreate tablespace ATMV datafile D:/oracle/product/10.2.0/oradata/orcl/ATMV.dbf size 100m autoextend on next 50m maxsize unlimited;-查询所有表空间名称-sqlselect tablespace_name from dba_tablespaces;-请问如何查询表在哪个表空间中？sqlSELECT tablespace_name FROM USER_TABLES WHERE table_name = YOUR_TABLENAME sqlSELECT tablesp

16、ace_name FROM DBA_TABLES WHERE table_name = YOUR_TABLENAME and owner=表的OWNER-用大写字母-在指定表空间创建表SQL create table orcbase_tablespace_table1 ( a char(3),b char(4) tablespace orcbase_tablespace1;-ORACLE服务= 实例+数据库实例是一群为你服务的进程，数据库是你真实需要的数据比如你去饭店吃饭享受服务，厨师+服务员的服务相当于实例，食材、饭菜什么相当于数据库的数据简单来说有时候这两个名词有时可以互换使用、但是其概念

17、还是不同的。实例 instance =进程 + 进程所使用的内存(SGA)数据库 database=物理操作系统文件或磁盘（disk）的集合（redo文件+control文件+data文件+临时文件）数据库实例也称作服务器, 是用来访问数据库文件集的存储结构及后台进程的集合.1、一个数据库可以被多个实例访问(称为真正的应用群集选项).2、一个实例在其生存期内只能装载(alter database mount)和打开(alter database open)一个数据库3、Oracle的实例在启动以后，只能load一次数据库，如果想把数据库与Instance断开，然后再重新挂在一个数据库Insta

18、nce，那么就需要你首先把数据库Instance进程结束，然后重新建立这个instance的一个进程，再load另外一个数据库。否则肯定要抛除ORA-16169错误，说数据库已经被打开。因为一个数据库Instance在其生存期中最多只能load和打开一个instance。.4、另外实例可以在没有数据文件的情况下单独启动 startup nomount , 通常没什么意义 -select instance_name from v$instance; 查看自己Oracle的sid。-Oracle 数据库实例支持4种状态，包括打开（OPEN）、关闭（CLOSE）、已装载（MOUNT）和已启动（NOM

19、OUNT）。打开。启动实例，装载并打开数据库。该模式是默认的启动模式，它允许任何有效用户连接到数据库，并执行典型的数据访问操作。 关闭。将 Oracle 实例从允许用户访问数据库的状态转换为休止状态。关闭操作首先终止用户访问数据库所需的进程，然后释放计算机中供Oracle 运行使用的内存。已装载。启动实例并装载数据库，但不打开数据库。该模式用于更改数据库的归档模式或执行恢复操作，还可以用于数据文件恢复。因为此状态下没有打开数据库，所以不允许用户访问。 已启动。启动实例，但不装载数据库。该模式用于重新创建控制文件，对控制文件进行恢复或重新创建数据库等。因为此状态下没有打开数据库，所以不允许用户访

20、问。该状态也称为“不装载”。 -SQLPLUS /NOLOG -CONNECT SYS AS SYSDBA 使用/NOLOG参数表示启动SQL Plus，但不连接到Oracle 数据库。-SHUTDOWN NORMAL 数据库会一直等待当前连接到数据库的用户都断开连接后，再关闭数据库实例。因此，通常执行SHUTDOWN NORMAL命令都需要等待很长时间，一般不采用这种方式来关闭数据库实例。-SHUTDOWN IMMEDIATE 在执行立即关闭过程中，数据库将不允许建立新的连接，也不允许开始新的事务。所有未提交的事务都会被执行回滚操作。对于比较复杂的事务，回滚操作可能持续很长时间，因此立即关闭

21、操作的执行时间也许并不像名字中所描述的那么快。-SHUTDOWN TRANSACTIONAL在执行事务处理关闭时，数据库将不允许建立新的连接，也不允许开始新的事务。当所有事务都处理完成后，仍然连接到当前实例的客户端将被断开。 -SHUTDOWN ABORT 在执行中止关闭时，数据库将不允许建立新的连接，也不允许开始新的事务。所有正在执行的客户端SQL语句将被立即中止，没有提交的事务也不被回滚，立即切断所有在线用户的连接。因此，中止关闭是最快速的关闭 Oracle 数据库的方式。 -STARTUP PFILE = F:appAdministratorproduct11.1.0db_1dbsspf

22、_init.ora有些情况下，需要临时使用非默认的初始化参数文件中的配置信息来启动数据库实例。使用SYS用户以系统管理员（SYSDBA）身份连接到SQL Plus。如果数据库实例处于启动状态，可以使用SHUTDOWN IMMEDIATE命令将其关闭。然后执行下面的命令： -STARTUP NOMOUNT 启动数据库实例，但不装载数据库 ，STARTUP NOMOUNT方式启动数据库实例可以执行重建数据库或数据文件等操作。-STARTUP MOUNT以启动数据库实例，同时装载数据库，并不打开数据库。STARTUP MOUNT 方式启动数据库实例可以执行数据库日志归档、数据库介质恢复、重定位数据文

23、件和重做日志文件等操作。-STARTUP FORCE 强制启动数据库实例，命令相当于先执行SHUTDOWN ABORT命令，然后再执行STARTUP-STARTUP RESTRICT以限制模式启动数据库实例，只有拥有 RESTRICT SESSION 权限的用户才可以访问数据库。-STARTUP RECOVER在数据库启动时开始介质恢复，执行STARTUP RECOVER命令的效果相当于首先执行 RECOVER DATABASE命令，然后再启动数据库实例。-用户可以使用STARTUP NOMOUNT命令启动数据库实例，执行重建数据库和数据文件等任。执行完成后，就需要装载数据库实例。此时可以执行

24、下面的命令：ALTER DATABASE MOUNT -用户可以使用 STARTUP MOUNT 命令启动数据库实例,打开数据库实例ALTER DATABASE OPEN 为了防止用户事务修改数据库中的数据，可以只读方式打开数据库，代码如下ALTER DATABASE OPEN READ ONLY -然后执行下面的 SELECT语句查看当前数据库实例的活动状态。SELECT ACTIVE_STATE FROM V$INSTANCE; ALTER SYSTEM UNQUIESCE; 然后执行下面的 SELECT语句查看当前数据库实例的活动状态。-使用下面的命令可以挂起数据库。 ALTER SYS

25、TEM SUSPEND; 使用下面的命令可以将数据库恢复到正常状态。 ALTER SYSTEM RESUME; 视图V$INSTANCE中DATABASE_STATE 列保存数据库的当前状态，它的可能取值如下。-查看用户会话信息,过系统视图 V$SESSION 可以查看每个会话的详细信息。SELECT USERNAME, PROCESS, PROGRAM, STATUS FROM V$SESSION; COL USERNAME FORMAT A10 COL PROCESS FORMAT A10 COL PROGRAM FORMAT A20 -查看用户权限信息,通过系统视图 DBA_TAB_PR

26、IVS 可以查看用户的权限信息SELECT GRANTEE, TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE rownum=10; COL GRANTEE FORMAT A15 COL TABLE_NAME FORMAT A15 COL PRIVILEGE FORMAT A15 -使用口令文件（也称为密码文件）认证方式可以跟踪所有SYSDBA和SYSOPER用户的口令信息ORAPWD FILE=F:appAdministratorproduct11.1.0db_1databasemyPwdFile.ora PASSWORD=syspwd ENTRI

27、ES =40 -忘记DBA口令的解决办法-视图DBA_ROLES中可以查询到角色的信息SELECT * FROM DBA_ROLES; PASSWORD_REQUIRED -表明角色是否需要使用口令来启用-使用 DBA_PROFILES 视图 查看概要文件信息SELECT * FROM DBA_PROFILES WHERE PROFILES=DEFAULT; COL PROFILE FORMAT A20 COL RESOURCE_NAME FORMAT A20 COL LIMIT FORMAT A15 -使概要文件生效,只有将初始化参数文件中的 RESOURCE_LIMIT参数设置为TRUE后

28、，概要文件才能生效。SHOW PARAMETER RESOURCE_LIMIT-使用CREATE PROFILE 语句创建概要文件CREATE PROFILE AdminProfile LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION 10000 CPU_PER_CALL DEFAULT CONNECT_TIME 500 IDLE_TIME 90 PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 100 PASSWORD_REUSE_MAX UNLIMITED -使用 DBA_PROFILES 视图 查看概要文件信息 SELE

29、CT * FROM DBA_PROFILES WHERE PROFILES=DEFAULT; select PROFILES from dba_profiles;-将概要文件授予用户CREATE USER PROFILE 创建一个用户NEWUSER，同时将其授予AdminProfile 概要文件CREATE USER NEWUSER PROFILE AdminProfile IDENTIFIED BY pwd; -从逻辑结构上来看，Oracle 采用一种层次结构来管理数据，主要由表空间、表、视图、索引、段、区、数据块和方案（Schema）等组成。 Oracle 数据库的逻辑结构包括表空间（ta

30、blespace）、方案（schema）对象、段（segment）、区间（extent）和数据块（data block）等。数据库由若干个表空|组成，表空间由表、索引、视图等逻辑对象组成，表由段组成，段由区组成，区则由数据块组成。-oracle中方案和表空间的区别在哪里？方案是针对用户的，一般情况下一个用户对应一个方案，而且用户名和方案名相同，在用户没有包含对象时这个用户没有对应的方案。 方案是一组用户对象的集合，oracle通过方案来管理用户对象。 表空间是oracle数据库的逻辑存储结构中的一部分，oracle的逻辑存储结构如下： database- tablespaces- segments- ext