######### Create a database ---- Look $ oracle_home / rdbms / admin / buildal.sql ############ Create Database DB01 MaxLogfiles 10 MaxDataFiles 1024 MaxInstances 2 logFile Group 1 ('/u01/oradata/db01/log_01_db01.rdo') size 15m, group 2 ('/u01/oradata/db01/log_02_db01.rdo') Size 15m, Group 3 ('/ u01 / oradata / db01 / log_03_db01. rdo ') SIZE 15M, datafile' u01 / oradata / db01 / system_01_db01.dbf ') SIZE 100M, undo tablespace UNDO datafile' /u01/oradata/db01/undo_01_db01.dbf 'SIZE 40M default temporary tablespace TEMP tempfile' / u01 / oradata /db01/temp_01_db01.dbf 'size 20m Extent Management local uniform size 128k character set al32ute8 national character set al16utf16 set time_zone =' American / new_york '; ################################################################################################################################################################################################################ ######## 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 pace; 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'); ####################################################################################################################### From V $ TABLESPACE; SELECT * from V $ log; select * from v $ log; select * from v $ backup; / * Backup User Table Space * / 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 file * / alter database backup controlfile to '../filepath/control.bak '; / * Backup control file, turn the binary control file to ASC text file * / alter Database backup controlfile to TRACE; ####################################################################### ######### Archive log list; ALTER system archive log start; - Start automatic archive ALTER SYSTEM SWITCH LOGFILE; - Force for a log Switch alter system checkpoint; - Force CheckPoint Alter Tablspace users Begin backup; alter tablespace offline; / * checkpoint frequency synchronization parameters of the FAST_START_MTTR_TARGET, the higher the frequency synchronization, the shorter the time required to restore the system * / show parameter fast; show parameter log_checkpoint; / * Add a log group * / alter database add logfile group 3 ('/ $$ $ac_file6.rdo' size 10m); / * Join a member of the log group * / ALTER DATABASE Add logfile member '/ $ oracle_home / oracle / ORA_LOG_F Ile6.rdo 'to group 3; / * Delete log group: The current log group cannot delete; the active log group cannot delete; non-archived log groups cannot delete * / alter Database Drop logfile group 3; / * Delete the log group A member, but the last member of each group cannot be deleted * / alter Database Drop Logfile Member '$ ORACLE_HOME / ORACLE / ORA_LOG_FILE6.RDO'; / * Clear Online Logs * / ALTER DATABASE CLOGFILE '$ ORACLE_HOME / ORACLE / ora_log_file6.rdo '; alter database clear logfile group 3; / * Clear non-archive log * / alter database clear unarchived logfile group 3; / * log file is renamed * / 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; / * Database archive mode to the interchange of non-invasive modes, can be changed to the mount state; startup mount; then open the database. * / alter Database noarchiveLog / ArchiveLog; Achive log start; - Start automatic archiving ALTER SYSTEM ALL; - Manually archive all log files Select * from v $ archived_log; show parameter log_archive; ##### 分析 日 log file logmnr ############ ## 1) the init.ora parameter set utl_file_dir 2) restart oracle 3) create directory file desc dbms_logmnr_d; dbms_logmnr_d.build; 4) added to the log file add / remove log file dhms_logmnr.add_logfile dbms_logmnr.removefile 5) start logmnr dbms_logmnr .start_logmnr 6) Analysis of the content query V $ logmnr_content --sqlredo / sqlundo practice: DESC DBMS_LOGMNR_D; / * Do some operations for the data table, prepare for the recovery operation * / update table set Qty = 10 where stor_id = 6380; delete Table WHERE STOR_ID = 7066; / *********************************** / UTL_FILE_DIR path 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 ######### s f f f 对; / * The correspondence between tablespace and data files * / SELECT T1.NAME, T2.NAME FROM V $ TABLESPACE T1, V $ DATAFILE T2 WHERE T1.TS # = T2.TS #; ALTER TABLESPACE USERS ADD DATAFILE 'PATH' SIZE 10M; SELECT * FROM DBA_ROLLBACK_SEGS; / * Limit the user in a certain use limit table space * / 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 recommended local management, without the use of dictionary management, use as local space bitmap management table , no voluntary system tablespace contention; * / create userdata datafile '$ ORACLE_HOME / oradata / userdata01.dbf' size 100M extent management local uniform size 1m tablespace; create tablespace userdata datafile '$ ORACLE_HOME / oradata / userdata01.dbf 'size 100m extent management; / * When creating a table space, set the segment space management mode in the table space, here is automatic management * / 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 (can not be used in a dictionary management module) * / create undo TABLESPACE UNDO1 DATAFILE '$ ORACLE_HOME / ORADATA / undo101.dbf' size 40m Extent Management local; show parameter undo; / * temporary tablespace * / create Temporary TableSpace U serdata tempfile '$ ORACLE_HOME / oradata / undo101.dbf' size 10m extent management local; / * set the default database temporary table space * / alter database default temporary tablespace tablespace_name; / * System / temporary / line can not be undo tablespace offline * / alter tablespace tablespace_name offline / online; alter tablespace tablespace_name read only; / * renaming user table space * / alter tablespace tablespace_name rename datafile '$ ORACLE_HOME / oradata / undo101.dbf' to '$ ORACLE_HOME / oradata / undo102.dbf '; / * Rename the system table space, but you must use the database SHUTDOWN before renaming, and restart to Mount status * / 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; / * to table space extension space * / alter tablespace userdata add datafile' $ oracle_home / oraData / undo102.dbf 'size 10m; / * Set tablespace to OMF status * / 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; / * table Some partition Move to another table space * / alter table table_name move partition_name; ###### racle storage structure and rateships ######## / * Handmade distribution table space segment partition ( Extend) Size * / alter table kong.test12 Allocate Extent (Size 1M DataFile '$ Oracle_Home / ORADATA / UNDO102.DBF'); alter table kong.test12 deallocate unused; --- Release Table Distinguished partition SHOW Parameter DB ; ALTER SYSTEM SET DB_8K_CA che_size = 10m; --- configuration memory block parameter block select 8k * from dba_extents / dba_segments / data_tablespace; select * from dba_free_space / dba_data_file / data_tablespace; / * number of bytes occupied by the object * / select sum (bytes) From dba_extents where onwer = 'kong' and segment_name = 'Table_name'; ###################################################################################################################################### 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 error rollback * / alter system set undo_suppress_errors = true; / * In automatic management mode, it will not truly establish RBS1;
The manual management mode can be created, and it is a private return segment * / create rollback segment rbs1 tableSpace undotbs; Desc dbms_flashback; / * After submitting the modified data, 9i provides the flashing operation of the old data, will modify Data is only read to the user, but this part of the data will not restore it 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; / * Round statistics * / select end_time, begin_time, undoblks from v $ undost; / * undo table space size calculation formula: undospace = [UR * (UPS * DBS)] (dbs * 24) UR: UNDO_RETENTION Reserved Time (Second) UPS: Rolling Data Block DBS: System EXTENT and FILE SIZE (That is, db_block_size) * / SELECT * FROM DBA_ROLLBACK_SEGS / V $ ROLLNAME / V $ ROLLSTAT / V $ undostat / v $ session / v $ transaction; show parameter transactions; show parameter rollback; / * in the manual management mode, the establishment of public rollback segment * / create public rollback segment prbs1 tablespace undotbs; alter rollback segment rbs1 online; --- - / * in a manual management mode, initSID.ora specified in a manual management mode undo_management = manual, rollback_segment = ( 'rbs1', 'rbs2', ...), transactions = 100, transactions_per_rollback_segment = 10 then shutdown immediate, startup Pfile = .... / ???. Ora * / ########## managing Tables ########### * char type maxlen = 2000; VA RChar2 Type Maxlen = 4000 Bytes Rowid is 18-bit 64-backed strings (10 BYTES 80 BITS) ROWID composed: Object # (Object Number) - 32BITS, 6 RFILE # (relative file number) - 10Bits, 3 BLOCK # (Block) - 22BITS, 6 ROW # (Line Number) - 16BITS, 3-bit 64 Encompanification: AZ, AZ, 0-9, /, Total 64 Symbol DBMS_ROWID Packages can be It provides an explanation of the rowid * / select rowid, dbms_rowid.rowid_block_number (rowid), dbms_rowid.rowid_row_number (rowid) from table_name; (20) constraint ck_1 check create table test2 (id int, lname varchar2 (20) not null, fname varchar2 ( 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 / * Manually assigned partitions, The assigned data file must be a data file in the table space in the table * / alter table user.table_name allocate extent (size 500k datafile '...); / * Space in the release * / alter table Table_Name Deallocate Unused; alter Table Table_name deallocate unused keep 8k; / * Move the tablespace of the non-field table to the new table space, after the mobile table space, the index object in the original table will be unavailable, must rebuild * / 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 / * table to the unused columns marked * / alter table user.table_name set unused column comments CASCADE CONSTRAINTS; / * drop table not made a mark row * / alter table user.table_name drop unused columns checkpoint 1000; / * When the Drop Col is exception, use Continue to prevent re-deletion of column * / alter Table User.Table _Name Drop Column 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 a bitmap index * / create bitmap index index_name on table_name (column_name1, column_name2) tablespace tablespace_name; / * index can not pctused * / create [bitmap] index index_name on table_name ( column_name) tablespace tablespace_name pctfree 20 storage (inital 100k next 100k); / * index large amount of data logs best not to do * / create [bitmap] index index_name table_name (column_name1, column_name2) tablespace_name pctfree 20 storage (inital 100k next 100k) NOLOGGING;
/ * Create reverse 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 construction of the table * / 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; / * to create BitMap Index Assignment Memory Space Parameters to Accelerate Search * / Show Parameter Create_bit; / * Change the Storage Parameters of Index * / Alter Index Index_name Pctfree 30 Storage (Initial 200k Next 200k); / * Hand-allocated a partition for indexing * / alter index index_name allocate extent (size 200k datafile '$ ORACLE / oradata / ..'); / * index release useless space * / alter index index_name deallocate unused; / * index rebuild * / alter index index_name rebuild tablespace tablespace_name; / * Interchange of normal index and reverse index * / alter index index_name rebuild tablePACE TABLESPACE_NAME REVERSE; / * When rebuilding the index, do not lock the table * / alter index index_name rebuild online; / * give index finishing debris * / alter in dex index_name COALESCE; / * index analysis, in fact, the statistical update process * / 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; ---- Cancel monitor / * View related to index information * / select * from dba_indexes / dba_ind_column / dbs_ünd_expressions / v $ object_usage; ############### integrity) ########## alter table table_name drop constraint constraint_name; ---- drop constraints 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 foreign key constraint * / alter Table Table_name add constraint constraint_name foreign key (column_name1) References Table_name (Column_name1); / * Do not test the old data, only new data [Enable / Disable: constraint / Do not restore new data; Novalidate / Validate: Do not verify / for old data] * / alter Table Table_name add constraint constraint_name check (Column_name Like 'B%') Enable / Disable NoValidate / Validate; / * Modify Constraint Conditions, Delay Verification , validation commit * / alter table table_name modify constraint constraint_name initially deferred; / * modify the constraints, immediately verify * / alter table table_name modify constraint constraint_name initially immediate; alter session set constraints = deferred / immediate; / * drop a foreign key The primary key table, with the Cascade constraints parameter class delete * / drop Table Table_name cascade constraints; / * When the TRUNCATE foreign key table, set the foreign key to invalid, then truncate; * / truncate table table_name; / * Set constraint condition Invalid * / alter Table Table_Name; ALTER TABLE TABLE_NAME Enable NoValidate constraint constraint_name; / * Put the invalid constraints of data rows into the table of Exception, this table records the line number of violations of data constraints; before this To build an Exceptions table * / alter table Table_name add constraint constraint_name check olumn_name> 15) enable validate exceptions into exceptions; / * Create a script to run exceptions table * / start $ ORACLE_HOME / rdbms / admin / utlexcpt.sql; / * get a table or view constraint information * / select * from user_constraints / dba_constraints / dba_cons_column; ######################################################################################################################################################################################################################################################################################### UNLOCK / Open; ---- Lock / Open User; ALTER USER USER_NAME PASSWORD Expire; --- Set Password Expression / * Create a password configuration file, FAILED_LOGIN_ATTEMPTS password to lose how many times lock, Password_lock_times pointed out how many days after the password is automatically unlocked * / crete profile_name limited failed_login_attempts 3 password_lock_times 1/1440;
/ * Create a password configuration file * / 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; / * the establishment of resource configuration file * / 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; / * Setting password unlock time * / alter profile_name limited password_lock_time 1/24; / * password_life_time Description Dimensional Dates, Password_Grace_time refers to how many days after the password expires after the first successful login, change the password * / alter profile profile_name limit password_lift_time 2 password_grace_time 3; / * password_reuse_time refers password number of days can be reused, the maximum number of PASSWORD_REUSE_MAX password can be reused * / alter profile profile_name limit password_reuse_time 10 [password_reuse_max 3]; alter user user_name identified by input_password ; --- Modify User Password Drop Profile Profile_name; / * After establishing Profile, and specify it to a user, you must use cascade to delete * / drop profile_name cascade; alter system set resource_limit = true; --- Enable voluntary restrictions, default is false / * Configuring Resource Parameters * / ALTER Profile Profile_nam E LIMIT CPU_PER_SESSION 10000 Connect_time 60 IDLE_TIME 5; / * Resource Parameters (session level) CPU_PER_SESSION Each session occupies the CPU Time unit 1/100 second sessions_per_user allows each user's parallel session number CONNECT_TIME to allow the connection time unit minute iDLE_TIME connection is idle After how much time, the automatic disconnection unit minical_reads_per_session read block number private_sga users can use private spaces used in SGA Number of units Bytes (Call level) CPU_PER_CALL Each time (1/100 second) calls CPU time Logical_Reads_Per_Call each time the call the number of blocks read * / alter profile profile_name limit cpu_per_call 1000 logical_reads_per_call 10; desc dbms_resouce_manager; --- resource Manager package / * Get the resource information table or view * / select * from dba_users / dba_profiles; ###### managing Users ############ s p 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 default database-level temporary table space * / alter database default temporary tablespace tablespace_name; / * the development of database-level Default table space * / alter Database default tableSpace tableSpace_name; / * Create an OS_Authent_prefix, indicating the prefix corresponding to Oracle and OS password, 'OPS $' value for this parameter, this value can be 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; / * limit users to modify the table space, rollback table space and temporary table space is not allowed to grant quota * / alter user user_name Quota 5m on tableSpace_name; / * Delete the user or delete the cascade user (with the object's object to use cascade, delete it together below) * / drop user_name [cascade]; / * What tables are in each user What limit is there * / desc dba_ts_quotas; select * from dba_ts_quotas where username = '...'; / * Change the user's default table space * / alter user user_name default TableSpace TableSpace_name; ######## Managing privilegs ############ gT create table, create session to user_name; Grant Crea TE Any Table To User_Name; Revoke Create Any Table from User_Name; / * Grants Sitid Syntax, PUBLIC Identity All users, with admin option allows permissions to grant permissions * / Grant System_privs, [...] TO [USER / ROLE / PUBLIC], [....] [WITH OPTION]; SELECT * FROM V $ pWFILE_USERS; / * When the O7_DICTIONARY_ACCESSIBLITY parameter is True, you can identify the SELECT ANY TABLE, including the system table, can be Select, Otherwise, the system table is not included; the default is false * / show parameter o7; / * Because O7_DICTIONARY_ACCESSIBLITY is a static parameter, it cannot be dynamically changed, so it is added to scope = SPFILE, it will take effect when starting next time * / alter system set o7_dictionary_accessiblity = true scpe = SPFILE; / * Permissions to certain fields in the object, such as the permissions of certain fields in the SELECT * / GRANT [Object_Privs (Column, ....)], [...] on object_name to user / role / public, ... with grant option;
/ * Oracle is not allowed to grant permission to select a column, but can be granted insert, permissions for a column 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_PRIVS; / * DB / OS / NONE Audit is recorded in database / operating system / non-auditing default is none * / show parameter audit_trail; / * Start the table's SELECT Action * / Audit SELECT ON User .table_name by session; / * by session issued a Command in each session only recorded once, by Access, each Command is recorded * / audit [create table] [select / update / insert on object by session / access] [WHENEVER successful / not successful]; desc dbms_fga; --- further design, you can use dbms_fgs package / * cancel audit * / noaudit select on user.table_name; / * check to be audited information * / select * from all_def_audit_opts / dba_stmt_audit_opts / dba_priv_audit_opts / dba_obj_audit_opts; / * get 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_nam e; create role role_name; create role role_name identified by password; create role role_name identified externally; set role role_name; ---- activation 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 the user logs on, the default activation default role * / 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, set 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%'; ----'_ 'Match a single character / * Using the character function (on the right, the field contains a character, the left is filled with a character to the fixed bit, On the right, a character is filled to a fixed bit) * / select substr (col1, -3, 5), INSTR (Col2, 'g'), LPAD (col3, 10, '$'), RPAD (col4, 10, '%) ') from table_name; / * Using a digital function (round to the left, round, round, take a finger) * / select runk (col2), mod (color) from table_name; / * Using the date function (calculated several weeks during the two days, the two days of time is divided into a few months, in a month, a few months, the next date of a certain date, the last date of a date, Take a different date of the month from a date, a single month of the month) * / 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 a null function (when EXPR1 is empty EXPR2 / when EXPR1 is air-taking EXPR2, otherwise take EXPR3 / when EXPR1 = EXPR2 return empty) * / 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; - ---- Using the Case function select table1.col1, table2.col2 from table1 [cross join table2] | ----- Cartesi connection [Natural Join Table2] | ----- with the same name in two tables Column connection [Join Table2 Using (Column_Name)] | ----- A list or few columns in the same name in both tables [Join Table2 on (Table1.col1 = Table2.col2] | [Left | Right | Full Outer Join Table2 ------ equivalent to ( ) =, = (
) Connection, all-way connection ON (Table1.col1 = Table2.col2)]; -------- SQL 1999 JOIN syntax; 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
ALTER TABLE TABLE_NAME DROP UNUSED Column; --- Delete Rename Table_name1 to Table_name2; --- Rename Table Comment On Table Table_name is 'Comment Message'; ---- Give Table In Yet Information Create Table Table_name (Col1 Int Not Null, Col2 Varchar2 (20), Col3 Varchar2 (20), Constraint UK_TEST2_1 Unique (COL2, COL3))); ----- Defining Constrained Conditions ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_TEST2 PRIMARY KEY ( COL1, COL2, ...); ---- Create a primary key / * established 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; ---- primary cascade delete key alter table table_name disable / enable constraint constraint_name; --- - Make constraints temporarily / * delete columns, and delete the constraints of this column * / alter Table Table_name Drop Column Column_name cascade constraint; select * from user_constraints / user_cons_column; --- constraint condition related view ##### ######### Create Views ########################################### CE | NOFORCE] View View_name [(Alias [, alias] ...)] As SubQuery [with check option "] [with read only"]; -------- Create a syntax of the view EXAMPLE : Create or Replace View TestView As SELECT COL1, COL2, Col3 from Table_name; ------ Create View / * Using Alias * / Create Or Replace View TestView As SELECT COL1, SUM (Col2) Col2_alias from table_name; / * creation Complex view * / create view view_name (alias1, alias2, alias3, alias4) AS SELECT D.COL1, MIN (E.COL1), Max (E.COL1), AVG (E.COL1) from table_name1 E, table_name2 d Where e .col2 = d.col2 group by d.col1; / * When modifying data with Update, the condition of the COL1> 10 must be met, which cannot be changed. * / create or report * from table_name as select * from table_name WHERE col1> 10 with check option;
/ * Change the value of the view. You can modify the table data with Update syntax for simple views, but complex views do not change.
Such as using 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 <= n; / * Find a list of three largest records * / EXAMPLE: SELECT ROWNUM AS RANK, COL1, Col2 from (SELECT Col1, Col2 from Table_name Order By Col2 DESC) Where rownum <= 3; ### ########## # # [# u] [[({MaxValue N | NOMAXVALUE}) [{MaxValue N | NomaxValue}] [{MaxValue N | NomaxValue}] {MINVALUE n | NOMINVALUE}] [{CYCEL | NOCYCLE}] [{CACHE n | NOCACHE}]; ----- created SEQUENCE example: CREATE SEQUENCE sequence_name INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; select * from user_sequences ; --- sequence current record of the user view select sequence_name.nextval, sequence_name.currval from dual; ----- sequence of reference alter sequence sequence_name INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; ----- modifications sequence, not Change the starting program DROP sequence sequence_name; ---- Delete sequence create [public] synonym synonym_name for object; ------ Create synonym DROP [P Ublic] synonym synonym_name; ---- Delete synonym create public database link link_name useing object; ---- Create DBLINK SELECT * from Object_name @ link_name; ---- Access to objects in the remote database / * Union operation, it will be two A collection of integrated intersections, and sorts Data * / SELECT COL1, COL2, COL3 from table1_name Union Select COL1, COL2, COL3 from Table2_name; / * Union ALL operation, two sets of intersection portions are not compressed, and no data is sorted * / SELECT COL1, COL2, Col3 from Table1_name Union All Select Col1, Col2, Col3 from Table2_name; / * INTERSECT Operation, Search for two sets of intersections, it will compress the duplicate data, and sorted * / select col1, col2, Col3 from table1_name intersect select col1, col2, col3 from table2_name; / * minus operation, set minus, it will compress two sets of repeated records after subtract, and sort data * / select colip1, col2, col3 from table1_name Minus Select Col1 , col2, col3 from table2_name;
