Oracle FAQ (1)

Oracle FAQ (1)

01. How can I view oracle version information?

02. How can I view the transaction information locked by the system?

03. How to obtain which users are using the database?

04. What is the maximum number of fields in the data table?
The maximum number of columns in a table or view is 1000.
05. How can I find the database SID?
Select name from v $ database;, you can also directly view the init. ora File
06. How do I query table space information?


07. How do I count the total number of records in two tables?


08. How to obtain the current date of the system. If the number of days is greater than 15, 1 is displayed; otherwise, 0 is displayed?

09. What is the difference between drop user and drop user cascade?
Drop user: only deletes users. drop user × cascade: deletes all tables and views under this user name.
10. Cascade deletion is not set in the database table. How to use SQL statements, for example:
The DEPT_NO field in the EMP table is a foreign key.
The DEPT_NO field in the POS table is a foreign key.
The DEPT table has the DEPT_NO field,
How can I delete the EMP table and the related data in the POS table When deleting the data in the DEPT table?
Method 1: trigger Solution

create or replace trigger delete_deptbefore delete on DEPTfor each rowbegin delete from EMP where DEPT_NO = :old.DEPT_NO; delete from POS where DEPT_NO = :old.DEPT_NO;end;

Method 2: Modify the foreign key settings of the slave table to the "on delete cascade" mode.
A) first query the names of foreign keys in the EMP and POS tables (if you already know the foreign key name, this step can be omitted)

 select CONSTRAINT_NAME,TABLE_NAME from user_constraints where CONSTRAINT_TYPE ='R' and TABLE_NAME in('EMP','POS');

B) Delete the Foreign keys in the EMP and POS tables, and re-establish the foreign key mode that allows cascading deletion.

Alter table EMP drop constraint foreign key name; alter table POS drop constraint foreign key name; alter table EMP add constraint foreign key name foreign key (DEPT_NO) references DEPT (DEPT_NO) on delete cascade; alter table POS add constraint foreign key name foreign key (DEPT_NO) references DEPT (DEPT_NO) on delete cascade;