Shulou(Shulou.com)06/01 Report--

This article shows you what oracle commonly used classic SQL queries are, which are concise and easy to understand, which can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Classic SQL queries commonly used in oracle

Common SQL queries:

1. View the name and size of the tablespace

Select t.tablespace_name, round (sum (bytes/ (1024-1024)), 0) ts_size

From dba_tablespaces t, dba_data_files d

Where t.tablespace_name = d.tablespace_name

Group by t.tablespace_name

2. View the name and size of the tablespace physical file

Select tablespace_name, file_id, file_name

Round (bytes/ (1024,1024), 0) total_space

From dba_data_files

Order by tablespace_name

3. View the name and size of the rollback segment

Select segment_name, tablespace_name, r.status

(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent

Max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn (+)

Order by segment_name

4. View the control file

Select name from v$controlfile

5. View log files

Select member from v$logfile

6. Check the use of tablespaces

Select sum (bytes) / (1024024) as free_space,tablespace_name

From dba_free_space

Group by tablespace_name

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE

(B.BYTES*100) / A.BYTES "% USED", (C.BYTES*100) / A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A, S, B, S, Y, C, C, etc.

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME

7. View database objects

Select owner, object_type, status, count (*) count# from all_objects group by owner, object_type, status

8. Check the database version

Select version FROM Product_component_version

Where SUBSTR (PRODUCT,1,6) = 'Oracle'

9. View the creation date and archiving method of the database

Select Created, Log_Mode, Log_Mode From V$Database

10. Capture SQL that has been running for a long time

Column username format a12

Column opname format a16

Column progress format a8

Select username,sid,opname

Round (sofar*100 / totalwork,0) | |'%'as progress

Time_remaining,sql_text

From v$session_longops, v$sql

Where time_remaining 0

And sql_address = address

And sql_hash_value = hash_value

/

eleven. View the parameter information of the data table

SELECT partition_name, high_value, high_value_length, tablespace_name

Pct_free, pct_used, ini_trans, max_trans, initial_extent

Next_extent, min_extent, max_extent, pct_increase, FREELISTS

Freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks

Empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size

Last_analyzed

FROM dba_tab_partitions

-WHERE table_name =: tname AND table_owner =: towner

ORDER BY partition_position

twelve。 View transactions that have not yet been committed

Select * from v$locked_object

Select * from v$transaction

thirteen. Find out which processes object is used by

Select

P.spid

S.sid

S.serial# serial_num

S.username user_name

A.type object_type

S.osuser os_user_name

A.owner

A.object object_name

Decode (sign (48-command))

one,

To_char (command), 'Action Code #' | | to_char (command)) action

P.program oracle_process

S.terminal terminal

S.program program

S.status session_status

From v$session s, v$access a, v$process p

Where s.paddr = p.addr and

S.type = 'USER' and

A.sid = s.sid and

A. objectfully supported ATTR'

Order by s.username, s.osuser

fourteen. Rollback segment view

Select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents

Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs

V$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes

Sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs

V$rollname where v$rollname.name (+) = sys.dba_rollback_segs.segment_name and

V$rollstat.usn (+) = v$rollname.usn order by rownum

fifteen. Resource-consuming process (top session)

Select s.schemaname schema_name, decode (sign (48-command), 1

To_char (command), 'Action Code #' | | to_char (command)) action, status

Session_status, s.osuser os_user_name, s.sid, p.spid, s.serial# serial_num

Nvl (s.username,'[Oracle process]') user_name, s.terminal terminal

S.program program, st.value criteria_value from v$sesstat st, v$session s, v$process p

Where st.sid = s.sid and st.statistic# = to_number ('38') and (' ALL' = 'ALL'

Or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

sixteen. Check the status of lock

Select / * + RULE * / ls.osuser os_user_name, ls.username user_name

Decode (ls.type, 'RW',' Row wait enqueue lock', 'TM',' DML enqueue lock', 'TX'

'Transaction enqueue lock',' UL', 'User supplied lock') lock_type

O.object_name object, decode (ls.lmode, 1, null, 2, 'Row Share', 3

'Row Exclusive', 4,' Share', 5, 'Share Row Exclusive', 6,' Exclusive', null)

Lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2

From sys.dba_objects o, (select s.osuser, s.username, l.type

L.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s

V$lock l where s.sid = l.sid) ls where o.object_id = ls.id1 and o.owner

'SYS' order by o.owner, o.object_name

seventeen. Check waiting (wait)

SELECT v$waitstat.class, v$waitstat.count count, SUM (v$sysstat.value) sum_value

FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets'

'consistent gets') group by v$waitstat.class, v$waitstat.count

eighteen. Check the sga situation

SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC

nineteen. View catched object

SELECT owner, name, db_link, namespace

Type, sharable_mem, loads, executions

Locks, pins, kept FROM v$db_object_cache

