Oracle442 application scenarios --------- database logic object management application scenarios, database Logic Design

Source: Internet
Author: User

Oracle442 application scenarios --------- database logic object management application scenarios, database Logic Design


Use Case 128: create a temporary table

Create a temporary table temp_goods

Create global temporary table temp_goods
(GoodsId NUMBER,
GoodsNum NUMBER,
Price NUMBER)
On commit delete rows;

Create a temporary tablespace

Create temporary tablespace tbs_t1
TEMPFILE 'tbs _ t1.f' SIZE 50 m REUSE AUTOEXTEND ON
MAXSIZE UNLIMITED
Extent management local uniform size 64 K;

Create temp_goods1 in the tablespace tb_t1;

Create global temporary table temp_goods1
(GoodsId NUMBER,
GoodsNum NUMBER,
Price NUMBER)
ON COMMIT DELETE ROWS
TABLESPACE tbs_t1;

Application Scenario 129: view the system view of table information

COL TABLE_NAME FORMAT A10
COL COLUMN_NAME FORMAT A10
COL DATA_TYPE FORMAT A10
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = 'ployees ';

Application Scenario 130: modify a table in Enterprise Manager

Application Scenario 131 use DESC statements to view table results

The statement for viewing the result of the table employee is as follows:

Desc system. Employee

Scenario 132: Use the alter table statement to add columns to a TABLE

Alter table system. Attributes ADD (Description VARCHAR2 (1000 ));

Use Case 133: Use the alter table statement to modify the column name

Alter table system. lifecycle ments
Rename column Description TO Descript;

Use Case 134: Use the alter table statement to delete a queue

Alter table hrman. lifecycle ments
Rename column Description TO Descript;

Application Scenario 136: Create and verify primary key constraints

Alter table hrman. Attributes ADD (Description varchar2 (1000 ));
Desc hrman. Administrative ments;
Alter table hrman. lifecycle ments
Set unused (Description );
Desc hrman. Administrative ments;
Delete all unavailable columns in the table:
Alter table hrman. lifecycle ments
Drop unused columns;


Application Scenario 137: Create and verify non-empty Constraints

Create Table user2 and specify the username column and userpwd column as non-empty Constraint

Create table hrman. Users2
(UserId NUMBER,
UserName VARCHAR2 (40) not null,
UserPwd VARCHAR2 (40) not null,
CONSTRAINT PK_USERID primary key (UserId)
);

Set the username column in the constraint to not null.

Alter table hrman. Users MODIFY UserName not null;

Use the following statement to set the column to NULL:

Alter table hrman. Users MODIFY UserName NULL;

The username value of the data generator inserted into Table user2 is null:

Insert into hrman. Users2 (UserId, UserPwd) VALUES (1, '123 ');


Application Scenario 138: create unique verification Constraints

Create a unique constraint on uername:

Create table Users3
(UserId Number Primary Key,
UserName Varchar2 (40) not null unique,
UserPwd Varchar2 (40) NOT NULL
);
Create user4 with the same structure as the table user:
Create table hrman. Users4
(UserId number primary key,
UserName VRCHAR2 (40 ),
UserPwd VARCHAR2 (40 ),
CONSTRAINT UK_USERNAME UNIQUE (UserName)
);

Set the unique constraint for the username column of the table user:
Alter table hrman. Users
Add constraint UK_USERNAME1
UNIQUE (UserName );

Insert two records into the table user using insert so that their column values are repeated: username = test-

Insert into hrman. Users VALUES (100, 'test', 'test ');
Insert into hrman. Users VALUES (101, 'test', '20140901 ');


Use Case 139: create verification check Constraints

Define check constraints on the username column:
Create table hrman. Users5
(UserId number primary key,
UserName varchar2 (40 ),
UserPwd varchar2 (40)
CONSTRAINT CK_USERPWD CHECK (LENGTH (UserPwd)> = 6)
);

Use alter to create check constraints:

Alter table hrman. Users
Add constraint CK_USERPWD1 CHECK (LENGTH (UserPwd)> = 6 );

The length of the userpwd column inserted using insert is less than 6:

Insert into hrman. Users VALUES (102, 'user', 'pwd ');

Scenario 140: Create and verify foreign key constraints

