ORACLE DBA常用脚本.docx
- 文档编号:7677085
- 上传时间:2023-01-25
- 格式:DOCX
- 页数:14
- 大小:24.21KB
ORACLE DBA常用脚本.docx
《ORACLE DBA常用脚本.docx》由会员分享,可在线阅读,更多相关《ORACLE DBA常用脚本.docx(14页珍藏版)》请在冰豆网上搜索。
ORACLEDBA常用脚本
ORACLEDBA常用脚本及命令
(一)
1、查看表空间的名称及大小
selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_size
fromdba_tablespacest,dba_data_filesd
wheret.tablespace_name=d.tablespace_name
groupbyt.tablespace_name;
2、查看表空间物理文件的名称及大小
selecttablespace_name,file_id,file_name,
round(bytes/(1024*1024),0)total_space
fromdba_data_files
orderbytablespace_name;
3、查看回滚段名称及大小
selectsegment_name,tablespace_name,r.status,
(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,
max_extents,v.curextCurExtent
Fromdba_rollback_segsr,v$rollstatv
Wherer.segment_id=v.usn(+)
orderbysegment_name;
4、查看控制文件
selectnamefromv$controlfile;
5、查看日志文件
selectmemberfromv$logfile;
6、查看表空间的使用情况
selectsum(bytes)/(1024*1024)asfree_space,tablespace_name
fromdba_free_space
groupbytablespace_name;
SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,
(B.BYTES*100)/A.BYTES"%USED",(C.BYTES*100)/A.BYTES"%FREE"
FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREEC
WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;
7、查看数据库库对象
selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status;
8、查看数据库的版本
SelectversionFROMProduct_component_version
WhereSUBSTR(PRODUCT,1,6)='Oracle';
9、查看数据库的创建日期和归档方式
SelectCreated,Log_Mode,Log_ModeFromV$Database;
10、查看当前所有对象
SQL>select*fromtab;
11、建一个和a表结构一样的空表
SQL>createtablebasselect*fromawhere1=2;
SQL>createtableb(b1,b2,b3)asselecta1,a2,a3fromawhere1=2;
12、察看数据库的大小,和空间使用情况
SQL>coltablespaceformata20
SQL>selectb.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_name,b.file_id,b.bytes
orderbyb.tablespace_name
/
dba_free_space--表空间剩余空间状况
dba_data_files--数据文件空间占用情况
13、查看现有回滚段及其状态
SQL>colsegmentformata30
SQL>SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBACK_SEGS;
14、查看数据文件放置的路径
SQL>colfile_nameformata50
SQL>selecttablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_filesorderbyfile_id;
15、显示当前连接用户
SQL>showuser
16、把SQL*Plus当计算器
SQL>select100*20fromdual;
17、连接字符串
SQL>select列1||列2from表1;
SQL>selectconcat(列1,列2)from表1;
18、查询当前日期
SQL>selectto_char(sysdate,'yyyy-mm-dd,hh24:
mi:
ss')fromdual;
19、用户间复制数据
SQL>copyfromuser1touser2createtable2usingselect*fromtable1;
20、视图中不能使用orderby,但可用groupby代替来达到排序目的
SQL>createviewaasselectb1,b2frombgroupbyb1,b2;
21、通过授权的方式来创建用户
SQL>grantconnect,resourcetotestidentifiedbytest;
SQL>conntest/test
一、ORACLE的表的分类:
1、REGULARTABLE:
普通表,ORACLE推荐的表,使用很方便,人为控制少。
2、PARTITIONEDTABLE:
分区表,人为控制记录的分布,将表的存储空间分为若干独立的分区,记录按一定的规则存储在分区里。
适用于大型的表。
二、建表
1CREATETABLE表名(EMPNONUMBER
(2),NAMEVARCHAR2(20))PCTFREE20PCTUSED50
STORAGE(INITIAL200KNEXT200KMAXEXTENTS200PCTINCREASE0)TABLESPACE表空间名称
[LOGGING|NOLOGGING]所有的对表的操作都要记入REDOLOG,ORACLE建议使用NOLOGGING;
[CACHE|NOCACHE]:
是否将数据按照一定的算法写入内存。
2、关于PCTFREE和PCTUSED
A、行迁移和行链接
B、PCTFREE:
制止INSERT,为 UPDATE留FREE空间
C、PCTUSED:
为恢复INSERT操作,而设定的。
三、拷贝一个已经存在的表:
CREATETABLE新表名STORAGE(。
。
)TABLESPACE表空间
ASSELECT*FROM老表名;
当老表存在约束,触发的时候,不会拷过去。
四、修改表的参数
ALTERTABLE名称PCTFREE20PCTUSED50STOAGE(MAXEXTENTS1000);
五、手工分配空间:
ALTERTABLE名称ALLOCATEEXTENT(SIZE500KDATAFILE'。
。
');
1、SIZE选项,按照NEXT分配
2、表所在表空间与所分配的数据文件所在的表空间必须一样。
六、水线
1、水线定义了表的数据在一个BLOCK中所达到的最高的位置。
2、当有新的记录插入,水线增高
3、当删除记录时,水线不回落
4、减少查询量
七、如何回收空间:
ALTERTABLE名称DEALLOCATEUNUSED[KEEP4[M|K]]
1、当空间分配过大时,可以使用本命令
2、如果没有加KEEP,回收到水线
3、如果水线《MINEXTENTS的大小回收到MINEXTENTS所指定的大小
八、TRUNCATE一个表
TRUNCATETABLE表名,表空间截取MINEXTENT,同时水线重置。
九、DROP一个表
DROPTABLE表名[CASCADECONSTRAINTS]
当一个表含有外键的时候,是不可以直接DROP的,加CASCADECONSRIANTS将外键等约束一并删掉。
十、信息获取
1、dba_object
2dba_tables:
建表的参数
3DBA_SEGMENTS:
组合查询的连接字段:
DBA_TABLES的table_name+dba_ojbect的object_name+dba_segments的SEGMENT_NAME
用户的管理
一、ORACLE的安全域
1、TABLESPACEQUOTAS:
表空间的使用定额
2、DEFAULTTABLESPACE:
默认表空间
3、TEMPORARYTABLESPACE:
指定临时表空间。
4、ACCOUNTLOCKING:
用户锁
5、RESOURCELIMITE:
资源限制
6、DIRECTPRIVILEGES:
直接授权
7、ROLEPRIVILEGES:
角色授权先将应用中的用户划为不同的角色,
二、创建用户时的清单:
1、选择一个用户名称和检验机制:
A,看到用户名,实际操作者是谁,业务中角色。
2、选择合适的表空间:
3、决定定额:
4、口令的选择:
5、临时表空间的选择:
先建立一个临时表空间,然后在分配。
不分配,使用SYSTEM表空间
6、CREATEUSER
7、授权:
A,用户的工作职能
B,用户的级别
三、用户的创建:
1、命令:
CREATEUSER名称IDENTIFIEDBY口令DEFAULTTABLESPACE默认表空间名TEMPOARAY
TABLESPACE临时表空间名
QUOTA15MON表空间名
[PASSWORDEXPIRE]:
当用户第一次登陆到ORACLE,创建时所指定的口令过期失效,强迫用户自己定义一个新口令。
[ACCOUNTLOCK]:
加用户锁
QUOTAUNLIMITEDONTABLESPACE:
不限制,有多少有多少。
[PROFILE名称]:
受PROFILE文件的限制。
四、如何控制用户口令和用户锁
1、强迫用户修改口令:
ALTERUSER名称IDENTIFIEDBY新口令PASSWORDEXPIRE;
2、给用户加锁:
ALTERUSER名称ACCOUNT[LOCK|UNLOCK]
3、注意事项:
A、所有操作对当前连接无效
B、1的操作适用于当用户忘记口令时。
五、更改定额
1、命令:
ALTERUSER名称QUOTA0ON表空间名
ALTERUSER名字QUOTA(数值)K|M|UNLIMITEDON表空间名;
2、使用方法:
A、控制用户数据增长
B、当用户拥有一定的数据,而管理员不想让他在增加新的数据的时候。
C、当将用户定额设为零的时候,用户不能创建新的数据,但原有数据仍可访问。
六、DROP一个USER
1、DROPUSER名称
适合于删除一个新的用户
2、DROPUSER名称CASCADE:
删除一个用户,将用户的表,索引等都删除。
3、对连接中的用户不好用。
七、信息获取:
1、DBA_USERS:
用户名,状态,加锁日期,默认表空间,临时表空间
2、DBA_TS_QUOTAS:
用户名,表空间名,定额。
两个表的连接字段:
USERNAME
GRANTCREATESESSIONTO用户名
PROFILE的管理(资源
文件)
一、PROFILE的管理内容:
1、CPU的时间
2、I/O的使用
3、IDLETIME(空闲时间)
4、CONNECTTIME(连接时间)
5、并发会话数量
6、口令机制:
二、DEFAULTPROFILE:
1、所有的用户创建时都会被指定这个PROFILE
2、DEFAULTPROFILE的内容为空,无限制
三、PROFILE的划分:
1、CALL级LIMITE:
对象是语句:
当该语句资源使用溢出时:
A、该语句终止
B、事物回退
C、SESSION连接保持
2、SESSION级LIMITE:
对象是:
整个会话过程
溢出时:
连接终止
四、如何管理一个PROFILE
1、CREATEPROFILE
2、分配给一个用户
3、象开关一样打开限制。
五、如何创建一个PROFILE:
1、命令:
CREATEPROFILE名称
LIMIT
SESSION_PER_USER2
CPU_PER_SESSION1000
IDLE_TIME60
CONNECT_TIME480
六、限制参数:
1、SESSION级LIMITE:
CPU_PER_SESSION:
定义了每个SESSION占用的CPU的时间:
(1/100秒)
2、SESSION_PER_USER:
每个用户的并发连接数
3、CONNECT_TIME:
一个连接的最长连接时间(分钟)
4、LOGICAL_READS_PER_SESSION:
一次读写的逻辑块的数量
5、CALL级LIMITE
CPU_PER_CALL:
每个语句占用的CPU时间
LOGICAL_READS_PER_CALL:
七、分配给一个用户:
CREATEUSER名称。
。
。
。
。
。
PROFILE名称
ALTERUSER名称PROFILE名称
八、打开资源限制:
1、RESOURCE_LIMT:
资源文件中含有
2、ALTERSYSTEMSETRESOURCE_LIMIT=TRUE;
3、默认不打开
九、修改PROFIE的内容:
1、ALTERPROFILE名称参数 新值
2、对于当前连接修改不生效。
DROP一个PROFILE
1、DROPPROFILE名称
删除一个新的尚未分配给用户的PROFILE,
2、DROPPROFILE名称CASCADE
3、注意事项
A、一旦PROFILE被删除,用户被自动加载DEFAULTPROFILE
B、对于当前连接无影响
C、DEFAULTPROFILE不可以被删除
信息获取:
1、DBA_USERS:
用户名,PROFILE
2、DBA_PROFILES:
PROFILE及各种限制参数的值
每个用户的限制:
PROFILE(关键字段)
PROFILE的口令机制限制
1、限制内容
A、限制连续多少次登录失败,用户被加锁
B、限制口令的生命周期
C、限制口令的使用间隔
2、限制生效的前提:
A、RESOURCE_LIMIT:
=TRUE
BORACLE\RDBMS\ADMIN\UTLPWDMG.SQL
3、如何创建口令机制:
CREATEPROFILE名称
SESSIONS_PER_USER
.....
password_life_time30
failed_log_attempts3
password_reuse_time3
4、参数的含义:
AFAILED_LOGIN_ATTEMPTS:
当连续登陆失败次数达到该参数指定值时,用户加锁
BPASSWORD_LOCK_TIME:
加锁天数
CPASSWORD_LIFE_TIME:
口令的有效期(天)
DPASSWORD_GRACE_TIME:
口令修改的间隔期(天)
EPASSWORD_REUSE_TIME:
口令被修改后原有口令隔多少天被重新使用。
FPASSWORD_REUSE_MAX:
口令被修改后原有口令被修改多少次被重新使用。
rem 这需要统计某个具体用户的"Table,index,column,constraits"
rem================================================================
rem 全部表-列定义table_cols.txt
setlin110pages3000
columntable_nameformata30
columndata_type formata12
columndata_default formata8
columncolumn_nameformata22
columnCidformat 999
columnLenformat 9999
columnPrecformat 99
columnScaleformat 99
select TABLE_NAME,COLUMN_ID"Cid",COLUMN_NAME,DATA_TYPE,DATA_LENGTH"Len",
nvl(DATA_PRECISION,'-1')"Prec",nvl(DATA_SCALE,'-1')"Scale",
NULLABLE, DATA_DEFAULT
from USER_TAB_COLUMNS;
rem========TAB=============
select*fromtab;
spooloff
spooluser_indexes.txt
rem================================================================
rem 全部索引定义user_index.txt
columntable_nameformata22
columnindex_nameformata28
columnindex_typeformata7
columncolumn_nameformata18
column#format 99
columnInitformat 999999;
select a.table_name,t.cache"C",a.index_name,
column_position"#",column_name,
UNIQUENESS,a.INITIAL_EXTENT/1024"Init"
fromuser_indexesa,user_ind_columnsc,user_tablest
where c.INDEX_NAME=a.INDEX_NAME
anda.table_name=t.table_name
orderby a.table_name,a.index_name,column_position;
spooloff
spooluser_Obj_Table_Index.txt
rem================================================================
rem 用户对象,表和索引userObj_Table_Index.txt
setlin111pages333
columntable_nameformata24
columnindex_nameformata32
columntablespace_namea12
columnInitformat 999999;
rem由于用户要关心的是我自己的详细数据的存放位置,下面分别得出index,tables
selecttablespace_name,table_name,cache,initial_extent/1024"Init"
fromuser_tables orderbytablespace_name,table_name;
selecttablespace_name,table_name,index_name,initial_extent/1024"Init"
fromuser_indexes orderbytablespace_name,table_name,index_name;
spooloff
spooluser_constraints.txt
rem================================================================
rem 全部表-列约束_user_constraints.txt
columnCONSTRAINT_NAMEformata30
columnTABLE_NAMEformata30
columnr_CONSTRAINT_NAMEformata20
selectCONSTRAINT_NAME,
CONSTRAINT_TYPE, TABLE_NAME,R_CONSTRAINT_NAME,DELETE_RULE
fromuser_constraints
orderby CONSTRAINT_TYPE,TABLE_NAME;
spooloff
spoolc:
\user_index1rebld.sql
rem================================================================
rem 重建全部索引
remselect'alterindex'||index_name||'rebuild;'fromuser_indexes
rem wheretable_name='GWNEWS';
select'alterindex'||INDEX_NAME||'rebuildtablespaceindexes;'fromuser_indexes;
spooloff
rem@index1rebld.sql
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE DBA常用脚本 DBA 常用 脚本