twenty. View V$SQLAREA

SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS

VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS

USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS

BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA

twenty-one. View the number of object categories

Select decode (o.typewriter page1) index', 2 recordings table', 3, 'CLUSTER', 4,' VIEW', 5

'SYNONYM', 6,' SEQUENCE', 'OTHER') object_type, count (*) quantity from

Sys.obj$ o where o.type# > 1 group by decode.

, 'CLUSTER', 4,' VIEW', 5, 'SYNONYM', 6,' SEQUENCE', 'OTHER') union select

'COLUMN', count (*) from sys.col$ union select' DB LINK', count (*) from

twenty-two. View object categories by user

Select u.name schema, sum (decode (o.typewriter, 1,1, NULL) indexes

Sum (decode (o.typewriter, 2,1, NULL)) tables, sum (decode (o.typewriter, 3,1, NULL))

Clusters, sum (decode (o.typewriter, 4,1, NULL) views, sum (decode (o.typewriter, 5,1)

NULL)) synonyms, sum (decode (o.typewriter, 6,1, NULL)) sequences

Sum (decode (o.typewriter, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))

Others from sys.obj$ o, sys.user$ u where o.type# > = 1 and u.user# =

O.owner# and u.name 'PUBLIC' group by u.name order by

Sys.link$ union select 'CONSTRAINT', count (*) from sys.con$

twenty-three. Information about connection

1) check which users are connected

Select s.osuser os_user_name, decode (sign (48-command), 1, to_char (command)

'Action Code #' | | to_char (command)) action, p.program oracle_process

Status session_status, s.terminal terminal, s.program program

S.username user_name, s.fixed_table_sequence activity_meter,''query

0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num

From v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'

Order by s.username, s.osuser

2) check the resource usage of the corresponding connection according to v.sid

Select n.name

V.value

N.class

N.statistic#

From v$statname n

V$sesstat v

Where v.sid = 71 and

V.statistic# = n.statistic#

Order by n.class, n.statistic#

3) View the running sql of the corresponding connection according to sid

Select / * + PUSH_SUBQ * /

Command_type

Sql_text

Sharable_mem

Persistent_mem

Runtime_mem

Sorts

Version_count

Loaded_versions

Open_versions

Users_opening

Executions

Users_executing

Loads

First_load_time

Invalidations

Parse_calls

Disk_reads

Buffer_gets

Rows_processed

Sysdate start_time

Sysdate finish_time

'>' | | address sql_address

'N' status

From v$sqlarea

Where address = (select sql_address from v$session where sid = 71)

24. Query tablespace usage

Select a.tablespace_name Tablespace name

100-round ((nvl (b.bytesfreemagen0) / a.bytes_alloc) * 100Pol 2) "occupancy (%)"

Round (a. Bytespromoallocach1024 and1024 and2) "capacity (M)"

Round (nvl (b. Bytesfree freegraghting 0) / 1024tick 1024pr 2) "Idle (M)"

Round ((a.bytes_alloc-nvl (b.bytesfolk freegrad0)) / 1024Universe 1024pr 2) "use (M)"

Largest "maximum extension (M)"

To_char (sysdate,'yyyy-mm-dd hh34:mi:ss') "sampling time"

From (select f.tablespace_name

Sum (f.bytes) bytes_alloc

Sum (decode (f. Autoextensible.YEScription.maxbytesRetroductionNozzybook.bytes)) maxbytes

From dba_data_files f

Group by tablespace_name) a

(select f.tablespace_name

Sum (f.bytes) bytes_free

From dba_free_space f

Group by tablespace_name) b

(select round (max (ff.length) * 16 take 1024) Largest

Ts.name tablespace_name

From sys.fet$ ff, sys.file$ tf,sys.ts$ ts

Where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#

Group by ts.name, tf.blocks) c

Where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

25. Query the degree of fragmentation of the tablespace

Select tablespace_name,count (tablespace_name) from dba_free_space group by tablespace_name

Having count (tablespace_name) > 10

Alter tablespace name coalesce

Alter table name deallocate unused

Create or replace view ts_blocks_v as

Select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space

Union all

Select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents

Select * from ts_blocks_v

Select tablespace_name,sum (bytes), max (bytes), count (block_id) from dba_free_space

Group by tablespace_name

twenty-six. Query which database instances are running

Select inst_name from v$active_instances

=

# create a database-look $ORACLE_HOME/rdbms/admin/buildall.sql #

Create database db01

Maxlogfiles 10

Maxdatafiles 1024

Maxinstances 2

Logfile

