Oracle Database Data Object Analysis (on)
The most basic of the Oracle Database Data Objects are tables and views, other constraints, sequences, functions, stored procedures, packages, triggers, and more. The operation of the database can be substantially incorporated into the operation of the data object, understanding and mastering the Oracle database object is a shortcut to learning Oracle. Tables and views Oracle are the basic structure of data storage. Oracle8 introduces partition tables and object tables, Oracle8i introduces a temporary table, making the table more powerful. The view is a logical expression of data in one or more tables. This article we will discuss how to create and manage simple tables and views. The management table table can be regarded as a row and column electronic data table, which is a structure in the relational database. Establish a table with CREATE TABLE statement, while establishing a table, you must define a table name, column, and column data type and size. E.g:
Create Table Products (PROD_ID NUMBER (4), PROD_NAME VAECHAR2 (20), STOCK_QTY NUMBER (5, 3));
In this way, we have established a table named Products, the keyword table, the table name, and then define three columns, and specifies the data type and size of the column. At the same time you create a table, you can specify the integrity constraints of the table, or the integrity constraints can also be specified, and the ordinary constraints on the column are NOT NULL, and the discussion of constraints will be carried out later. You can give a default value when establishing or changing the table. The default is when an increase is increasing, and the Oracle is considered to be the default value when an item is incremented. The following data dictionary views provide information tables and table columns:......... DBA_TABLES DBA_ALL_TABLES USER_TABLES USER_ALL_TABLES ALL_TABLES ALL_ALL_TABLES DBA_TAB_COLUMNS USER_TAB_COLUMNS naming table name identifies a table ALL_TAB_COLUMNS table, it should be as described in the table name Table, Oracle's table name or column name can up to 30 strings. The table name should begin with letters, and you can include numbers, underscores, #, $, and so on in the table name. The establishment of a table from other tables can be determined using a query from a table based on one or more tables, the data type and size of the table are determined by the query. Establishing a query for this form of tables You can select all columns in other tables or select only partial columns. Use keywords as a CREATE TABLE statement, for example:
SQL> CREATE TABLE EMP AS SELECT * from Employeetable Createdsql> Create Table Y as SELECT * from x where no = 2
It should be noted that if the query involves the long data type, CREATE TABLE .... As SELECT ... will not work. Changing Table Definitions After the setup, sometimes we may need to modify the table, such as changing the definition of the column, change the default, add new columns, delete columns, etc. Oracle uses the ALTER TABLE statement to change the definition 1, increasing column grammar:
Alter table [schema.] Table_name add column_definition
example:
ALTER TABLE ORDERS ADD ORDER_DATE DATE; TABLE ALTER
For the already existing data line, the value of the new column will be NULL. 2, more transformed grammar:
ALTER TABLE [Schema.] Table_name Modify Column_name New_Attribute;
Example: ALTER TABLE ORDERS MODITY (Quantity Number (10, 3), Status Varchar2 (15));
In this example, we modify the table orderers, add the length of the Status column to 15, minus the Quantity column to 10, 3; the rules of the modified column are as follows:. You can increase the length of the column of string data type, digital data type column Accuracy. If the length of the column is reduced, the column should not contain any value, all data rows are null. When the data type is changed, the value of the column must be null. For decimal numbers, it can increase or decrease but not to reduce his Accuracy. 3. Delete the data column Optimize the Oracle database, the only way is to delete columns, re-establish the database. There are many ways to delete columns in Oracle8i, you can delete unused data columns or can indicate that the column is not used and then deleted. Deleting the syntax of the data column is:
Alter table [schema.] Table_name Drop {Colum Column_Names | (Cascade Constrains]} [Cascade Constrains]
It should be noted that the index and integrity constraints of the column are also deleted at the same time as the column is deleted. Note Keyword Cascade Constrains, if the deleted column is part of the multi-column constraint, then this constraint is also deleted at the same time relative to other columns. If users are worried that the delete column is spent too much in a large database, they can mark them as unused data columns, and the syntax that is not used is as follows:
Alter table [schema.] Table_name set unused {color column_names | (colorn_names)} [cascade constrains]
This statement is marked by one or more data columns as unused data columns, but does not delete data in the data column, nor free of the occupied disk space. However, unused data is not displayed in the view and data dictionary, and the name of the data column will be deleted, and the new data column can use this name. Based on the index, constraints, statistics, etc. of the data column, will be deleted. Deleting unused data columns is:
Alter Table [Schema.] Table_name drop {unused colum | column contractue} Delete table and change the table name Delete table is very simple, but it is an irreversible behavior. Grammar:
DROP TABLE [SCHEMA] TABLE_NAME [CASCADE CONSTRAINTS] After deleting the table, the index, trigger, permissions, and integrity constraints on the table are also deleted. Oracle cannot delete a view, or other program unit, but Oracle will indicate that they are invalid. If the deleted table involves reference primary key or unique keyword integrity constraint, the DROP TABLE statement must contain the Cascade Constraints substring. Change the table name rename command to be renamed to the table and other database objects. The Oracle system will automatically transfer to the new table based on the integrity constraints, indexes, and permissions of the old table. Oracle simultaneously enables all old-based database objects, such as views, programs, functions, etc. Grammar:
Rename Old_name to new_name; example:
SQL> RENAME ORDERS TO PURCHASE_ORDERS; Table Renamed truncate Truncate command is similar to the drop command, but he is not deleting the entire data table, so index, integrity constraint, trigger, permissions, etc. will not be deleted. The partial table and view space will be released by default. If the user does not want to release the table space, the TRUNCATE statement is included in the TRUNCATE statement. The truncate command syntax is as follows: Truncate {Table | Cluster} [Schema.] Name {drop | reuse storage} example:
SQL> TRUNCATE TABLE T1; TABLE TRUNCATE. Management View View is a simplified description of data in one or more tables, and users can view the view as a storage query or a virtual table. Query is only stored In the Oracle data dictionary, the actual data is not stored in any other place, so the establishment view does not need to consume other spaces. The view can also hide complex queries, such as multi-table queries, but users can only see views. The view can have a column name that is different from the column name of the table based on him. Users can establish a view that restricts other users access. Create a view creation view command to create a view, a query that defines a view can be built on one or more tables, or other views. The query does not have a for Update substring, and the ORDER BY substrs are not supported in the early Oracle8i versions. Now the Create View can have the Order By substring. example:
SQL> CREATE View Top_Emp AS SELECT Empno Employee_ID, ENAME Employee_name, Salary From Empwhere Salry> 2000 User You can change the column name while creating a view, and then add the column name to name after the view name. Reproduction The view needs to include the or replace substring.
SQL> CREATE VIEW TOP_EMP (Employee_ID, Employee_Name, Salary) AS SELECT Empno, ENAME, SALARY FROM EMPWHER SALY> 2000 If the view is included in the created error, the view will not be created. But if you need to create a view with an erroneous view, you must bring the Force option in the CREATE VIEW statement. Such as:
Create Force View ORDER_STATUS AS SELECT * FROM PURCHASE_ORDERSWHER STATUS = 'AppPove'; SQL> / Warning: View Create With Compiration ErroS This will create a view called Order_Status, but the status of such a view is illegal, if the state Variation can be recompiled, and its state is also legitimate. Obtaining data from the view From the view to obtain data from the view, the user can use the view in the connection and subquery, or the SQL function, and strings of all SELECT statements. Insert, update, delete data users can update, insert, and delete data by view update, insert, and delete data through views under certain restrictions. If the view is connected multiple tables, only one table can be updated at a time. All enabled columns can be checked in the data dictionary user_updateTable_columns. The user can use the with substring in the Create View. The with read Only substring represents the viewed view is a read-only view that cannot be updated, inserted, and deleted. The WITH CHECK OPTION indicates that insert and update operations, but should meet the conditions of WHERE skewers. This condition is the condition of creating view WHERE clauses, such as in the above example creates a view top_emp, in which the user cannot insert the Salary less than 2000 data line. Delete View Delete View Use the Drop View command. At the same time, the view definition is deleted from the data dictionary. The view based on the view is also deleted, and other functions, views, programs, etc. involving the view will be considered illegal. Example: DROP View Top_EMP;
Integrity constraint integrity constraints are used to enhance data integrity, Oracle provides five integrity constraints: Check Not Null Unique Primary Foreign Key Integrity Constraint is a rule that does not occupy any database space. Integrity Constraints In the Data Dictionary, use during SQL or PL / SQL. Users can indicate that constraints are enabled or disabled. When the constraint is enabled, he enhances the integrity of the data, otherwise, then, the constraint always exists in the data dictionary. Disable constraints, use ALTER statements
ALTER TABLE TABLE_NAME DISABLE CONSTRAINT CONSTRAINT_NAME
or
ALTER TABLE Policies Disable consTRaint Chk_gender
If you want to re-enable the constraint:
Alter Table Policies Enable construint chk_gender
Delete constraint
Alter Table Table_name Drop Constraint Constraint_name
or
ALTER TABLE Policies Drop Constraint Chk_gender;
Check Constraints On the data column The check constraint requires a special Boolean condition or set the data column to true. The value of at least one data column is NULL, the Check constraint is used to enhance the simple business rules for data content in the table. User Use the Check Constrained Guarantee The consistency heck constraint can involve other data columns of the row of CHECK constraints but cannot involve other rows or other tables, or call functions sysdate, uid, user, useerenv. If the user's business rules need this type of data check, you can use the trigger. Check constraints do not protect the LOB data type data column and objects, nested tables, Varry, Ref, etc. Single data columns can have multiple Check constraints, and a check constraint protects multiple data columns. Creating a table Check Constraint Using the CREATE TABLE statement, changing the constraints of the table use the ALTER TABLE statement. Syntax: constraint [constraint_name] check (condition);
The Check constraint can be created or added to a table constraint. When the Check constraints protect multiple data columns, you must use the table constraint syntax. The constraint name is optional and if this name does not exist, Oracle will generate a unique name starting with SYS_. example:
CREATE TABLE policies (policy_id NUMBER, holder_name VARCHAR2 (40), gender VARCHAR2 (1) constraint chk_gender CHECK (gender in ( 'M', 'F'), marital_status VARCHAR2 (1), date_of_birth DATE, constraint chk_marital CHECK (marital_status in ( 'S', 'm', 'd', 'w')));
NOT NULL Constraint Not Null Constraint Application On a single data column, and his protection data column must have a data value. Under the default situation, Oracle allows any columns to have NULL values. Certain business rules require a data column that must be valued, and the Not Null constraint will ensure that all data rows of the column value. example:
Create Table Policies (Policy_ID Number, Holder_Name Varchar2 (40) Not Null, Gnder Varchar2 (1), Marital_Status Varchar2 (1), DATE_OF_BIRTH DATE NOT NULL);
The ALTER TABLE statement for NOT NULL is slightly different from other constraints.
Alter Table Policies Modify Holder_name Not Null
Unique constraint unique constraints can protect multiple data columns in the table to ensure that the data in any two lines in the protected data column is different. Uniqueness constraints are created together with the table, and after the uniqueness constraint is created, you can use the ALTER TABLE statement to modify. Grammar:
COLUMN_NAME DATA_TYPE CONSTRAINT CONSTRAINT_NAME UNIQUE
If unique constraints protect multiple data columns, uniqueness constraints are increased as a table constraint. The syntax is as follows:
ConsTRAINT CONSTRAINT_NAME (Column) Unique Using Index TableSpace (TableSpace_name) Storage
Uniqueness constraints are enhanced by a B-Tree index, so special features can be used in the USING substring, such as tablespace or storage parameters. The CREATE TABLE statement also creates a unique index to create a uniqueness constraint.
CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vin VARCHAR2 (10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN unique_auto UNIQUE (policy_id, vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)); the user can disable not To be bound, but he still exists, disabling uniqueness constraints using the ALTER TABLE statement
ALTER TABLE INSURED_AUTOS DISABLE CONSTRAIN UNIQUE_NAME
Delete uniqueness constraints, use the ALTER TABLE .... DROP Constrain statement
Alter Table Insured_Autos Drop Constrain Unique_Name
Note that the user cannot delete the uniqueness constraints of the tables with external keys. In this case, the user must first disable or delete the external key. Deleting or disabling uniqueness constraints typically delete the associated unique index, thereby reducing database performance. Regular deletion or disabling uniqueness constraints may result in loss of performance errors. To avoid this error, you can take the following steps: 1. Creating a non-unique index on the unique binding data column. 2. Add a uniqueness key constraint to the only primary key constraint. The primary key of the table can protect one or more columns, and the primary key constraint can work together with the Not Null constraint to each data column. The combination of NOT NULL constraints and unique constraints will ensure that the primary key is uniquely identified each row. Like a unique constraint, the primary key is enhanced by the B-Tree index. Creating primary key constraints Create together with the table if the table has been created, you can use the ALTER TABLE statement.
Create Table Policies (Policy_ID Number CONSTRAINT PK_POLICYES PRIMARY, HOLDER_NAME VARCHAR2 (40), GENDER VARCHAR2 (1), Marital_Status varcha2 (1), DATE_OF_BIRTH DATE
Like unique constraints, if the primary key constrains protects multiple data columns, you must be created as a table constraint.
CREATE TABLE insured_autos (policy_id NUMBER, vin VARCHAR2 (40), coverage_begin DATE, coverage_term NUMBER, CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id, vin) USING INDEX TABLESPACE indexSTORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0));
Disable or delete the primary key must be used with the ALTER TABLE statement
Alter Table Policies Drop Primary Key;
or
ALTER TABLE POLICIES DISABLE PRIMARY Key;
Foreign Key Constraint External Key Constraint Protects one or more data columns to ensure that data of each data row contains one or more NULL values, or at the same manner, both of the protected data columns, both of which have primary key constraints or uniqueness constraints. The reference (primary key or unique constraint) constraint can protect the same table or protect different tables. Unique to the primary key and uniqueness constraints will not imply a B-Tree index. When processing an external key, we often use the childkeeple and sub-table (Child Table), the parent table represents a table that is referenced primary or unique constraints, and the child table represents a table that references primary keys and unique constraints. Creating an external key Using the CREATE TABLE statement, if the table has been established, use the ALTER TABLE statement. CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT policy_fkREFERENCE policies (policy_idON DELETE CASCADE, vin VARCHAR2 (40), coverage_begin DATE, coverage_term NUMBER, make VARCHAR2 (30), model VARCHAR (30), year NUMBER, CONSTRAIN auto_fk FROEIGN KEY (make, model, Year) References Automobiles (Make, Model, Year) On Delete Set Null;
The ON Delete substring tells Oracle If the Parent Record is deleted, what is the child records. By default, it is prohibited to delete the parent record in the absence of subcord. The processing of the NULL value in the data column of the external key and NULL values in the data column of the external key may result in an unpredictable result. Oracle uses ISO Standar Match None rules to enhance external key constraints. This rule stages that if any external key role is included with a NULL value, then any data column that keeps the key is not match in the parent table. For example, in the parent table Automobiles, the primary key acts on the data column make, model, year, the user uses the table inSured_AUTOS with an external constraint to pointomomobiles, note that there is a data row in Insures_autos, a NULL value, this line of data has been Through constraints, even if the MAKE column is not displayed in the parent table Automobiles, as follows: Table 1 Automobiles
Make Model Yearford Taurus2000toyotacamry 1999
Table 2 Insured_AUTOS
Policy_idmake model year576 ford taurus 2000577 Toyotacamry 1999 578 Tucker Null 1949
Deferred constraint checking constraints two cases, one is to test whether the data meets the constraints after each statement, this test is called an immediate constraint test (IMMEDIATELY CHECKING), the other is in the transaction After the processing is completed, the data is checked is called the delay constraint test. In the default, the Oracle Constraint Test is an immediate test, and if the constraint is not satisfied, it will first be an error message, but the user can select the delay constraint inspection through the SET Constraint statement. The syntax is as follows:
Set constraint constraint_name | all defeerred | immediate -;
Sequences Oracle sequence is a continuous digital generator. Sequences are often used in artificial keywords, or sorted to data lines. Otherwise, the data line is disorderly. Like constraints, the sequence exists only in the data dictionary. The serial number can be set to rise, a decrease, and may not limit or reuse until a limit value. Create a sequence Using the set sequence statement. Create Sequence [Schema] Sequence Keyword Keyword includes the value below:
KeyWord Description Start With defines the first number generated by the sequence. The default is a 1Increment BY defined serial number is rising or dropped. For a descending sequence increment BY, the minimum value of the minValue defines the sequence, this is the descending sequence Limit value. By default, this value is NominValue, NominValue, for the maximum number of serial sequences for sequencing to -10E26.maxValue sequences. This is the limit value in the ascending sequence, the default maxValue is NomaxValue, NomaxValue, for ascending to 10E26, for descending order -1. CYCLE Setting Series Values After reaching the restriction value, you can repeat the NOCYCLE Settings The sequence value cannot be repeated after the limit value is reached, which is the default setting. When trying to generate a value of MaxValue 1, an exception Cache defines the inner storage value occupied by the serial value. ?? 0NOCache forces the data dictionary update at each serial number, and ensure that the START WITH value must be equal to or greater than MinValue when the sequence is created between the sequence values. Delete sequence Using DROP SEQUENCE statement
The Drop Sequence Sequence_name Indexes Index is a data structure that improves query performance. In this part we will discuss how indexes improve query performance. Oracle provides the following indexes: B-Tree, Hash, Bitmap, etc. Index Types based on the original table, based on function-based index (Domain) index, actual application, is mainly B-Tree index and Bit map index, so we will focus on these two index types. The B-Tree Index B-Tree Index is the most common index. The index established under default conditions is the index of this type. The B-Tree index can be unique or unique, which can be a single (one column) or connection (multiple columns). The B-TREE index provides the best performance when retrieving a high base data column (the high-centron data column refers to the column has a lot of different values). A more efficient method is provided for removing smaller data B-Tree indexes than full table retrieval. However, when the range exceeds 10% of the table, it cannot improve the performance of the retrieved data. As the name is implied, the B-Tree index is based on binary tree, composed of branch block and leaf block, and branch dry blocks contains index columns (keywords) and another index. address. The leaves include a keyword and a RowID for each match in the table. The bitmap index bitmap index is mainly used for decision support system or static data, and the row-level lock is not supported. The bitmap index can be a simple (single column) or a connection (multiple columns), but most of the most is simple. Bitmap index is preferably used for low to the Cardinal column, where multi-bit map indexes on these columns can be used in conjunction with the AND or OR operator. The bitmap index uses the bitmap as the key value, and each of the data lines in the table contains True (1), false (0), or NULL value. The bitmap of the bitmap index stores in the page node of the B-Tree structure. The B-Tree structure makes the lookup bitmap very convenient and fast. In addition, the bitmap is stored in a compressed format, so the occupied disk space is much smaller than the B-Tree index. Synonyms (Synyms) is an alias for another data object. Public synonyms are for all users, relatively Private synonyms, only for account owners or accounts for authority or authority. Synonyms in the local database can represent tables, views, sequences, programs, functions, or packet and other data objects, can also represent objects of another database via links. Create synonymous syntax as follows: create [public] synynym synonym_name for [Schema.] Object [@db_link]; example:
Create public synynym policies for poladm.policies@prod; create synonym plan_table for system.plan_table;
Procedures and function processes and functions are stored in the database in the database, and the function can have multiple parameters and there is a return value without parameters. The process has zero or more parameters, no return value. Functions and processes can receive or return zero or more values through the parameter list, and the main difference between functions, and processes is not to return values, but their call mode. The process is called: Pay_INVOLUME (INVOICE_NBR, 30, DUE_DATE); function is called in a legal expression: ORDER_VOLUMN: = Open_ORDERS (Sysdate, 30); The syntax of the creation process is as follows:
CREATE [OR REPLACE] PROCEDURE [. Schema] procedure_name [parameter_lister] {AS | IS} declaration_sectionBEGINexecutable_section [EXCEPTIONexception_section] END [procedure_name] The syntax for each parameter is as follows: paramter_name mode datatype [(: = | DEFAULT) value] mode, there are three forms of : IN, OUT, INOUT. In indicates that the value of the actual parameter is passed to the process when the calling process is passed, and the form parameter is considered to be read-only. When the process ends, the control will return to the control environment, and the value of the actual parameter will not change. The value of the actual parameter will be ignored when the process is called, and the internal form parameters can only be assigned, and the data cannot be read from the process, and the content of the formal parameters after the process will be given the actual parameters. This mode is a combination of IN and OUT; the value of the actual parameters within the process will pass to the form parameters, the value of the situation parameters can be written, and the value of the situation parameters will be given the actual parameters after the process is over. The syntax of the creation function is basically the same, the only difference is that the function has a Returen clause.
CREATE [OR REPLACE] FINCTION [schema.] Function_name [parameter_list] RETURN returning_datatype {AS | IS} declaration_sectionBEGINexecutable_section [EXCEPTION] exception_sectionEND [procedure_name]
There must be or more Return statements in the execution part of the function. You can call a single line function and group function in the creation function, for example:
CREATE OR REPLACE FUNCTION my_sin (DegreesIn IN NUMBER) RETURN NUMBERIS pi NUMBER = ACOS (-1); RadiansPerDegree NUMBER; BEGINRadiansPerDegree = pi / 180; RETURN (SIN (DegreesIn * RadiansPerDegree)); END
The bag is a container that is bundled with the process, function, and data structures; the package consists of two parts: external visual packing specification, including function head, process head, and external visual data structure; another part is the main body (Package Body), the cover body contains all the bundled processes and functions, execution, and exception handling parts. Packaged PL / SQL programs and a big difference without packaging, the package data has been existing during the entire session of the user, and when the user gets the execution authorization, it is equal to all programs and data structures in the package specification. Permission. But you can't just authorize only a function or process in the package. The package can be overloaded from the process and function, and multiple programs can be declared in the package, and the correct program is called according to the number of parameters and the data type of the data. Creating a package must first create a package specification, create the syntax of the package specification as follows:
CREATE [OR REPLACE] PACKAGE package_name {AS | IS} public_variable_declarations | public_type_declarations | public_exception_declarations | public_cursor_declarations | function_declarations | procedure_specificationsEND [package_name]
Create a package body Using the CREATE PACKAGE BODY statement:
CREATE [OR REPLACE] PACKAGE BODY package_name {AS | IS} private_variable_declarations | private_type_declarations | private_exception_declarations | private_cursor_declarations | function_declarations | procedure_specificationsEND [package_name] Private data packet structures are those inside the body, for the called program is not visible. The trigger is a program that automatically performs a response database. Can be set to trigger or execute before or after the trigger event. Events that can trigger trigger events include the following: DML event DDL event database event DML event trigger can be a statement or row-level trigger. The DML statement trigger triggers the DML row-level trigger before or after the speech statement is triggered before or after the row changes affecting the statement. Users can define multiple triggers to a single event and type, but there is no way to enhance the command of the multi-trigger trigger. The following table lists the trigger events that users can take advantage of:
Event Trigger Description INSERT Trigger Trigger DELETE Trigger Trigger DELETE Trigger Trigger CREATE When you remove a row from a table or view when you use a CREATE statement to use the CREATE statement to use the CREATE statement when you are inserting a row. Item Increases an object When the trigger ALTER triggers the trigger DROP when using the ALTER statement to change a database or item, trigger the trigger Start when the trigger Start is triggered when using the DROP statement to remove the trigger Start to turn on the trigger, in After the event triggers shutdown to turn off the database, triggering the logon before the event is triggered when a session is established, and the event triggers logoff when the session is turned off. Trigger the trigger when the server is triggered, and the event is triggered.
The syntax for creating a trigger is as follows:
Create [or report] Trigger_name {before | after | instead of} Eventon {Table_OR_VIEW_NAME | DATABASE} [for Each Row [hen condition]] TRIGGER_BODY
Only the DML trigger (INSERT, UPDATE, DELETE) statement can use the INSTEAD OF trip and only table DML triggers can be a Before or an AFTER trigger. Like constraints can be set to disable or enable to turn off or open their actuat (Execute), set the trigger to disable or enable the use of ALTER TRIGGER statements:
Alter Trigger trigger_name enable; alter trigger trigger_name disable;
Use the ALTER TABLE statement to disable or enable the table
Alter Trigger Table_name Disable All Trigger; ALTER TRIGGER TABLE_NAME ENABLE All Trigger;
Delete trigger uses DROP TRIGGER
DROP TRIGGER TRIGGER_NAME;
Data Dictionary Oracle Data Dictionary contains metadata of the user database. With Underline Table Name, with Obj $, UET $, Source $, which is created by SQL.BSQ scripts during the execution of the CREATE DATABASE statement. Under normal circumstances, users rarely access these tables. Script Catalog.sql (usually at $ ORACLE_HOME / RDBMS / Admin) runs immediately after the CREATE DATABASE statement, create a data dictionary view. The data dictionary view can be roughly divided into three categories:. Prefix the data dictionary view of User_, contains information about the object owned by the user. The data dictionary view of the ALL_ contains information that the user is currently accessible. The prefix is a DBA_ 's data dictionary view, which contains information about all objects and permissions owned by the database. There are view families like DBA_Tables, All_Tables, and User_Tables, all of the data dictionary views. There are more than 100 view families in Oracle, so we must fully introduce these views of these views that are monotonous and not much. The most important and most common view families are listed in the table below, and you need to pay attention to each view family has a DBA_, an all_ a USER_ view. View Family Description Col_privs contains column permissions for tables, including grantors, granted people, and permissions EXTENTS data range information, such as data files, data segment names (segment_name), and size indexes index information, such as type, uniqueness And the table IND_COLUMNS index column information, such as the column of the index Objects object information, such as status, and DDL TIMEROLE_PRIVS role, such as the GRANT and Admin Options Segments table and index data segment information, such as TableSpace and Storagesequecnces sequence information For example, Cache, Cycle, and AST_NUMBERSOURCE, in addition to all built-in processes, functions, functions of the trigger, such as reference objects and database links DB_LINKSYS_PRIVS system permissions, such as Grantee, Privilege, Admin Option Tab_Columns Table, The column information of the view, the data type TAB_PRIVS table permissions, such as the grant, the grant, the author, and the permissions TABLES table information, such as tablespace, storage parameters, and data line quantity Triggers trigger information, For example, type, event, trigger, User information, such as temporary and default table space Views view information, including view definitions
There are also some unused data dictionary tables in Oracle, but these tables are not a real dictionary family, they are all important single views.
View Name Description User_col_privs_made User granted the listed permission user_col_privs_recd User Joller USER_TAB_PRIVS_MADE User Grants Other Personal_Tab_Privs_Recd User Get Terms Limits
The main thing in the other dictionary is V $ view, which is called because they all start with V $ or GV $. V $ view is based on X $ virtual view. V $ view is owned by the SYS user. Under the default situation, only SYS users and users with DBA system privileges can see all views, users without DBA privileges can see User_ and all_ view, but not See the DBA_ view. In contrast to DBA_, ALL, and User_ view, these views are visually given information. In large system uptime, manually enter each statement manually enter the statement of the username variable, then enter each username, which takes a few hours to write a SQL statement, generate the needs of the ALTER USER statement, Then do it, it takes only a few minutes to obviously choose the method of generating SQL: SELECT 'ALTER USER' || Username || 'Temporary TableSpace Temp;' from dba_userswhere Username <> 'sys' and temporary_tablespace <> 'Temp';
The result of this query will be processed from a file and then executed:
ALTER USER SYSTEM TEMPORARY TABLESPACE temp; ALTER USER OUTLN TEMPORARY TABLESPACE temp; ALTER USER DBSNMP TEMPORARY TABLESPACE temp; ALTER USER SCOTT TEMPORARY TABLESPACE temp; ALTER USER DEMO TEMPORARY TABLESPACE temp;
