7 Database Logical Object Management

Create a normal table: (in scenario HR)
SYS as [email protected]>create TABLE HR. Gmy (
2 emp_id number,
3 Emp_name VARCHAR2 () not NULL,
4 SEX VARCHAR2 (2),
5 TITLE VARCHAR2 (50),
6 wage Number (8,2),
7 Idcard VARCHAR2 (20),
8 dep_id Number
9);

Create a temporary table (default is saved in the default temp table space)
On COMMIT Delete ROWS: Creates a transaction-level staging table that deletes all data from the table each time the transaction is committed
On COMMIT preserver ROWS: Creates a session-level staging table that deletes all data from the table when the session ends
SYS as [email protected]>create GLOBAL Temporary TABLE temp_goods
2 (GOODSLD number,
3 Goodnum number,
4 prive number)
5 on COMMIT DELETE ROWS;
Create a temporary table space
CREATE Temporary tablespace tbs_t1
Tempfile ' Tbs_t1. DBF ' SIZE 50M reuse autoextend on
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K

System views related to tables:
Display information for all relational tables in the database
Dba_tables
All_tables
User_tables
View index information for a table
Dba_indexes
All_indexes
User_indexes
SELECT index_name from dba_indexes WHERE owner= ' HR ';
Display information for tables, views, and columns in a cluster
Dba_tab_columns
All_tab_columns
User_tab_columns
Show comment information for tables and views
Dba_tab_comments
All_tab_comments
User_tab_comments
Show comment information for columns in tables and views
Dba_col_comments
All_col_comments
User_col_comments
displaying properties of external tables in a database
Dba_external_tables
All_external_tables
User_external_tables
Displaying data sources for external tables
Dba_external_locations
All_external_locations
User_external_locations
Display histogram information for tables and views
Dba_tab_histograms
All_tab_histograms
User_tab_histograms
To display optimization statistics for a table
Dba_tab_statistics
All_tab_statistics
User_tab_statistics
Display statistics and histogram information for columns in tables and views
Dba_tab_col_statistics
All_tab_col_statistics
User_tab_col_statistics
Displays the modifications that occurred in the table after the statistics collection from the last table was completed
Dba_tab_modifications
All_tab_modifications
User_tab_modifications
Displays information and encryption algorithms for all encrypted columns
Dba_encrypted_columns
All_encrypted_columns
User_encrypted_columns
Show all unused columns in a table
Dba_unused_col_tabs
All_unused_col_tabs
User_unused_col_tabs
Show partially completed drop COLUMNR action values in the table
Dba_partial_drop_tabs
All_partial_drop_tabs
User_partial_drop_tabs

To add a column to a table by using the ALTER TABLE statement
ALTER TABLE HR. RSC ADD (DESCRIPTION VARCHAR2 (100));

To modify a column name by using the ALTER TABLE statement
ALTER TABLE HR. RSC RENAME COLUMN DESCRIPTION to descript;

To delete a column by using the ALTER TABLE statement
ALTER TABLE HR. RSC DROP COLUMN descript;

To set a column to be unavailable by using ALTER TABLE
ALTER TABLE HR. RSC SET UNUSED (DESCRIPTION);

Remove all columns that are not available for a table
ALTER TABLE HR. RSC DROP UNUSED COLUMNS;

Create and validate primary KEY constraints (column values do not allow duplicate records)
SYS as [email protected]>create TABLE HR. USERS
2 (USERID number PRIMARY KEY,
3 USERNAME VARCHAR2 (40),
4 userpwd VARCHAR2 (40));
SYS as [email protected]>create TABLE HR. USERS1
2 (USERID number,
3 userpwd VARCHAR2 (40),
4 CONSTRAINT Pk_userid PRIMARY KEY (USERID));
SYS as [email protected]>alter TABLE HR. USER2
2 ADD CONSTRAINT userid_pk PRIMARY KEY (USERID);

Create and validate non-null constraints (column values do not press a null value)
ALTER TABLE HR. The USERS MODIFY USERID is not NULL;

Create and validate unique constraints (only one primary key exists for a table, and if other columns are unique, you can use unique constraints)
SYS as [email protected]>alter TABLE HR. USERS
2 ADD CONSTRAINT Userpwd_uni UNIQUE (USERPWD);

SYS as [email protected]>create TABLE HR. USER1
2 (USERID number PRIMARY KEY,
3 USERNAME VARCHAR2 (20),
4 userpwd VARCHAR2 () not NULL UNIQUE,
5 CONSTRAINT uk_username UNIQUE (USERNAME));

Create and Validate CHECK constraints (constraints on the properties of a field through a function)
SYS as [email protected]>create TABLE HR. USER1
2 (USERID number PRIMARY KEY,
3 USERNAME VARCHAR2 (40),
4 userpwd VARCHAR2 (40),
5 CONSTRAINT ck_userpwd CHECK (LENGTH (USERPWD) >=6));