Create a foreign key constraint fk_emp_depid for the dep_id column and the dep_id column of the table employee :,

Alter table hrman. Employees
Add constraint FK_EMP_DEPID
Foreign key (Dep_id) references hrman. sums (Dep_id );

Insert a record to the table:

Insert into hrman. Employees (Emp_id, Emp_name, Dep_id)
VALUES (1, 'johnne', 3 );

Application Scenario 141: Set the attribute of the DEFAULT Column

Create Table users6 and set the default value of userpwd to 11111:

Create table hrman. Users6
(UserId Number Primary Key,
UserName Varchar2 (40) not null unique,
UserPwd Varchar2 (40) DEFAULT ('20140901 ')
);

Insert a record to the table:

Insert into hrman. Users6 (UserId, UserName) VALUES (1, 'user ');

Use Case 142: delete a table

Drop table hrman. USERS;

Application Scenario 143: View and manage the view in Enterprise Manager

Use Case 144: Create a view in Enterprise Manager

SELECT e. Emp_Id, e. Emp_Name, d. Dep_Name
From hrman. Employees e, HRMAN. Departments d
WHERE e. Dep_Id = d. Dep_Id

Use Case 145: use create view

Create view v_emp1

Create view hrman. V_EMP
AS
SELECT e. Emp_Id, e. Emp_Name, d. Dep_Name
From hrman. Employees e, HRMAN. Departments d
WHERE e. Dep_Id = d. Dep_Id;

View the user's hrman view and its defined text information:

SELECT VIEW_NAME, text from DBA_VIEWS
Where owner = 'hrman ';


Application Scenario 146: modify a view

Modify view hrman. v_emp1:

Create or replace view hrman. V_EMP
AS
SELECT e. Emp_Id, e. Emp_Name, e. Sex, e. Wage, d. Dep_Name
From hrman. Employees e, HRMAN. Departments d
WHERE e. Dep_Id = d. Dep_Id;

Use Case 147: delete a view

DROP view HR. v_emp1;

Application Scenario 148: index management page in Enterprise Manager

Application Scenario 149: Create an index in Enterprise Manager

Application Scenario 150:

Use the tablespace users to save the index:

Create index hrman. IX_EMPNAME on hrman. EMPLOYEES (EMP_NAME)
TABLESPACE Users;

Is the table HRMAN. Enmployees:

Create index hrman. UQ_IDCARD on hrman. EMPLOYEES (IDCARD)
TABLESPACE Users;

View the views and definition text of hrman:

SELECT INDEX_NAME, TABLE_NAME FROM DBA_INDEXES
Where owner = 'hrman ';

Application Scenario 151: Modify an index

Alter index hrman. IX_EMPNAME UNUSABLE;

Alter index hrman. IX_EMPNAME REBUILD;

Alter index hrman. IX_EMPNAME rename to IX_EMPNAME_1;

Use Case 152: delete an index

Drop index hrman. IX_EMPNAME_1;

Application Scenario 153: View and manage the materialized view in Enterprise Manager

Use Case 154: Create a view in Enterprise Manager

Application Scenario 155: Use the create materialized view statement to CREATE a MATERIALIZED VIEW

Create a materialized mv_emp1:

Create materialized view hrman. MV_EMP1
REFRESH FORCE
ON DEMAND
AS
SELECT e. Emp_Id, e. Emp_Name, d. Dep_Name
From hrman. Employees e, HRMAN. Departments d
WHERE e. Dep_Id = d. Dep_Id;

Application Scenario 156: modify a view

Set the refresh type to CONPLETE:
Alter materialized view hrman. MV_EMP
REFRESH COMPLETE
On demand;

Use Case 157: delete a Materialized View

Drop materialized view hrman. MV_EMP;

 

Use Case 158: Create a cluster

Create cluster hrman. HrCluster (dep_id NUMBER)
PCTUSED 80
PCTFREE 5
SIZE 500
TABLESPACE users
STORAGE (INITIAL 200 K
NEXT 300 K
MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 33 );

Use Case 159: Create a cluster Table

Create DeptInfo in the cluster HRMAN. HrCluster:

Create table hrman. DeptInfo
(
Dep_id number primary key,
Dep_name VARCHAR2 (100) NOT NULL
)
Cluster hrman. HrCluster (Dep_id );


