oracle recover accidentally deleted data, unlock the other sql statement

2011-05-13  来源:本站原创  分类:Database  人气:119 

Note: The database version is 10g, but also applies to most 9i, 9i there is no flashback.

1 have not developed accidentally deleted all the library's database tables was scared to death. The results found the following statement to one hour to restore the data before! Very simple.

Note that the system using the administrator login:

select * from table as of timestamp sysdate-1/12 / / check two hours before a table of data! Since the two hours before the data have been and continue to how to do, know it. .

If you drop a table, how do? ? See below:

drop table table name;
After the restore accidentally deleted the database tables: (absolutely ok, I have done it, sweat), but remember to remove what the table name.
flashback table table to before drop;

2 query to get the current database lock, and unlock:

Check the lock
SELECT / * + rule * / 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;
Unlock
alter system kill session 'sid, serial';
If the solution can not. Kill the process directly under the back os kill -9 spid

ORA-28000: account is locked

Because many users enter the wrong password is automatically locked.

Solution: alter user user_name account unlock;

3 queries on database users, permissions-related statements:

1 View All Users:
select * from dba_user;
select * from all_users;
select * from user_users;

2 View user's system privileges:
select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;

3 See the user object permissions:
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

4. All roles:
select * from dba_roles;

5 See the user has the role:
select * from dba_role_privs;
select * from user_role_privs;

4 Several frequently used oracle view: Note the table name in uppercase ....................

1 query the oracle of all user information
select * from dba_user;
2 only query the user and password
select username, password from dba_users;
3 queries the current user information
select * from dba_ustats;
4 queries users can access the view text
select * from dba_varrays;
5 query text in all views in the database
select * from dba_views;
6. Query all indexes
select * from user_indexes;
All inquiries form
select * from user_tables;
Check all constraints
select * from user_constraints;
All query objects
select * from user_objects;

5. View the current statement being executed in the database, then you can continue to do many, many things, such as query execution plan, etc.

(1) view the process in a database session
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.paddr = b.addr
and b.spid = &spid;

(2) view the objects in the database was locked and the related sessions
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.sid = b.session_id and
c.object_id = b.object_id;

(3) view the session is executing the SQL
select sql_text from v $ sqlarea where address =
(Select sql_address from v $ session where sid = & sid);

6 lookup table structure: table name uppercase! !

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

7 ranks of the exchange:

Example of a table:
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;

The following is a column switch: Creating a view
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;

The following is the creation of a null value is not vertical table:
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;

8 The following may be frequently used oracle dba view it.

1. Example: Known hash_value: 3111103299, query sql statement:
select * from v $ sqltext
where hashvalue = '3111103299 '
order by piece
(2) View the most resource-consuming 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;

3 View of a SQL statement resource consumption:
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V $ SQLAREA
WHERE hash_Value = 228801498AND address = hextoraw ('CBD8E4B0');

4 dynamic sql statement query execution plan:
First, use the following statement to find statements in the implementation of the planned address and hash_code
SELECT sql_text, address, hash_value FROM v $ sql t
where (sql_text like '% FUNCTION_T (table name uppercase !)%')
Then:
SELECT operation, options, object_name, cost FROM v $ sql_plan
WHERE address = 'C00000016BD6D248' AND hash_value = 664376056;

5 queries the oracle version:
select * from v $ version;

6. Some of the parameters to query the database:
select * from v $ parameter

7. Find your session information
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V $ SESSION WHERE audsid = userenv ('SESSIONID');

8 When the machine known case of search session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V $ SESSION
WHERE terminal = 'pts / tl' AND machine = 'rgmdbs1';

9. Find a specific session is currently running sql statements. Assuming sessionID 100
select b.sql_text
from v $ session a, v $ sqlarea b
where a.sql_hashvalue = b.hash_value and a.sid = 100

9. Tree connect by sorting:

Query tree data structure, while the inside layer of the data to sort
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
<SPAN> ORDER SIBLINGS BY last_name; </ SPAN>

Here is the query results
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

10 times more things to write, actually has forgotten the most basic sql syntax, all written out below, the basic oracle statements can be found here. Is a very basic statement!

