• Random talk on database (1)


    From studying in university to working on the performance optimization of professional database in the past 10 years, I have been in touch with database for 20 years. During this period, I have made countless mistakes and gained a lot, but they are all complicated. Every time I train new colleagues or talk with my […]

  • Tutorial illustration of Oracle VM VirtualBox installing centos7 operating system


    Installation procedure Download address: for version 6.0, first install Oracle VM VirtualBox, and then click new After the new page pops up:Set name, installation folder, type and versionThe name should be in English, and the installation path should be placed on the disk with large solid-state disk memory space. The following version is recommended. After […]

  • MySQL similar Oracle rownum writing examples


    Rownum is a unique writing method in Oracle. Rownum can be used to get the first data in Oracle, or limit the number of batch writes when batch writes data How to write the first data in MySQL SELECT * FROM t order by id LIMIT 1; How to write the first data in Oracle […]

  • The course of Oracle setting up scheduled task backup database under Linux


    1. View the character set of the database The character set of the database must be consistent with the environment variables set under Linux, otherwise there will be garbled code. The following two SQL statements can be found: select * from nls_database_parameters t where t.parameter=’NLS_CHARACTERSET’; select * from v$nls_parameters where parameter=’NLS_CHARACTERSET’; The query result is: […]

  • Introduction to Oracle regular expression


    Here are some examples to illustrate the use of regular expressions to deal with some common problems in work.1. REGEXP_SUBSTR REGEXP_ The substr function uses regular expressions to specify the start and end points of the return string, and returns source_ String a string with the same VARCHAR2 or CLOB data in the string character […]

  • Oracle database backup and restore tool written in batch


    This is written for Oracle and can be used on different computers, because it will automatically read the environment variables of the current computer, so as to obtain the installation path of Oracle. The only deficiency is that an intermediate text file will be generated during the running process of the program, but this does […]

  • OracleOraDb10g_ How to solve the problem that the home1tnsslistener service cannot be started


    The result is oracleoradb10g_ The home1tnsslistener service cannot be started. There may be three situations: One listener.ora Unable to start due to file configuration error 2. The related environment variables are not set properly 3. When deleting the client, the server’s relevant registry information will be deleted by mistake, resulting in the failure to start […]

  • Operation tutorial of cascade query, cascade deletion and cascade update in Oracle Database


    Hierarchical Queries In Oracle database, there is a method to realize cascade query Select * // the field to query From table // table with child pin ID and parent pin ID Start with selfid = ID // give a startid (the field name is the child pin ID, and the start ID number) Connect […]

  • Example tutorial of creating auto increment primary key in Oracle Database


    When designing the database table, we found that Oracle does not have the setting of auto increment primary key. Google understands that Oracle itself does not support auto increment primary key, which needs to be implemented through sequence and trigger. Create table student Create Table Student( ID number (12) primary key, — self increment of […]

  • Introduction of XML processing function in Oracle


    1. EXTRACT(XMLType_instance,Xpath_string) This function is used to return the corresponding content under the XML node path Example: Copy codeThe code is as follows: SELECT extract(value(a),’/root/main’) data FROM xmltable a ; 2. EXTRACTVALUE(XMLType_instance,Xpath_string) This function returns data for a specific XML node path Example: Copy codeThe code is as follows: Select extractvalue (value (a), ‘/ root […]

  • Oracle functions


    1. ASCII returns the decimal number corresponding to the specified character; SQL> select ascii(‘A’) A,ascii(‘a’) a,ascii(‘0’) zero,ascii(‘ ‘) space from dual; A         A      ZERO     SPACE ——— ——— ——— ——— 65        97        48        32 2. Chr gives an integer and returns the corresponding character; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C — –Zhao a […]

  • Judging whether a field is a number in Oracle


    In our normal development, we may encounter the problem of judging whether a column is composed of numbers. We all know that Oracle does not provide us with such a ready-made function. According to my experience, I have summed up two effective methods (column name: column, table name: table) Use the translate + 1 Copy […]