- Delete the Duo Record of the BM in Table A Delete from a a where a.rowid! = (SELECT MAX (RowID) from a b where A.bm = b.bm);
- Find a record in Table B in Table A Select * from a where not exists (SELECT 'X' from b where A.bm = b.bm);
- Remove the record in Table B in Table A delete from a where not exists (SELECT 'x' from b where a.bm = b.bm);
- Find a page query for the specified number of records SELECT * FROM (SELECT NJC_NBDW. *, ROWNUM RR from NJC_NBDW WHERE ROWNUM <5) B Where B.RR> 2;
- View whether data files are automatically extended - use system tables: --v $ datafile: Store information on data files in the database - V $ fileStat: Statistics to access data files in the storage system
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE from DBA_DATA_FILES ORDER BY FILE_ID;
- See the Oracle version and installed which options Col Parameter Format A60COL Value Format A10Select * from sys.v_ $ Option;
- View the remaining memory SELECT POOL, NAME, SGASIZE / 1024/1024 "Free Space (k)", Round (Bytes / Sgasize * 100, 2) ", Round (Bytes / Sgasize * 100, 2)" Free Space Percent (%) "From (select sum (bytes) sgasize from sys.v_ $ sgastat) s, sys.v_ $ sgastat f where f.name = 'free memory'
--SGA area summary SELECT SUM (BYTES) SGASIZE from Sys.v_ $ SGASTAT;
- View which users have sysdba, sysoperselect * from v $ pwfile_users;
- Constraint in the query table Select * from user_constraints where table_name = 'njc_nbdw';
- Reconstruction index
Alter Index Index Name RebuildTableSpace Index Table Space Name Storage (Initial initial value Next extension) NOLOGGING
--the high-level structure of a top-n analysis query is: select [column_list], rownumfrom (select [colum_list] from table order by top_n_colum) where rownum <= n; - example of top_n analysis: - to display The Top Three Earner Names and Sararies from The Emp Tableselect Name, Salary, RownumFrom (SELECT NAME, SALARY FROM EMP ORDER BY SALY DESC) Where rownum <= 3;
- Using External Table - You cannot use VARCHAR, otherwise the query is wrong, strange -1, create a directory Create or Replace Directory Emp_Dir as 'e: / flat_files'; - 2, create External TableCreate Table Oldemp (Empno Number, empname varchar2 (20), birthdate date) organization external (type oracle_loader default directory emp_dir access parameters (records delimited by newLine badfile 'bad emp' logfile 'log_emp' fields terminated by ',' (empno char, empname char, birthdate char date_format date mask "dd-mon-yyyy")) location ( 'emp1.txt')) parallel 5 reject limit 200; - create index with create tablecreate table new_emp {employee_id number primary key using index (create index emp_id_index on new_emp (employee_id) ), First_name varchar2 (30), last_name varcha2 (30)))))
--Find WHETER THE DATABASE IS Archiving Modest Archiver from V $ Instance;
- How to view this machine IP address on the Oracle server? Select Sys_Context ('useerenv', 'ip_address') from dual;
- Give the table, Collects Comment on Table Table IS 'Table Note'; Comment On Column Table. Column IS 'Column Note';
- How to get a carriage return in the string SELECT 'WELCOME TO VISIT' || CHR (10) || 'www.9cbs.net' from DUAL
- How to track sql statement executed by the user alter session set sql_trace = true; select username, sid, serial # from v $ session where username = 'TESGE'; EXECUTE dbms_system.set_sql_trace_in_session (& SID, & SERIALNUM, TRUE);
Use Tkprof ****. Trc newname.trc in CMD; format the monitored SQL
- Take a front strip, the previous, the next select * from njc_nbdw where dw_id in (SELECT MAX (DW_ID) from njc_nbdw where dw_id <18) Union All (DW_ID) from NJC_nbdw where dw_id> 18) Union All Select 18 dw_id from dual)
- Find the previous record of the DW_ID 22, the next and the record record. select * from njc_nbdw where dw_id> = (select dw_id from (select dw_id from njc_nbdw where dw_id <22 order by dw_id desc) where rownum <2) and rownum - Query DB Blocks Get: SELECT NAME, VALUE from V $ Sysstat WHERE Name in ('DB Block Gets', 'Consistent Gets', 'Physical Reads'); - Query hits the hit rate, requires more than 90%. Hit Ratio = 1 - (Physical Reads / (DB Block Gets Consistent Gets) --Buffer Pool Hit Ratiosselect Name, Physical_Reads, DB_BLOCK_GETS, CONSISTENT_GETS, 1 - (Physical_Reads / (DB_BLOCK_GETS Consistent_Gets) "Hit Ratio" from v $ buffer_pool_statistics; - Query the user's SQL performance Select Area. * From V $ SQLAREA AREA, V $ session s where area.hash_value = s.sql_hash_valueand area.address = s.sql_address and s.sid = & SID; - the SID can be used The following statement gets the SELECT SID FROM V $ session where username = 'TESGE'; - SQLSELECT SQL_TEXTFROM V $ SQLTEXT AWHERE A.hash_Value = (Select SQL_HASH_VALUE FROM V $ SID ') Order By Piece ASC - Query SharePool Space Select Free_Space, Free_Count, Request_Failures, Request_Misses, Last_Failure_Size from V $ Shared_Pool_ReServed; - Force the use of a rod-specific variable, modify the session alter session set cursor_sharing = force - Modify system ALTER SESTEM SET CURSOR_SHARING = force; Check the system authority for Role, try the following SQL> Conn T / TConnected.SQL> Select * from session_roles; Role ---------------------------- CONNECTRESOURCE SQL> SELECT privilege2 from role_sys_privs3 where role = 'resource'; - View Database Link Select * from sys.link $; - View Database Current Global Instance Name Select * from global_name; - How many fields in the query table SELECT Count (Column_name) from user_tab_columns where table_name = 's_emp' - the data in this table is also moved to new table space while moving this table to a new table space. ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME is in the data file; DB_Cache_HIT_RATIO.SQL / * The Database Buffer Cache Hit Ratio Should Be Greater Than 90% ON OLTPSYSTETEM. * // * Otherwise Increase The size of db_cache_size z SGA_MAX_SIZEBOUNDARY. * / Select 1- (PHY.Value - Lob.Value - Diru Value) /ses.value "Cache Hit Ratio" from V $ SYSSTAT SES, V $ SYSSSTAT LOB, V $ SYSSTAT DIR, V $ sysstat phywhere ses.name = ' Session Logical Reads'and Dir.name = 'Physical Reads Direct'and Lob.Name =' Physical Reads Direct (LOB) 'and PHY.NAME =' Physical Reads' / Library_cache_hit_ratio.sql / * The Overall Library Cache Hit Ratio Should Be Greater Than 99% * // * if The Reloads to Pins Ratio Is Great Than 1%, Increase Shared_Pool_Size. * / Select SUM (Pins-Reloads) / SUM (Pins) * 100 "Library Cache Hit Ratio" from v $ librarycache / select sum (pins "Executions", SUM (Reloads) "Misses", SUM (Reloads) / SUM (Pins) ) "Reload Ratio" from v $ librarycache / Dictionary_cache_hit_ratio.sql / * The Overall Dictionary Cache Hit Ratio Should Be Greater Than 85%. * / Select (gets-getmisses) / sum (gets) "Dictionary Cache Hit Ratio" from V $ ROWCACHE / ------------------------- -------------------------------------------------- --- Calculate the cache hit ratio for the library cache with the folowing query: SELECT SUM (PINHITS) / SUM (PINS) "Hit Ratio", SUM (SUM (PINS) "Reload Percent" from V $ librarycachewhere namespace in ('SQL Area', 'Table / Procedure', 'Body', 'Trigger'); The hit ratio should be 85% (ie 0.85). The reload percent should be very low, 2% (ie 0.02) or less. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. Although less likely, the init.ora Parameter Open_CURSORS May Also Need to Increased. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- SQL1.SeLect from the database level lookup client SID, UserName from V $ session; find the session ID of the client you want to find. Select SQL_Text from V $ SQLText a where a.hash_value = (Select SQL_HASH_VALUE FROM V $ Session B Where B.SID = '& SID') Order by Piece ASC / ---------------- -------------------------------------------------- -------------------- Unix multi-process server, you can see the PID through the PS command select / * ORDERED * / sql_text from v $ sqltext awhere a.hash_value = (select sql_hash_value from v $ session bwhere b.paddr = (select addr from v $ process cwhere c.spid = '& spid')) order by piece asc /