Create the table EmpInfo in HRMAN. HrCluster:

Create table hrman. EmpInfo
(
Emp_id number primary key,
Emp_name VARCHAR2 (50) not null,
Sex VARCHAR2 (2 ),
Title VARCHAR2 (50 ),
Wage NUMBER (8, 2 ),
IdCard VARCHAR2 (20 ),
Dep_id NUMBER
)
Cluster hrman. HrCluster (Dep_id );

Application Scenario 160: Use the DBA_CLUSTERS view to view Cluster Information

View the cluster Table column information in HRMAN:

COL CLUSTER_NAME FORMAT A20
Col owner format A20
COL TABLESPACE_NAME FORMAT A20
SELECT CLUSTER_NAME, OWNER, TABLESPACE_NAME, CLUSTER_TYPE FROM DBA_CLUSTERS;

Application Scenario 161: Use the DBA_CLU_COLUMNS view to view the column information of the cluster Table

Col owner format A10
COL CLUSTER_NAME FORMAT A15
COL CLU_COLUMN_NAME FORMAT A10
COL TABLE_NAME FORMAT A10
COL TAB_COLUMN_NAME FORMAT A15
SELECT * FROM DBA_CLU_COLUMNS
Where owner = 'hrman ';

Use Case 162: Create a Cluster Index

Create a cluster index on the village HRMAN. HrCluster:

Create index hrman. IX_EMP_DEPT
On cluster hrman. HrCluster
TABLESPACE users
STORAGE (INITIAL 50 K
NEXT 50 K
MINEXTENTS 2
MAXEXTENTS 10
PCTINCREASE 33 );

Use Case 163: modify a cluster

Alter cluster hrman. HrCluster
PCTUSED 60
PCTFREE 30;

Use Case 164: delete a cluster

Drop cluster hrman. HrCluster
INCLUDING TABLES
Cascade constraints;


Use Case 165: Create a hashed Cluster

Create a HashCluster in the hrman solution:

Create table hrman. Area
(
AreaId NUMBER (5, 0) primary key,
AreaName VARCHAR2 (200 ),
UpperId NUMBER (5, 0)
)
Cluster hrman. HashCluster (AreaId );

Create a table area on the hashed cluster to save the region information.

Create cluster STCluster (Id NUMBER)
SIZE 512 single table hashkeys 500;

Application Scenario 166: control the space usage of hash Clusters

Application Scenario 167: estimate the size of space required by the hash Cluster

Application Scenario 168: Modify and delete a hash Cluster

Application Scenario 169: view the hash function in the DBA_CLUSTER_HASH_EXPRESSIONS View

SELECT * FROM DBA_CLUSTER_HASH_EXPRESSIONS;

Use Case 170: Create Sequence

Create sequence hrman. EMP_S
MINVALUE 1
NOMAXVALUE
Start with 1
Increment by 1
NOCYCLE
CACHE 20;

Application Scenario 171: Modify Sequence

Alter sequence hrman. EMP_S
MAXVALUE 10000;

Use Case 172: delete Sequence

Drop sequence hrman. EMP_S;

Use Case 173: sequence usage

Create sequence hrman. USER_S
MINVALUE 1
NOMAXVALUE
Start with 1
Increment by 1
NOCYCLE
CACHE 20;

Insert into hrman. users values (HRMAN. USER_S.NEXTVAL, 'admin', 'pass ');

Use Case 174: Create Synonyms

Create public synonym HrEmployees for hrman. Employees;

Application Scenario 175: use the System View DBA_SYNONYMS to view synonym Information

Col owner format A10
COL SYNONYM_NAME FORMAT A10
COL TABLE_OWNER FORMAT A10
COL TABLE_NAME FORMAT A10
COL DB_LINK FORMAT A10
SELECT * FROM DBA_SYNONYMS
WHERE TABLE_NAME = 'ployees ';

Use Case 176: Use synonyms in DML statements

COL EMP_NAME FORMAT A20
Col title format A10
SELECT Emp_name, Title, Wage FROM HrEmployees;


Use Case 177: delete Synonyms

Drop public synonym HrEmployees;

 

Copyright Disclaimer: you are welcome to reprint it. I hope you can add the original article address while reprinting it. Thank you for your cooperation.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.