Experience in Oracle Database Development (2)

1. analysis table
Analyze table mzbs. db_code estimate statistics sample 20 percent;

2. Table space management and user management

-- View tablespaces and data files

Select file_name, tablespace_name, autoextensible from dba_data_files;

-- Data table space

Create tablespace user_data
Logging
Datafile 'd:/Oracle/oradata/orcl/test. dbf' size 50 m reuse,
'C:/users01112.dbf' size 50 m Reuse
Autoextend
On next 1280 K maxsize 16383 M extent management local

-- Modify the path of the tablespace Data File

Alter tablespace app_data
Rename
Datafile '/disk4/app_data_01.dbf'
To '/disk5/app_data_01.dbf ';

Alter Database
Rename File '/disk1/system_01.dbf'
To '/disk2/system_01.dbf ';

-- Temporary tablespace

Create temporary
Tablespace user_data_temp tempfile 'd:/temp0111.dbf'
Size 50 m reuse autoextend
On next 1024 K maxsize 16383 M extent management Local Uniform
Size 1024 K

-- Add data files

Alter tablespace user_data
Add datafile 'C:/users01113.dbf' size 50 m;

Alter tablespace user_data
Add datafile 'C:/users01114.dbf' size 50 m
Autoextend on
;

-- Delete a tablespace

Drop tablespace user_data including contents;

 

-- Modify the storage parameters of a tablespace

Alter tablespace tablespacename
Minimum extent 2 m;

Alter tablespace tablespacename
Default storage (
Initial 2 m
Next 2 m
Maxextents 999 );

-- Online/offline/read-only tablespace

Alter tablespace tablespacename offline/online/read only;

 

-- Modify the data file size
Alter Database
Datafile 'C:/users01113.dbf' resize 40 m;

-- Create a user and grant permissions

Create user user_data profile default identified by user_data
Default
Tablespace user_data temporary
Tablespace user_data account unlock;
 
Grant connect to user_data;
Grant resource to user_data;

 

3. Table Management

-- Create a table

Creae table tablename
(Column1 colutype default (value) not null)
(Column2 colutype default (value) not null );

-- Index storage allocation for table Creation

Create Table summit. employee (ID number (7) Constraint employee_id_pk primary key deferrable using index storage (initial 100 K next 100 K)
Tablespace indx,
Last_name varchar2 (25) Constraint employee_last_name_nn not null,
Dept_id number (7 ))
Tablespace data;

-- Modify the storage allocation of a table

Alter table tablename
Pctfree 30
Pctused 50
Storage (next 500 k
Minextents 2
Maxextents 100 );

Alter table tablename
Allocate extent (size 500 k
Datafile '/disk3/data01.dbf ');

-- Move the table to another tablespace

Alter table tablename move tablespace tablespacename;

-- Recycle idle space (recycle to high-water mark)
To recycle all data, truncate table tablename is required.

Alter table tablename
Deallocate unused;

-- Delete a table (along with the used constraint)

Drop table tablename
Cascade constraints;

-- Add columns to the table

Alter table tablename
Add column colutype default (value) not null;

-- Delete columns in a table

Alter table tablename
Drop column columnname;

Alter table tablename
Drop column columnname
Cascade constraints checkpoint 1000;

-- Mark column unavailable

Alter table tablename
Set unused column columnname
Cascade constraints;

-- Delete columns marked as unavailable

Alter table tablename
Drop unused columns checkpoint 1000;

-- Option to continue deleting Columns

Alter table tablename
Drop columns continue checkpoint 1000;

-- Put the table in buffer_pool

Alter table tablename
Storage (buffer_pool recycle );

-- Avoid dynamic extent allocation

Alter table tablename allocate extent;

-- Put the table in the cache

Alter table tablename allocate Cache/nocache;

4. index management

-- Create an index

Create index indexname on tablename (columnname );

Create index indexname on tablename (columnname) tablespace tablespacename;

-- Re-create an index

Alter index indexname rebuild tablespace;

-- Index allocation parameters

Alter index indexname
Storage (next 400 K
Maxextents 100 );

-- Release index space

Alter index indexname
Allocate extent (size 200 K
Datafile '/disk6/indx01.dbf ');

Alter index indexname
Deallocate unused;

-- Refresh the index tablespace fragments

Alter index indexname coalesce;

-- Delete an index

Drop index indexname

-- Put the index in buffer_pool

Alter index cust_name_idx
Rebuild
Storage (buffer_pool keep );

5. constraint management

-- Create a primary key

Alter table tablename
Add constraint constraintname primary key (column1, column2)

-- Invalid Constraints

Alter table tablename enable novalidate constraint constraintname;

Alter table tablename enable validate constraint constraintname;

-- Delete Constraints

Alter table tablename drop constraint constraintname;

Drop table tablename cascade constraints; (delete the foreign key used after the table is deleted)

-- Add a default value to the column

Alter table tablename
Modify columnname default (value) not null;

-- Add a foreign key to the table
Alter table tablename
Add constraint constraintname
Foreign key (column) References table1name (column1 );

6. Security Policy

-- Encrypted transmission

Set the client environment variable ora_encrypt_login to true.
Set the server parameter dblink_encypt_login to true.

-- Database Administrator Security Policy

A. change the password of sys/system immediately after the database is created (the password must be changed after 9.2)
B. Only the database administrator can log on to the system as sysdba.
C. Create administrators of different roles and assign different permissions.

For example, an object is created for maintenance.
Database adjustment and Maintenance
Create a user to assign a role
Close startup
Restore backup

-- Security policies of application developers

A. the developer's privilege can only be granted to the test and development database.
B. Free developers and controlled developers
Free developers: Create Table/index/procedure/package
Controlled developers: do not have the above Permissions

7. Log File Management

-- Switch log files

Alter system switch logfile;

-- Add log files

Alter database add logfile
('/Disk3/log3a. rdo ',
'/Disk4/log3b. rdo') size 1 m;

-- Add log members

Alter database add logfile Member
'/Disk4/log1b. rdo' to Group 1
'/Disk4/log2b. rdo' to group 2;

-- Delete a log file

Alter database drop logfile group 3;

-- Delete a log Member

Alter database drop logfile member '/disk4/log2b. dbf ';

-- Clear Log File Content

Alter database clear logfile '/disk3/log2a. rdo ';