Oracle442 application scenarios --------- database logical object management application scenarios

Oracle442 application scenarios --------- database logical object management application scenarios

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;