Oracle Common Problem Set (ii)

Oracle
71. Application of kernel parameters

Shmmax

Implication: This setting does not determine how much of the memory the Oracle data library or operating system uses, but only the maximum number of memory that can be used. This setting also does not affect the internal nuclear resources of the operating system.



Setup method: 0.5* Memory

Example: Set shmsys:shminfo_shmmax=10485760

Shmmin

Meaning: The minimum size of a shared memory.

Setup method: generally set to 1.

Example: Set shmsys:shminfo_shmmin=1:

Shmmni

Meaning: The largest number of memory segments used by the system.

Example: Set shmsys:shminfo_shmmni=100

Shmseg

Meaning: The maximum number of shared memory segments that each user can use.

Example: Set shmsys:shminfo_shmseg=20:

Semmni

Meaning: The largest number of semaphore identifierer in a system.

Setting method: The value of this variable is set to the largest of all Oracle's Init.ora in this system and the value of that processes plus 10.



Example: Set semsys:seminfo_semmni=100

Semmns

Meaning: The largest number of emaphores in a system.

Setting method: This value can be calculated by: the sum of the values of the processes in the Initsid.ora of each Oracle instance (excluding the maximum processes parameters) + The largest processesx2+10xoracle example.



Example: Set semsys:seminfo_semmns=200

SEMMSL:

Meaning: The largest number of semaphore in a set.

Setup method: Set to 10+ the value of the largest processes in the Initsid.ora of all Oracle instances.

Example: Set semsys:seminfo_semmsl=-200

72. How can I see which user has sysdba, sysoper?

Sql>conn Sys/change_on_install

Sql>select * from V_$pwfile_users;



73. How to separate one or more tables?

Exp User/Password tables= (table 1,..., table 2)



74. How to single out one or more user?

Exp System/manager owner= (user 1, user 2,..., user n) file= document



75. How to do the full text of the CLOB?

SELECT * from A WHERE dbms_lob.instr (a.a, ' K ', 1,1) >0;



76. How to display the prior connection user

Show USER



77. How to view the path of the data file placement

Col file_name Format A50

Sql> Select Tablespace_name,file_id,bytes/1024/1024,file_name from

Dba_data_files ORDER by file_id;



78. How to view the now rolling back section and its state

Sql> Col Segment Format A30

Sql> SELECT

Segment_name,owner,tablespace_name,segment_id,file_id,status from

Dba_rollback_segs



79. How do I change the check range for the initial definition of a single position?

sql> ALTER TABLE XXX drop constraint constraint_name;

And then create new bundles:

sql> ALTER TABLE XXX add constraint constraint_name check ();



What are the common system files for Oracle?

The following video shows these file information: V$database,v$datafile,v$logfile v$controlfile

V$parameter;



81. Connect Inner JOIN

Select a.* from Bsempms A,bsdptms b where a.dpt_no=b.dpt_no;



82. How to connect outside

Select a.* from Bsempms A,bsdptms b where a.dpt_no=b.dpt_no (+);

Select a.* from Bsempms A,bsdptms b wherea.dpt_no (+) =b.dpt_no;



83. How to execute this SQL file

sql>@ $PATH/filename.sql;



84. How to quickly clear a large table

Sql>truncate table table_name;



85. How to find out how many data library examples

Sql>select * from V$instance;



86. How to find out how many tables there are in the data library

Sql>select * from All_tables;



87. How to test the time used to perform SQL sentences

Sql>set timing on;

Sql>select * FROM TableName;



There are a lot of things that you might see when you're using Oracle, especially for starters, today I'm simply going to wrap it up and give it to everyone,

I hope we can help! And we explore together, together to move!



It is not necessary for Oracle masters to be seen.



The inverse of CHR () is

ASCII ()

SELECT CHAR (DUAL) from;

SELECT ASCII (' A ') from DUAL;



89. String connections

SELECT CONCAT (col1,col2) from TABLE;

SELECT col1| | COL2 from TABLE;



90. How do you bring the results of select out into a text file?

Sql>spool C:\ABCD. TXT;

Sql>select * FROM table;

SQL >spool off;



91. How do I estimate the I/O number of the SQL execute

Sql>set autotrace on;

Sql>select * from TABLE;

OR

Sql>select * from V$filestat;

can view IO numbers



92. How to change the size of the sqlplus under the

ALTER TABLE table_name Modify (field_name VARCHAR2 (100));

Change the big line, change the small no (unless all is empty)



93. How to find out the information of one day

SELECT * FROM table_name where

Trunc (Date-=to_date) (' 2003-05-02 ', ' yyyy-mm-dd ');



How do I insert a full year date in the SQL language sentence?

CREATE TABLE Bsyear (d date);

INSERT INTO Bsyear

Select To_date (' 20030101 ', ' YYYYMMDD ') +rownum-1

From All_objects

where RowNum <= to_char (to_date (' 20031231 ', ' yyyymmdd '), ' ddd ');