Create and validate a FOREIGN KEY constraint (used to establish a connection relationship between two tables, the primary table column must be a PRIMARY KEY constraint or a unique constraint)
SYS as [email protected]>alter TABLE HR. USER1
2 ADD CONSTRAINT USERID
3 FOREIGN KEY (USERID) REFERENCES HR. USER2 (USERID);

Set the default property of a column
SYS as [email protected]>create TABLE HR. USER3
2 (USERID number PRIMARY KEY,
3 USERNAME VARCHAR2 (+) not NULL UNIQUE,
4 userpwd VARCHAR2 (+) DEFAULT (' 123456 '));

To delete a table:
DROP TABLE HR. USERS;

To create a view:
SYS as [email protected]>create VIEW HR. Emp_dep
As
SELECT E.employee_id,e.first_name,d.department_name from HR. EMPLOYEES e,hr. Departments D WHERE e.department_id=d.department_id;

To view the views you have created:
SELECT View_name,text from Dba_views WHERE owner= ' HR ';
SELECT View_name,text from Dba_views WHERE owner= ' HR ' and view_name= ' EMP_DEP ';

Modify the view to modify the SQL statement in the OEM

To delete a view:
DROP VIEW HR. Emp_dep

To create an index:
CREATE [unique| BITMAP] INDEX [<schema_name>.] <INDEX_NAME>
On [schema_name.] <table_name>[table_column]
(<column>[asc| DESC]) <INDEX_SUB><INDEX_attribute>

Index_sub: You can specify the table space where the index is saved
LOCAL STORE in (<TABLE_SPACE>)

Index_attribute: Specify indexes for sorting, compression, online status, and other properties
Nosort| SORT
REVERSE
compress<int>| Nocompress
Logging| Nologging
ONLINE

To create an index:
CREATE INDEX HR. Gmy_emp_name_ix on HR. Gmy (Emp_name);

CREATE UNIQUE INDEX HR. Dep_id_uni on HR. Gmy (dep_id);

View index Information

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

Setting the specified index is not available:
ALTER INDEX HR. Gmy_emp_name_ix unusable;

Set enable the specified index:
ALTER INDEX HR. Gmy_emp_name_ix REBUILD;

To rename an index:
ALTER INDEX HR. Gmy_emp_name_ix RENAME to Rsc_name_ix; Do not add scheme name to back

To delete an index:
DROP INDEX HR. Rsc_name_ix;


Manifested views:
Create a solid view
CREATE materialized VIEW <name>
Fast| complete| Force
On [demand| COMMIT]
with {PRIMARY KEY | ROWID}
[USING INDEX <storage_option>]
[refresh<refresh_option>| Never Refersh]
[Forupdate] [{enable| DISABLE} QUERY REWRITE]
As
SELECT statement

SYS as [email protected]>create materialized VIEW HR. Mv_emp1
2 REFRESH Force
3 on DEMAND
4 AS
5 SELECT * from EMPLOYEES;

Materialized view created.


To view the manifested view:
SYS as [email protected]>select mview_name from dba_mviews WHERE owner= ' HR ';

Mview_name
------------------------------
Mv


Modify the definition of a manifested view
ALTER materialized VIEW HR. Mv_emp1
REFRESH Complete
On DEMAND

To delete a manifested view:
DROP materialized VIEW HR. MV_EMP1;


ORACLE Cluster
To create a cluster:
CREATE CLUSTER [< scheme name;.] < cluster name > (< key column 1>< data type >,< key column 1>< data type >)
Pctused <INT>
PCTFREE <INT>
Initrans <INT>
Maxtrans <INT>
SIZE <INT> k| M
Tablespace < table space name >
STORAGE clause
To create a cluster:
SYS as [email protected]>create CLUSTER HR. Hruster (dep_id number)
2 pctused 80
3 PCTFREE 5
4 SIZE 500
5 tablespace USERS
6 STORAGE (
7 INITIAL 200K
8 NEXT 300K
9 Minextents 2
Ten MAXEXTENTS UNLIMITED
Pctincrease 33);
To create a cluster table:
SYS as [email protected]>create TABLE HR. Deptinfo (
2 dep_id number PRIMARY KEY,
3 Dep_name VARCHAR2 (+) not NULL
4)
5 CLUSTER HR. Hruster (dep_id);

SYS as [email protected]>create TABLE HR. Empinfo
2 (
3 emp_id number PRIMARY KEY,
4 Emp_name VARCHAR2 () not NULL,
5 SEX VARCHAR2 (2),
6 TITLE VARCHAR2 (50),
7 wage Number (8,2),
8 Idcard VARCHAR2 (20),
9 dep_id number)
Ten CLUSTER HR. Hruster (dep_id);

