Part II, Oracle architecture system
[Q] Oracle's data type [A] Common data types have a CHAR fixed length character field, the maximum length of 2000 byte Nchar multibly character set fixed length character domain, the length follows the character set, Up to 2000 characters or 2000 bytes VARCHAR2 variable length character field, the maximum length of 4000 characters NVARCHAR2 multi-character set variable length character domain, the length follows the character set, up to 4000 characters or 4000 byte Date is used to store a fixed length (7 byte) character fields of all dates, and the time is stored as part of the date. Unless the date format is replaced by setting the NLS_DATE_FORMAT parameter of the init.ora file, the date is represented in DD-MON-YY format, such as 13-APR-99 represents 1999.4.13 Number variable length value column, allowed value 0 , Positive and negative numbers.
Number values typically store with 4 bytes or less bytes, up to 21-byte long variable length character fields, maximum lengths to 2GB RAW represents variable length character fields of binary data, up to 2000 bytes Longraw represents the variable length character field of binary data, up to 2GB mlslabel only for TrustedoCle, this data type uses 2 to 5 byte BLOB binary large objects per line, the maximum length is 4GB Clob characters, the maximum length is 4GB NClob multi-character set Clob data type, the maximum length is 4GB BFile external binary file, the size is determined by the operating system to represent the binary data of the ROWID, the value of Oracle8RowID is 10 bytes, and the Limited RowID format used in Oracle7 For 6 byte urowid for binary data for data addressing, the maximum length is 4000 bytes [q] What is the common keywords in Oracle, cannot be used for object name [A] Take 8i version as an example, general retention key words can not be used as object names ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN bY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TAB Le To Trigger Uid Unique Update User Validate Values VARCHAR VARCHAR2 View WHENE WHERE WITH DESIORDE VOSVED_WORDS View [Q] How to View Database Version [A] SELECT * FROM V $ Version contains version information, core version information, bit Number information (32-bit or 64-bit), etc., on the LINUX / UNIX platform, you can view, such as File $ Oracle_Home / Bin / Oracle [q] how to view database parameters [a] show parameter parameter name By show parameter spfile, you can use the spfile file or select * from v $ parameter In addition to this part of the parameters, Oracle also has a lot of implicit parameters, you can view: Select Name, Value, Decode (Isdefault, 'True', 'Y', 'n') AS "default", decode (ISEM, 'True', 'Y', 'N') AS SESMOD, DECODE (ISYM, 'Immediate', 'I'
, 'Deferred', 'D', 'False', 'N') ASMOD, DECODE (IMOD, 'Modified', 'U', 'Sys_Modified', 'S', 'N') AS Modified, Decode (IADJ) , 'TRUE', 'Y', 'N') as Adjusted, description FROM (--GV $ SYSTEM_PARAMETER SELECT x.inst_id as instance, x.indx 1, ksppinm as NAME, ksppity, ksppstvl as VALUE, ksppstdf as isdefault , decode (Bitand (Ksppiflg / 256, 1), 1, 'True', 'False') AS ISEM, DECODE (Bitand (Ksppifl / 65536, 3), 1, 'Immediate', 2, 'DeferRed', 'False ') AS ISYM, DECODE (Bitand (Ksppstvf, 7), 1,' Modified ',' False ') AS IMOD, DECODE (Bitand (Ksppstvf, 2), 2,' True ',' False ') AS IADJ, KSPPPDESC AS Description from x $ ksppi x, x $ ksppsv y where x.indx = y.indx and substr (ksppinm, 1, 1) = '_' and x.inst_id = sernv ('instance')) Order by name [q ] How to view the database character set [A] Database server character set Select * from NLS_DATABASE_PARAMETERS, from PROPS $, which is a character set that represents the database. Client character set environment Select * from nls_instance_parameters, comes from V $ parameter, indicating the settings of the client's character set, may be parameter files, environment variables, or registry session character set environment Select * from NLS_Session_Parameters, come from V $ NLS_Parameters, indicating that the session is your own setting, which may be the environment variable of the session or the ALTER session complete, if the session does not have a special setting, will be consistent with NLS_INSTANCE_PARAMETERS. The client's character set requires the same as the server to correctly display the non-ASCII characters of the database. If multiple settings exist, the Alter Session> Environment Variable> Registry> Parameter file character set is consistent, but the language settings can be different, and the language settings are recommended in English. If the character set is ZHS16GBK, the nls_lang can be American_america.zHS16GBK. [Q] How to modify the character set [A] 8i or more version can modify the character set through Alter Database, but it is only limited to subset to superchard, and it is not recommended to modify the PROPS $ table, which will cause serious errors.
Startup nomount; Alter database mount exclusive; Alter system enable restricted session; Alter system set job_queue_process = 0; Alter database open; Alter database character set zhs16gbk; [Q] how to build a function based on an index [A] 8i above, to ensure Query_rewrite_enabled = trueQuery_rewrite_integrity = trustedcompatible = 8.1.0
Above Crete Index IndexName on Table (Function (Field)); [Q] How to move a table or table partition [A] Syntax ALTER TABLENAME MOVE [TABLESPACE New_NAMESTORAGE (Initial
50M
NEXT
50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging] moving partition syntax alter table tablename move (partition partname) [update global indexes] After then must rebuild the index Alter index indexname rebuild If the table has Lob segments, the normal Alter not move Lob Segments to other tablespaces, but just moving the watch, mobile LOB segment ALTER TABLENAME MOVE LOB (LOBSEGNAME) Store AS (TABLESPACE NEWTS); [Q] How to get the current SCN [A] 9i The following version Select Max (KTuxescnw * Power (2,32) ktuxescnb) from x $ ktuxe; if it is 9i or more, you can also obtain the SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from DUAL; [Q] RowID structure and composition [A ] 8 or more version of RowID composition OOOOOOFFFBBBBBBRR8 or less RowID composition (also called restricted ROWID) bbbbbbbbb.rrr.ffff, o is object ID, f is file ID, B is block ID, R is a line ID If we query a table RowID, according to the block information, you can know how many blocks do it exactly the table, and it knows how much data space occupies (this data space is not equal to the allocation space of the table) [Q] How to get the object's DDL statement [A] Third-party tools will not say will mainly talk about 9i or later dbms_metadata1, to obtain a single object DDL statements set heading offset echo offset feedback offset pages offset long 90000select dbms_metadata.get_ddl ( 'TABLE', 'TABLENAME', 'SCAME') from Dual; If you get the script of the entire user, you can use the following statement select dbms_metadata.get_ddl ('Table', u.table_name) from user_tables u; of course, if it is an index, you need to modify the related Table to INDEX [Q] how to create constraints Indexing on other tablespaces [a] 1, create an index first, create Constraint 2. Create Create Table Test (C1 Number Constraint PK_C1_ID Primary Keyusing Index TableSpace Useridex, C2 VARCHAR2 PK_C1_ID PRIMARY KEYUSINT PK_C1_ID PRIMARY KEYUSINT PK_C1_ID PRIMARY KEYUSINT PK_C1_ID PRIMARY KEYUSINT PK_C1_ID PRIMARY KEYUSING INDEX TABLESPACE Userdex, C2 VARCHAR2 (10)) TABLESPACE Userdate; [Q] How do you know that those tables have not established primary key [a] general, table The primary key is necessary, there is no primary key to say that it does not meet the design specification.
SELECT table_nameFROM User_tables t WHERE NOT EXISTS (SELECT table_nameFROM User_constraints cWHERE constraint_type = 'P'AND t.table_name = c.table_name) other relevant data interpretation dictionary table column user_constraints user_tables user_tab_columns user_indexes index table constraints and column constraints user_ind_columns index and user_cons_columns The relationship of the column [q] dbms_output prompts the buffer is not enough, how to add [A] dbms_output.enable (20000); ] 9I The above version can use RNAME commands Alter Table username.tabname Rename Column SourceColumn To DestColumn9i The following versions can be used in the way CREATE TABLE ... AS SELECT * ASOURETABLE. In addition, 8i above can delete columns support the ALTER TABLE UserName.TabName SET UNUSED (ColumnName) CASCADE CONSTRAINTSALTER TABLE UserName.TabName DROP (ColumnName) CASCADE CONSTRAINTS [Q] how to install sqlplus to help [A] SQLPLUS help must be installed manually, SHELL scripts are available for $ oracle_home / bin / helpins, you must first set the system_pass environment variable, such as $ setenv system_pass system / manager $ helpins If this environment variable is not set, you will be prompted when you run the script. Of course, in addition to The shell script can also be installed using the SQL script, so you don't have to set the environment variable, however, we must log in with system. $ SQLPLUS System / Manager SQL> @? / SQLPLUS / Admin / Help / Helpbld.sql Helpus.sql installation, you can use the following method to help SQL> Help Index [Q] how to quickly download Oracle Patch [A ] We first get the download server address, there is ftp://updates.racle.com on the http page, then log in with FTP, the username and password are Metalink username and password, as we know the patch number 3095277 (9204 patch set ), Then ftp> CD 3095277 250 Changed Directory OK. Ftp> LS 200 Port Command OK. 150 opening data connection for file listing. P3095277_9204_aix64-5l
.zip p3095277_9204_AIX64.zip ...... p3095277_9204_WINNT.zip 226 Listing complete Data connection has been closed ftp:.. 208 bytes received in 0.02Seconds 13.00Kbytes / sec ftp> know this information, we use flashget, network ants can be downloaded. . Add the following connection ftp://updates.oracle.com/3095277/p3...04_aix64-5l.zip or replace the back part of the desired content, if it is flashget, network ant Please enter the authentication username and password, Is your Metalink username and password! [Q] How to move data file [A] 1, turn off the database, use the OS copy A.SHUTDOWN IMMEDITE Turn the database b. Copy data files under the OS C.Startup Mount Start to Mount D. RARTER DATABASE RENAME DATAFILE 'Old file' to 'new file'; E.ALTER DATABASE open; Open Database 2, using RMAN online operation RMAN> SQL "ALTABASE DATAFILE '' File Name '' 'Offline"; RMAN> Run {2> Copy DataFile' Old File Location'3> To 'New File Location'; 4> Switch DataFile 'Old File Location'5> To DataFileCopy' New File Location '; 6>} RMAN> SQL "ALTABASE DATAFILE' 'File Name' 'Online"; Description: Use OS copies can also be operated online, do not close the database, like the RMAN's steps, using the RMAN as the principle of using the OS copy, COPY is copy data file, equivalent to the OS, and Switch is equivalent to ALTER DATABASE RENAME, is used to update the control file. [Q] If you manage online log groups and members [A] are common, if you pay attention to the line number at the OPA / RAC, add a log file group ALTER DATABASE Add logfile [group n] 'file full name' size10m
In this group, add a member ALTER DATABASE ADD Logfile Member 'file full name' to group n; delete a log member ALTER DATABASE DROP LOGFILE MEMBER 'file in this group'; delete the entire log group ALTABASE DROP LOGFILE GROUP n; [q] how to calculate the size of Redo Block [A] Calculation method is (Redo size redo WaStage) / Redo Blocks Written 16 See the following example SQL> SESSSTAT WHERE NAME LIKE '% REDO% '; name value ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ---- ---------- Redo synch Writes 2 redo synch time 0 redo entries 76 redo size 19412 redo bufferocation retries 0 redo wastage 5884 redo writer latching time 0 redo writes 22 redo blocks written 51 redo write time 0 redo log space requests 0 redo log space wait time 0 redo log switch interrupts 0 redo ordering marks 0 SQL> select (19412 5884) / 51 16 '"Redo Black (Byte)" from Dual; Redo Black (Byte) --------------------------------------------------------------------------------------------------- Contains the following entry, you can see Database Entry Checkpoint Progress Records Data File Records Temp file Records TableSpace Records log file recording. FILE HISTORY RECORDS OFFLINE RANGE RECORDS ARCHIVED LOG RECORDS BACKUP SET RECORDS BACKUP PIECE RECORDS BACKUP DATAFILE RECORDS BACKUP LOG RECORDS DATAFILE COPY RECORDS BACKUP DATAFILE CORRUPTION RECORDS DATAFILE COPY CORRUPTION RECORDS DELETION RECORDS PROXY COPY RECORDSINCARNATION RECORDS [Q] If it is found in the table bad blocks, how Retrieve other unshire data [A] First, you need to find a bad block ID (you can run the DBVERIFY implementation), assume that it is
If the section is an index, remove it and rebuild. Create Table Good_Table AS SELECT from Bad_Table WhereE Rowid Not in (SELECT ROWID from Bad_Table Where Substr (RowID, 10, 6) =
[Q] I created all users of the database, can I delete these users? [A] When the Oracle database is created, create a series of default users and tablespaces, the following is their list · sys / change_on_install or internal system user , Data Dictionary Owner, Super Permissions Owner (Sysdba) Create a script: • / rdbms / admin / sql.bsq and various cat * .sql recommended to create a password immediately After this user cannot be deleted • System / Manager database default management user Have DBA Role Permissions Create Scripts: • / Rdbms / Admin / SQL.BSQ Recommended Change After Creating Password This user cannot be deleted • Outln / Outln Optimization Plan Storage Outline User Create Script:? / Rdbms / admin / SQL.BSQ It is recommended to change the password immediately after the creation This user cannot be deleted --------------------------------------- ------------ · Scott / Tiger, Adams / Wood, Jones / Steel, Clark / Cloth and Blake / Paper. Experiment, Test User, An Example EMP and DEPT Creation Script:? / Rdbms /admin/utlsampl.sql can modify the password user can be deleted, delete or lock the product environment, HR / HR (Human Resources), OE / OE (ORDER Entry), Sh / SH (Sales History). experiment, test user Include Script Employees with DEPARTMENTS: • / demo / schema / mksample.sql You can modify the password user can be deleted, the product environment recommends delete or lock · DBSNMP / DBSNMPORACLE Intelligent Agent creates a script:? / Rdbms / admin / catsnmp . SQL, Called from catalog.sql can change your password - you need to place a new password to snmp_rw.r file If you don't need Intelligent Agents, you can delete ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------- The following users are optional to install the user, if not, you don't need to install · CTXSYS / Ctxsysoracle interface management user creates a script:? / Ctx / admin / dr0cs Ys.sql · TracesVR / TraceOracle TRACE Server creates scripts: • / rdbms / admin / otrcr.sql · Ordplugins / OrdpluginsObject Relational Data (ORD) User Used by Time Series, etc. Created script:? / ord / admin / orderinst.sql · ORDSYS / ORDSYSOBJECT RELATIONAL DATA (ORD) User Used by Time Series, etc creation script:? / Ord / admin / ordinst.sql · dssys / dssysoracle Dynamic Services and Syndication Server creates scripts:? / Ds / sql / dssys_init.sql · MDSys / MDSysoracle Spatial Administrator User creates scripts:? / Ord / admin / order.sql · Aurora $ Orb $ Unauthenticated / Invalidused for Users Who Do Not Authenticate In Aurora / ORB creation script:
