网站策划培训,写微信小程序需要什么软件,wordpress收录排名插件,连云港网站建设哪家好1.曾经不小心把开发库的数据库表全部删除#xff0c;当时吓的要死。结果找到下面的语句恢复到了1个小时之前的数据#xff01;很简单。 注意使用管理员登录系统#xff1a; select * from 表名 as of timestamp sysdate-1/12 //查询两个小时前的某表数据#xff01;既然两… 1.曾经不小心把开发库的数据库表全部删除当时吓的要死。结果找到下面的语句恢复到了1个小时之前的数据很简单。 注意使用管理员登录系统 select * from 表名 as of timestamp sysdate-1/12 //查询两个小时前的某表数据既然两小时以前的数据都得到了继续怎么做知道了吧。。 如果drop了表怎么办见下面 drop table 表名; 数据库误删除表之后恢复,不过要记得删除了哪些表名。 flashback table 表名 to before drop; 2.查询得到当前数据库中锁以及解锁 查锁 SELECT s.username, decode(l.type,TM,TABLE LOCK, TX,ROW LOCK, NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid s.sid AND l.id1 o.object_id() AND s.username is NOT NULL; 解锁 alter system kill session sid,serial; 如果解不了。直接倒os下kill进程kill 9 spid ORA-28000:账户被锁定 因为密码输入错误多次用户自动被锁定. 解决办法:alter user user_name account unlock; 3.关于查询数据库用户权限的相关语句 查看所有用户 select * from dba_user; select * from all_users; select * from user_users; 查看用户系统权限 select * from dba_sys_privs; select * from all_sys_privs; 10.select * from user_sys_privs; 查看用户对象权限 select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; 查看所有角色 20.select * from dba_roles; 查看用户所拥有的角色 select * from dba_role_privs; select * from user_role_privs; 几个经常用到的oracle视图注意表名使用大写.................... 查询oracle中所有用户信息 select * from dba_user; 只查询用户和密码 select username,password from dba_users; 查询当前用户信息 select * from dba_ustats; 查询用户可以访问的视图文本 select * from dba_varrays; 查询数据库中所有视图的文本 select * from dba_views; 查询全部索引 select * from user_indexes; 查询全部表格 select * from user_tables; 查询全部约束 select * from user_constraints; 查询全部对象 select * from user_objects; 查看当前数据库中正在执行的语句然后可以继续做很多很多事情例如查询执行计划等等 (1).查看相关进程在数据库中的会话 Select a.sid,a.serial#,a.program, a.status , substr(a.machine,1,20), a.terminal,b.spid from v$session a, v$process b where a.paddrb.addr and b.spid spid; (2).查看数据库中被锁住的对象和相关会话 select a.sid,a.serial#,a.username,a.program, c.owner, c.object_name from v$session a, v$locked_object b, all_objects c where a.sidb.session_id and c.object_id b.object_id; (3).查看相关会话正在执行的SQL select sql_text from v$sqlarea where address ( select sql_address from v$session where sid sid ); (1).查看相关进程在数据库中的会话 Select a.sid,a.serial#,a.program, a.status , substr(a.machine,1,20), a.terminal,b.spid from v$session a, v$process b where a.paddrb.addr and b.spid spid; (2).查看数据库中被锁住的对象和相关会话 select a.sid,a.serial#,a.username,a.program, c.owner, c.object_name from v$session a, v$locked_object b, all_objects c where a.sidb.session_id and c.object_id b.object_id; (3).查看相关会话正在执行的SQL select sql_text from v$sqlarea where address ( select sql_address from v$session where sid sid ); 查询表的结构表名大写 select t.COLUMN_NAME, t.DATA_TYPE, nvl(t.DATA_PRECISION, t.DATA_LENGTH), nvl(T.DATA_SCALE, 0), c.comments from all_tab_columns t, user_col_comments c whEre t.TABLE_NAME c.table_name and t.COLUMN_NAME c.column_name and t.TABLE_NAME UPPER(OM_EMPLOYEE_T) order by t.COLUMN_ID 行列互换 Sql代码 建立一个例子表 CREATE TABLE t_col_row( ID INT, c1 VARCHAR2(10), c2 VARCHAR2(10), c3 VARCHAR2(10)); INSERT INTO t_col_row VALUES (1, v11, v21, v31); INSERT INTO t_col_row VALUES (2, v12, v22, NULL); INSERT INTO t_col_row VALUES (3, v13, NULL, v33); INSERT INTO t_col_row VALUES (4, NULL, v24, v34); INSERT INTO t_col_row VALUES (5, v15, NULL, NULL); INSERT INTO t_col_row VALUES (6, NULL, NULL, v35); INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); COMMIT; 下面的是列转行:创建了一个视图 CREATE view v_row_col AS SELECT id, c1 cn, c1 cv FROM t_col_row UNION ALL SELECT id, c2 cn, c2 cv FROM t_col_row UNION ALL SELECT id, c3 cn, c3 cv FROM t_col_row; 下面是创建了没有空值的一个竖表: CREATE view v_row_col_notnull AS SELECT id, c1 cn, c1 cv FROM t_col_row where c1 is not null UNION ALL SELECT id, c2 cn, c2 cv FROM t_col_row where c2 is not null UNION ALL SELECT id, c3 cn, c3 cv FROM t_col_row where c3 is not null; Sql代码 建立一个例子表 CREATE TABLE t_col_row( ID INT, c1 VARCHAR2(10), c2 VARCHAR2(10), c3 VARCHAR2(10)); INSERT INTO t_col_row VALUES (1, v11, v21, v31); INSERT INTO t_col_row VALUES (2, v12, v22, NULL); INSERT INTO t_col_row VALUES (3, v13, NULL, v33); INSERT INTO t_col_row VALUES (4, NULL, v24, v34); INSERT INTO t_col_row VALUES (5, v15, NULL, NULL); INSERT INTO t_col_row VALUES (6, NULL, NULL, v35); INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); COMMIT; 下面的是列转行:创建了一个视图 CREATE view v_row_col AS SELECT id, c1 cn, c1 cv FROM t_col_row UNION ALL SELECT id, c2 cn, c2 cv FROM t_col_row UNION ALL SELECT id, c3 cn, c3 cv FROM t_col_row; 下面是创建了没有空值的一个竖表: CREATE view v_row_col_notnull AS SELECT id, c1 cn, c1 cv FROM t_col_row where c1 is not null UNION ALL SELECT id, c2 cn, c2 cv FROM t_col_row where c2 is not null UNION ALL SELECT id, c3 cn, c3 cv FROM t_col_row where c3 is not null; 建立一个例子表 CREATE TABLE t_col_row( ID INT, c1 VARCHAR2(10), c2 VARCHAR2(10), c3 VARCHAR2(10)); INSERT INTO t_col_row VALUES (1, v11, v21, v31); INSERT INTO t_col_row VALUES (2, v12, v22, NULL); INSERT INTO t_col_row VALUES (3, v13, NULL, v33); INSERT INTO t_col_row VALUES (4, NULL, v24, v34); INSERT INTO t_col_row VALUES (5, v15, NULL, NULL); INSERT INTO t_col_row VALUES (6, NULL, NULL, v35); INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); COMMIT; 下面的是列转行:创建了一个视图 CREATE view v_row_col AS SELECT id, c1 cn, c1 cv FROM t_col_row UNION ALL SELECT id, c2 cn, c2 cv FROM t_col_row UNION ALL SELECT id, c3 cn, c3 cv FROM t_col_row; 下面是创建了没有空值的一个竖表: CREATE view v_row_col_notnull AS SELECT id, c1 cn, c1 cv FROM t_col_row where c1 is not null UNION ALL SELECT id, c2 cn, c2 cv FROM t_col_row where c2 is not null UNION ALL SELECT id, c3 cn, c3 cv FROM t_col_row where c3 is not null; 下面可能是dba经常使用的oracle视图吧。呵呵 Sql代码 示例已知hash_value:3111103299查询sql语句 select * from v$sqltext where hashvalue3111103299 order by piece 查看消耗资源最多的SQL SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets 10000000OR disk_reads 1000000 ORDERBY buffer_gets 100 * disk_reads DESC; 查看某条SQL语句的资源消耗 SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA WHERE hash_Value 228801498AND address hextoraw(CBD8E4B0); 查询sql语句的动态执行计划: 首先使用下面的语句找到语句的在执行计划中的address和hash_code SELECT sql_text, address, hash_value FROM v$sql t where (sql_text like %FUNCTION_T(表名大写!)%) 然后: SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address C00000016BD6D248 AND hash_value 664376056; 查询oracle的版本: select * from v$version; 查询数据库的一些参数: select * from v$parameter 查找你的session信息 SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid userenv(SESSIONID); 当machine已知的情况下查找session SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE terminal pts/tl AND machine rgmdbs1; 查找当前被某个指定session正在运行的sql语句。假设sessionID为100 select b.sql_text from v$session a,v$sqlarea b where a.sql_hashvalueb.hash_value and a.sid100 Sql代码 示例已知hash_value:3111103299查询sql语句 select * from v$sqltext where hashvalue3111103299 order by piece 查看消耗资源最多的SQL SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets 10000000OR disk_reads 1000000 ORDERBY buffer_gets 100 * disk_reads DESC; 查看某条SQL语句的资源消耗 SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA WHERE hash_Value 228801498AND address hextoraw(CBD8E4B0); 查询sql语句的动态执行计划: 首先使用下面的语句找到语句的在执行计划中的address和hash_code SELECT sql_text, address, hash_value FROM v$sql t where (sql_text like %FUNCTION_T(表名大写!)%) 然后: SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address C00000016BD6D248 AND hash_value 664376056; 查询oracle的版本: select * from v$version; 查询数据库的一些参数: select * from v$parameter 查找你的session信息 SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid userenv(SESSIONID); 当machine已知的情况下查找session SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE terminal pts/tl AND machine rgmdbs1; 查找当前被某个指定session正在运行的sql语句。假设sessionID为100 select b.sql_text from v$session a,v$sqlarea b where a.sql_hashvalueb.hash_value and a.sid100 示例已知hash_value:3111103299查询sql语句 select * from v$sqltext where hashvalue3111103299 order by piece 查看消耗资源最多的SQL SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets 10000000OR disk_reads 1000000 ORDERBY buffer_gets 100 * disk_reads DESC; 查看某条SQL语句的资源消耗 SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA WHERE hash_Value 228801498AND address hextoraw(CBD8E4B0); 查询sql语句的动态执行计划: 首先使用下面的语句找到语句的在执行计划中的address和hash_code SELECT sql_text, address, hash_value FROM v$sql t where (sql_text like %FUNCTION_T(表名大写!)%) 然后: SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address C00000016BD6D248 AND hash_value 664376056; 查询oracle的版本: select * from v$version; 查询数据库的一些参数: select * from v$parameter 查找你的session信息 SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid userenv(SESSIONID); 当machine已知的情况下查找session SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE terminal pts/tl AND machine rgmdbs1; 查找当前被某个指定session正在运行的sql语句。假设sessionID为100 select b.sql_text from v$session a,v$sqlarea b where a.sql_hashvalueb.hash_value and a.sid100 树形结构connect by 排序 Sql代码 查询树形的数据结构同时对一层里面的数据进行排序 SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id 100 CONNECT BY PRIOR employee_id manager_id ORDER SIBLINGS BY last_name; 下面是查询结果 LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL ------------------------- ----------- ---------- ---------- King 100 1 Cambrault 148 100 2 Bates 172 148 3 Bloom 169 148 3 Fox 170 148 3 Kumar 173 148 3 Ozer 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103 102 3 Austin 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106 103 4 Errazuriz 147 100 2 Ande 166 147 3 Banda 167 147 3 Sql代码 查询树形的数据结构同时对一层里面的数据进行排序 SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id 100 CONNECT BY PRIOR employee_id manager_id ORDER SIBLINGS BY last_name; 下面是查询结果 LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL ------------------------- ----------- ---------- ---------- King 100 1 Cambrault 148 100 2 Bates 172 148 3 Bloom 169 148 3 Fox 170 148 3 Kumar 173 148 3 Ozer 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103 102 3 Austin 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106 103 4 Errazuriz 147 100 2 Ande 166 147 3 Banda 167 147 3 查询树形的数据结构同时对一层里面的数据进行排序 SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id 100 CONNECT BY PRIOR employee_id manager_id ORDER SIBLINGS BY last_name; 下面是查询结果 LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL ------------------------- ----------- ---------- ---------- King 100 1 Cambrault 148 100 2 Bates 172 148 3 Bloom 169 148 3 Fox 170 148 3 Kumar 173 148 3 Ozer 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103 102 3 Austin 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106 103 4 Errazuriz 147 100 2 Ande 166 147 3 Banda 167 147 3 有时候写多了东西居然还忘记最基本的sql语法下面全部写出来基本的oracle语句都在这里可以找到了。是很基础的语句 Sql代码 在数据字典查询约束的相关信息 SELECT constraint_name, constraint_type,search_condition FROM user_constraints WHERE table_name EMPLOYEES; //这里的表名都是大写 2对表结构进行说明 desc Tablename 3查看用户下面有哪些表 select table_name from user_tables; 4查看约束在那个列上建立 SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name EMPLOYEES; 10结合变量查找相关某个表中约束的相关列名 select constraint_name,column_name from user_cons_columns where table_name tablename 12查询数据字典看中间的元素 SELECT object_name, object_type FROM user_objects WHERE object_name LIKE EMP% OR object_name LIKE DEPT% 14查询对象类型 SELECT DISTINCT object_type FROM user_objects ; 17改变对象名表名视图,序列 rename emp to emp_newTable 18添加表的注释 COMMENT ON TABLE employees IS Employee Information; 20查看视图结构 describe view_name 23在数据字典中查看视图信息 select viewe_name,text from user_views 25查看数据字典中的序列 select * from user_sequences 33得到所有的时区名字信息 select * from v$timezone_names 34显示对时区‘US/Eastern’的时区偏移量 select TZ_OFFSET(US/Eastern) from DUAL--dual英文意思是‘双重的’ 显示当前会话时区中的当前日期和时间: ALTER SESSION SET NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS;--修改显示时间的方式的设置 ALTER SESSION SET TIME_ZONE -5:0;--修改时区 SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;--真正有用的语句 SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间含有时区 SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间不含有时区 35显示数据库时区和会话时区的值 select datimezone,sessiontimezone from dual; 13普通的建表语句 CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); 15使用子查询建立表: CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id 80; 6添加列// alter table EMP add column (dept_id number(7))错误 alter table EMP add (dept_id number(7)); 7删除一列 alter table emp drop column dept_id; 8添加列名同时和约束 alter table EMP add (dept_id number(7) constraint my_emp_dept_id_fk references dept(ID)); 9改变列//注意约束不能够修改 的 alter table dept80 modify(last_name varchar2(30));//这里使用的是modify而不是alter! 24增加一行 insert into table_name values(); 5添加主键: alter Table EMP add constraint my_emp_id_pk primary key (ID); 11添加一个有check约束的新列 alter table EMP add (COMMISSION number(2) constraint emp_commission_ck check(commission0)) 16删除表 drop table emp; 19创建视图 CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id 80; 21删除视图 drop view view_name 22找到工资最高的5个人。top-n分析行内视图 select rownum,employee_id from (select employee_id,salary from employees order by salary desc) where rownum5; 26建立同义词 create synonym 同义词名 for 原来的名字 或者 create public synonym 同义词名 for 原来的名字 27建立序列注意这里并没有出现说是哪个表里面的序列 CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE 28使用序列 insert into dept(ID,NAME) values(DEPT_ID_SEQ.nextval,Administration); 29建立索引//默认就是nonunique索引除非使用了关键字unique CREATE INDEX emp_last_name_idx ON employees(last_name); 30建立用户可能有错详细查看帮助 create user username(用户名) identified by oracle(密码) default tablespace data01(表空间名//默认存在system表空间里面) q
