SQL error message sorting and solution (continuous update)

Tidy up the SQL you have met a variety of error messages and corresponding solutions, convenient for later review, the main platform for Oracle:

• ORA-01461: You can only bind a long value to insert a long column:
  • Cause: When inserting an operation, the data is larger than the field setting, and Oracle automatically converts the data to a long type and then reports the Insert failure error.
  • FIX: Change the size of the data, or set the field to Clob or blob type.
• "Ora-01012:not logged on" and "Connected to an idle instance":
  • Cause: When restarting an Oracle server, if you accidentally disconnect when you run the shutdown instruction to shut down the database, you may encounter the following error when you want to enter the Oracle server again after booting:
    • sqlplus /nolog when you want to connect to the database conn username/password as sysdba  , you are reporting an error when you use enter SQL Plus Connected to an idle instance.  .
    • In the SQL Plus interface, startup  errors are reported when you want to start the database directly ORA-01012: not logged on  .

    By consulting the data, the reason for the error is that Oracle has been locked out and no longer allowed other operations after the shutdown database exited unexpectedly.

  • FIX:

    • First shut down the Oracle process:

       PS - EF |  grep ora_dbw0_$oracle_sid //  find ORACLE process  kill  - 9  PID // Kill the process by finding the PID number of the process 

    • And then restart Oracle:

       sqlplus / Nolog Span style= "color: #808080;" >//  Enter SQL Plus startup  // start Oracle 

    Finally attaches a command description to close Oracle to avoid the wrong shutdown:

    • shutdown normal (shut down the database after all connections are disconnected)
    • Shutdow n Transactional (active disconnect after all transactions and shut down the database)
    • shutdown immediate (active disconnect and transaction)
    • shutdown abort (closes the database immediately because the operation does not Synchronizes data, empties rollback segments, does not trigger checkpoints, so is dangerous , instance recovery is required for each startup
• ORA-00913: Too many values:
  • Reason: The number of fields does not match when inserting data into the table, for example

     insert  into  table_1 (?,?) values  (?, ?, ?)

    The insertion value is one more than the field, the error is reported

  • FIX: The Insert field is modified to the same number as the insert value to resolve the
• ORA-01791: Not an selected expression error:
  • Cause: When you query a statement with SELECT, the error is reported when you use distinct to sort by the order by.

    Select distinct  from where Order  by B;

    This is because when order by column B is sorted against distinct, distinct cannot find column B.

  • WORKAROUND: Add column B to the query column, i.e.:

    Select distinct  from where Order  by B;

SQL error message sorting and solution (continuous update)