1 query constraints in the data dictionary information related to:
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints WHERE table_name = 'EMPLOYEES';
/ / Here the table name in capital letters!
Two pairs of table structure description:
desc Tablename
3 What are the table below to view the user
select table_name from user_tables;
4 Check that out on the constraints:
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
10 combination of variables to find the relevant constraints in a table column name associated:
select constraint_name, column_name from user_cons_columns where table_name = '& tablename'
Query the data dictionary to see the middle of 12 elements:
SELECT object_name, object_type
FROM user_objects
WHERE object_name LIKE 'EMP%'
OR object_name LIKE 'DEPT%'
14 queries Object Type:
SELECT DISTINCT object_type FROM user_objects;
17 to change the name of the object: (table name, view, sequence)
rename emp to emp_newTable
18 added the comment:
COMMENT ON TABLE employees IS 'Employee Information';
20 view view structure:
describe view_name
23 views in the data dictionary to view the information:
select viewe_name, text from user_views
25 in the sequence data dictionary view:
select * from user_sequences
33 names of all of the time zone information:
select * from v $ timezone_names
34 shows the time zone 'US / Eastern' timezone offset
select TZ_OFFSET ('US / Eastern') from DUAL - dual English means 'double'
Displays the current session time zone in the current date and time:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24: MI: SS'; - change the time display mode settings
ALTER SESSION SET TIME_ZONE = '-5:0'; - time zone changes
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; - really useful statement!
SELECT CURRENT_TIMESTAMP FROM DUAL; - return time is the current date and time with time zone
SELECT CURRENT_TIMESTAMP FROM DUAL; - return time is the current date and time, without time zone! ! !
35 shows a database session time zone and time zone values:
select datimezone, sessiontimezone from dual;

13 common build table statement:
CREATE TABLE dept
(Deptno NUMBER (2),
dname VARCHAR2 (14),
loc VARCHAR2 (13));
15 set up the table with a subquery:
CREATE TABLE dept80
AS SELECT employee_id, last_name,
salary * 12 ANNSAL,
hire_date FROM employees WHERE department_id = 80;
6 Add line: / / alter table EMP add column (dept_id number (7)); error! !
alter table EMP add (dept_id number (7));
7 Delete one:
alter table emp drop column dept_id;
8 and constraints while adding column names:
alter table EMP add (dept_id number (7)
constraint my_emp_dept_id_fk references dept (ID));
9 to change the column: / / Note constraint can not modify! !
alter table dept80 modify (last_name varchar2 (30 ));// used here is modify not alter!
24 add a line:
insert into table_name values ​​();

