Small Oracle usage

 

I. query tables and fields corresponding to foreign keys

1. Use the following SQL statement to query the table corresponding to the foreign key and the field p represents the primary key R represents the foreign key
Select a. constraint_name, A. table_name, B. constraint_name
From user_constraints A, user_constraints B
Where a. constraint_type = 'R'
And B. constraint_type = 'p' and A. r_constraint_name = B. constraint_name

2. Find the corresponding table and column, delete the foreign key table data based on the foreign key encoding, and then delete the primary table data.

3. display the foreign key table and foreign key constraint name
Select .*
From user_constraints A, user_constraints B
Where a. constraint_type = 'R'
And B. constraint_type = 'P'
And B. table_name = 'table name'
And a. r_constraint_name = B. constraint_name

 

2. If you know the date of employment (start_date), how to know the number of employees in the week (in full English) is displayed in sequence from Monday to Sunday.

Select to_char (sysdate, 'day', 'nls _ date_language = American ') from dual;

Select next_day ('11-Aug-26 ', 'saturday') from dual; -- query the date of the next Saturday

Iii. How to view the package source code in Oracle

Select * From all_source

Where type = 'package body'

And owner = 'dev _ SPs' and name = 'Wo _ validate_ SQL'

Order by name, line

4. Use DBMS to retrieve table creation statements

Select dbms_metadata.get_ddl ('table', 'test _ yixl_t2 ') from dual;

-- 'Test _ yixl_t2 ': The table name must be capitalized.

The result is as follows:

Create Table "apps". "test_yixl_t2"

("T1" varchar2 (10 ),

"T2" varchar2 (10 ),

"T3" varchar2 (10 ),

"T4" varchar2 (10 ),

"T5" varchar2 (10 ),

"T0" Date

) Pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress Logging

Storage (initial 131072 next 131072 minextents 1 maxextents 2147483645

Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

Tablespace "pai_ts_tx_data"

"

You can also execute the following statement to retrieve the custom DDL:

Select dbms_metadata.get_ddl ('type', 'ixl _ table_type ') from dual;

The result is:

Create or replace type "apps". "yixl_table_type" is table of varchar2 (200 );

 

5. The difference between the two dates is several months.

SelectMonths_between(Sysdate, sysdate-180) from dual;

6. escape characters

In the following usage, you can find whether the zdlx field contains the 'job _ o' string

select * from t_zd where zdlx like '%JOB/_O%' escape '/';

select * from t_zd where instr(zdlx,'JOB_O')>0; 

select * from t_zd where regexp_like(zdlx,'JOB_O'); 

 

VII. Recovery Process after drop table

(For detailed syntax instructions, see 《oraclerecycler .doc.)

1. Create Table A and Create Table A (T1 varchar2 (10 ));

Insert into a values ('aaaaaaaaa ');

Commit;

2. Delete table a drop Table

3. Create Table A again (T1 varchar2 (10), T2 number (10 ));

Insert into a values ('bbbbbbbbbbbbbb', 2 );

Commit;

4. If you want to retrieve table A and data created in step 1

(1) first, rename table a created in step 3. The syntax is as follows:

Alter table a Rename to a_bak; -- if the RENAME operation is not performed, an error will be reported during the next (2) restoration.

(2) execute Step 1 to restore the table structure and data of Table:

Flashback Table A to before drop Rename to;

(3) you're done... In this case, the data in select * from a is the data inserted in step 1.

8. get_line and put_line

Set serveroutput on; Declare V_line varchar2 (50 ); I number: = 3; Begin Dbms_output.enable (1000000 ); Dbms_output.put_line ('test line '); Dbms_output.get_line (v_line, I ); Dbms_output.put_line (v_line | ';'); Dbms_output.put_line ('I =' | I | ';'); End; / Result: Test Line; I = 0;

9. Cast usage:

Select to_char (cast (0.567 as decimal (), '0. 000') from dual

10. How to restore a row just deleted

Step 1: Find the operation time for deleting data from (query in the V $ SQL or V $ sqlarea view)
Select R. first_load_time, R. SQL _text, R. optimizer_mode, R. module, R. Action, R. last_active_time
From v $ sqlarea R
Order by R. first_load_time DESC;
Step 2:
Create Table t_table_recove
As
Select * From t_table
As of Timestamp to_timestamp ('2017-06-02 11:36:53 ', 'yyyy-mm-dd hh24: MI: ss ');

To_timestamp ('2017-06-02 11:36:53. 123', 'yyyy-mm-dd hh24: MI: Ss. ff ')

Put the recovered data in the original table.

11. Typical Writing of the last 20 records in the query result set

Select * from (

Select rownum as my_rownum, table_a. * from

(Select * From user_objects where object_type = 'table') table_a

Where rownum <= 160) Where my_rownum> 140;