Oracle表误删、数据误删、默认数据库时间、用户创建、权限分配、DB2锁表

欢迎转发评论
1.超出表空间配额
CREATE TABLE TABLE1(COL1 NUMBER，COL2 NUMBER) STORAGE(MINEXTENTS 4)
以dba权限链接 alter user scott quota 100m on 表空间user01；
2.表空间不足
alter tablespace user01 add datafile ‘d:\demo\user01_2.dbf’ size 10m AUTOEXTEND ON NEXT 10M
3.误删表
drop table emp
flashback table emp to before drop;
4.删除所有表数据
truncate table;会保留表结构
5.对表
alter table emp enable row movement;启动行移动后
commit后可退回
FLASHBACK TABLE EMP TO TIMESTAMP TO_TIMESTAMP(‘2016-12-09 01:05:22’,‘YYYY-MM-DD HH24:MI:SS’)
6.删除数据库实例sid
oradim -delete -sid orcl
7.启动数据库服务
net start oracleserviceorcl
8.以dba方式无密码链接数据库
sqlplus / as sysdba
9.关闭数据库
在dba的权限下输入
10.数据库实例和数据库的关系
数据库是文件的集合，实例是进程和共享内存组成的服务
shutdown
11.查看数据库运行状态
select status from v$instance
12.关闭数据库
shutdowm immediate//直接关闭，回滚所有未提交事务
shutdown normal//等待用户连接断开
13.sys用户下有dual表
dual在oracle中有公共同义词
14.name/password@监听器名称 连接监听器，不加@符号则连接的是本地的实例
15.desc dual查询dual的表结构
16.查看当前用户
select user from dual；
查看系统时间
select sysdate from dual;
17.DML数据操作语言select insert update delete
DDL 数据定义语言 create drop alter truncate
DCL数据库控制语言 grant revoke
18.彻底清空表，数据库中的表不放到回收站
drop table tablename purge;
清除指定表：purge table <table_name>;
清除当前用户的回收站：purge recyclebin;
清除所有用户的回收站：purge dba_recycl ebin;
19.权限授予和收回
grant revoke
20.用户创建
create user username identified by password tablespace 表空间名(default tablespace users)
用户和他下面的所有对象称为scagema
drop user username cascade;
21.解锁用户
alter user username account unlock;//第一次访问的时候输入密码
22.Oracle安装过程中出错
netca dbca进行重新配置https://ls–20161006gev:1158/em/网页去配置数据库
23.sqlplus快捷键
ctrl+c=exit

33.更改数据字段类型
alter table TBL_WORK_DIV modify TOTAL_PAYMENT NUMBER(38,10);

34.查询guid
select rawtohex(sys_guid()) from dual

35.递归
select code from base.org start with code=‘1’ connect by prior code=parent
36.根据rowid删除重复行
select min(rowid) from Table where (col1,col2) in (select col1,col2 from Table group by col1,col2 having count(1) >1);

delete from Table where rowid in(select min(rowid) from Table where (col1,col2) in (select col1,col2 from Table group by col1,col2 having count(1) >1))

36.增加默认时间
ALTER TABLE STATIS.CAPITAL ADD UPDATETIME_INVEST TIMESTAMP DEFAULT CURRENT TIMESTAMP
37.多表联合更新
UPDATE A a SET (a.a1,a.a2)=(SELECT b.b1,b,b2 FROM B b WHERE b.b3=a.a3)

37.DB2查看表空间
select substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB,
sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE
from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_content_type,tbsp_page_size
　　order by 1

38、插入数据库当前时间
values(to_timestamp(‘2012-01-01 12:10:10.10’,‘yyyy-mm-dd hh24:mi:ss.ff’));
insert into db2test(t12,t13,t14) values (current_date,current_Time,current_timestamp);

39、表解锁
CALL SYSPROC.ADMIN_CMD(‘REORG table STATIS.CAPITAL’) ;
select distinct tabschema,tabname from SYSIBMADM.LOCKS_HELD lh
select * from SYSIBMADM.LOCKS_HELD lh

40、DB2查看所有表
select name from sysibm.systables where type=‘T’ and creator=‘DB2ADMIN’