Automatic oracle maintenance and oracle Maintenance

Automatic oracle maintenance and oracle Maintenance
Check whether the ORACLE automatic maintenance task is disabled
SQL> select t. client_name, t. status from dba_autotask_client t;
CLIENT_NAME STATUS
------------------------------------------------------------------------
Auto optimizer stats collection DISABLED
Auto space advisor DISABLED
SQL tuning advisor DISABLED
SQL>
Oracle maintenance problems

Well, the first question:
-----------
SQL> create table test (
2 id number (2 ),
3 name char (20)
4 );

Table created

SQL> alter table test add primary key (id); -- add id as the primary key

Table altered

SQL> select constraint_name, constraint_type, index_name
2 from user_constraints
3 where table_name = 'test'; -- View binding information of a table

CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
---------------------------------------------------------------------------
SYS_C0084246 P SYS_C0084246

SQL> select index_name, index_type, uniqueness
2 from user_indexes
3 where table_name = 'test'; -- queries the index information of a table.

INDEX_NAME INDEX_TYPE UNIQUENESS
-------------------------------------------------------------------
SYS_C0084246 NORMAL UNIQUE

---------------------
That is to say, Oracle automatically adds an index when creating a primary key.

Second question:
The user_indexes table is used to query all index objects of the current user;

Third question:
Create index ID_NAME on TEST (NAME );
Delete index: drop index ID_NAME;
If there are binding conditions related to the index, delete the constraint first;

Fourth and fifth questions:
This is relatively broad, including hardware I/O performance, Oracle SGA, PGA configuration, index performance, etc. It is best to directly baidu Oracle performance optimization.

Are tables and data files in oracle databases the same concept?

Database Structure and space management
An ORACLE database is a collection of data and is processed into a unit. An ORACLE database has a physical structure and a logical structure.
The physical database structure is determined by the operating system files that constitute the database. Each ORACLE database consists of three types of files: data files, log files, and control files. Database files provide real physical storage for database information.
The logical database structure is the database structure involved by the user. The logical structure of an ORACLE database is determined by the following factors:
L one or more tablespaces
L database mode objects (tables, views, indexes, aggregates, sequences, and stored procedures)
Logical storage structures such as tablespaces (dataspace), segments (segments), and ranges govern how physical spaces of a database are used. Schema objects use the relationships between them to form a database relationship design.

1) physical structure

(1) data files
Each ORACLE database has one or more physical data files ). A database data file contains all the database data. Data in logical database structures (such as tables and indexes) is physically stored in database data files. Data Files have the following features:
L a data file is only associated with one database.
L once created, the data file size cannot be changed
L a tablespace (logical unit of database storage) is composed of one or more data files.
The data in the data file can be read and stored in the ORACLE storage zone as needed. For example, if you want to access some data in a table in the database, if the request information is not in the memory storage area of the database, it is read from the corresponding data file and stored in the memory. When you modify and insert new data, you do not need to write the data file immediately. To reduce the total number of disk outputs and improve performance, data is stored in the memory. Then, the ORACLE background process DBWR decides how to write data to the corresponding data file.

(2) log files
Each database has two or more redo log files, and each log file group is used to collect database logs. The main function of a log is to record the changes made to the data, so all the changes made to the database are recorded in the log. In the event of a fault, if you cannot permanently write the modified data to the data file, you can use the log to get the modification, so the existing operation results will never be lost.
Log files are mainly used to protect databases to prevent faults. To prevent faults in log files, ORACLE allows mirrored redo logs to maintain two or more log copies on different disks.
The information in the log file is only used when the database is recovered due to system faults or media faults. These faults prevent the database data from being written into the database data file. However, when any lost data is opened next time, ORACLE automatically uses the information in the log file to restore the database data file.

(3) Control File
Each ORACLE database has a control file that records the physical structure of the database and contains the following types of information:
L database name;
L name and location of database data files and log files;
L database creation date.
To ensure security, allow control files to be mirrored.
Each time an ORACLE database instance is started, its control files are used to identify the database and log files. They must be opened when you start database operations. When the physical composition of a database is changed, ORACLE automatically changes the control file of the database. Control Files must also be used for data recovery.

2) Logical Structure
The logical structure of the database contains tablespaces, segments, ranges (extent), data blocks, and schema objects.
(1) tablespace
A database is divided into one or more logical units, which are called table spaces ). A tablespace can combine related logical structures. DBA can use tablespaces to do the following:
L control the disk allocation of database data.
L allocate the determined Space share to the database user.
L by making a single ...... remaining full text>