GROUP 1 ('/ u01qoradata SIZE) SIZE 15m

GROUP 2 ('/ u01qoradata SIZE) SIZE 15m

GROUP 3 ('/ u01qoradata SIZE) SIZE 15m

Datafile 'u01CompOradata SIZE Db01According to db01Universe db01.dbf') Oradata

Undo tablespace UNDO

Datafile'/ u01qoradata SIZE db01Universe 01Universe db01.dbf' oradata 40m

Default temporary tablespace TEMP

Tempfile'/ u01qoradata SIZE db01Universe 01tobacco db01.dbf' oradata 20m

Extent management local uniform. Size 128k

Character set AL32UTE8

National character set AL16UTF16

Set time_zone='America/New_York'

# data dictionary #

Set wrap off

Select * from v$dba_users

Grant select on table_name to user/rule

Select * from user_tables

Select * from all_tables

Select * from dba_tables

Revoke dba from user_name

Shutdown immediate

Startup nomount

Select * from v$instance

Select * from v$sga

Select * from v$tablespace

Alter session set nls_language=american

Alter database mount

Select * from v$database

Alter database open

Desc dictionary

Select * from dict

Desc v$fixed_table

Select * from v$fixed_table

Set oracle_sid=foxconn

Select * from dba_objects

Set serveroutput on

Execute dbms_output.put_line ('sfasd')

# Control file #

Select * from v$database

Select * from v$tablespace

Select * from v$logfile

Select * from v$log

Select * from v$backup

/ * back up user tablespaces * /

Alter tablespace users begin backup

Select * from v$archived_log

Select * from v$controlfile

Alter system set control_files='$ORACLE_HOME/oradata/u01/ctrl01.ctl'

'$ORACLE_HOME/oradata/u01/ctrl02.ctl' scope=spfile

Cp $ORACLE_HOME/oradata/u01/ctrl01.ctl $ORACLE_HOME/oradata/u01/ctrl02.ctl

Startup pfile='../initSID.ora'

Select * from v$parameter where name like 'control%'

Show parameter control

Select * from v$controlfile_record_section

Select * from v$tempfile

/ * backup control files * /

Alter database backup controlfile to'.. / filepath/control.bak'

/ * back up the control file and change the binary control file into an asc text file * /

Alter database backup controlfile to trace

# redo log #

Archive log list

Alter system archive log start;-- starts automatic archiving

Alter system switch logfile;-- forced a log switch.

Alter system checkpoint;-- forces a checkpoint

Alter tablspace users begin backup

Alter tablespace offline

/ * checkpoint synchronization frequency parameter FAST_START_MTTR_TARGET. The higher the synchronization frequency, the shorter the system recovery time * /

Show parameter fast

Show parameter log_checkpoint

/ * join a log group * /

Alter database add logfile group 3 ('/ $ORACLE_HOME/oracle/ora_log_file6.rdo' size 10m)

/ * join a member of the log group * /

Alter database add logfile member'/ $ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3

/ * Delete log groups: current log groups cannot be deleted; active log groups cannot be deleted; non-archived log groups cannot be deleted * /

Alter database drop logfile group 3

/ * Delete a member of a log group, but the last member of each group cannot be deleted * /

Alter databse drop logfile member'$ORACLE_HOME/oracle/ora_log_file6.rdo'

/ * clear online log * /

Alter database clear logfile'$ORACLE_HOME/oracle/ora_log_file6.rdo'

Alter database clear logfile group 3

/ * clear non-archive logs * /

Alter database clear unarchived logfile group 3

/ * rename log file * /

Alter database rename file'$ORACLE_HOME/oracle/ora_log_file6.rdo' to'$ORACLE_HOME/oracle/ora_log_file6a.rdo'

Show parameter db_create

Alter system set db_create_online_log_dest_1='path_name'

Select * from v$log

Select * from v$logfile

/ * the exchange from database archiving mode to non-archiving mode can not be changed until it is started to mount; startup mount; and then open the database. * /

Alter database noarchivelog/archivelog

Achive log start;--- starts automatic archiving

Alter system archive all;-- archives all log files manually

Select * from v$archived_log

Show parameter log_archive

# analyze log file logmnr #

1) set utl_file_dir parameters in init.ora

2) restart oracle

3) create directory file

Desc dbms_logmnr_d

Dbms_logmnr_d.build

4) add log file add/remove log file

Dhms_logmnr.add_logfile

Dbms_logmnr.removefile

5) start logmnr

Dbms_logmnr.start_logmnr

6) query the analyzed content v$logmnr_content-- sqlredo/sqlundo

Practice:

Desc dbms_logmnr_d

/ * do some operations on the data table to prepare for the recovery operation * /

Update table set qty=10 where stor_id=6380

Delete table where stor_id=7066

/ * /

The path to utl_file_dir

Execute dbms_logmnr_d.build ('foxdict.ora','$ORACLE_HOME/oracle/admin/fox/cdump')

Execute dbms_logmnr.add_logfile ('$ORACLE_HOME/oracle/ora_log_file6.log',dbms_logmnr.newfile)

Execute dbms_logmnr.start_logmnr (dictfilename= >'$ORACLE_HOME/oracle/admin/fox/cdump/foxdict.ora')

