1.创建用户、赋权限、删除用户
create user test identified by test default test users temporary tablespace temp; grant connect to test ; grant resource to test ; grant dba to test ; drop user test cascade;
2.修改用户密码&解除锁定(需要dba权限)
alter user scott identified by tiger; alter user scott account unlock;
3.查询锁表
select'alter system kill session '''|| c.sid ||''||','|| c.serial# ||''';', a.object_id, a.session_id, b.object_name, c.* from v$locked_object a, dba_objects b, v$session c where a.object_id = b.object_id and a.session_id = c.sid(+) and schemaname ='scott' order by logon_time
4.左右连接去除笛卡尔积
关于左连接和右连接总结性的一句话: 左连接where只影向右表,右连接where只影响左表。 left join select * from tbl1 left join tbl2 where tbl1.id = tbl2.id 左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据。
5.增加新的主键约束
alter table 表名 add constraint 主键名 primary key(字段名); alter table dept add constraint pk_dept primary key(deptno, dname);
6.级联删除外键(删除父表记录时,同时删除子表记录)
alter table 子表 add constraint fk_activity_id foreign key (id) references 父表 (id) on delete cascade;
7.修改表名(表名大小写问题)
alter table "dept" rename to dept;
8.将本用户下全部sequence查询出来,并拼成创建语句
select 'create sequence '||sequence_name|| ' minvalue '||min_value|| ' maxvalue '||max_value|| ' start with '||last_number|| ' increment by '||increment_by|| (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';' from user_sequences
常用sql待续,欢迎留言补充
如对本文有疑问, 点击进行留言回复!!
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句(时间格式、去重、去特殊符号、查询表空间等)
Navicate 如何导出数据库中的存储过程、事件、视图等?
每日一记:Oracle升级2020年4月份的数据库补丁Database Patch Set Update : 11.2.0.4.200414 (30670774)
Oracle 19.3 dataguard for Redhat 7.6
网友评论