The second part, Oracle architecture [Q] Oracle's data type [A] Common data type has a CHAR fixed length character field, the maximum length of up to 2000 bytes Nchar multibly character set fixed length character domain, Length with the character set, up to 2000 characters or 2000 bytes VARCHAR2 variable length character field, the maximum length of 4000 characters NVARCHAR2 multi-byte character set variable length character domain, length with the character set Up to 4000 characters or 4000 byte DATEs are 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 bytes UroWID for binary data for data addressing, the maximum length is 4000 bytes
[Q] Oracle's common keywords, cannot be used for object name [A] Take 8i version as an example, generally reserved keywords that cannot be used to do object name Access Add All Alter and any as as as, book, 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 TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH more information can view v $ reserved_words view
[Q] How to view the database version [A] Select * from v $ version contains version information, core version information, bit information (32-bit or 64-bit), etc. View, such as File $ ORACLE_HOME / BIN / ORACLE [Q] How to view database parameters [a] show parameter parameter name, if you can see if you can use SPFILE file or select * from v $ parameter In addition to this part of the parameters, Oracle There is 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') AS SYSMOD, 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 (Ksppiff / 256, 1), 1, 'True', 'False') AS ISEM, DECODE (Bitand (KSPPIFLG / 65536 , 3), 1, 'IMMEDIATE', 2, 'DEFERRED' , 2), 2, 'true', 'false') AS IADJ, KSPPDESC AS Description from x $ ksppi x, x $ ksppsv y wherere x.indx = y.indx and substr (ksppinm, 1, 1) = '_' and x.inst_id = uchesserenv ('instance')) Order by Name
[Q] How to view the database character set [A] Database server character set Select * from NLS_DATABASE_PARAMETERS, which is 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 System Enable Restricted Session; ALTER System Set Job_Queue_Process = 0; ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET ZHS16GBK;
[Q] How to establish a function index [A] 8i or more, make sure query_rewrite_enabled = trueQuery_rewrite_integrity = trustedcompatible = 8.1.0 above Create Index IndexName on table (Fired);
[Q] how to move a table or table partition [A] Syntax Alter table tablename move moving table [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] must then rebuild the index alter index indexName Rebuild If there is a LOB segment, then normal ALTER cannot move the LOB segment to other tablespaces, but only move the watch, you can use the following method to move LOB segment ALTER TABLENAME MOVE LOB (LOBSEGNAME) Store As (TableSpace Newts);
[Q] How to get the current SCN [A] 9i SELECT MAX (KTuxescnw * Power (2,32) KTuxescnb) from x $ ktuxe; if it is 9i or later, you can also get SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM Dual;
[Q] RowID structure and composition [A] 8 or higher version of RowID composition oooooofffbbbbbrr8 or less RowID composition (also called restricted ROWID) bbbbbbbbbbbb.rrr.ffff, O is object ID, F is file ID, B is block ID, R is a line ID If we query a table's RowID, depending on the block information, you can know how many blocks do it exactly, 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 do not say mainly say that 9i or more version of DBMS_Metadata1, get a single object of DDL statement 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; Table to INDEX
[Q] How to create a constrained index on another table space [A] 1, create an index first, create a constraint 2, create create table test (c1 number constraint pk_c1_id primary keyx, c2 varchaint pk_c1_id primary key ingusing index tablespace useridex, c2 varcha2 (10) )) TABLESPACE Userdate;
[Q] How do you know that those tables do not build primary key [A], the primary key of the table is necessary, and there is no primary key to say that it does not meet the design specifications. 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 Column relationship
[Q] dbms_output prompts the buffer is not enough, how to add [A] dbms_output.enable (20000);
[Q] How to modify the column name [A] 9i or more version You can use the RNAME command ALTER TABLE Username.tabname Rename Column SourceColumn To DestColumn9i The following versions can be used to use create Table ... as select * from sourceTable. In addition, 8i can support deleting alter table usrname.tabname set unused (columnname) Cascade constraintsalter table username.tabname Drop (ColumnName) Cascade Constraints
[Q] How to install SQLUS installation [A] SQLPLUS help must be manually installed, the shell script is $ oracle_home / bin / helpins must set the system_pass environment variable first, such as: $ setnv system_pass system / manager $ helpins if Without setting the environment variable, you will prompt the Environment variable when you run the script, in addition to the shell script, you can also use the SQL script installation, 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 ), the 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> I know this information, we can download with FlashGet, the 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] is a common action, if you pay attention to the wire number under OPA / RAC Add a log file group ALTER DATABASE Add logfile [group n] 'file full name' size 10m; in this Add a member ALTER DATABASE ADD Logfile Member 'file full name' to group n; delete a log member ALTABASE DROP logfile men 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 Buffer ALOCATION 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) --------------------------------12 [q] Control files contain which basic content [A] Control file mainly contains the following entries can see DATABASE eNTRY CHECKPOINT PROGRESS RECORDS REDO THREAD RECORDS LOG fILE RECORDS DATA fILE RECORDS TEMP fILE RECORDS TABLESPACE RECORDS LOG fILE HISTORY RECORDS OFFLINE RANGE RECORDS ARCHIVED LOG RECORDS BACKUP SET RECORDS BACKUP PIECE RECORDS BACKUP DATAFILE RECORDS BACKUP LOG file content by dump control Records DataFile Copy R Ecords Backup DataFile Corruption Records DataFile Copy Corruption Records Deletion Records Proxy Copy Recordsincarnation Records
[Q] If there is a bad block in the table, how to retrieve other unshire data [A] First, you need to find a bad block ID (you can run the DBVERIFY implementation), assume that it is
[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:
