Oracle语句.docx
《Oracle语句.docx》由会员分享，可在线阅读，更多相关《Oracle语句.docx（71页珍藏版）》请在冰豆网上搜索。
Oracle语句
system默认:
manager
sys默认:
change_on_install
使用SQLPlus登录数据库时，system使用密码manager可直接登录。
但如果是sys用户，密码必须加上assysdba，即完整密码为：
change_on_installassysdba
Oracle误操作后，怎么恢复？
1.查询误操作语句执行的时间点
例如执行了update操作，如果查询全部的，可以把where条件去掉。
selectr.FIRST_LOAD_TIME,r.*fromv$sqlarearwhereSQL_TEXTlike'update%'orderbyr.FIRST_LOAD_TIMEdesc;
2.新建一张恢复表。
例如对表TB_PP_ORDER_ITEMS误操作update后，恢复。
新建一张TB_PP_ORDER_ITEMS_recove表恢复原来的TB_PP_ORDER_ITEMS
createtableTB_PP_ORDER_ITEMS_recoveasselect*fromTB_PP_ORDER_ITEMSasoftimestampto_timestamp（'2015-12-10/15:
34:
04','yyyy-mm-ddhh24:
mi:
ss'）;
Oracle单独恢复一张表数据（如果是exp的dmp文件，先droptable表，因为imp没有replace参数）
impppys/ppysfile=/home/oracle/20151225.dmptables=tb_pp_member_cart;
查看表空间使用情况
SELECTUPPER（F.TABLESPACE_NAME）"表空间名",
D.TOT_GROOTTE_MB"表空间大小（M）",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已使用空间（M）",
TO_CHAR（ROUND（（D.TOT_GROOTTE_MB-F.TOTAL_BYTES）/D.TOT_GROOTTE_MB*100,2）,'990.99'）||'%'"使用比",
F.TOTAL_BYTES"空闲空间（M）",
F.MAX_BYTES"最大块（M）"
FROM（SELECTTABLESPACE_NAME,
ROUND（SUM（BYTES）/（1024*1024）,2）TOTAL_BYTES,
ROUND（MAX（BYTES）/（1024*1024）,2）MAX_BYTES
FROMSYS.DBA_FREE_SPACE
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
ORDERBY1;
查看临时表空间使用情况
SELECTa.tablespace_name,a.BYTEStotal,a.bytes-nvl（b.bytes,0）free FROM（SELECT tablespace_name,SUM（bytes）bytesFROMdba_temp_filesGROUPBYtablespace_name）a,（SELECT tablespace_name,SUM（bytes_cached）bytesFROMv$temp_extent_poolGROUPBYtablespace_name）bWHEREa.tablespace_name=b.tablespace_name（+）;
或者：
selectTABLESPACE_NAME,BYTES_USED/1024/1024used_MB,BYTES_FREE/1024/1024free_MBfromV$TEMP_SPACE_HEADER;
修改密码
运行cmd命令行
sqlplus/nolog 无用户名登录
conn/assysdba 连接到数据本地数据
alterusersystemidentifiedbypassword; 修改System密码 为password
alteruseruser01identifiedbyuser10;
alterusersystemidentifiedbysystem;
解锁方法
alterusersystemaccountunlock;
oracle创建表空间
1、
createtemporarytablespaceuser_temptempfile'D:
\oracle\product\Data\user_temp.dbf'size50mautoextendonnext50mmaxsize20480mextentmanagementlocal;
2、
createtablespaceuser_dataloggingdatafile'D:
\oracle\product\Data\user_data.dbf'size50mautoextendonnext50mmaxsize20480mextentmanagementlocal;
3、
createuserbelstargzidentifiedbybelstargzdefaulttablespaceuser_datatemporarytablespaceuser_temp;
4、
grantconnect,resource,dbatobelstargz;
1、查询oracle的连接数
select count（*） from v$session;
2、查询oracle的并发连接数
select count（*） from v$session where status='ACTIVE';
3、查看不同用户的连接数
select username,count（username） from v$session where username is not null group by username;
建表
createtableCONTACTS（IDNUMBER（10）notnull,NAMEVARCHAR2（20）notnull,PHONENUMBER（15）,ADRRESSVARCHAR2（100））;
修改表名称
altertableCONTACTSrenametoMYTestTB;
修改列名称
altertableMYTestTBrenamecolumnADRRESStoaddress;
添加字段
altertable表名add（字段名类型）;
altertableMYTestTBadd（emailvarchar2（100））;
删除字段
altertable表名dropcolumn字段名;
altertableMYTestTBdropcolumnemail;
修改列属性
altertable表名modify（字段名新属性）;
altertableMYTestTBmodify（NAMEVARCHAR2（100））;
添加主键
altertable表名addconstraint主键名primarykey（column1,column2,....,column）
altertableMYTestTBaddconstraintPK_MYTestTBprimarykey（ID）;
注意：
这里的主键名是自己定义的一个字符串，可以不是表中字段名（习惯写成：
PK_表名的格式,oracle中自动建立的
主键名是PK_表名的格式），不过要牢记啊，删除的时候用到的也是这个名！
括号中的才是表中存在的字段。
也可以在建表的时候设置
idnumber（6）primarykey,
查看表结构
Desc表名
删除表
droptableMYTestTB;
指令会将表放到回收站里用flashbacktable"BIN$1Oiy3qm/QJubov1BwBUOgw==$0" to before drop;
就能恢复。
droptableMYTestTBpurge;是绕过回收站，彻底删除
删除数据
DELETE （删除数据表里记录的语句）
DELETE FROM表名 WHERE 条件;
注意：
删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.
如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
TRUNCATE TABLE 表名;
此操作不可回退.
远程登录Oracle
/nolog
conngdcpms/Clic1234@10.112.1.2:
10100/gdcpms
oracle如何知道当前装载的数据库是哪个？
selectnamefromv$database;
那如何知道当前操作的表空间是那个？
selectnamefromv$tablespace;
查看实例名
SQL>selectinstance_namefromv$instance;
查看数据文件:
select*fromv$datafile;
查看用户的临时表空间
selecttablespace_name,file_name,bytes/1024/1024file_size,autoextensiblefromdba_temp_files;
--tablespace_name表空间名字
--file_name表空间存放地址
--bytes/1024/1024file_size表空间大小
--autoextensible是否自动扩展
;
查看tablespace和datafiles之间的对应关系（通过表空间的号连接在一起）：
selectstatus,t1.name,t2.namefromv$tablespacet1,v$datafilet2wheret1.ts#=t2.ts#;
查看当前用户的用户名和默认表空间：
SQL>selectusername,default_tablespacefromuser_users;
selectusername,DEFAULT_TABLESPACEfromdba_users;
更改表空间
altertablespaceusers
2adddatafile'E:
\ORACLE\ORADATA\XINER\USERS02.DBF'size10m;
如何创建表空间：
（[]均表示可选）
createtablespace：
创建表空间
tablecontrols：
表空间名称
datafile：
默认文件位置
size：
表空间大小
autoextendonnext100M：
自动扩展表空间100M，当原始空间使用完时
maxsize unlimited：
无限制大小
loggingonlinepermanent：
永久在线记录
SQL>createtablespaceice
2datafile'e:
\oracle\oradata\xiner\ice.dbf'size5m
3extentmanagementdictionary
4defaultstorage（
5initial100k
6next100k
7pctincrease10）
8offline;
表空间已创建。
创建用户
createuseruser01identifiedbyu01;
查询用户对话
SQL>selectusername,serial#,sidfromv$session;
登录一个数据库
Sqlplus帐号/密码@数据库实例名assysdba
1.查看所有用户：
select*fromdba_users;
select*fromall_users;
select*fromuser_users;
2.查看用户或角色系统权限（直接赋值给用户或角色的系统权限）：
select*fromdba_sys_privs;
select*fromuser_sys_privs;（查看当前用户所拥有的权限）
3.查看角色（只能查看登陆用户拥有的角色）所包含的权限
sql>select*fromrole_sys_privs;
4.查看用户对象权限：
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
5.查看所有角色：
select*fromdba_roles;
6.查看用户或角色所拥有的角色：
select*fromdba_role_privs;
select*fromuser_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限（查询时需要相应权限）
select*fromV$PWFILE_USERS
8.SqlPlus中查看一个用户所拥有权限
SQL>select*fromdba_sys_privswheregrantee='username';其中的username即用户名要大写才行。
比如：
SQL>select*fromdba_sys_privswheregrantee='TOM';
9、Oracle删除指定用户所有表的方法
select'Droptable'||table_name||';'fromall_tableswhereowner='要删除的用户名（注意要大写）';
10、删除用户
dropuseruser_namecascade;如：
dropuserSMCHANNELCASCADE
DROPTABLESPACE表空间名INCLUDINGCONTENTSANDDATAFILES;//删除表空间
11、获取当前用户下所有的表：
selecttable_namefromuser_tables;
12、删除某用户下所有的表数据:
select'truncatetable '||table_namefromuser_tables;
13、禁止外键ORACLE数据库中的外键约束名都在表user_constraints中可以查到。
其中constraint_type='R'表示是外键约束。
启用外键约束的命令为：
altertabletable_nameenableconstraintconstraint_name
禁用外键约束的命令为：
altertabletable_namedisableconstraintconstraint_name
然后再用SQL查出数据库中所以外键的约束名：
select'altertable'||table_name||'enableconstraint'||constraint_name||';'fromuser_constraintswhereconstraint_type='R'
select'altertable'||table_name||'disableconstraint'||constraint_name||';'fromuser_constraintswhereconstraint_type='R'
14、赋予用户dba权限；
SQL>grantdbatotest_u01;
15、查看当前用户拥有的所有角色
SQL>SELECT*FROMUSER_ROLE_PRIVS;
16、查看当前用户所有权限
SQL>select*fromsession_privs;
17、查看用默认表空间
selectusername,default_tablespacefromuser_users;
selectusername,default_tablespacefromuser_userswhereusername='USER01';
18.查看数据库所有角色。
select*fromdba_roles;
19、查看所有用户所有角色。
可查询其他用户拥有的角色
select*fromdba_role_privs;
select*fromdba_role_privswhereGRANTEE='OPERATE';//OPERATE用户的所有角色
select*fromdba_role_privswhereGRANTEE='user01';
20、查询某个角色所具有的系统权限
select*fromdba_sys_privswheregrantee='角色名'
21、查询某个用户所具有的系统权限
SQL>selectprivilegefromdba_sys_privswheregrantee='MALL'
2union
3selectprivilegefromdba_sys_privswheregranteein（selectgranted_rolefromdba_role_privswheregrantee='MALL'）;
22、执行oracle脚本（.sql文件）
Sqlplus/assysdba;
Connuser/user;
@/oradata/aaa.sql;
23查询数据库数据量：
查数据库占空间大小
selectsum（bytes）/1024/1024/1024GBfromdba_data_files;
wheretablespace_name='RPT_MRT';
2.查数据库实际的数据大小
selectsum（bytes）/1024/1024/1024GBfromdba_segments;
selectowner,tablespace_name,sum（bytes）/1024/1024/1024fromdba_segmentsgroupbyowner,tablespace_name;
3.查除了系统用户所有的用户占的实际大小
selectround（sum（bytes）/1024/1024/1024,2）||'G'fromdba_segmentswhereownernotin（'MDSYS','OUTLN','CTXSYS','OLAPSYS','HR','SYSTEM','EXFSYS','SCOTT','DBSNMP','ORDSYS','SYSMAN','OE','PM','SH','XDB','ORDDATA','IX','SYS','WMSYS'）;
4.查询某个用户实际数据量大小
selectround（sum（bytes）/1024/1024/1024,2）||'G'fromdba_segmentswhereowner='HKMALL';
round函数为四舍五入
5.查询用户的表的大小。
SQL>selectsum（bytes）/（1024*1024）as"size（M）"fromuser_segments;
SQL>selectsum（bytes）/（1024*1024）as"size（M）"fromuser_segmentswheresegment_name=upper（'&table_name'）;
查询所有的表的大小
selectSEGMENT_NAME,TABLESPACE_NAME,sum（BYTES/1024/1024）||'M'fromUSER_extentswhereSEGMENT_TYPE='TABLE'groupbySEGMENT_NAME,TABLESPACE_NAME
6.查询用户下所有的表
select*fromuser_tables;
24.只导出表结构，不导出数据
exp dzjc/hldgajjzd@zhpt rows=n file=f:
\dzjc.dmp owner=（dzjc）
25.查询一条数据
select * from xxx where rownum <= 1
26、查看临时表空间
selecttablespace_name,file_name,bytes/1024/1024file_size,autoextensiblefromdba_temp_files;
给用户授权建立视图
SQL>grantcreateanyviewtopetl;
Plsql插入中文乱码：
1.查询数据库段字符集：
selectuserenv（'language'）fromdual;AMERICAN_AMERICA.ZHS16GBK
2.或者select * from v$nls_parameters;看NLS_CHARACTERSET是否等于ZHS16GBK
3.以上ok，设置客户端字符集：
右键 ＂我的电脑＂，在＂属性＂－－＂设置＂－－＂环境变量＂－－＂系统变量＂中增加一项，其中变量为为NLS_LANG ，变量值为AMERICAN_AMERICA.ZHS16GBK．
4.重启plsql，再插入。
。
。
。
解决
Plsql插入的内容在服务器端查询不到。
。
。
原来plsql端插入语句后要执行：
commit
使用expdp导出表的分区数据：
expdp user_name/passworddirectory=dmpdirdumpfile=test.dmptables=table_name:
partition_anme,table_name:
partition_name;
如果需要压缩加上压缩命令：
expdp user_name/passworddirectory=dmpdirdumpfile=test.dmptables=table_name:
partition_anme,table_name:
partition_namecompression=all;
select*fromuser_segments
升级会员 