Oracle442 Application Scenarios-----------database logical Object Management scenarios

Scenario 128: Creating a temporary table

Create a temporary table that holds the temporary selection of product information temp_goods

CREATE GLOBAL Temporary TABLE temp_goods
(Goodsid number,
Goodsnum number,
Price number)
On COMMIT DELETE ROWS;

Create a temporary table space

CREATE Temporary tablespace tbs_t1
Tempfile ' tbs_t1.f ' SIZE 50m reuse autoextend on
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

Create temp_goods1 in Tablespace tb_t1;

CREATE GLOBAL Temporary TABLE temp_goods1
(Goodsid number,
Goodsnum number,
Price number)
On COMMIT DELETE ROWS
Tablespace tbs_t1;

Scenario 129: View a 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 = ' EMPLOYEES ';

Scenario 130: Modifying tables in Enterprise Manager

Scenario 131 viewing table results using the DESC statement

The statements that view the table employee results are as follows:

DESC SYSTEM. Employee

Use Scenario 132: Add a column to a table by using the ALTER TABLE statement

ALTER TABLE SYSTEM. Departments ADD (Description VARCHAR2 (1000));

Scenario 133: Modifying column names by using the ALTER TABLE statement

ALTER TABLE SYSTEM. Departments
RENAME COLUMN Description to descript;

Scenario 134: Use the ALTER TABLE statement to delete a queue

ALTER TABLE Hrman. Departments
RENAME COLUMN Description to descript;

Scenario 136: Creating and validating primary KEY constraints

ALTER TABLE Hrman. Departments ADD (Description varchar2 (1000));
DESC Hrman. Departments;
ALTER TABLE Hrman. Departments
SET UNUSED (Description);
DESC Hrman. Departments;
To delete all the unavailable columns in the table:
ALTER TABLE Hrman. Departments
DROP UNUSED COLUMNS;

Scenario 137: Creating and validating non-null constraints

CREATE table User2 and specify username columns and userpwd as non-null constraints

CREATE TABLE Hrman. Users2
(UserId number,
UserName VARCHAR2 (+) not NULL,
Userpwd VARCHAR2 (+) 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;

Insert data into table User2 the username value is null:

INSERT into Hrman. Users2 (UserId, Userpwd) VALUES (1, ' 123456 ');

Scenario 138: Create a validation unique constraint

To establish a unique constraint on Uername:

CREATE TABLE Users3
(UserId number Primary Key,
UserName Varchar2 (+) not NULL UNIQUE,
Userpwd Varchar2 (+) 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 username column of the table user to a unique constraint:
ALTER TABLE Hrman. Users
ADD CONSTRAINT uk_username1
UNIQUE (UserName);

Insert two records into table user using insert to make their column values duplicate: username=test-"

INSERT into Hrman. Users VALUES (+, ' test ', ' test ');
INSERT into Hrman. Users VALUES (101, ' Test ', ' 1234 ');

Scenario 139: Create a validation check constraint

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)
);

To create a CHECK constraint using alter:

ALTER TABLE Hrman. Users
ADD CONSTRAINT ck_userpwd1 CHECK (LENGTH (USERPWD) >=6);

Inserting records using Insert is a userpwd column that is worth less than 6 length:

INSERT into Hrman. Users VALUES (102, ' user ', ' pwd ');

Scenario 140: Creating and validating foreign KEY constraints

Create a FOREIGN KEY constraint fk_emp_depid for the dep_id column of the dep_id column and table employee for table departments:,

ALTER TABLE Hrman. Employees
ADD CONSTRAINT Fk_emp_depid
FOREIGN KEY (dep_id) REFERENCES Hrman. Departments (DEP_ID);

Insert a record into the table:

INSERT into Hrman. Employees (emp_id, Emp_name, dep_id)
VALUES (1, ' Johney ', 3);

Scenario 141: Set the properties of the default column

To create the table USERS6, set the default value for Userpwd to: 11111:

CREATE TABLE Hrman. Users6
(UserId number Primary Key,
UserName Varchar2 (+) not NULL UNIQUE,
Userpwd Varchar2 (+) DEFAULT (' 111111 ')
);

Insert a record into the table:

INSERT into Hrman. USERS6 (UserId, UserName) VALUES (1, ' user ');

Scenario 142: Deleting a table

DROP TABLE Hrman. USERS;

Scenario 143: View and manage views in Enterprise Manager

Scenario 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

Scenario 145: Using the CREATE view

Create a 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 view of the user Hrman and the text information it defines:

SELECT view_name, TEXT from Dba_views
WHERE owner= ' Hrman ';

Scenario 146: Modifying a view

To modify the 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;

Scenario 147: Deleting a view

DROP View HR.V_EMP1;

