Oracle learning tips-040720

1. There are two ways to manage the tablespace that stores data: dictionary management and local management. We recommend that you use local management.

2. When a database user is created, the resource permission allows the user to create database objects. Otherwise, the user creates any database
An error is reported when the object is: ORA-01031.

3. There are three options for deleting a primary key:
(1) If a foreign key record that references it exists, it cannot be deleted. This is the default situation (the on Delete clause is not specified.
In this case ).
(2) If a foreign key record that references it exists, delete the primary table record and the corresponding slave Table Record: On Delete cascade.
(3) If a foreign key record that references it exists, delete the primary table record and set the value of the foreign key to NULL: On Delete set null.

4. When creating a table, Oracle automatically creates an index for the primary key and unique constraints, and the index name is automatically generated by the system.

5. The index segment space will not be reused. Therefore, after the index key values are inserted, updated, and deleted multiple times, the space utilization efficiency will change.
Low. Run the following code to check the space usage 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;

If the fragmentation of the leaf row of the index exceeds 10%, rebuild the index:
Alter index idx_name rebuild;

Notes: index_stats only stores the results of the last analyze index... validate structure.

6. When the view reference changes, the view becomes invalid. You need to modify or re-compile the View:
Alter view view_name compile;
A view, also known as a virtual table, does not actually store data. Therefore, it helps increase the query speed and relies on the view when generating reports.
It is not a wise choice. What it can do is to construct some fixed queries to make complex query statements clearer.
The main purpose of a view is to restrict user access to data.

7. synonyms allow access to another user's objects, just like access to their own objects.
The following statement creates a synonym:
-- Grant permissions to another user.
Grant select, update, delete, insert on Table1 to user_ B;
-- Create a synonym
Create synonym Table1 for user_a.table1;
-- User_ B can use the following statement to access user_a.table1:
Select count (*) from Table1;
The premise for creating the synonym is that user_ B does not have an object with the same name as Table1.

If user_a and user_ B are on different hosts, you also need to use a database link that spans different numbers.
Data warehouse server bridge.
-- Create a database link
Create database link dblink_name connect to SERVICE_NAME using user_name
Identified by password;
-- Create a synonym
Create synonym Table1 for user_a.table1 @ dblink_name;

8. The trigger code should not be too long. As a transaction, the entire trigger cannot add commit or rollback to the trigger.
Statement.

9. Data Migration
1>. data migration between SQL Server and Oracle:
Create an ODBC Data Source from Oracle Data Service and perform data processing on the SQL Server server.
Import and export. There may be some automatic data conversion procedures. Please observe.
2>. import data in the format of operating system files to Oracle:
Use the built-in Oracle tool: sqlldr
Sqlldr USERID/password control = controlfile. CTL