95. If you modify the table name

ALTER TABLE Old_table_name Rename to New_table_name;



96. How do I get the return state value of the command?

Sqlcode=0



97. How to know the rights of the user

SELECT * from Dba_sys_privs;



98. What is the difference between the oracle9i from the Internet and the Standard Edition sold in the marketplace?

There is no difference in functionality, except that Oracle has clear rules; Oracle products downloaded from the website are not to be used for commercial purposes or to be invaded.



99. How do you judge whether the library is running in the mode of in-line or in the non-in-file mode?

Enter the Dbastudio,--〉 library---to view.



What are the differences between Sql>startup pfile and ifile,spfiled?

Pfile is the Oracle traditional initialization reference file, in text format.

IFile is similar to the Include in C language, used to introduce another file into

SPFile is a new and implicit reference file in 9i, the binary format

You should only pick up pfile after startup.



101. How to search for the first n records?

SELECT * from EmpLOYEE WHERE rownum < n

Order BY Empno;



102. How to know how much Oracle support on the machine and how many customers

Sql>conn internal;

Sql>show parameter processes;



Can db_block_size be modified?

Generally not, do not recommend this to do.



104. How to calculate the total number of two tables

Select (select count (id) from AA) + (select COUNT (id) from BB)

from dual;



105. How do you find the nth large value in a column by using an SQL sentence?

SELECT * FROM

(select T.*,dense_rank () over (order by Sal) rank from employee)

where rank = N;

