Some accumulated experience in Oracle database development (II.)

oracle| Data | database
1. Analyze table Mzbs.db_code estimate STATISTICS SAMPLE PERCENT;

2. Table space Management and user management

--View table spaces 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 50m reuse, ' c:\USERS01112. DBF ' SIZE 50m reuse autoextend on NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT Local

--Modify the path of the table space 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 table space

CREATE temporary tablespace user_data_temp tempfile ' D:\TEMP0111. DBF ' SIZE 50M reuse autoextend on NEXT 1024K MAXSIZE 16383M EXTENT MANAGEMENT Local uniform SIZE 1024K

--Adding data files

ALTER tablespace user_data ADD datafile ' c:\USERS01113. DBF ' SIZE 50M;

ALTER tablespace user_data ADD datafile ' c:\USERS01114. DBF ' SIZE 50M autoextend on;

--Remove Tablespace

DROP tablespace user_data including CONTENTS;



--Modifying the storage parameters of the table space

ALTER tablespace tablespacename MINIMUM EXTENT 2M;

ALTER tablespace tablespacename DEFAULT STORAGE (INITIAL 2M NEXT 2M maxextents 999);

--Table space Online/offline/read-only

ALTER tablespace tablespacename Offline/online/read only;



--Modify the data file size ALTER DATABASE datafile ' c:\USERS01113. DBF ' RESIZE 40M;

--Create a user, give permission

CREATE USER User_data Profile default identified by User_data default tablespace user_data temporary TABLESP ACE user_data account UNLOCK; GRANT CONNECT to User_data; GRANT RESOURCE to User_data;



3, the table management

--Create a table

Creae TABLE tablename (COLUMN1 colutype Default (value) not null) (COLUMN2 colutype Default (value) is not NULL);

--index storage allocation for building tables

CREATE TABLE Summit.employee (ID number (7) CONSTRAINT employee_id_pk PRIMARY KEY deferrable USING INDEX STORAGE (INITIAL 10 0K NEXT 100K) tablespace indx, last_name VARCHAR2 CONSTRAINT employee_last_name_nn not null,dept_id number (7)) Tablespace data;

--Modify the table's storage allocation

ALTER TABLE tablenamepctfree 30PCTUSED 50STORAGE (NEXT 500KMINEXTENTS 2MAXEXTENTS 100);

ALTER TABLE tablenameallocate EXTENT (SIZE 500KDATAFILE '/disk3/data01. DBF ');

--Move the table to another table space

ALTER TABLE tablename move tablespace tablespacename;

-Reclaim free space (recycle to High-water mark) Truncate TABLE is required for all recycling TableName

ALTER TABLE tablenamedeallocate unused;

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

DROP TABLE Tablenamecascade CONSTRAINTS;

--Adding columns to the table

ALTER TABLE tablenameadd COLUMN colutype DEFAULT (VALUE) not NULL;

--Delete columns from the table

ALTER TABLE tablenamedrop COLUMN columnname;

ALTER TABLE tablenamedrop COLUMN columnnamecascade CONSTRAINTS CHECKPOINT 1000;

--tag columns are not available

ALTER TABLE tablenameset unused COLUMN columnnamecascade CONSTRAINTS;

--Delete columns that are marked as unavailable

ALTER TABLE Tablenamedrop unused COLUMNS CHECKPOINT 1000;

--Continue deleting column options

ALTER TABLE tablenamedrop COLUMNS CONTINUE CHECKPOINT 1000;

--Put the watch in the Buffer_pool.

ALTER TABLE tablename STORAGE (Buffer_pool recycle);

--Avoid dynamic allocation of extent

ALTER TABLE tablename ALLOCATE EXTENT;

--Put the watch 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-build Index

ALTER INDEX indexname REBUILD tablespace tablespace;

--Index allocation parameters

ALTER INDEX indexnamestorage (NEXT 400K maxextents 100);

--Free index space

ALTER INDEX indexnameallocate EXTENT (SIZE 200KDATAFILE '/disk6/indx01.dbf ');

ALTER INDEX indexnamedeallocate unused;

--Reorganize Index Table space fragmentation

ALTER INDEX IndexName Coalesce;

--Delete Index

DROP INDEX IndexName

--Put the index in the Buffer_pool

ALTER INDEX cust_name_idx REBUILD STORAGE (Buffer_pool KEEP);

5. Constraint Management

--Establish primary key

ALTER TABLE tablenameadd CONSTRAINT constraintname PRIMARY KEY (COLUMN1,COLUMN2)

--Invalidates the constraint

ALTER TABLE tablename ENABLE novalidate CONSTRAINT constraintname;

ALTER TABLE tablename ENABLE VALIDATE CONSTRAINT constraintname;

--Delete Constraint

ALTER TABLE tablename DROP CONSTRAINT constraintname;

DROP TABLE tablename CASCADE CONSTRAINTS (deletes the foreign key used after the table is deleted)

--Add default values to columns

ALTER TABLE tablenamemodify 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 to set the server-side parameter Dblink_encypt_login to True

--Database Administrator security Policy

A, the library immediately modified Sys/system password (9.2 must modify its password) b, only the database administrator to SYSDBA login system C, establish different roles of administrators, assign different permissions

For example: object creation in maintenance database adjustment and maintenance Create user Assignment role start shutdown Restore Backup-application developer's security policy

A, the developer's privileges can only be given permission B, free developer, controlled developer free Developer: Create table\index\procedure\package controlled Developer: no more permissions

7. log File Management

--Switch log files

ALTER SYSTEM SWITCH LOGFILE;

--Adding log files

ALTER DATABASE ADD LOGFILE ('/disk3/log3a.rdo ', '/disk4/log3b.rdo ') size 1M;

--Adding log Members

ALTER DATABASE ADD LOGFILE member '/disk4/log1b.rdo ' to Group 1 '/disk4/log2b.rdo ' to group 2;

--Delete log files

ALTER DATABASE DROP LOGFILE GROUP 3;

--Delete Log Members

ALTER DATABASE DROP LOGFILE member '/DISK4/LOG2B.DBF ';

--Clear Log file contents

ALTER DATABASE Clear LOGFILE '/disk3/log2a.rdo ';