ORACLE常用脚本文档格式.docx
《ORACLE常用脚本文档格式.docx》由会员分享，可在线阅读，更多相关《ORACLE常用脚本文档格式.docx（24页珍藏版）》请在冰点文库上搜索。
GROUPBYtablespace_name）f,
（SELECTdd.tablespace_name,round（SUM（dd.bytes）/（1024*1024）,2）Tot_grootte_Mb
FROMsys.dba_data_filesdd
GROUPBYdd.tablespace_name）d
WHEREd.tablespace_name=f.tablespace_name
ORDERBY4DESC
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表结构一样的空表
createtablebasselect*fromawhere1=2;
createtableb（b1,b2,b3）asselecta1,a2,a3fromawhere1=2;
12、察看数据库的大小，和空间使用情况
coltablespaceformata20
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、查看现有回滚段及其状态
colsegmentformata30
SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBACK_SEGS;
14、查看数据文件放置的路径
colfile_nameformata50
selecttablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_filesorderbyfile_id;
15、显示当前连接用户
showuser
16、把SQL*Plus当计算器
select100*20fromdual;
17、连接字符串
select列1||列2from表1;
selectconcat（列1,列2）from表1;
18、查询当前日期
selectto_char（sysdate,'
yyyy-mm-dd,hh24:
mi:
ss'
）fromdual;
19、用户间复制数据
copyfromuser1touser2createtable2usingselect*fromtable1;
20、视图中不能使用orderby，但可用groupby代替来达到排序目的
createviewaasselectb1,b2frombgroupbyb1,b2;
21、通过授权的方式来创建用户
grantconnect,resourcetotestidentifiedbytest;
conntest/test
ORACLE常用脚本
（2）
一、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常用脚本（3）
用户的管理
一、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的操作适用于当用户忘记口令时。
五、更改定额
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：
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不可以被删除
信息获取：
用户名，PROFILE
2、DBA_PROFILES:
PROFILE及各种限制参数的值
每个用户的限制：
PROFILE（关键字段）
PROFILE的口令机制限制
1、限制内容
A、限制连续多少次登录失败，用户被加锁
B、限制口令的生命周期
C、限制口令的使用间隔
2、限制生效的前提：
A、RESOURCE_LIMIT:
=TRUE
BORACLE\RDBMS\ADMIN\UTLPWDMG.SQL
3、如何创建口令机制：
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:
口令被修改后原有口令被修改多少次被重新使用。
ORACLE常用脚本（4）
rem这需要统计某个具体用户的"
Table,index,column,constraits"
rem================================================================
rem全部表-列定义table_cols.txt
setlin110pages3000
columntable_nameformata30
columndata_typeformata12
columndata_defaultformata8
columncolumn_nameformata22
columnCidformat999
columnLenformat9999
columnPrecformat99
columnScaleformat99
selectTABLE_NAME,COLUMN_ID"
Cid"
COLUMN_NAME,DATA_TYPE,DATA_LENGTH"
Len"
nvl（DATA_PRECISION,'
-1'
）"
Prec"
nvl（DATA_SCALE,'
Scale"
NULLABLE,DATA_DEFAULT
fromUSER_TAB_COLUMNS;
rem========TAB=============
select*fromtab;
spooloff
spooluser_indexes.txt
rem全部索引定义user_index.txt
columntable_nameformata22
columnindex_nameformata28
columnindex_typeformata7
columncolumn_nameformata18
column#format99
columnInitformat999999;
selecta.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
wherec.INDEX_NAME=a.INDEX_NAME
anda.table_name=t.table_name
orderbya.table_name,a.index_name,column_position;
spooluser_Obj_Table_Index.txt
rem用户对象，表和索引userObj_Table_Index.txt
setlin111pages333
columntable_nameformata24
columnindex_nameformata32
columntablespace_namea12
rem由于用户要关心的是我自己的详细数据的存放位置，下面分别得出index,tables
selecttablespace_name,table_name,cache,initial_extent/1024"
fromuser_tablesorderbytablespace_name,table_name;
selecttablespace_name,table_name,index_name,initial_extent/1024"
fromuser_indexesorderbytablespace_name,table_name,index_name;
spooluser_constraints.txt
rem全部表-列约束_user_constraints.txt
columnCONSTRAINT_NAMEformata30
columnTABLE_NAMEformata30
columnr_CONSTRAINT_NAMEformata20
selectCONSTRAINT_NAME,
CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,DELETE_RULE
fromuser_constraints
orderbyCONSTRAINT_TYPE,TABLE_NAME;
spoolc:
\user_index1rebld.sql
rem重建全部索引
remselect'
alterindex'
||index_name||'
rebuild;
fromuser_indexes
remwhereta
升级会员 