View cluster information using the Dba_clusters view
SYS as [email protected]>select cluster_name,owner,tablespace_name,cluster_type from dba_clusters;

Cluster_Name OWNER Tablespace_name Clust
------------------------------ ------------------------------ ------------------------------ -----
Smon_scn_to_time_aux SYS Sysaux INDEX
c_cobj# SYS SYSTEM INDEX
c_file#_block# SYS SYSTEM INDEX
c_mlog# SYS SYSTEM INDEX
c_obj# SYS SYSTEM INDEX
c_obj#_intcol# SYS SYSTEM INDEX
c_rg# SYS SYSTEM INDEX
c_toid_version# SYS SYSTEM INDEX
c_ts# SYS SYSTEM INDEX
c_user# SYS SYSTEM INDEX
Hruster HR USERS INDEX

To view column information for a cluster table using the Dba_clu_columns view
SELECT * from Dba_clu_columns WHERE owner= ' HR ';


Create a clustered index: (the table cannot have any data when it is created)
SYS as [email protected]>create INDEX HR. Emp_dep_ix
2 on CLUSTER HR. Hruster
3 Tablespace USERS
4 STORAGE
5 (INITIAL 50K NEXT 50K minextents 2 MAXEXTENTS pctincrease 33);


To modify a cluster:
ALTER cluster|< scheme name >|< cluster name >
SIZE <INT> k| m| G
NEXT <INT> k| m| G
Minextents <INT>
MAXEXTENTS <INT>
Pctused <INT>
PCTFREE <INT>
Initrans <INT>
Maxtrans <INT>
Tablespace <TABLESPACE_NAME>
STORAGE <sentence>

SYS as [email protected]>alter CLUSTER HR. Hruster
2 pctused 80
3 PCTFREE 20;

Cluster altered.


To delete a cluster:

SYS as [email protected]>drop CLUSTER HR. Hruster
2 including TABLES
3 CASCADE CONSTRAINTS;

Cluster dropped.

Create a hash cluster
CREATE CLUSTER |<schema_name>|<cluster_name> (<KEY_COLUMN><DATA_TYPE>,...)
Tablespace <TABLESPACE_NAME>
Pctused <INT>
PCTFREE <INT>
Initrans <INT>
Maxtrans <INT>
SIZE <INT> k| M
STORAGE clause
HASH is <CLUSTER_KEY_COLUMN> Hashkeys <HASH_VALUE_MAX_NUM>

SYS as [email protected]>create CLUSTER HR. Hashcluster (areaid number (5,0))
2 tablespace USERS
3 STORAGE (
4 INITIAL 200K
5 NEXT 50K
6 minextents 2
7 MAXEXTENTS UNLIMITED
8 Pctincrease 33)
9 HASH is areaid Hashkeys 200;

To create a table in a hash value:
SYS as S[email protected]>create TABLE HR. Hatb_area
2 (
3 Areaid Number (5,0) PRIMARY KEY,
4 areaname VARCHAR2 (200),
5 Upperld Number (5,0)
6)
7 CLUSTER HR. Hashcluster (Areaid);

Table created.

Use dba_cluster_hash_expressions to view hash functions
SELECT * from Dba_cluster_hash_expressions;

Create a sequence
SYS as [email protected]>create SEQUENCE HR. Gmy_seq
2 MINVALUE 1
3 Nomaxvalue
4 START with 1
5 INCREMENT by 1
6 nocycle
7 CACHE 20;
To modify a sequence:
SYS as [email protected]>alter SEQUENCE HR. Rsc_seq
2 MAXVALUE 100000;

Use of sequences:
Get the latest serial number: Seq_name. Nextval
SYS as [email protected]>insert into HR. RSC VALUES (HR. Rsc_seq. Nextval, ' RSC ');

SYS as [email protected]>select * from HR.RSC;
rsc_id Rsc_name
---------- --------------------
2 RSC



To create a synonym:
CREATE [public] synonym [< scheme name;.] < synonyms > for [< scheme name;.] < object name > [@< database chain name;]

SYS as [email protected]>create synonym HR. Hrgmy for HR. Gmy;

To create a public synonym:
CREATE public synonym Hrgmy for HR. Gmy;


Use System view dba_synonyms to view information about synonyms (user_synonyms,all_synonyms)
SYS as [email Protected]>desc dba_synonyms;
Name Null? Type
----------------------------------------------------------------- -------- -------------------------------------- ------
OWNER not NULL VARCHAR2 (30)
Synonym_name not NULL VARCHAR2 (30)
Table_owner VARCHAR2 (30)
table_name not NULL VARCHAR2 (30)
Db_link VARCHAR2 (128)

To delete a synonym:
DROP synonym HR. HRRSC;

DROP public synonym Hrgmy;

7 Database Logical Object Management