/ * Table * /
The user can only create a table in the tablespace only at the same time as the appropriate system permissions and table space quotas.
Create a table (in your own mode)
If Extent Management Local Autoallocate is specified in the table space, you can only specify the three storage parameters of INITIAL, NEXT, MINEXTENTS in Storage. If the table space is in the table, you cannot specify any Storage clause for the table When the table space is located, Extent Management Dictionary can specify any parameters in the Storage clause of the table.
6 storage parameters can be set in the Storage clause of the CREATE TABLE statement: initial / next / pctincrease / mineXtents / maxExtents / buffer_pool
Create Table Employees (Empno Number (5) Primary Key, ENAME VARCHAR2 (15) Not Null, Age Number (2) Check (age Number (2) Check (agn> = 18 and age <= 65) * / job varchar2 (10), hiredate date default (sysdate), sal number (7,2), deptno number (3) not null, constraint emp_deptno_fk references dept) tablespace userspctfree 20pctused 40storage (initial 50k next 50k maxextents 10 pctincrease 25);
Create a table (in other modes) CREATE TABLE HR.EMPOLYEES (....);
Cache clause: The Nocache clause will be used by default when you create a table, but for a small but often query table, you can specify the cache clause when you create it, and Oracle will put it in the database Cache LRU list.
Create a temporary table Create Global Temporary Table Work_Area (....) on commit delete rows; Create Global Temporary Table Work_Area (....) on commit preserve rows;
On Commit Delete Rows: Transaction Level, Oracle will delete all records in the Temporary table when you submit a transaction, delete all records of the on commit preserve rows: session level, Oracle will only delete all records in the temporary table when the session is aborted.
Modification table
Table is created, some attributes cannot be modified, as the initial and minexts parameters set in the Storage clause are not modified.
Add Field ALTER TABLE Employees Add (Age Number (2));
Modify the field parameter (the modified column does not have data) ALTER TABLE Employees Modify (age Number (3))
Modify Field name ALTER TABLE TABLE_NAME RENAME Column Column1 To Column2 is only used in 9i or later
Delete a field ALTER TABLE Employees Drop Column Age Deletes Multiple Fields ALTER TABLE Employees Drop (Age, SAL)
Marked fields as unused state ALTER TABLE Employees Set Unused (Sal, Comm)
Delete Unused Fields ALTER TABLE Employees Drop Unused (Sal, Comm)
Modify the table parameter setting (after the table creation, you cannot modify the INITIAL and MINEXTENTS two parameters) ALTER TABLE Employees PctFree 30 Pctused 60 RALTER TABLE Employees Storage (Next 512K Pctincrease 0 MaxExtents Unlimited);
Move tables to other tablespaces (this operation can rebuild the storage structure of the table) ALTER TABLE Employees MoveTablespace UserS02Storage (Initial 128k Next 128k Minexts 1 Pctincrease 0);
You can also move the table to a new data segment of the same table space: Alter Tabel Employees MoveStorage (Initial 20k ...);
Recycling table free space alter table temp_employees deallocate unused;
Remove all records in the table (before deleting the table containing the primary code, you must first disable the external code of the primary code in other tables) delete from Employees; Truncate Table Employees DELETE.
Delete Table / All External Code Constraints DROP TABLE EMPLOYEES;
If you contain the primary code or unique code that is referenced by the other tables in the table to be deleted, and you want to delete the related external code constraints in other tables while deleting this table, you can specify a CASCADE DROP TABLE Employees Cascade Constraints
Analysis Table Verified the storage structure (with the Validate Structure clause), check if there is a bad block, Oracle inserts the table contains the recorded RowID in the table to the Invalid_Rows table) Analyze Table Employees Validate Structure
(Need to create an invalid_rows table: SQL> @i: /oracle/ora90/rdbms/admin/utlvalid.sql;)
Statistical information collection form (can obtain statistical information analyzed by querying user_table / all_table / dba_table data dictionary) analyze table employees compute statistics (full table) analyze table employees estimate statistics sample 200 rows (partial data) analyze table employees estimate statistics Sample 20 percent (partial data)
Renown table (Note What objects should be queried to depend on the table, because these objects will be in Invalid status) Rename Employees to EMP
Information Table data dictionary all_tables / dba_tables / user_tables table information all_tab_columns / dba_tab_columns / user_tab_columns table / view field information all_objects / dba_objects / user_objects all modes of objects, including the creation date all_extents / dba_extents / user_extents allocation table region information (region belongs Table space, including blocks, size of the area)
/ * View * /
Only the definition of the view is saved in the database, and the view does not occupy the actual storage space.
Using views can achieve the following purposes: * Provide data security guarantees * Hidden data complexity * Simplified query statements * Separate applications and basic tables * Execute some queries that must be used * Save complex query
Fixed view: If you do not include the following structure in the defined query in the connection view, this connection view is updatable: * Collector (Union, InterSect, Minus, etc.) * Distinct keyword * Group by, Order By, Connect BY or START with Sub-query * Packet functions You can perform Insert, Update, DELETE operations for non-connection views and updatable connection views. Oracle will automatically determine which views can be updated, in data dictionary view all / dba / user_updata_columns Which fields in the view are recorded in the view.
Access to the view depends on access to the base object of the view, and the owner of the view must obtain access to the base object, and cannot obtain these privileges through the role.
Create a view: create view sales_staff as select Empno, ename, Deptno from Emp where deptno = 10with check option constraint sales_staff_cnst;
If the WITH CHECK OPTION clause is specified when the view is created (actually created a constraint called sales_staff_cnst), the record of the subquery that does not meet the defined view when the view is inserted or UPDATE operation, will not be accepted by the view. , Such as: INSERT INTO SALES_STAFF VALUES (7584, 'Oster', 10) - Success Insert INTO SALES_STAFF VALUES (7591, 'Williams', 30) - Failure, because the deptno field value 30, does not conform to the sub-Sales_staff view Query requirements
If you do not want to modify the base table through the view, you can specify the use read only clause: create view sales_staff as select Empno, Ename, Deptno from Emp where deptno = 10with read only;
Redefile view (all objects depending on the view will become Invalid status): Create or Replace View Sales_staff Asselect Empno, ENAME, Deptno from Emp where deptno = 10with Check option constraint sales_staff_cnst;
Delete view: Drop View Sales_staff;
View data dictionary: ALL_VIEWS / DBA_VIEWS / USER_VIEWS view description information