5 Add the primary key:
alter Table EMP add constraint my_emp_id_pk primary key (ID);
11 Adding a new column check constraint:
alter table EMP
add (COMMISSION number (2) constraint emp_commission_ck check (commission> 0))
Delete Table 16:
drop table emp;
19 create a view:
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees WHERE department_id = 80;
21 deleted view:
drop view view_name
22 found the highest wages of five individuals. (Top-n analysis) (inline view)
select rownum, employee_id from (select employee_id, salary from
employees order by salary desc)
where rownum <5;
26 establishment of synonyms:
create synonym synonym name for the original name of the synonym name or create public synonym for the original name
27 establishing the sequence: (Note that this does not appear that there is a sequence which table!!)
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE
28 Use the sequence:
insert into dept (ID, NAME) values ​​(DEPT_ID_SEQ.nextval, 'Administration');
29 Indexing: / / default is nonunique index, unless the use of keywords: unique
CREATE INDEX emp_last_name_idx ON employees (last_name);
30 create a user: (may be wrong, detailed view to help)
create user username (username)
identified by oracle (password)
default tablespace data01 (table space name / / default table space exists inside the system)
quota 10M (set size, up to unlimited) on the table space name / / must be allocated a quota!
31 Create a role: create ROLE manager
Assigned role permissions: grant create table, create view to manage
Given user role: grant manager to DENHAAN, KOCHHAR (two users)
32 assign permissions:
GRANT update (department_name, location_id)
ON departments
TO scott, manager;
Recovery rights
REVOKE select, insert
ON departments
FROM scott;
36 from time to extract year, month, day: use the function extract
select extract (year from sysdate) year, extract (month from sysdate),
extract (day from sysdate) from dual;
37 using the function to be a few months after the date: to_yminterval ('01-02 '), said in February with 1, not to the day! !
select hire_date, hire_date + to_yminterval ('01-02 ') as hire_date_new from employees where department_id = 20
How many days after the date received: Date added directly to digital!
select hire_date +3 from employees where department_id = 20
38 is generally a function of time:
MONTHS_BETWEEN ('01-SEP-95 ', '11-JAN-94') - the number of months between two dates, and returns a float
ADD_MONTHS ('11-JAN-94 ', 6) - Add the number of months
NEXT_DAY ('01-SEP-95 ',' FRIDAY ') - next Friday's date
LAST_DAY ('01-FEB-95 ') - the last day of the month!
ROUND (SYSDATE, 'MONTH') - rounded on
ROUND (SYSDATE, 'YEAR') - rounded off in
TRUNC (SYSDATE, 'MONTH') - on stage
TRUNC (SYSDATE, 'YEAR') - cut in
39 group statement: statement and advanced applications:
SELECT department_id, job_id, SUM (salary), COUNT (employee_id) FROM employees
GROUP BY department_id, job_id;
Having to use constraints:
1.group by rollup: combination of column n to get n +1 case
SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id <60 GROUP BY ROLLUP (department_id, job_id);
2.group by cube: get 2 ^ n case
SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id <60 GROUP BY CUBE (department_id, job_id);
3 Use grouping to get a line out the situation in the form, only to return 1 and 0: is empty, then it returns 1, otherwise returns 0 (be careful not backwards!)
SELECT department_id DEPTID, job_id JOB, SUM (salary), GROUPING (department_id) GRP_DEPT, GROUPING (job_id) GRP_JOB
FROM employees WHERE department_id <50 GROUP BY ROLLUP (department_id, job_id);
4.grouping sets: based on a combination of conditions need to be developed
SELECT department_id, job_id, manager_id, avg (salary) FROM employees GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id));
40from the use of sub-query: returns greater than the change in each sector and the sector average wage of employee information
SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, - where is the sub-query below, mainly the return is a set of data!
(SELECT department_id, AVG (salary) salavg FROM employees GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary> b.salavg;
41exists statement used:
SELECT employee_id, last_name, job_id, department_id
FROM employees outer - the following exists inside the SELECT to select it is just a character or figure can be
WHERE EXISTS (SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);
42 powerful with statement:
WITH
dept_costs AS (- defines a temporary table
SELECT d.department_name, SUM (e.salary) AS dept_total - during which defines a temporary column dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name), / * Note that there is a comma * /
avg_cost AS (
SELECT SUM (dept_total) / COUNT (*) AS dept_avg
FROM dept_costs) - Here's the second reference to a temporary table inside the previously defined between temporary tables and columns!
SELECT * FROM dept_costs WHERE dept_total> (SELECT dept_avg FROM avg_cost) ORDER BY department_name; --- the last query used a temporary table in front
43 traverse the tree:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id; - bottom-up traversal of the tree.

44. Update statement
UPDATE employees SET
job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
WHERE first_name | | '' | | last_name = 'Douglas Grant';

UPDATE TABLE (SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1
WHERE p.pno IN (123, 456);

11 import and export dmp file:

imp username / password @ database, ignore = y file = backup file log = D: \ DBtest \ db_bak \ imp.log

exp system / manager @ TEST file = d: \ daochu.dmp full = y

12 large object fields blob: blob field size view:

select dbms_lob.getLength (field name) from table name;

13 The following is a collection of interesting sql statement, maybe what you need:

- Creating a visit during working hours only view
create or replace view newviewemp
as
select * from table name
where exists (select 1 from dual where sysdate> =
to_date (to_char (sysdate, 'yyyy-mm-dd') | | '08: 00:00 ',' yyyy-mm-dd hh24: mi: ss')
and sysdate <
to_date (to_char (sysdate, 'yyyy-mm-dd') | | '18: 00:00 ',' yyyy-mm-dd hh24: mi: ss'))

Article taken from: http://wfly2004.blog.163.com/blog/static/1176427201124359139/

相关文章
  • oracle recover accidentally deleted data, unlock the other sql statement 2011-05-13

    Note: The database version is 10g, but also applies to most 9i, 9i there is no flashback. 1 have not developed accidentally deleted all the library's database tables was scared to death. The results found the following statement to one hour to restor

  • [Summary] oracle restore accidentally deleted data, unlock the other sql statements 2010-04-20

    1. Who do not care development on database table to delete all, was scared to death. We found the following statement to one hour to restore the data before! Very simple. Note that using the administrator login system: select * from table name as of

  • oracle restore accidentally deleted data, unlock the other sql statements 2010-04-27

    1. Have accidentally developed delete all on database table, then threatened to death. We found the following statement to one hour to restore the data before! Very simple. Note that using the administrator login system: select * from table name as o

  • [Summary] oracle restore accidentally deleted data, unlock the other sql statements (switch) 2010-04-28

    Transfer from: http://www.javaeye.com/topic/648858 1. Who do not care development on database table to delete all, was scared to death. We found the following statement to one hour to restore the data before! Very simple. Note that using the administ

  • oracle recover accidentally deleted data 2011-01-08

    Note: The database version is 10g, but also applies to most of 9i, 9i flashback no. 1. Who does not care to develop a database table to delete all the library was scared to death. Results found the following statement to restore the data before 1 hou

  • oracle recover accidentally deleted table and the table update error 2010-09-14

    Today do not accidentally delete a table. Oh to find a way to recover, really simple ah According to the characteristics of ORACLE10G, when I do Drop Table, or delete all time, Oracle will put the deleted data records into a database table or Recycle

  • oracle recover accidentally deleted 2011-06-09

    select text from user_views as of timestamp to_date ('20100805120000 ',' yyyymmddhh24miss') where view_name = 'XXXX'; select * from dba_source as of timestamp to_timestamp ('2011-06-09 21:50 ',' yyyy-mm-dd hh24: mi ') where owner =' ZZY ' REVIEW: In

  • Recover accidentally deleted data Oralce 2010-12-14

    If you do not accidentally delete a table in Oracle database, you can use the SQL statement back to one hour before the data select * from table as of timestamp sysdate-1/12 / / check two hours before a table of data! drop table table name; / / delet

  • ORACLE database recover accidentally deleted 2011-06-02

    Colleagues back at the operation database oracle 10g, way to delete before deleting so that I do not know trancate not SCN (system change number), its English spelling is: System Change Number, it is very important in a database data structure. SCN p

  • Accidentally deleted data recovery 2011-08-19

    Accidentally deleted data recovery Accidentally, delete the wrong, also empty the Recycle Bin, supposed ah? As long as three steps, you can delete and empty the Recycle Bin back your stuff Steps: 1, click the "Start - Run and enter regedit (open the

  • Data acquisition commonly used SQL statement 2011-08-25

    Data acquisition commonly used SQL statement Applied to the same SQL statement will be slightly different in the different databases, the requirements for character variables, the correlation function changes, and the different rules of grammar, etc.

  • oracle accidentally deleted data recovery Xiangjie table 2010-06-29

    1, undo_retention parameter query, modify: With the show parameter undo; command to see the time to set database parameters undo_retention. Displays the following: undo_management string AUTO undo_retention integer 10800 undo_suppress_errors boolean

  • oracle restore accidentally deleted the table and the table update error 2010-09-14

    Today, accidentally deleted a table. Oh to find a resume of the method is really simple ah According to the characteristics of ORACLE10G, when I do a Drop Table, or delete all the time, Oracle will to be deleted data records into a database table or

  • Oralce accidentally deleted data recovery 2010-12-14

    If you do not accidentally delete a table in the Oracle database, you can use the SQL statement back to the data prior to 1 hour select * from table as of timestamp sysdate-1/12 / / query a table for two hours before the data! rop table table name; /

  • SQL2005 accidentally deleted data recovery method 2011-01-06

    As my colleagues wrong job script, resulting in a important last night systems are about 2 million data table is deleted. When found, the case of the morning, the middle of the night (after misoperation) back over the data, yesterday's backup has bee

  • oracle to recover accidentally deleted a table 2010-09-16

    http://blog.csdn.net/lihan6415151528/archive/2010/01/11/5172827.aspx

  • Oracle how to recover accidentally deleted table 2011-01-10

    SELECT * FROM user_recyclebin WHERE original_name LIKE 'FINANCE_%' ORDER BY droptime DESC; flashback TABLE tm_customer_address TO BEFORE drop

  • linux recover accidentally deleted files 2011-03-05

    If it is ext2 Reference http://www.docin.com/p-31938687.html Experiment a bit, it really feasible to take a hard test, such as the sdb2 #mkfs.ext2 /dev/sdb2 #mount /dev/sdb2 /mnt/sdb2 #cd /mnt/sdb2 #vim test i Enter haha :wq Save #rm test #cd .. #umo

  • Retrieve accidentally deleted data by dbms_flashback collection 2011-08-17

    Using DBMS_FLASHBACK to first pay attention to the following items: 1, you first need to flash back to the user to perform matrix package of rights in this flashback, as follows: grant execute on dbms_flashback to scott; 2, the SYS user can not perfo

  • How to recover accidentally deleted files (recycle bin has been emptied) 2010-08-30

    1, click the "Start - Run and enter regedit (open registry) 2, expand: HEKEY--LOCAL--MACHIME/SOFTWARE/microsoft/WINDOWS / CURRENTVERSION / EXPLORER / DESKTOP / NAMESPACE outside left blank click "New" , Select: "Primary Key", and