Oracle trail notes

Keywords: Database Oracle Session SQL

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)
  v_sql varchar(1000);
  v_Title varchar(1000);
  v_username varchar(1000);
    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;

Verification of ID card number

--Regular expression writing
       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,';
	CASE LENGTHB(p_idcard)
			-- 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}$';
				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;
				RETURN 0;
			END IF;
			-- 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]$';
				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;
					RETURN 0;
				END IF;
				RETURN 0;
			END IF;
			RETURN 0; -- Wrong number of digits of ID card number
END cfn_checkidcard;

create or replace function cFN_IdentityNumberCheck(IdentityNumberCheck varchar2)
	return number is
	v_num number(1);
	if (length(IdentityNumberCheck) <> 18) then
		v_num := 0;
		return v_num;
		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;
							v_num := 0;
							return v_num;
						end if;
						v_num := 0;
						return v_num;
					end if;
					v_num := 0;
					return v_num;
				end if;
				v_num := 0;
				return v_num;
			end if;
			v_num := 0;
			return v_num;
		end if;
	end if;

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
 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

--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
  select SEQ_tablename.nextval into :new.ID from dual;
--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:

--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
--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
--View the character set of the database
select * from nls_database_parameters;
--What to see session Which rollback segments are in use
SELECT Rollback segment name,
	s.sid,s.serial#, s.username user name,
	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
--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


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.
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.
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.

ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file&rdquo;

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=&rsquo;

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 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

Posted by techjosh on Sat, 13 Jun 2020 23:52:50 -0700