# tablespace #

Select * form. V$tablespace

Select * from v$datafile

/ * correspondence between tablespaces and data files * /

Select t1.namedirection t2.name from v$tablespace T1 where t1.ts#=t2.ts# vicidatafile T2

Alter tablespace users add datafile 'path' size 10m

Select * from dba_rollback_segs

/ * limit the usage limit of users in a tablespace * /

Alter user user_name quota 10m on tablespace_name

Create tablespace xxx [datafile 'path_name/datafile_name'] [size xxx] [extent management local/dictionary] [default storage (xxx)]

Exmple: create tablespace userdata datafile'$ORACLE_HOME/oradata/userdata01.dbf' size 100m AUTOEXTEND ON NEXT 5m MAXSIZE 200m

Create tablespace userdata datafile'$ORACLE_HOME/oradata/userdata01.dbf' size 100m extent management dictionary default storage (initial 100k next 100k pctincrease 10) offline

After / * 9i, oracle recommends using local management instead of dictionary management, because local uses bitmap to manage tablespaces, which will not result in voluntary contention for system tablespaces.

Create tablespace userdata datafile'$ORACLE_HOME/oradata/userdata01.dbf' size 100m extent management local uniform. Size 1m

Create tablespace userdata datafile'$ORACLE_HOME/oradata/userdata01.dbf' size 100m extent management local autoallocate

/ * when creating a tablespace, set the segment space management mode in the tablespace. Here, automatic management is used * /

Create tablespace userdata datafile'$ORACLE_HOME/oradata/userdata01.dbf' size 100m extent management local uniform. Size 1m segment space management auto

Alter tablespace userdata mininum extent 10

Alter tablespace userdata default storage (initial 1m next 1m pctincrease 20)

/ * undo tablespace (cannot be used in dictionary management mode) * /

Create undo tablespace undo1 datafile'$ORACLE_HOME/oradata/undo101.dbf' size 40m extent management local

Show parameter undo

/ * temporary tablespace*/

Create temporary tablespace userdata tempfile'$ORACLE_HOME/oradata/undo101.dbf' size 10m extent management local

/ * set the database default temporary tablespace * /

Alter database default temporary tablespace tablespace_name

/ * system / temporary / online undo tablespaces cannot be offline*/

Alter tablespace tablespace_name offline/online

Alter tablespace tablespace_name read only

/ * rename user tablespace * /

Alter tablespace tablespace_name rename datafile'$ORACLE_HOME/oradata/undo101.dbf' to'$ORACLE_HOME/oradata/undo102.dbf'

/ * rename the system tablespace, but you must shutdown the database and restart it to the mount state before renaming * /

Alter database rename file'$ORACLE_HOME/oradata/system01.dbf' to'$ORACLE_HOME/oradata/system02.dbf'

Drop tablespace userdata including contents and datafiles;---drop tablespce

/ * resize tablespace,autoextend datafile space*/

Alter database datafile'$ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10m maxsize 500m

/ * resize datafile*/

Alter database datafile'$ORACLE_HOME/oradata/undo102.dbf' resize 50m

/ * expand the tablespace * /

Alter tablespace userdata add datafile'$ORACLE_HOME/oradata/undo102.dbf' size 10m

/ * set the tablespace to OMF state * /

Alter system set db_create_file_dest='$ORACLE_HOME/oradata'

Create tablespace userdata;---use OMF status to create tablespace

Drop tablespace userdata;---user OMF status to drop tablespace

Select * from dba_tablespace/v$tablespace/dba_data_files

/ * move a partition of a table to another tablespace * /

Alter table table_name move partition partition_name tablespace tablespace_name

# ORACLE storage structure and relationships #

/ * manually assign the partition (extend) size of the tablespace segment * /

Alter table kong.test12 allocate extent (size 1m datafile'$ORACLE_HOME/oradata/undo102.dbf')

Alter table kong.test12 deallocate unused;-release unused partitions in the table

Show parameter db

Alter system set db_8k_cache_size=10m;-configure 8k blocks of memory space block parameters

Select * from dba_extents/dba_segments/data_tablespace

Select * from dba_free_space/dba_data_file/data_tablespace

/ * number of bytes occupied by the data object * /

Select sum (bytes) from dba_extents where nwer='kong' and segment_name = 'table_name'

# UNDO Data #

Show parameter undo

Alter tablespace users offline normal

Alter tablespace users offline immediate

Recover datafile'$ORACLE_HOME/oradata/undo102.dbf'

Alter tablespace users online

Select * from dba_rollback_segs

Alter system set undo_tablespace=undotbs1

/ * ignore the error prompt for the rollback segment * /

Alter system set undo_suppress_errors=true

/ * in automatic management mode, rbs1; is not really established. In manual management mode, it can be established, and it is a private rollback segment * /

Create rollback segment rbs1 tablespace undotbs

