Oracle trail learning experience
stored procedure
Annotate a custom column name
Incoming parameters: field name and annotation
create or replace procedure csp_col_comment ( p_column_name varchar, --Field name p_Title varchar, --field comment p_RetVal Out integer) as v_sql varchar(1000); v_Title varchar(1000); v_username varchar(1000); begin v_Title := ''''||p_Title||''''; --Get current user name select user into v_username from dual; for tb in(select * from DBA_COL_COMMENTS a where a.comments is null and upper(a.owner) = upper(v_username) and a.column_name = upper(P_column_name) and exists(select 1 from user_tab_columns b where a.table_name = b.table_name and a.column_name = b.column_name) ) loop v_sql := 'comment on column '||tb.table_name||'.'||tb.column_name ||' is '||v_Title; dbms_output.put_line (v_sql); execute immediate v_sql; end loop; p_RetVal := 0; commit; end;
Verification of ID card number
--Regular expression writing CREATE OR REPLACE FUNCTION cfn_checkidcard(p_idcard IN VARCHAR2) RETURN INT IS /* ID card verification, regular expression writing */ v_regstr VARCHAR2(2000); v_sum NUMBER; v_mod NUMBER; v_checkcode CHAR(11) := '10X9876 Five 432'; v_checkbit CHAR(1); v_areacode VARCHAR2(2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,'; BEGIN CASE LENGTHB(p_idcard) WHEN 15 THEN -- 15 position IF INSTRB(v_areacode, SUBSTR(p_idcard, 1, 2) || ',') = 0 THEN RETURN 0; END IF; IF MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 2)) + 1900, 400) = 0 OR (MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 2)) + 1900, 100) <> 0 AND MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 2)) + 1900, 4) = 0) THEN -- leap year v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$'; ELSE v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$'; END IF; IF REGEXP_LIKE(p_idcard, v_regstr) THEN RETURN 1; ELSE RETURN 0; END IF; WHEN 18 THEN -- 18 position IF INSTRB(v_areacode, SUBSTRB(p_idcard, 1, 2) || ',') = 0 THEN RETURN 0; END IF; IF MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 4)), 400) = 0 OR (MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 4)), 100) <> 0 AND MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 4)), 4) = 0) THEN -- leap year v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$'; ELSE v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$'; END IF; IF REGEXP_LIKE(p_idcard, v_regstr) THEN v_sum := (TO_NUMBER(SUBSTRB(p_idcard, 1, 1)) + TO_NUMBER(SUBSTRB(p_idcard, 11, 1))) * 7 + (TO_NUMBER(SUBSTRB(p_idcard, 2, 1)) + TO_NUMBER(SUBSTRB(p_idcard, 12, 1))) * 9 + (TO_NUMBER(SUBSTRB(p_idcard, 3, 1)) + TO_NUMBER(SUBSTRB(p_idcard, 13, 1))) * 10 + (TO_NUMBER(SUBSTRB(p_idcard, 4, 1)) + TO_NUMBER(SUBSTRB(p_idcard, 14, 1))) * 5 + (TO_NUMBER(SUBSTRB(p_idcard, 5, 1)) + TO_NUMBER(SUBSTRB(p_idcard, 15, 1))) * 8 + (TO_NUMBER(SUBSTRB(p_idcard, 6, 1)) + TO_NUMBER(SUBSTRB(p_idcard, 16, 1))) * 4 + (TO_NUMBER(SUBSTRB(p_idcard, 7, 1)) + TO_NUMBER(SUBSTRB(p_idcard, 17, 1))) * 2 + TO_NUMBER(SUBSTRB(p_idcard, 8, 1)) * 1 + TO_NUMBER(SUBSTRB(p_idcard, 9, 1)) * 6 + TO_NUMBER(SUBSTRB(p_idcard, 10, 1)) * 3; v_mod := MOD(v_sum, 11); v_checkbit := SUBSTRB(v_checkcode, v_mod + 1, 1); IF v_checkbit = upper(substrb(p_idcard, 18, 1)) THEN RETURN 1; ELSE RETURN 0; END IF; ELSE RETURN 0; END IF; ELSE RETURN 0; -- Wrong number of digits of ID card number END CASE; EXCEPTION WHEN OTHERS THEN RETURN 0; END cfn_checkidcard; create or replace function cFN_IdentityNumberCheck(IdentityNumberCheck varchar2) return number is v_num number(1); begin if (length(IdentityNumberCheck) <> 18) then v_num := 0; return v_num; else select 1 into v_num from dual where regexp_like(substr(IdentityNumberCheck, 11, 2), '\d{2}'); if (v_num = 1) then select 1 into v_num from dual where regexp_like(substr(IdentityNumberCheck, 13, 2), '\d{2}'); if v_num = 1 then if (substr(IdentityNumberCheck, 11, 2) >= 1 and substr(IdentityNumberCheck, 11, 2) <= 12) then if (substr(IdentityNumberCheck, 13, 2) >= 1 and substr(IdentityNumberCheck, 13, 2) <= 31) then if (ascii(substr(IdentityNumberCheck, 17, 1)) >= 48 and ascii(substr(IdentityNumberCheck, 17, 1)) <= 57) then v_num := 1; return v_num; else v_num := 0; return v_num; end if; else v_num := 0; return v_num; end if; else v_num := 0; return v_num; end if; else v_num := 0; return v_num; end if; else v_num := 0; return v_num; end if; end if; end;
Common classification of SQL(Structured Query Language) statements
1. DDL, data definition language
create, alter, drop, truncate (other: rename)
--Annotate tables and fields comment on table tablename is 'Table name meaning'; comment on column tablename.columnName is 'Field meaning'; --Create a synonym for the table for the current user begin for j in(select * from dba_tables where owner='username' and upper(table_name) not like upper('%bak%') ) loop execute immediate 'create synonym '||j.table_name||' for username.'||j.table_name ; end loop ; end ; --establish BDlink create public database link BDlinkName connect to username identified by "password" using 'HOST:PORT/SERVICE_NAME'; --Create tablespace create tablespace tablespacename datafile 'C:\app\Administrator\oradata\BPHRTEST\system.dbf' size 3000M autoextend on next 5M maxsize unlimited; --Tablespace extension alter database datafile 'C:\APP\ADMINISTRATOR\ORADATA\XYSHARE\BPHRTES.DBF' resize 4096m; --Assign table spaces to users alter user username default tablespace tablespacename; --Create user name create user username identified by password; --Change user password alter user user name identified by New password; --Reset index alter index tablename rebuild online ; alter index INDEX_name rebuild online ; --Modify the time format of the current session alter session set nls_date_format='yyyy-mm-dd'; --Shut down process alter system kill session 'sid, serial#'; --Modify database character set encoding ALTER DATABASE character set INTERNAL_USE ZHS16GBK; ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE ZHS16GBK; --Create auto add column method --1,Create sequence create sequence SEQ_tablename start with 1 increment by 1 maxvalue 999999999999999999 --2,Create trigger create or replace trigger TIB_tablename_SEQ before insert on tablename for each row Begin select SEQ_tablename.nextval into :new.ID from dual; End; --Modifying the self increment of an auto increment sequence alter sequence username.SEQName increment by 1; --Change of starting amount of auto increase column select username.SEQName.nextval from dual; --Modify the archive mode of the database --1.Change non archive mode to archive mode: conn / as sysdba (with DBA Identity connection database) shutdown immediate; (Shut down the database now) startup mount (Start the instance and load the database without opening) alter database archivelog; (alter database archivelog ) alter database open; (Open database) alter system archive log start; (Enable automatic archiving) exit (sign out) --2.Change archive mode to non Archive Mode: SHUTDOWN NORMAL/IMMEDIATE; STARTUP MOUNT; ALTER DATABASE NOARCHIVELOG; ALTER DATABASE OPEN; --Rename table name: change table tab1 Named tab2 rename tab1 to tab2 ; --Turn on parallel function in stored procedure alter session enable parallel dml; --Turn off foreign key constraints alter table table_name disable constraint constraint_name; --Turn on foreign key constraint alter table table_name enable constraint constraint_name; --Close trigger alter table tablename disable all triggers; --Open trigger alter table tablename enable all triggers;
2. DML, data manipulation language
insert, delete, select, update
--View tablespace information select * from SYS.DBA_DATA_FILES where TABLESPACE_NAME = 'SYSTEM'; --View database Archive Mode select name,log_mode from v$database; --Get current user name select user from dual; --Comments for columns of all tables and views select * from DBA_COL_COMMENTS a where length(a.comments) > 0 and a.owner = 'username'; select * from DBA_TAB_COMMENTS a where length(a.comments) > 0 and a.owner = 'username'; --Extended information in all tablespaces of database select a.owner,a.segment_name,a.segment_type,a.BLOCK_ID,(a.BYTES/1024/1024)||'M' "Expansion area size" from dba_extents a where a.owner = 'username' and a.segment_name = 'tablename' order by a.segment_type; --View table extensions( Extent)information select table_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables where table_name = upper(tablename); --View table constraints SELECT constraint_name, table_name,a.CONSTRAINT_TYPE, case when a.CONSTRAINT_TYPE = 'P' then 'Primary key' when a.CONSTRAINT_TYPE = 'U' then 'Unique constraint' when a.CONSTRAINT_TYPE = 'R' then 'Foreign key' when a.CONSTRAINT_TYPE = 'C' then 'Check' end as "Constraint name", a.status,a.OWNER FROM all_constraints a WHERE upper(table_name) = upper('tablename') and upper(owner) = upper('username'); --See which table is locked select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id; --Query lock session and close session SELECT P.SPID, S.SID, S.SERIAL#, S.USERNAME, S.PROGRAM FROM V$PROCESS P, V$SESSION S WHERE P.ADDR=S.PADDR AND S.STATUS='KILLED'; --View the character set of the database select * from nls_database_parameters; --What to see session Which rollback segments are in use SELECT r.name Rollback segment name, s.sid,s.serial#, s.username user name, t.status,t.cr_get,t.phy_io,t.used_ublk, t.noundo,substr(s.program, 1, 78) Operating procedures FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r WHERE t.addr = s.taddr and t.xidusn = r.usn ORDER BY t.cr_get,t.phy_io --Trigger to view table select a.TABLE_NAME,a.COLUMN_NAME,a.STATUS,trigger_name,a.OWNER from all_triggers a where upper(table_name)= upper('tablename') and a.OWNER = upper('username'); --Decimal processing select ceil(3.01) from dual; --Round up 4 select floor(5.234) from dual; --Round down 5 select trunc(5.56,1) from dual; --Keep decimal places without rounding 5.5 select round(5.564,1) from dual; --Keep decimal places and round them 5.6 --View tablespace information select * from SYS.DBA_DATA_FILES where TABLESPACE_NAME = 'SKYSYSTEM'; --View database Archive Mode select name,log_mode,a.* from v$database a; --Redo log file information: select * from V$logfile; --Query the usage of the current tablespace select a.tablespace_name, a.bytes / 1024 / 1024 "sum MB", (a.bytes - b.bytes) / 1024 / 1024 "used MB", b.bytes / 1024 / 1024 "free MB", round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%" from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by ((a.bytes - b.bytes) / a.bytes) desc; -- View the path of data file placement select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; --Copy data between users copy from user1 to user2 create table2 using select * from table1; --Check whether the data file is automatically expanded select file_id,file_name,tablespace_name,autoextensible from dba_data_files order by file_id; --View the size of the space occupied by the user table select segment_name,tablespace_name,bytes,blocks from user_segments where segment_type='TABLE'; --Find out primary key and forgen key Relation table of select a.owner Foreign key owner, a.table_name Foreign key table, c.column_name Foreign key column, b.owner Primary key owner, b.table_name Primary key table,d.column_name Primary key column from user_constraints a,user_constraints b,user_cons_columns c,user_cons_columns d where a.r_constraint_name=b.constraint_name and a.constraint_type='R' and b.constraint_type='P' and a.r_owner=b.owner and a.constraint_name=c.constraint_name and b.constraint_name=d.constraint_name and a.owner=c.owner and a.table_name=c.table_name and b.owner=d.owner and b.table_name=d.table_name;
3. DCL, data control language
grant, revoke, set role
--Authorize users grant sysdba,connect,session to username ; --Authorize users to access process permissions grant select on v_$process to username ; --Authorize users to access any table grant select any table to username ; ----Functions of authorizing users to access data dictionary alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile; --Do anything grant EXECUTE ANY PROCEDURE to username ;
4. TCL, transaction control
commit, rollback, savepoint (others: lock table, set constraint(s), set transaction)
5. System control
alter system
6. Session control
alter session
7. Audit control
audit,noaudit
8. Other statements
comment, explain plan, analyze, validate, call
Introduction to archiving mode
1. Introduction
Archive log is an inactive redo log backup. By using archive log, you can keep all redo history records. When the database is in ARCHIVELOG mode and the log is switched, the background process ARCH will save the contents of redo log to the archive log. When the database fails in media, you can use data file backup, Archive log and redo log can fully recover the database
--------
2. Archive and non Archive Mode of database:
1. When the database is in NOARCHIVELOG mode, the redo log file is not archived, and the old redo log file is directly overwritten. At this time, the database can only be recovered from routine failure.
characteristic:
a. In the event of a log switch (alter system switch logfile), the LGWR background process can unconditionally overwrite the contents of the original INACTIVE redo log file.
b. When backing up the database, you must first use the SHUTDOWN or SHUTDOWN IMMEDIATE command to shut down the database before you can back up all the physical files through the operating system. That is, cold backup. Online hot backup is not possible.
c. In NOARCHIVELOG mode, you can only protect the database from routine failures, but not from media failures.
d. NOARCHIVELOG mode does not need to consider the problem of storage space.
2. When the database is in ACRCHIVELOG mode, the system helps the DBA to automatically archive the redo log file through the background process ARCn. After the archive, the old redo log file is overwritten and copied to a designated location to become the archive log file. In this case, the database can be further recovered from the media failure. Data loss can be minimized and data availability can be maximized.
characteristic:
a. When ACRCHIVELOG mode. The database can be hot backed up online.
b. In case of data file corruption, except for the data files of SYSTEM tablespace, other tablespaces can be recovered when the database is OPEN.
c. When performing a database backup, you can not only complete the recovery, but also specify a specific point in time.
Oracle common error codes and Solutions
1. Insufficient rollback segment table space: ORA-01650
ORA-01650:unable to extend rollback segment NAME by NUM intablespace NAME :
Cause: the above ORACLE error is caused by insufficient table space of rollback segment, which is also the most common ORACLE error information of ORACLE data administrator. When the user is doing a very large data operation, resulting in the shortage of the existing rollback segments, so that the allocated rollback segment table space is full and can no longer be allocated, the above error will appear.
Solution:
ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file”
Command to add a table space to the specified data. You can add one or more table spaces according to the specific situation. Of course, it's also related to the
For bare disk devices, if your host's bare disk device has no extra space, it is recommended that you do not lightly increase the size of the rollback segment's table space. You can use the following statements to first query the remaining table space:
Select user_name,sql_text from V$open_cursor where user_name=’
If there is more spare space, a large rollback segment can be added to the table space to avoid the above errors.
2. Insufficient ORACLE temporary segment table space: ORA-01652
ORA-01652:unable to extend temp segment by num in tablespace name
Cause: the ORACLE temporary segment table space is insufficient, because ORACLE always allocates continuous space as much as possible. Once there is not enough allocatable space or the allocation is not continuous, the above phenomenon will occur.
3. A rollback segment and a tablespace have reached the limit of MAXEXTENTS parameter setting: ORA-01628
ORA-01628: max # of extents num reached for rollback segment num
Cause: this error is usually caused by a rollback segment and a table space that has reached the limit set by the MAXEXTENTS parameter. It should be noted that this MAXEXTENTS is not the hardware limit of the rollback segment or tablespace. The hardware limit depends on the database creation time init.ora DB specified in file_ BLOCK_ The value of the size parameter.
4. The specified tablespace is full: ORA-01688
ORA-01688: unable to extend table name.name partition NAME by NUM in tablespace NAME
Cause: the specified tablespace is full and cannot be expanded.
Oracle special package
DBMS_ The procedure and function that alert allows the application to name and signal a warning condition without polling
DBMS_ The process of obtaining a certain number of DDL statements within a PL/SQL program
DBMS_Describe describes the process of API for stored procedures and functions
DBMS_ The process and function of job management BLOBs, CLOBs, NCLOBs and BFILEs
DBMS_ The procedure and function that output allows PL/SQL program to generate terminal output
DBMS_ The procedure and function of pipe to allow database session to use pipeline communication (communication channel)
DBMS_ The procedure and function of SQL executing dynamic SQL in PL/SQL program
DBMS_Utility DBMS_Utility
UTL_File allows PL/SQL programs to read and write text files on the server file system
ROLLBACK commands that cannot be rolled back
Some commands have an implicit auto commit feature, the most common of which are:
ALTER: modify table, index, user, tablespace, etc
CREATE: CREATE table, view, synonym, trigger, database link, snapshot, sequence, user, tablespace and other objects
DROP: delete any objects that have been created
RENAME: modify the names of tables, indexes, and views
How to change the size of the current online log file
Implementation method:
Method: add a new large log file and delete the old one
Suppose there are three log groups, one member in each group, and the size of each member is 1MB. Now I want to change the size of the members of these three log groups to 10MB;
--1,Create 2 new log groups alter database add logfile group 4 ('D:\ORACLE\ORADATA\ORADB\REDO04_1.LOG') size 1024k; alter database add logfile group 5 ('D:\ORACLE\ORADATA\ORADB\REDO05_1.LOG') size 1024k; --2,Switch the current log to a new log group alter system switch logfile; alter system switch logfile; --3,Delete old log group alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; --4,Delete files in original log groups 1, 2 and 3 under the operating system --5,Rebuild log group 1, 2, 3 alter database add logfile group 1 ('D:\ORACLE\ORADATA\ORADB\REDO01_1.LOG') size 10M; alter database add logfile group 2 ('D:\ORACLE\ORADATA\ORADB\REDO02_1.LOG') size 10M; alter database add logfile group 3 ('D:\ORACLE\ORADATA\ORADB\REDO03_1.LOG') size 10M; --6,Switch log group alter system switch logfile; alter system switch logfile; alter system switch logfile; --7,Delete intermediate transition log groups 4, 5 alter database drop logfile group 4; alter database drop logfile group 5; --8,Delete the files in the original log groups 4 and 5 under the operating system --9,Back up the current latest control file SQL> connect internal SQL> alter database backup controlfile to trace resetlogs
Install ORACLE client to turn off memory checking
During the installation of Oracle client, we sometimes encounter the failure of memory detection, which leads to the failure of Oracle client installation. Xiaobian has encountered similar situations. After consulting the data, use the following dos command to shut down the memory detection during Oracle installation, and the installation is successful:
dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false