oracle经常使用的dba操作

一、表空间操作1.1 创建永久表空间永久表空间包含存储在数据文件中的持久性模式对象。语法CREATE[SMALLFILE|BIGFILE]TABLESPACEtablespace_name { DATAFILE {[filename|ASM_filename][SIZEinteger[K|M|G|T|P|E]][REUSE][AUTOEXTEND {OFF|ON[NEXTinteger[K|M|G|T|P|E]][MAXSIZE { UNLIMITED|integer[K|M|G|T|P|E]}]}]|[filename | ASM_filename|(filename | ASM_filename[,filename | ASM_filename])][SIZEinteger[K|M|G|T|P|E]][REUSE]} { MINIMUM EXTENTinteger[K|M|G|T|P|E]|BLOCKSIZEinteger[K]|{ LOGGING|NOLOGGING }|FORCELOGGING|DEFAULT[{ COMPRESS|NOCOMPRESS }]storage_clause|{ ONLINE|OFFLINE }|EXTENT MANAGEMENT {LOCAL[AUTOALLOCATE|UNIFORM[SIZEinteger[K|M|G|T|P|E]]]|DICTIONARY }|SEGMENT SPACE MANAGEMENT { AUTO|MANUAL }|FLASHBACK {ON|OFF}[MINIMUM EXTENTinteger[K|M|G|T|P|E]|BLOCKSIZEinteger[K]|{ LOGGING|NOLOGGING }|FORCELOGGING|DEFAULT[{ COMPRESS|NOCOMPRESS }]storage_clause|{ ONLINE|OFFLINE }|EXTENT MANAGEMENT {LOCAL[AUTOALLOCATE|UNIFORM[SIZEinteger[K|M|G|T|P|E]]]|DICTIONARY }|SEGMENT SPACE MANAGEMENT { AUTO|MANUAL }|FLASHBACK {ON|OFF}]}示例CREATE TABLESPACE WNO704 LOGGING DATAFILE ‘/opt/oracle/app/oradata/qhfwbzdb/WNO704.dbf’ SIZE 100 M AUTOEXTEND ON NEXT 32 M MAXSIZE 500 M EXTENT MANAGEMENT LOCAL;1.2 创建临时表空间临时表空间包含存储在会话期间存在的临时文件中的模式对象。语法CREATE[SMALLFILE|BIGFILE]TEMPORARYTABLESPACEtablespace_name[TEMPFILE {[filename|ASM_filename][SIZEinteger[K|M|G|T|P|E]][REUSE][AUTOEXTEND {OFF|ON[NEXTinteger[K|M|G|T|P|E]][MAXSIZE { UNLIMITED|integer[K|M|G|T|P|E]}]}]|[filename | ASM_filename|(filename | ASM_filename[,filename | ASM_filename])][SIZEinteger[K|M|G|T|P|E]][REUSE]}[TABLESPACEGROUP{ tablespace_group_name|}][EXTENT MANAGEMENT {LOCAL[AUTOALLOCATE|UNIFORM[SIZEinteger[K|M|G|T|P|E]]]|DICTIONARY }]示例CREATE TEMPORARY TABLESPACE WNO704_TEMP TEMPFILE ‘/opt/oracle/app/oradata/qhfwbzdb/WNO704_TEMP.dbf’ SIZE 100 M AUTOEXTEND ON NEXT 32 M MAXSIZE 500 M EXTENT MANAGEMENT LOCAL;1.3 UNDO表空间如果Oracle数据库以自动撤消管理模式运行则会创建撤消表空间来管理撤消数据。语法:CREATE[SMALLFILE|BIGFILE]UNDOTABLESPACEtablespace_name[DATAFILE {[filename|ASM_filename][SIZEinteger[K|M|G|T|P|E]][REUSE][AUTOEXTEND {OFF|ON[NEXTinteger[K|M|G|T|P|E]][MAXSIZE { UNLIMITED|integer[K|M|G|T|P|E]}]}]|[filename | ASM_filename|(filename | ASM_filename[,filename | ASM_filename])][SIZEinteger[K|M|G|T|P|E]][REUSE]}[EXTENT MANAGEMENT {LOCAL[AUTOALLOCATE|UNIFORM[SIZEinteger[K|M|G|T|P|E]]]|DICTIONARY }][RETENTION { GUARANTEE|NOGUARANTEE }]示例CREATE UNDO TABLESPACE WNO704_UNDO DATAFILE ‘/opt/oracle/app/oradata/qhfwbzdb/WNO704_UNDO.f’ SIZE 5 M AUTOEXTEND ON RETENTION GUARANTEE;1.4 表空间删除1.4.1 删除空的表空间但是不包含物理文件DROP TABLESPACE tablespace_name;1.4.2 删除非空表空间但是不包含物理文件DROP TABLESPACE tablespace_name including contents;1.4.3 删除空表空间包含物理文件DROP TABLESPACE tablespace_name including datafiles;1.4.4 删除非空表空间包含物理文件DROP TABLESPACE tablespace_name including contents AND datafiles;1.4.5 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段就要加上CASCADE CONSTRAINTSDROP TABLESPACE tablespace_name including contents AND datafiles CASCADE CONSTRAINTS;1.5表空间查看与监控1.5.1 查看表空间的名称及大小SELECTt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_sizeFROMdba_tablespaces t,dba_data_files dWHEREt.tablespace_named.tablespace_nameGROUPBYt.tablespace_name;1.5.2 查看表空间物理文件的名称及大小SELECTtablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spaceFROMdba_data_filesORDERBYtablespace_name;1.5.3 查看回滚段名称及大小SELECTsegment_name,tablespace_name,r.STATUS,(initial_extent/1024)initialextent,(next_extent/1024)nextextent,max_extents,v.curext curextentFROMdba_rollback_segs r,v$rollstat vWHEREr.segment_idv.usn()ORDERBYsegment_name;1.5.4 查看控制文件SELECT NAME FROM v$controlfile;1.5.5 查看日志文件SELECT MEMBER FROM v$logfile;1.5.6 查看表空间的使用情况SELECTsum(bytes)/(1024*1024)ASfree_space,tablespace_nameFROMdba_free_spaceGROUPBYtablespace_name;SELECTa.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes*100)/a.bytes% used ,(c.bytes*100)/a.bytes% free FROMsys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free cWHEREa.tablespace_nameb.tablespace_nameANDa.tablespace_namec.tablespace_name;1.5.7 查看数据库库对象SELECTowner,object_type,status,count(*)countFROMall_objectsGROUPBYowner,object_type,status;1.5.8 查看数据库的版本SELECTversionFROMproduct_component_versionWHEREsubstr(product,1,6)oracle;1.5.9 查看数据库的创建日期和归档方式SELECTcreated,log_mode,log_modeFROMv$database;--1g1024mb--1m1024kb--1k1024bytes--1m11048576bytes--1g1024*11048576bytes11313741824bytesSELECTa.tablespace_name表空间名,total表空间大小,free表空间剩余大小,(total-free)表空间使用大小,total/(1024*1024*1024)表空间大小(g),free/(1024*1024*1024)表空间剩余大小(g),(total-free)/(1024*1024*1024)表空间使用大小(g),round((total-free)/total,4)*100使用率 %FROM(SELECTtablespace_name,sum(bytes)freeFROMdba_free_spaceGROUPBYtablespace_name)a,(SELECTtablespace_name,sum(bytes)totalFROMdba_data_filesGROUPBYtablespace_name)bWHEREa.tablespace_nameb.tablespace_name二、用户操作2.1 创建用户CREATE USER WNO704 IDENTIFIED BY wno704db312 DEFAULT TABLESPACE WNO704 TEMPORARY TABLESPACE WNO704_TEMP;2.2 删除用户DROP USER WNO704 CASCADE;若用户拥有对象(例如进行过增删改查等等操作)则不能直接删除否则将返回一个错误值。指定关键字CASCADE,可删除用户所有的对象然后再删除用户。三、权限3.1 增加权限GRANT connect,resource,dba TO monitor;GRANT CREATE SESSION TO monitor;3.2 删除权限REVOKE CONNECT, RESOURCE FROM 用户名;3.3 常用权限CREATE SESSION 允许用户登录数据库权限REATE TABLE 允许用户创建表权限UNLIMITED TABLESPACE 允许用户在其他表空间随意建表四、角色4.1 创建角色CREATE ROLE 角色名;4.2 授权角色GRANT SELECT ON 表名 TO 角色名;4.3 删除角色DROP ROLE 角色名;4.4 与权限安全相关的数据字典表有:ALL_TAB_PRIVS ALL_TAB_PRIVS_MADE ALL_TAB_PRIVS_RECD DBA_SYS_PRIVS DBA_ROLES DBA_ROLE_PRIVS ROLE_ROLE_PRIVS ROLE_SYS_PRIVS ROLE_TAB_PRIVS SESSION_PRIVS SESSION_ROLES USER_SYS_PRIVS USER_TAB_PRIV4.5 oracle的系统和对象权限列表alteranycluster 修改任意簇的权限alteranyindex修改任意索引的权限alteranyrole 修改任意角色的权限alteranysequence 修改任意序列的权限alteranysnapshot修改任意快照的权限alteranytable修改任意表的权限alteranytrigger修改任意触发器的权限altercluster 修改拥有簇的权限alterdatabase修改数据库的权限alterprocedure修改拥有的存储过程权限alterprofile 修改资源限制简表的权限alterresource cost 设置佳话资源开销的权限alterrollbacksegment 修改回滚段的权限altersequence 修改拥有的序列权限altersession修改数据库会话的权限altersytem 修改数据库服务器设置的权限altertable修改拥有的表权限altertablespace修改表空间的权限alteruser修改用户的权限analyze使用analyze命令分析数据库中任意的表、索引和簇 auditany为任意的数据库对象设置审计选项 audit system 允许系统操作审计backupanytable备份任意表的权限 becomeuser切换用户状态的权限commitanytable提交表的权限createanycluster 为任意用户创建簇的权限createanyindex为任意用户创建索引的权限createanyprocedure为任意用户创建存储过程的权限createanysequence 为任意用户创建序列的权限createanysnapshot为任意用户创建快照的权限createanysynonym 为任意用户创建同义名的权限createanytable为任意用户创建表的权限createanytrigger为任意用户创建触发器的权限createanyview为任意用户创建视图的权限createcluster 为用户创建簇的权限createdatabaselink 为用户创建的权限createprocedure为用户创建存储过程的权限createprofile 创建资源限制简表的权限createpublicdatabaselink 创建公共数据库链路的权限createpublicsynonym 创建公共同义名的权限createrole 创建角色的权限createrollbacksegment 创建回滚段的权限createsession创建会话的权限createsequence 为用户创建序列的权限createsnapshot为用户创建快照的权限createsynonym 为用户创建同义名的权限createtable为用户创建表的权限createtablespace创建表空间的权限createuser创建用户的权限createview为用户创建视图的权限deleteanytable删除任意表行的权限deleteanyview删除任意视图行的权限deletesnapshot删除快照中行的权限deletetable为用户删除表行的权限deleteview为用户删除视图行的权限dropanycluster 删除任意簇的权限dropanyindex删除任意索引的权限dropanyprocedure删除任意存储过程的权限dropanyrole 删除任意角色的权限dropanysequence 删除任意序列的权限dropanysnapshot删除任意快照的权限dropanysynonym 删除任意同义名的权限dropanytable删除任意表的权限dropanytrigger删除任意触发器的权限dropanyview删除任意视图的权限dropprofile 删除资源限制简表的权限droppubliccluster 删除公共簇的权限droppublicdatabaselink 删除公共数据链路的权限droppublicsynonym 删除公共同义名的权限droprollbacksegment 删除回滚段的权限droptablespace删除表空间的权限dropuser删除用户的权限executeanyprocedure执行任意存储过程的权限executefunction执行存储函数的权限executepackage 执行存储包的权限executeprocedure执行用户存储过程的权限forceanytransaction管理未提交的任意事务的输出权限forcetransaction管理未提交的用户事务的输出权限grantanyprivilege 授予任意系统特权的权限grantanyrole 授予任意角色的权限indextable给表加索引的权限insertanytable向任意表中插入行的权限insertsnapshot向快照中插入行的权限inserttable向用户表中插入行的权限insertview向用户视图中插行的权限lockanytable给任意表加锁的权限 managertablespace管理备份可用性表空间的权限referencestable参考表的权限 restrictedsession创建有限制的数据库会话的权限selectanysequence 使用任意序列的权限selectanytable使用任意表的权限selectsnapshot使用快照的权限selectsequence 使用用户序列的权限selecttable使用用户表的权限selectview使用视图的权限 unlimitedtablespace对表空间大小不加限制的权限updateanytable修改任意表中行的权限updatesnapshot修改快照中行的权限updatetable修改用户表中的行的权限updateview修改视图中行的权限