Desc dbms_flashback

/ * after submitting the modified data, 9i provides the flashback operation of the old data and reads the pre-modified data only to the user, but this part of the data will not be restored in the table, but a mapping of the old data * /

Execute dbms_flashback.enable_at_time ('26-JAN-04:12:17:00 pm')

Execute dbms_flashback.disable

/ * Statistics of rollback segment * /

Select end_time,begin_time,undoblks from v$undostat

/ * the formula for calculating the size of undo table space: UndoSpace= [UR * (UPS * DBS)] + (DBS * 24)

UR: how long the UNDO_RETENTION is retained (seconds)

UPS: rollback blocks per second

DBS: system EXTENT and FILE SIZE (i.e. db_block_size) * /

Select * from dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction

Show parameter transactions

Show parameter rollback

/ * establish a common rollback segment in manual management mode * /

Create public rollback segment prbs1 tablespace undotbs

Alter rollback segment rbs1 online;---- in manual management mode

/ * in manual management mode, specify undo_management=manual, rollback_segment= ('rbs1','rbs2',...), and rollback_segment= in initSID.ora.

Transactions=100 、 transactions_per_rollback_segment=10

Then shutdown immediate, startup pfile=....\? .ora * /

# Managing Tables #

/ * char type maxlen=2000;varchar2 type maxlen=4000 bytes

Rowid is an 18-bit 64-bit string (10 bytes 80 bits)

Composition of rowid: object# (object number)-- 32 bits, 6 bits.

Rfile# (relative file number)-- 10 bits of 3 bits

Block# (block number)-- 22 bits, 6 bits.

Row# (line number)-- 16bitsline 3 digits

Base 64: a total of 64 symbols: Amura Zjimaqizhi 0-9 Jing Zuo Zeng Ji + 64 symbols

The functions in the dbms_rowid package can provide an explanation of rowid * /

Select rowid,dbms_rowid.rowid_block_number (rowid), dbms_rowid.rowid_row_number (rowid) from table_name

Create table test2

(

Id int

Lname varchar2 (20) not null

Fname varchar2 (20) constraint ck_1 check (fname like'k%')

Empdate date default sysdate)

) tablespace tablespace_name

Create global temporary table test2 on commit delete/preserve rows as select * from kong.authors

Create table user.table (...) Tablespace tablespace_name storage (...) Pctfree10 pctused 40

Alter table user.tablename pctfree 20 pctused 50 storage (...);-changing table storage

/ * assign partitions manually, and the assigned data file must be a data file in the tablespace where the table resides * /

Alter table user.table_name allocate extent (size 500k datafile '...')

/ * release unused space in the table * /

Alter table table_name deallocate unused

Alter table table_name deallocate unused keep 8k

/ * move the tablespace of the non-partitioned table to the new tablespace. After the tablespace is moved, the index objects in the original table will not be available and must be rebuilt * /

Alter table user.table_name move tablespace new_tablespace_name

Create index index_name on user.table_name (column_name) tablespace users

Alter index index_name rebuild

Drop table table_name [CASCADE CONSTRAINTS]

Alter table user.table_name drop column col_name [CASCADE CONSTRAINTS CHECKPOINT 1000];-drop column

/ * Mark unused columns in the table * /

Alter table user.table_name set unused column comments CASCADE CONSTRAINTS

/ * marked columns that are not used in the drop table * /

Alter table user.table_name drop unused columns checkpoint 1000

/ * when an exception occurs in drop col, use CONTINUE to prevent the previous column*/ from being deleted again

ALTER TABLE USER.TABLE_NAME DROP COLUMNS CONTINUE CHECKPOINT 1000

Select * from dba_tables/dba_objects

# managing indexes #

/ * create index*/

Example:

/ * create a general index * /

Create index index_name on table_name (column_name) tablespace tablespace_name

/ * create bitmap index * /

Create bitmap index index_name on table_name (column_name1,column_name2) tablespace tablespace_name

/ * pctused*/ cannot be used in the index

Create [bitmap] index index_name on table_name (column_name) tablespace tablespace_name pctfree 20 storage (inital 100k next 100k)

/ * indexing with a large amount of data is best not to log * /

Create [bitmap] index index_name table_name (column_name1,column_name2) tablespace_name pctfree 20 storage (inital 100k next 100k) nologging

/ * create inverted index * /

Create index index_name on table_name (column_name) reverse

/ * create a functional index * /

Create index index_name on table_name (function_name (column_name)) tablespace tablespace_name

/ * create constraints when creating tables * /