106. How do I add 2 years to the present date? (

Select Add_months (sysdate,24) from dual;



What does used_ublk mean by negative value?

It is "harmless".



What does 108 Connect string mean?

Should be the contents of the service name in the Tnsnames.ora.



109. How to expand the size of the redo log?

Set up a temporary redolog group, then switch to the magazine, delete the previous journal, and create a new journal.



is tablespace not larger than 4G?

There is no limit.



111. Returns the minimum integer value greater than n

SELECT ceil (N) from DUAL;



112. Returns the minimum integer value less than equal to n

SELECT FLOOR (N) from DUAL;



113. Return to the last day of the previous month

SELECT Last_day (sysdate) from DUAL;



114. How different user data are used to guide

IMP System/manager File=aa. DMP Fromuser=user_old touser=user_new

Rows=y indexes=y;



115. How to find the name of the primary key in the data library

Sql>select * from user_constraints WHERE constraint_type= ' P ' and

Table_name= ' table_name ';



116. The function of the combination of two fruit sets

Sql>select * FROM Bsempms_old INTERSECT SELECT * from Bsempms_new;

Sql>select * from the Bsempms_old UNION SELECT * from Bsempms_new;

Sql>select * from Bsempms_old UNION all SELECT * from bsempms_new;



117. Functions that subtract from two fruit sets

Sql>select * from Bsempms_old minus SELECT * from Bsempms_new;



118. How to configure sequence

Jian sequence Seq_custid

Create sequence Seq_custid start 1 incrememt by 1;

When building a table:

CREATE TABLE Cust

{cust_id smallint NOT NULL,

...}

When you insert:

INSERT INTO Table Cust

VALUES (Seq_cust.nextval, ...)



The usual notation of the various parts of a date

119&gt: The writing method of the year in which the time is taken

SELECT to_char (sysdate, ' YYYY ') from DUAL;



120&gt: The writing method of the month of the time

SELECT to_char (sysdate, ' MM ') from DUAL;



121&gt: The day of the time to write

SELECT to_char (sysdate, ' DD ') from DUAL;



122&gt: The writing method of time

SELECT to_char (sysdate, ' HH24 ') from DUAL;



123&gt: The writing method of the time point

SELECT to_char (sysdate, ' MI ') from DUAL;



124&gt: The writing of the Seconds of time

SELECT to_char (sysdate, ' SS ') from DUAL;



125&gt: The written method of the date of the time

SELECT TRUNC (sysdate) from DUAL;



126&gt: The time of Time to write

SELECT to_char (sysdate, ' HH24:MI:SS ') from DUAL;



127> date, the time form becomes the character shape

SELECT To_char (sysdate) from DUAL;



128&gt a string into a date or time form:

SELECT to_date (' 2003/08/01 ') from DUAL;



129> Returns the number of weeks to write the parameters:

SELECT to_char (sysdate, ' D ') from DUAL;



130&gt: Return to the first few days of the year

SELECT to_char (sysdate, ' DDD ') from DUAL;



131&gt. Returns the number of seconds between the time values specified in the midnight and the parameters:

SELECT to_char (sysdate, ' sssss ') from DUAL;



132&gt: Returns the writing of the first few weeks of a year in the parameters

SELECT to_char (sysdate, ' WW ') from DUAL;



There are a lot of things that you might see when you're using Oracle, especially for starters, today I'm simply going to wrap it up and give it to everyone,

I hope we can help! And we explore together, together to move!



It is not necessary for Oracle masters to be seen.



Virtual position



Currval and Nextval.

Create a sequence for a table

CREATE SEQUENCE empseq ...;

SELECT Empseq.currval from DUAL;

The number of the auto insert sequence

INSERT into EMP

VALUES (Empseq.nextval, ' LEWIS ', ' clerk ',

7902, Sysdate, 1200, NULL, 20);



134. RowNum

Ordinal number of rows sorted by setting

SELECT * from emp WHERE rownum < 10;

135. ROWID

Returns the physical address of a row

SELECT ROWID, ename from emp WHERE deptno = 20;



136. Convert n seconds to the hour and minute format?

Set Serverout on

Declare

N number: = 1000000;

RET VARCHAR2 (100);

Begin

RET: = trunc (n/3600) | | ' Hours ' | |

To_char (mod (n,3600), ' sssss '), ' fmmi ' cent ' ss ' seconds ');

Dbms_output.put_line (ret);

End



137. How to find out how to do a larger sort of progress?

SELECT B.tablespace, b.segfile#, b.segblk#, B.blocks, A.sid,

a.serial#,

A.username, A.osuser, A.status

From V$session A,v$sort_usage b

WHERE a.saddr = b.session_addr

Order by B.tablespace, b.segfile#, b.segblk#, b.blocks;



138. How do I find the SQL sentence for a larger sort of process?

Select/*+ ORDERED * * Sql_text from V$sqltext a

where A.hash_value = (

Select Sql_hash_value from V$session b

where B.sid = &sid and b.serial# = &serial)

Order by piece ASC;



139. How do I find a replay?

SELECT * FROM table_name

WHERE rowid!= (SELECT MAX (ROWID) from table_name D

WHERE table_name. COL1=D.COL1 and TABLE_NAME. COL2=D.COL2);

140. How do I delete the replay?

DELETE from table_name

WHERE rowid!= (SELECT MAX (ROWID) from table_name D

WHERE table_name. COL1=D.COL1 and TABLE_NAME. COL2=D.COL2);



141. How to quickly translate all the pictures?

SQL >spool VIEW1. Sql

SQL >select ' ALTER VIEW ' | | tname| | '

COMPILE; ' From TAB;

SQL >spool off

Then execute view1.sql.

SQL > @VIEW1. SQL;



The solution of ORA-01555 SNAPSHOT TOO old

Increase the value of the minextents, increase the size of the area, and set a high optimal value.

143. There is not enough space in the rollback section of the task, the table space is filled with (ORA-01560 error), and the rolling section is expanded to reach the parameters.

The maxextents value (ORA-01628) solution.

Add a file to the Rollback section table space or make an existing file larger; Increase the value of the maxextents.



144. How do I encrypt Oracle's storage process?

The following stored Chengnejong are placed in the Aa.sql file

Create or Replace procedure TESTCCB (i in number) as

Begin

Dbms_output.put_line (' Input parameters are ' | | To_char (i));

End

Sql>wrap Iname=a.sql;

Pl/sql wrapper:release 8.1.7.0.0-production on Tue Nov 27

22:26:48 2001

Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.

Processing Aa.sql to AA.PLB

Running AA.PLB.

Sql> @AA. PLB;



145. How to control the waiting of cases?

Select Event,sum (Decode (wait_time,0,0,1)) "Prev",

SUM (decode (wait_time,0,1,0)) "Curr", COUNT (*) "Tot"

From v$session_wait

Group BY event order by 4;



146. How to get back to the war in the section?

Select name, waits, gets, waits/gets "Ratio"

From V$rollstat C, V$rollname D

where C.usn = D.usn;



147. How to control the I/O ratio of the table space?

Select B.tablespace_name name,b.file_name "file", A.phyrds Pyr,

A.phyblkrd pbr,a.phywrts pyw, A.phyblkwrt PBW

From V$filestat A, Dba_data_files B

where a.file# = b.file_id

Order BY B.tablespace_name;



148. How do I control the I/O ratio of the file system?

Select substr (c.file#,1,2) "#", substr (c.name,1,30) "Name",

C.status, C.bytes, D.phyrds, D.phywrts

From V$datafile C, V$filestat D

where c.file# = d.file#;



149. How to find all the indexes under a user?

Select User_indexes.table_name,

User_indexes.index_name,uniqueness, COLUMN_NAME

From User_ind_columns, user_indexes

where user_ind_columns.index_name = User_indexes.index_name

and user_ind_columns.table_name = User_indexes.table_name

Order by User_indexes.table_type, User_indexes.table_name,

User_indexes.index_name, column_position;



150. How to control the hit rate of the SGA?

Select A.value + b.value "logical_reads", C.value "Phys_reads",

Round ((a.value+b.value)-c.value)/(A.value+b.value))

"BUFFER HIT RATIO"

From V$sysstat A, V$sysstat B, V$sysstat c

where a.statistic# = b.statistic# = 39

and c.statistic# = 40;