Scenario 148: Index management page in Enterprise Manager

Scenario 149: Creating 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;

Hrman for table. Enmployees column Idcard creates a unique index:

CREATE INDEX Hrman. Uq_idcard on Hrman. EMPLOYEES (Idcard)
Tablespace Users;

View the views that are owned by Hrman and their definition text information:

SELECT index_name, table_name from dba_indexes
WHERE owner= ' Hrman ';

Scenario 151: Modifying an index

ALTER INDEX Hrman. Ix_empname unusable;

ALTER INDEX Hrman. Ix_empname REBUILD;

ALTER INDEX Hrman. Ix_empname RENAME to Ix_empname_1;

Scenario 152: Deleting an index

DROP INDEX Hrman. Ix_empname_1;

Scenario 153: View and manage the manifested view in Enterprise Manager

Scenario 154: Create a view in Enterprise Manager

Scenario 155: Creating a solid view using the Create materialized view statement

To create a manifested 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;

Scenario 156: Modifying a view

Set the refresh type to Conplete:
ALTER materialized VIEW Hrman. Mv_emp
REFRESH Complete
On DEMAND;

Scenario 157: Deleting a solid view

DROP materialized VIEW Hrman. Mv_emp;

Scenario 158: Creating a Cluster

CREATE CLUSTER Hrman. Hrcluster (dep_id number)
Pctused 80
PCTFREE 5
SIZE 500
Tablespace users
STORAGE (INITIAL 200K
NEXT 300K
Minextents 2
MAXEXTENTS UNLIMITED
Pctincrease 33);

Scenario 159: Creating a cluster table

Create the Deptinfo in the cluster Hrman.hrcluster:

CREATE TABLE Hrman. Deptinfo
(
dep_id number PRIMARY KEY,
Dep_name VARCHAR2 (+) not NULL
)
CLUSTER Hrman. Hrcluster (dep_id);

CREATE TABLE Empinfo in Hrman.hrcluster:

CREATE TABLE Hrman. Empinfo
(
emp_id number PRIMARY KEY,
Emp_name VARCHAR2 () not NULL,
Sex VARCHAR2 (2),
Title VARCHAR2 (50),
Wage number (8, 2),
Idcard VARCHAR2 (20),
DEP_ID number
)
CLUSTER Hrman. Hrcluster (dep_id);

Scenario 160: View cluster information using the Dba_clusters view

To 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;

Scenario 161: Use the Dba_clu_columns view to view column information for a 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 ';

Scenario 162: Creating a Clustered Index

To create a clustered index on the village Hrman.hrcluster:

CREATE INDEX Hrman. Ix_emp_dept
On CLUSTER Hrman. Hrcluster
Tablespace users
STORAGE (INITIAL 50K
NEXT 50K
Minextents 2
MAXEXTENTS 10
Pctincrease 33);

Scenario 163: Modifying a cluster

ALTER CLUSTER Hrman. Hrcluster
Pctused 60
PCTFREE 30;

Scenario 164: Deleting a cluster

DROP CLUSTER Hrman. Hrcluster
including TABLES
CASCADE CONSTRAINTS;

Scenario 165: Creating a hash cluster

Create a hash cluster hashcluster on the Hrman scenario:

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 a hash cluster to hold geographic information

CREATE CLUSTER Stcluster (Id number)
SIZE of single TABLE Hashkeys 500;

Scenario 166: Controlling the spatial use of a hash cluster

Scenario 167: Estimating the amount of space required for a hash cluster

Scenario 168: Modifying and deleting a hash cluster

Scenario 169: Using the Dba_cluster_hash_expressions view to view the hash function

SELECT * from Dba_cluster_hash_expressions;

Scenario 170: Creating a sequence

CREATE SEQUENCE Hrman. emp_s
MINVALUE 1
Nomaxvalue
START with 1
INCREMENT by 1
Nocycle
CACHE 20;

Scenario 171: Modifying a sequence

ALTER SEQUENCE Hrman. emp_s
MAXVALUE 10000;

Scenario 172: Deleting a sequence

DROP SEQUENCE Hrman. emp_s;

Scenario 173: Use of sequences

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 ');

Scenario 174: Creating synonyms

CREATE public synonym hremployees for Hrman. Employees;

Scenario 175: Use System view dba_synonyms to view synonyms 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= ' EMPLOYEES ';

Scenario 176: Using synonyms in DML statements

COL Emp_name FORMAT A20
COL TITLE FORMAT A10
SELECT Emp_name, Title, wage from hremployees;

Scenario 177: Removing synonyms

DROP public synonym hremployees;

Copyright NOTICE: Welcome reprint, Hope in your reprint at the same time, add the original address, thank you with

Oracle442 Application Scenarios-----------database logical Object Management scenarios