Oracle Common FAQ V1.0 (2)

Oracle second part, Oracle Architecture system
[Q] Oracle has those data types
[A] common types of data are
Char fixed length character value field, maximum length up to 2000 bytes
NCHAR a fixed-length character value field of a multibyte character set, with a length of 2000 characters or 2000 bytes, depending on the character set
VARCHAR2 variable-length character value field, up to 4,000 characters in length
NVARCHAR2 variable-length character value field for multibyte character sets, with a character set of length, up to 4,000 characters or 4,000 bytes
Date is a fixed-length (7-byte) character value field that stores the full date, and the time is stored as part of the date. Unless
Replace the date format by setting the Nls_date_format parameter of the Init.ora file, otherwise the date is
DD-MON-YY format representation, such as 13-apr-99 said 1999.4.13
Number variable-length numeric columns, allowing values to be 0, positive, and negative. Number values are usually stored in 4 bytes or less, up to 21 bytes
Long variable length character value field, maximum length up to 2GB
Raw represents variable-length character value field for binary data, up to 2000 bytes
Longraw is a variable-length character value field that represents binary data, up to 2GB
Mlslabel is only for trustedoracle, this data type is 2 to 5 bytes per exercise
Blob binary large object with a maximum length of 4GB
Clob character large object with a maximum length of 4GB
NCLOB CLOB data type for multibyte character sets with a maximum length of 4GB
bfile External binaries, size determined by the operating system
ROWID represents rowid binary data, the Oracle8rowid value is 10 bytes, and the qualification used in Oracle7
rowID format is 6 bytes
Urowid binary data for data addressing with a maximum length of 4,000 bytes
[Q] What common keywords Oracle has and cannot be used for object names
[A] In the case of the 8i version, general reserved keywords cannot 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 DAT E DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT for from GRANT GROUP has identified Immedi ATE in INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT to 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 to view the 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), and so on
As for the bit information, on the Linux/unix platform, you can view it through file, such as
File $ORACLE _home/bin/oracle
[Q] How to view database parameters
[a]show parameter parameter name
For example, show parameter spfile to see if 9i uses SPFile file
or select * FROM V$parameter
In addition to this part of the argument, Oracle also has a large number of implied parameters, which can be viewed through the following statements:
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 (ksppiflg/256,1), 1, ' TRUE ', ' FALSE ') as Isem
, Decode (Bitand (ksppiflg/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
, Ksppdesc as DESCRIPTION
From X$ksppi X
, X$KSPPSV y
WHERE x.indx = Y.indx
and substr (ksppinm,1,1) = ' _ '
and x.inst_id = USERENV (' Instance ')
)
ORDER BY NAME
[Q] How to view the database character set
[A] Database server character Set select * from Nls_database_parameters, which originates from props$, is the character set representing the database.
Client Character Set Environment select * from Nls_instance_parameters, which originates from V$parameter,
Represents the setting of a client's character set, possibly a parameter file, an environment variable, or a registry
The session Character set environment select * from Nls_session_parameters, which originates from the V$nls_parameters, represents the session's own settings, either the environment variable of the session or the alter sessions complete. If the session does not have a special setting, it will be consistent with nls_instance_parameters.
The client's character set requires consistency with the server in order to correctly display non-ASCII characters of the database. If multiple settings exist, ALTER session> environment variable > registry > Parameter File
Character sets require consistency, but language settings can be different, and language settings are recommended in English. If the character set is ZHS16GBK, then Nls_lang can be AMERICAN_AMERICA.ZHS16GBK.
[Q] How to modify the character set
[a]8i The above version can modify the character set through ALTER DATABASE, but it is also limited to subsets to superset, and it is not recommended to modify the props$ table, which can 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 establish a function based index
[a]8i the above version to ensure
Query_rewrite_enabled=true
query_rewrite_integrity=trusted
compatible=8.1.0 above
Create Index indexname on table (function (field));
[Q] How to move a table or table partition
[A] Move the syntax of a table
Alter Table TableName Move
[Tablespace new_name
Storage (initial 50M next 50M
Pctincrease 0 pctfree pctused Initrans 2) nologging]
To move a partition's syntax
ALTER TABLE tablename Move (partition PartName)
[Update Global indexes]
After that, you must rebuild the index
Alter Index IndexName Rebuild
If the table has a LOB segment, then normal alter cannot move the LOB segment to another table space, but simply moves the table segment, and you can move the LOB segment by using the following method
ALTER TABLE TableName move
LOB (Lobsegname) store as (Tablespace newts);
[Q] How to obtain the current SCN
[A]9i the following version
Select Max (Ktuxescnw*power (2,32) +KTUXESCNB) from X$ktuxe;
If it is more than 9i, you can also use the following statement to obtain the
Select Dbms_flashback.get_system_change_number from dual;
[Q] Structure and composition of ROWID
[A]8 above version of ROWID composition
Oooooofffbbbbbbrrr
8 The following rowID composition (also called limited rowid)
BBBBBBBB. Rrrr. FFFF
Where, O is an object id,f is a file id,b is a block id,r is a row ID
If we query the rowid of a table, based on the information in the block, we know exactly how many blocks the table occupies, and then how much data space is occupied (this data space is not equal to the allocation space of the table).
[Q] How to get the DDL statement of the object
[A] Third-party tools do not say the main say more than 9i version of the Dbms_metadata
1. Get the DDL statement of a single object
Set Heading off
Set echo off
Set Feedback off
Set pages off
Set Long 90000
Select Dbms_metadata.get_ddl (' TABLE ', ' tablename ', ' Scame ') from dual;
If you get the entire user's script, 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 an index of a constraint on a different table space
[A]1, CREATE index first, then create constraint
2, using the following statement to create
CREATE TABLE Test
(C1 number constraint pk_c1_id primary key
Using index tablespace Useridex,
C2 VARCHAR2 (10)
) Tablespace userdate;
[Q] How to know that those tables do not have a primary key
[A] In general, a table's primary key is necessary, and a table without a primary key can be said to be inconsistent with the design specification.
SELECT table_name
From User_tables t
WHERE not EXISTS
(SELECT table_name
From user_constraints C
WHERE constraint_type = ' P '
and T.table_name=c.table_name)
Other Related Data dictionary interpretation
User_tables table
User_tab_columns the columns of the table
user_constraints constraints
The relationship between user_cons_columns constraints and columns
User_indexes Index
User_ind_columns the relationship between indexes and columns
[q]dbms_output hint buffer not enough, how to increase
[A]dbms_output.enable (20000);
In addition, if the Dbms_output information cannot be displayed,
Need to set
Set Serveroutput on
[Q] How to modify the table's column name
[a]9i above version can be used Rname command
ALTER TABLE Username.tabname
RENAME COLUMN SourceColumn to Destcolumn
9i The following versions can take the CREATE TABLE ... as SELECT * from SourceTable.
In addition, more than 8i can support the deletion of columns
ALTER TABLE Username.tabname
SET unused (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE Username.tabname
DROP (ColumnName) CASCADE CONSTRAINTS
[Q] How to install Help for Sqlplus
A Sqlplus help must be installed manually, shell script is $oracle_home/bin/helpins
Before you install, you must set up System_pass environment variables, such as:
$ setenv System_pass System/manager
$ helpins
If you do not set the environment variable, you will be prompted to enter the environment variable when the script is run
Of course, in addition to the shell script, you can also use SQL script installation, you do not have to set the environment variables, but we must log on with system.
$ sqlplus System/manager
Sql> @?/sqlplus/admin/help/helpbld.sql Helpus.sql
Once installed, you can use help like the following methods
Sql> Help Index
[Q] How to download Oracle patches quickly
[A] we first get the download server address, which is available on the HTTP page
Ftp://updates.oracle.com
Then use FTP login, username and password is metalink username and password
If we know the patch number 3095277 (Patch set for 9204), then
Ftp> CD 3095277
Changed directory OK.
Ftp> ls
PORT command OK.
Opening data connection for file listing.
P3095277_9204_aix64-5l.zip
P3095277_9204_aix64.zip
......
P3095277_9204_winnt.zip
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 ant can download.
Add the following connection
Ftp://updates.oracle.com/3095277/p3...04_AIX64-5L.zip
or replace the following section for the desired content
Note that if it is flashget, network ants Please enter the user name and password authentication, is your Metalink username and password!
[Q] How to move data files
[A]1, Shutdown database, use OS copy
A.shutdown immediate shut down the database
B. Copying data files to a new location under the OS
C.startup Mount launched to mount
D.alter database rename datafile ' old file ' to ' new file ';
E.alter database open; Open Database
2, using the Rman online operation
rman> SQL "ALTER DATABASE 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 "ALTER DATABASE datafile ' file name ' online";
Note: The OS copy is also available online and does not close the database, as with Rman steps, using Rman as the principle of using OS copy, copy is a copy of the data file in Rman, equivalent to the CP of the OS, and the switch is equivalent to alter DATABASE Rename, used to update control files.
[Q] If you manage online journal groups and members
[A] The following are common operations, if the thread number is noted under Opa/rac
Add a log file group
Alter database Add logfile [Group N] ' file full name ' size 10M;
Add a member to this group
Alter database Add LogFile member ' file full name ' to group N;
Delete a log member on this group
Alter database Drop logfile member ' file name ';
Delete entire log group
Alter database drop logfile Group N;
[Q] How to calculate the size of the redo block
[A] The calculation method is (redo size + redo wastage)/redo blocks written + 16
See the following examples specifically
Sql> select name, value from V$sysstat where name like '%redo% ';
NAME VALUE
---------------------------------------------------------------- ----------
Redo Synch writes 2
Redo Synch Time 0
Redo Entries 76
Redo Size 19412
Redo Buffer Allocation 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 Spaces Wait 0
Redo Log Switch interrupts 0
Redo Ordering Marks 0
Sql> Select (19412+5884)/51 + ' Redo Black (byte) ' from dual;
Redo Black (byte)
------------------
512
[Q] control what basic content the file contains
[A] The control file mainly contains the following entries, which can be seen through the dump control file contents
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 RECORDS
DataFile COPY RECORDS
BACKUP datafile Corruption RECORDS
datafile COPY Corruption RECORDS
Deletion RECORDS
PROXY COPY RECORDS
Incarnation RECORDS
[Q] If there are bad blocks found in the table, how to retrieve other data that is not bad
[A] First you need to find the ID of the bad block (you can run the dbverify implementation), assuming that the file is encoded as. Run the following query to find the segment name:
SELECT segment_name,segment_type,extent_id,block_id, blocks
From Dba_extents t
where
file_id =
and between block_id and (block_id + blocks-1)
Once you find a bad segment name, if the segment is a table, it's a good idea to create a temporary table and store the data. If the segment is an index, delete it, and then rebuild it.
CREATE TABLE Good_table
As
Select from Bad_table where rowID isn't in
(Select rowID
From Bad_table where substr (rowid,10,6) =)
Notice the difference between the limited rowid of 8 and the present rowid.
You can also use diagnostic event 10231
sql> ALTER SYSTEM SET EVENTS ' 10231 Trace name context forever,level 10 ';
Create a temporary table good_table the data in the table except the bad block is retrieved
Sql>create TABLE good_table as SELECT * from Bad_table;
Finally turn off the diagnostics event
sql> ALTER SYSTEM SET EVENTS ' 10231 trace name context off ';
For the structure of ROWID, you can also refer to the Dbms_rowid.rowid_create function.
[Q] I created all the users of the database, can I delete these users?
A When the Oracle database was created, a series of default user and tablespace spaces were created, and the following is their list
· Sys/change_on_install or INTERNAL
System user, data dictionary owner, Super Rights owner (SYSDBA)
Create script:?/RDBMS/ADMIN/SQL.BSQ and various Cat*.sql
Revise password immediately after creation
This user cannot be deleted
· System/manager
Database default administrative user, with DBA role permissions
Create script:?/rdbms/admin/sql.bsq
Revise password immediately after creation
This user cannot be deleted
· Outln/outln
Optimize the planned storage outline user
Create script:?/rdbms/admin/sql.bsq
Revise password immediately after creation
This user cannot be deleted
---------------------------------------------------
· Scott/tiger, Adams/wood, Jones/steel, Clark/cloth and Blake/paper.
Experiment, test user, include example table EMP and Dept
Create script:?/rdbms/admin/utlsampl.sql
can modify password
Users can be deleted and recommended to delete or lock in the product environment
hr/hr (Human), Oe/oe (Order Entry), sh/sh (Sales History).
Experiment, test user, include example table employees and departments
Create script:?/demo/schema/mksample.sql
can modify password
Users can be deleted and recommended to delete or lock in the product environment
· Dbsnmp/dbsnmp
Oracle Intelligent Agent
Create script:/rdbms/admin/catsnmp.sql, called from Catalog.sql
Can change password--need to place new password to Snmp_rw.ora file
If you do not need intelligent Agents, you can delete
---------------------------------------------------
The following users are optional installation users and do not need to install if they are not needed
· Ctxsys/ctxsys
Oracle intermedia (context cartridge) Admin user
Create script:?/ctx/admin/dr0csys.sql
· Tracesvr/trace
Oracle Trace Server
Create script:?/rdbms/admin/otrcsvr.sql
· Ordplugins/ordplugins
Object Relational Data (ORD) User used by Time Series, etc.
Create script:?/ord/admin/ordinst.sql
· Ordsys/ordsys
Object Relational Data (ORD) User used by Time Series, etc
Create script:?/ord/admin/ordinst.sql
· Dssys/dssys
Oracle Dynamic Services and syndication Server
Create script:?/ds/sql/dssys_init.sql
· Mdsys/mdsys
Oracle Spatial Administrator user
Create script:?/ord/admin/ordinst.sql
· Aurora$orb$unauthenticated/invalid
Used for the users who don't authenticate in Aurora/orb
Create script:?/javavm/install/init_orb.sql called from/javavm/install/initjvm.sql
· Perfstat/perfstat
Oracle Statistics Package (statspack) that supersedes Utlbstat/utlestat
Create script:?/rdbms/admin/statscre.sql