Create table user.table_name (column_name number (7) constraint constraint_name primary key deferrable using index storage (initial 100k next 100k) tablespace tablespace_name,column_name2 varchar2 (25) constraint constraint_name not null,column_name3 number (7) tablespace tablespace_name

/ * memory space parameters allocated to the creation bitmap index to speed up indexing * /

Show parameter create_bit

/ * change the storage parameters of the index * /

Alter index index_name pctfree 30 storage (initial 200k next 200k)

/ * manually assign a partition to the index * /

Alter index index_name allocate extent (size 200k datafile'$ORACLE/oradata/..')

/ * release useless space in the index * /

Alter index index_name deallocate unused

/ * Index Reconstruction * /

Alter index index_name rebuild tablespace tablespace_name

/ * Interchange between general index and inverted index * /

Alter index index_name rebuild tablespace tablespace_name reverse

/ * when rebuilding the index, the table is not locked * /

Alter index index_name rebuild online

/ * defragment the index * /

Alter index index_name COALESCE

/ * analyzing the index is actually a process of updating statistics * /

Analyze index index_name validate structure

Desc index_state

Drop index index_name

Alter index index_name monitoring usage;- monitors whether the index is used

Alter index index_name nomonitoring usage;---- cancels monitoring

/ * views about indexed information * /

Select * from dba_indexes/dba_ind_columns/dbs_ind_expressions/v$object_usage

# data integrity management (Maintaining data integrity) #

Alter table table_name drop constraint constraint_name;----drop constraint

Alter table table_name add constraint constraint_name primary key (column_name1,column_name2);-create a primary key

Alter table table_name add constraint constraint_name unique (column_name1,column_name2);-create a unique constraint

/ * create a foreign key constraint * /

Alter table table_name add constraint constraint_name foreign key (column_name1) references table_name (column_name1)

/ * do not validate old data, but only constrain new data [enable/disable: constrain / unconstrain new data; novalidate/validate: do not validate / validate a pair of old data] * /

Alter table table_name add constraint constraint_name check (column_name like'B%') enable/disable novalidate/validate

/ * modify constraints, delay verification, verify on commit * /

Alter table table_name modify constraint constraint_name initially deferred

/ * modify constraints and verify immediately * /

Alter table table_name modify constraint constraint_name initially immediate

Alter session set constraints=deferred/immediate

/ * drop a primary key table with a foreign key, cascaded deletion with cascade constraints parameter * /

Drop table table_name cascade constraints

/ * when truncate foreign key table, set the foreign key to invalid first, and then truncate;*/

Truncate table table_name

/ * invalid constraint * /

Alter table table_name disable constraint constraint_name

Alter table table_name enable novalidate constraint constraint_name

/ * put the data rows with invalid constraints into the exception table, which records the row numbers of the rows that violate the data constraints; before that, create the exceptions table * /

Alter table table_name add constraint constraint_name check (column_name > 15) enable validate exceptions into exceptions

/ * run the script that creates the exceptions table * /

Start $ORACLE_HOME/rdbms/admin/utlexcpt.sql

/ * get a table or view of constraint information * /

Select * from user_constraints/dba_constraints/dba_cons_columns

# managing password security and resources #

Alter user user_name account unlock/open;---- Lock / Open user

Alter user user_name password expire;--- setting password expires

/ * establish a password configuration file. How many times the failed_login_attempts password is locked, and how many days after password_lock_times the password is automatically unlocked * /

Create profile profile_name limit failed_login_attempts 3 password_lock_times 1/1440

/ * create a password profile * /

Create profile profile_name limit failed_login_attempts 3 password_lock_time unlimited password_life_time 30 password_reuse_time 30 password_verify_function verify_function password_grace_time 5

/ * create a resource profile * /

Create profile prfile_name limit session_per_user 2 cpu_per_session 10000 idle_time 60 connect_time 480

Alter user user_name profile profile_name

/ * set password unlocking time * /

Alter profile profile_name limit password_lock_time 1/24

/ * password_life_time indicates when the password file expires, and password_grace_time refers to how many days after the first successful login, the password can be changed.

Alter profile profile_name limit password_lift_time 2 password_grace_time 3

/ * password_reuse_time refers to the number of days in which passwords can be reused and the maximum number of times that password_reuse_max passwords can be reused * /

Alter profile profile_name limit password_reuse_time 10 [password_reuse_max 3]

Alter user user_name identified by input_password;- modifies user password

Drop profile profile_name

/ * after a profile is established and assigned to a user, you must use CASCADE to delete * /

Drop profile profile_name CASCADE

Alter system set resource_limit=true;--- enables voluntary restrictions. The default is false.

/ * configure resource parameters * /

Alter profile profile_name limit cpu_per_session 10000 connect_time 60 idle_time 5

/ * Resource parameters (session level)

Each session of cpu_per_session occupies the time unit of cpu in 100 seconds.

Number of parallel session allowed by sessions_per_user per user

The time unit in minutes allowed by connect_time to connect

After how long the idle_time connection is idle, it is automatically disconnected per minute.

Number of logical_reads_per_session read blocks

The private space unit bytes that private_sga users can use in SGA

(call level)

Time for cpu_per_call to call cpu each time (1 beat 100 seconds)

Number of blocks that can be read per call by logical_reads_per_call

, /

Alter profile profile_name limit cpu_per_call 1000 logical_reads_per_call 10

Desc dbms_resouce_manager;--- Explorer package

/ * get a table or view of resource information * /

Select * from dba_users/dba_profiles

# Managing users #

Show parameter os

Create user testuser1 identified by kxf_001

Grant connect,createtable to testuser1

Alter user testuser1 quota 10m on tablespace_name

/ * create a user * /

Create user user_name identified by password default tablespace tablespace_name temporary tablespace tablespace_name quota 15m on tablespace_name password expire

/ * set the default temporary tablespace at the database level * /

Alter database default temporary tablespace tablespace_name

/ * set default tablespaces at the database level * /

Alter database default tablespace tablespace_name

/ * users who create os-level auditing need to know os_authent_prefix, which indicates the prefix corresponding to oracle and os passwords. 'OPS$' is the value of this parameter. This value can be arbitrarily set * /

Create user user_name identified by externally default OPS$tablespace_name tablespace_name temporary tablespace tablespace_name quota 15m on tablespace_name password expire

/ * modify the quota for users to use tablespaces. Rollback tablespaces and temporary tablespaces are not allowed to grant quotas * /

Alter user user_name quota 5m on tablespace_name

/ * delete users or cascading users (if there are objects under the user object, use CASCADE, delete some of the objects under them) * /

Drop user user_name [CASCADE]

/ * under which tablespaces does each user have any limits * /

Desc dba_ts_quotas;select * from dba_ts_quotas where username='...'

/ * change the user's default tablespace * /

Alter user user_name default tablespace tablespace_name

# Managing Privileges #

Grant create table,create session to user_name

Grant create any table to user_name; revoke create any table from user_name

/ * Grant permissions syntax, public identifies all users, and with admin option allows permissions that can be granted to third parties * /

Grant system_privs, [.] To [user/role/public], [....] [with admin option]

Select * from v$pwfile_users

/ * when the O7_dictionary_accessiblity parameter is True, when identifying select any table, including the system table can also be select, otherwise, the system table is not included; the default is false*/

Show parameter O7

/ * since O7_dictionary_accessiblity is a static parameter and cannot be changed dynamically, scope=spfile will not take effect until the next startup * /

Alter system set O7_dictionary_accessiblity=true scope=spfile

/ * Grant permissions to certain fields in an object, such as permissions for certain fields in a table in select * /

Grant [object_privs (column,....)], [...] On object_name to user/role/public,... With grant option

/ * oracle does not allow you to grant permissions to a column of select, but you can grant permissions to a column of insert or update * /

Grant insert (column_name1,column_name2,...) On table_name to user_name with grant option

Select * from dba_sys_privs/session_privs/dba_tab_privs/user_tab_privs/dba_col_privs/user_col_privs

/ * db/os/none audit is recorded in database / operating system / no audit defaults to none*/

Show parameter audit_trail

/ * start the select action on the table * /

Audit select on user.table_name by session

/ * by session sends out command records only once in each session, while by access records each command * /

Audit [create table] [select/update/insert on object by session/access] [whenever successful/not successful]

For further design of desc dbms_fga;---, you can use the dbms_fgs package

/ * cancel audit * /

Noaudit select on user.table_name

/ * check the audited information * /

Select * from all_def_audit_opts/dba_stmt_audit_opts/dba_priv_audit_opts/dba_obj_audit_opts

/ * obtain audit records * /

Select * from dba_audit_trail/dba_audit_exists/dba_audit_object/dba_audit_session/dba_audit_statement

# Managing Role #

Create role role_name; grant select on table_name to role_name; grant role_name to user_name; set role role_name

Create role role_name

Create role role_name identified by password

Create role role_name identified externally

Set role role_name;-activate role

Set role role_name identified by password

Alter role role_name not identified

Alter role role_name identified by password

Alter role role_name identified externally

Grant priv_name to role_name [WITH ADMIN OPTION]

Grant update (column_name1,col_name2,...) On table_name to role_name

Grant role_name1 to role_name2

/ * establish default role. When a user logs in, default role*/ is activated by default.

Alter user user_name default role role_name1,role_name2,...

Alter user user_name default role all

Alter user user_name default role all except role_name1,...

Alter user user_name default role none

Set role role1 [identified by password], role2,....

Set role all

Set role except role1,role2,...

Set role none

Revoke role_name from user_name

Revoke role_name from public

Drop role role_name

Select * from dba_roles/dba_role_privs/role_role_privs/dba_sys_privs/role_sys_privs/role_tab_privs/session_roles

# Basic SQL SELECT #

Select col_name as col_alias from table_name

Select col_name from table_name where col1 like'_ o% matching; -'_ 'matches a single character

/ * use the character function (truncated on the right, the field contains a character, the left is filled with a character to a fixed number of digits, and the right is filled with a character to a fixed number of digits) * /

Select substr (col1,-3,5), instr (col2,'g'), LPAD (col3,10,'$'), RPAD (col4,10,'%') from table_name

/ * use numeric functions (rounding to the right / left digits, rounding, taking the remainder) * /

Select round (col1,-2), trunc (col2), mod (col3) from table_name

/ * use the date function (calculate the difference between the two-day periods by several weeks, the two-day periods by several months, add a few months to a month, and the next date of a date

The last date of the month in which a date is located, rounding off the month of a date and rounding the month of a date.

Select (sysdate-col1) / 7 week,months_between (sysdate,col1), add_months (col1,2), next_day (sysdate,'FRIDAY'), last_day (sysdate)

Round (sysdate,'MONTH'), trunc (sysdate,'MONTH') from table_name

/ * use the NULL function (when expr1 is empty expr2/ and expr1 is empty expr2, otherwise take expr3/ when expr1=expr2 returns null) * /

Select nvl (expr1,expr2), nvl2 (expr1,expr2,expr3), nullif (expr1,expr2) from table_name

Select column1,column2,column3, case column2 when '50' then column2*1.1

When '30' then column2*2.1

When '10' then column3/20

Else column3

End as ttt

From table_name;-use the case function

Select table1.col1,table2.col2 from table1

[CROSS JOIN table2] |-Cartesian connection

[NATURAL JOIN table2] |-join with columns of the same name in two tables

[JOIN table2 USING (column_name)] |-join with one or more columns of the same name in two tables

[JOIN table2

ON (table1.col1=table2.col2)] |

[LEFT | RIGHT | FULL OUTER JOIN table2-equivalent to (+) =, = (+) connection, full external connection

ON (table1.col1=table2.col2)];-JOIN syntax in SQL 1999

Example:

Select col1,col2 from table1 t1

Join table2 t2

On t1.col1=t2.col2 and t1.col3=t2.col1

Join table3 t3

On t2.col1=t3.col3

Select * from table_name where col1

< any (select col2 from table_name2 where continue group by col3); select * from table_name where col1 < all (select col2 from table_name2 where continue group by col3); insert into (select col1,col2,col3 form. table_name where col1>

50 with check option) values (value1,value2,value3)

MERGE INTO table_name table1

USING table_name2 table2

ON (table1.col1=table2.col2)

WHEN MATCHED THEN

UPDATE SET

Table1.col1=table2.col2

Table1.col2=table2.col3

...

WHEN NOT MATCHED THEN

INSERT VALUES (table2.col1,table2.col2,table2.col3,...);-merge statement

# CREATE/ALTER TABLE #

Alter table table_name drop column column_name;-drop column

Alter table table_name set unused (col1,col2,...);-the setting column is invalid, which is faster.

Alter table table_name drop unused columns;--- deletes columns that are set to be invalid

Rename table_name1 to table_name2;-rename table

Comment on table table_name is' comment message';---- put comment information on the table

Create table table_name

(col1 int not null,col2 varchar2 (20), col3 varchar2 (20)

Constraint uk_test2_1 unique (col2,col3));-define the constraints in the table

Alter table table_name add constraint pk_test2 primary key (col1,col2,...);-create primary key

/ * create a foreign key * /

Create table table_name (rid int,name varchar2 (20), constraint fk_test3 foreign key (rid) references other_table_name (id))

Alter table table_name add constraint ck_test3 check (name like'K%')

Alter table table_name drop constraint constraint_name

Alter table table_name drop primary key cascade;---- cascading delete primary key

Alter table table_name disable/enable constraint constraint_name;---- temporarily invalidates the constraint

/ * Delete the column and cascade delete the constraints under this column * /

Alter table table_name drop column column_name cascade constraint

Select * from user_constraints/user_cons_columns;--- constraint related views

# Create Views #

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias [, alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint_name]]

[WITH READ ONLY [CONSTRAINT constraint_name]];-Syntax for creating views

Example: Create or replace view testview as select col1,col2,col3 from table_name;-create a view

/ * use an alias * /

Create or replace view testview as select col1,sum (col2) col2_alias from table_name

/ * create a complex view * /

Create view view_name (alias1,alias2,alias3,alias4) as select d.col1Min (e.col1), max (e.col1), avg (e.col1) from table_name1

/ * when modifying data with update, the col1 > 10 of the view must be satisfied. If not, it cannot be changed. * /

Create or replace view view_name as select * from table_name where col1 > 10 with check option

/ * change the view value. Table data can be modified with update syntax for simple views, but not necessarily for complex views. For example, the columns of functions, group by, distinct, etc. * /

Update view_name set col1=value1

/ * TOP-N Analysis * /

Select [column_list], rownum from (select [column_list] from table_name order by Top-N_column) where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.