Oracle Learning Small-040720

zhaozj2021-02-16  78

1. There are two management methods: dictionary management, local management, and recommendation.

2. When creating a database user, Resource Permissions allows the user to create a database object, otherwise it will report an error when the user creates any database object: ORA-01031.

3. For the primary keys, there are three options when deleting: (1) If there is a reference to its foreign key record, it is not allowed to delete, which is the default (not specifying the ON DELETE clause is this). (2) If there is a reference to its foreign key record, remove the primary table record and the corresponding slave table record: On Delete Cascade. (3) If there is a reference to its foreign key record, remove the primary table record, set the end key value to empty: On Delete Set Null

4. When you create a table, for the primary key and the only constraint, Oracle will automatically create an index, and the index name is automatically generated by the system.

5. The index segment space will not be reused. Therefore, after multiple INSERT, UPDATE, DELETE, the key value of the index will be low, and the following code is used to check the space utilization of an index: analyze index idx_name validate STRUCTURE; SELECT name, del_lf_rows, lf_rows, round ((del_lf_rows / (lf_rows 0.0000000001)) * 100) "Freg Percent" FROM index_stats; debris if the index leaf row of more than 10%, considering the index is rebuilt: ALTER iNDEX idx_name Rebuild; Notes: index_stats only stores the result of the last Analyze INDEX ... Validate Structure.

6. The view will become invalid when the viewpoint is changed, and the view will be invalid. The query speed, depending on the report, depending on the view is not a wise choice, which can only construct some fixed queries, making the complex query statement look clear. The most important use of the view is to limit users access to data.

7. Synonyms can enable access to another user, just like accessing their own objects. The following is a statement that creates a synonym: User_a.table1 can be used as the following statement: select count (*) from table1; Prerequisite to create the above synonyms is the object that is not with Table1 in user_b. If User_a and user_b are on different hosts, there is also a link to a database link, which is a bridge across different database servers. - Create a database link CREATE DATABASE LINK DBLINK_NAME CONNECT TO SERVICE_NAME USING USER_NAME IDENTIFIED BY PASSWORD; - create synonyms CREATE SYNONYM TABLE1 FOR USER_A.TABLE1@DBLINK_NAME; 8 trigger code should not be too long, the entire transaction as a trigger, you can not. Add a COMMIT or ROLLBACK statement in the trigger.

9. Data migration 1>. Data migration between SQL Server and Oracle: The Oracle Data Services is built into an ODBC data source, and the data source is imported on the SQL Server server. There may be some automatic data conversion process, this should be taken to observe. 2>. Import the data in the form of the operating system file into Oracle: Tools with Oracle: SqlLDR Sqlldr Userid / Password Control = ControlFile.ctl

转载请注明原文地址:https://www.9cbs.com/read-14989.html

New Post(0)