A table space can only belong to a database
There is a minimum of a control file for each database (suggest 3, which is placed on different disks)
There is a minimum of a table space per database (System table space)
The purpose of establishing the SYSTEM table space is to try the same table as possible to improve the efficiency of use, and should only store data dictionary.
There are fewer online log groups for each database, each set of one online log file
A data file can only belong to a table space
Once a data file is added to a table space, it is no longer removed from this table space, and can no longer be added to other tablespaces.
Establish new tablespace needs to create new data files
Data files are formatted by Oracle to Oracle blocks, in previous versions of Oracle9i, the size of the Oracle block is set when the database is created,
And in the future, you can't change, you must change, you can only rebuild the database.
A segment segment can only belong to a table space, but can belong to multiple data files.
One area extent can only belong to a data file, that is, intervals, unable to cross the data file
PCTFREE and PCTUSED summation cannot be greater than or equal to 100
A single transaction cannot span multiple returns
Index table does not contain ROWID values
There is no benefit of returning to different sizes.
After commit, the data is not necessarily written immediately (data files)
An a transaction will be written to the redo log even if it is not submitted.
ORACLE 8.0.4, the default database established during initial installation, instance is named orcl
One block is 16KB (2K, 4K, 8K, 16K) Each database maximum file number (by block size) 2K block 2000 file 4K block 40000 file 8K block or above 65536 files
Oracle Server can start multiple databases at the same time
A plurality of version of the Oracle database system can be installed on a set of operating systems (UNIX can, NT can not be)
A set of Oracle database systems can have multiple Oracle databases and their corresponding instances
Each Oracle database has a database instance (except for OPS)
Therefore, there can be multiple Oracle database instances on a set of operating systems, which can start // Oracle8 data type char (n) n = 1 to 2000 byte fixed length string, N-byte length, if the length is not specified, default 1 byte length (a Chinese character is 2 bytes) varchar2 (n) n = 1 to 4000 bytes can grow long strings, specify the maximum length n when specifically defined, this data type can be digitized, letters, and ASCII All symbols in the code character set (or the character set standard such as the EBCDIC). If the data length does not reach the maximum value n, Oracle 8i automatically adjusts the field length according to the data size. If your data has spaces, Oracle 8i will automatically delete it. VARCHAR2 is the most common data type. The maximum length of the index can be made 3209. Number (m, n) m = 1 to 38n = -84 TO 127 can grow a long value column, allow 0, positive value, and negative value, m is the number of bits of all valid numbers, and n is the number of bits after the decimal point. Such as: Number (5, 2), the maximum value of this field is 99,999, and if the value exceeds the number of digits, the extra number of digits will be intercepted. Such as: Number (5, 2), but in the field of this field input 575.316, the value that is really saved to the field is 575.32. Such as: Number (3, 0), input 575.316, the real saved data is 575. Date No origin from January 1, 4712 BC, Oracle 8i actually saved the date data internally, in the definition, in the definition, respectively, minute, and seconds. The default format is DD-MON-YY, such as 07-November -00, represent November 7, 2000. Long no longer character column, the maximum length limit is 2GB, used for long string data that does not require a string search, if you want to perform character search, you need to use VARCHAR2 type. Long is an older data type that will gradually be replaced by large object data such as BLOB, Clob, NClob. RAW (n) n = 1 to 2000 variable length binary data, must indicate the maximum length N, Oracle 8i with this format, such as the format, such as the format, such as the format, such as the format, such as Miceosoft Word with this format when the specific definition field is specifically defined. Documentation. RAW is an older data type that will gradually be replaced by large object data such as BLOB, Clob, NClob. Long Raw has no long binary data, the maximum length is 2GB. Oracle 8i uses this format to save large graphics files or text files with formats, such as Miceosoft Word documents, as well as non-text files such as audio, video. In the same table, there is also a long Raw type. Long Raw is also an older data type, which will gradually be replaced by large object data such as BLOB, Clob, NClob. BlobClobnClob has no three large objects (LOB) to save large graphics files or text files with formats, such as Miceosoft Word documents, and audio, video, etc. non-text files, the maximum length is 4GB. There are several types of LOB depends on the type of the byte you use, and Oracle 8i is actually stored in the database inside the database. Special operations such as read, storage, write, etc. can be performed. BFILE has a large binary object file saved outside the database, the maximum length is 4GB. This external LOB type, through the database record, but the specific save of the data is performed outside the database.
Oracle 8i can read, query bfile, but cannot be written. The size is determined by the operating system. SQL (STRUITURED Query Language) Statement DDL, Data Definition Language: Create, ALTER, DROP, TRUNCATE (Created, Modifying Structure, Delete, Truncation) (Other: Rename)
DML, data manipulation language: INSERT, DELETE, SELECT, UPDATE (increasing, deletion, check, change)
DCL, Data Control Language: Grant, Revoke (Authorized, Recycling), Set Role
Transaction Control: Commit, Rollback, Savepoint (Other: Lock Table, SET Constraint (s), Set Transaction
Audit Control: Audit, NoAudit
System control: ALTER SYSTEM
Session Control: ALTER Session
Other statements: Comment, Explain Plan, Analyze, Validate, Validate, Call // SQL * Plus runs several commands in Plus 1. SQL * plus command is typically used to format query output, set the environment, edit Store SQL commands and PL / SQL block SQL> Show user sql> select user from dual;
2. SQL Command SQL> SELECT * from Tab;
3. PL / SQL block SQL> Begin dbms_output.put_line ('Hello World!'); End; //
Oracle implements a field to automatically add 1 Create sequence sequence name increment by 1 start with 1 maxValue 99999999 cycle;
When inserting data into the table, the SQL statement is written as follows:
SQL> INSERT INTO Name VALUES (sequence name .NextVal, column 1 value, column 2 value); // How to generate SQL batch files with SQL?
Software Environment: 1, Windows NT4.0 Oracle 8.0.42, Oracle Installation Path is: C: / ORANT
The problem is proposed: 1. Users need to execute one of the same SQL operations on each table under the database user. At this time, it is very troublesome to type the SQL statement over again.
Implementation Method: SQL> SET Heading Off - Disable Output Column Title SQL> Set Feedback Off - Disabled Display Fast Row Count Feedback Information
List the definition of all synonyms under the current user, can be used to test the true existence of synonyms SELECT 'DESC' || TNAME from Tab Where TabType = 'Synynym';
Query the number of records of all tables under users SELECT 'SELECT' '' || TNAME || '' ', count (*) from' || TNAME || ';' from tab where tabtype = 'table';
Give all the qualified tables to the select author to publicSelect 'grant select on' || table_name || 'to public;' from user_tables where "Condition";
Delete users under various objects SELECT 'DROP' || tabtype || '' || TNAME from Tab;
Delete eligible users Select 'Drop user' || username || 'cascade;' from all_users where user_id> 25;
Quickly compile all views - • When you pour the database into the new server (database reconstruction), you need to recompile the view again, --- Because the tablespace view is connected to the table of other tablespace There is a problem, which can be quickly compiled with the language characteristics of PL / SQL.
SQL> Spool on.sqlsql> select'Alter view '|| tname ||' Compile; 'from tab; sql> spool off then executes ON.SQL. SQL> @ on.sql, of course, authorize and create synonyms can also be made quickly, such as: SQL> SELECT 'GRANT SELECT ON' || TNAME || 'to user name;' from tab; sql> select 'create synonym' || TNAME || 'for username.' || tname || ';' from tab;
