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 50m REUSE, 'c: /USERS01112.DBF' SIZE 50m REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL
- Modify the path to 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 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
- Add 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;
- Delete table space
DROP TABLESPACE USER_DATA INCLUDING CONTENTS;
- Modify 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 data file size ALTER DATABASE DATAFILE 'C: /USERS01113.DBF' Resize 40M;
- Create a user, give permission
Create 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, the management of the table
- Create a table
Creae Table Tablename (Column1 Colutype Default (Value) NOT NULL (Value) NOT NULL);
CREATE TABLE summit.employee (id NUMBER (7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE (INITIAL 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2 (25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER (7)) TABLESPACE data;
- Modify the storage allocation of the table
Alter Table TablenamepctFree 30pctused 50Storage (Next 500KMINEXTENTS 2MAXEXTENTS 100);
Alter Table TablenameAllocate Extent (size 500kdatafile '/disk3/data01.dbf');
- Move the surface to another table space
Alter Table TableName Move TableSpace TableSpacename;
- Recycling idle space (Recycling High-Water Mark) All Recycling Requires Truncate Table TableName
Alter Table TablenaMedEallocate Unused;
- Delete the table (along with the construint)
Drop Table TablenamecaDe Constraints;
- Add columns to the table
Alter Table TablenameAdd Column Colutype Default (Value) Not null;
- Remove the columns in the table
Alter Table Tablenamedrop Column ColumnName;
Alter Table Tablenamedrop Column ColumnNamecascade Constraints Checkpoint 1000;
- Mark column is not available
Alter Table Tablenameset Unuse Column ColumnNameCascade Constraint
- Remove the column marked as unavailable
Alter Table Tablenamedrop Unuse Column CHECKPOINT 1000;
- Continue to delete column options
Alter Table Tablenamedrop Column Continue Checkpoint 1000;
- put the table in buffer_pool
ALTER TABLE TABLENAME Storage (Buffer_Pool Recycle);
- Avoid dynamic allocation of Extent
Alter Table TableName Allocate Extent;
- Put the table to 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-establish index
Alter Index IndexName Rebuild TableSpace TableSpace;
- Index allocation parameters
Alter Index IndexNameStorage (Next 400K MaxExtents 100);
- Release index space
Alter Index IndexNameAllocate Extent (size 200kdatafile '/disk6/indx01.dbf') ;alter index indexnamedeallocate unused
- Re-organize index table space debris
Alter Index IndexName Coalesce;
- Delete index
Drop Index IndexName
- put the index into 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)
- Cause the constraint
Alter Table Tablename Enable NoValidate ConsTRAINT CONSTRAINTNAME
Alter Table Tablename Enable Validate ConsTRAINTNAME
- Delete constraint
Alter Table TableName Drop Constraint ConstraintName
DROP TABLE TABLENAME CASCADE CONSTRAINTS; (deleted the foreign key used after deleting the table)
- Add the default value to the column
Alter Table Tablenamemodify ColumnName Default (Value) Not null;
- Increase the foreign key Alter Table TableName Add Constraint ConstraintName Foreign Key (Column) References Table1Name (Column1); COLUMN1);
6, safety strategy
- Encryption transmission
Set the client environment variable ORA_ENCRYPT_LOGIN to TRUE to set the server-side parameters dblink_encypt_login to TRUE
- Database Administrator Security Policy
a. Immediately modify the password of Sys / System immediately (5.2 must be modified after 9.2) B. Only the database administrator can log in to the system C with the SYSDBA to establish an administrator who is not true, allocates different permissions.
For example: Objects Create a maintenance database adjustment and maintenance Creating a User Assignment Role Start Off Recovery Backup - Application Developer's Security Policy
A. Developers' privilege can only give privileges B, free developers, controlled developers free developers: create table / index / procedure / package: No permissions
7, log file management
- Switch log file
Alter system switch logfile;
- Increase log files
Alter Database Add logfile ('/disk3/log3a.rdo' ,'/disk4/log3b.rdo') size 1m;
- increased 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 content
Alter Database Clear Logfile '/disk3/log2a.rdo';
