Using the short keyword to fill complex SQL in Oracle SQL Developer to make your work more efficient. Follow those simple steps to complete this setup in 5 minutes: Id Usage Template ss Search keyword in stored procedures SELECT * FROM USER_SOURCE WHERE lower(TEXT) LIKE lower('%[keyword]%'); st Search tables by keyword SELECT table_nameFROM user_tablesWHERE table_name LIKE '%[keyword]%'; sc Search tables by column keyword SELECT table_name, column_nameFROM all_tab_columnsWHERE UPPER(column_name) LIKE UPPER('%item_id%')order by UPPER(column_name), upper(table_name); tochar Convert date to char to_char([column], 'YYYY-MM-DD HH24:MI:SS') todate Convert char to date to_date('[2024-01-30]','yyyy-mm-dd') alter Alter table column ALTER TABLE [table_name] MODIFY (column_name datatype); analyze Analyze table analyze table [table name] compute statistics; datafile List data files SELECT file_name, tablespace_name, bytes/1024/1024||'M'FROM dba_data_files; kill Kill a dead lock query SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';', s.username, s.program, s.sql_id, s.sql_child_number, s.status, t.sql_textFROM v$session s left join v$sql t on s.sql_id=t.sql_idWHERE s.type != 'BACKGROUND' and users_executing>0; merge Merge into table MERGE INTO [target_table] tUSING () fON (t.item_id = f.item_id and t.loc_id=f.loc_id)WHEN MATCHED THENUPDATE SETt. = f.,t. = f.WHEN NOT MATCHED THENINSERT (item_id,loc_id)VALUES (f.item_id,f.loc_id); space Query table space usage SELECT a.tablespace_name,total,free,(total - free) rest,ROUND((total - free) / total * 100, 2) || '%' usedFROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 totalFROM dba_data_filesGROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 freeFROM dba_free_spaceGROUP BY tablespace_name) bWHERE a.tablespace_name = b.tablespace_nameORDER BY (total - free) DESC; adddatafile Add a data file to table space ALTER TABLESPACE [name]ADD DATAFILE 'name and path.DBF' SIZE 2000M AUTOEXTEND ON;

Oracle database has an user JVM to run Java program on the database server, which is much powerful than normal stored procedure. It can bind with standard stored procedure interface to integrate with other database functions. I will show a quick example here, and full document can be found at Oracle official website: https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdev/lot.html#List-of-Tables 2. Load Java code into Oracle database, use Oracle SQL Developer or loadjava command line tool. Here use SQL developer as example: 3. Define Stored Procedure interface 4. Call Java stored procedure 5. Receive procedure output 6. Receive exception stacktrace With the print stack trace section in my example code, you can output error stack to SQL output window, which is helpful to address issues.

Here introduce the process to remote debug Oracle procedure. Will also solve common issues as well. Debug Procedure 1. Firstly, you need to download and install latest Oracle SQL Developer from: https://www.oracle.com/database/sqldeveloper/technologies/download/ 2. Then need to specify a port for remote Oracle database to callback. Use a fixed port is easier for your firewall configuration. 3. Launch SQL Developer, choose Settings at menu. Go to Debugger setting and config as below, you can choose other ports below 60000. Please follow all settings in red box, except the port you can customize. 4. Redirect traffic from the port to your machine. Configure the port forwarding at your home router: 5. Now you can use SQL developer connect to remote database and open a procedure for debug. Before start, you need to recompile this procedure for debug, right click on procedure and choose "Compile for Debug". It will show a bug logo on the name. 6. Then add some break point in your procedure by double click at the left of code line. 7. Use "Debug..." function to run the procedure. It will promp you to input your IP, use your public IP. 8. You will see some connect progress in debug log 9. Once it hit the break point, your code will stop and highlight the current line. You can find regular control buttons at top and there are some useful panels at below. This is the happy ending of the story, if you have any issue, below section may helps. Trouble shooting Debug Hung ORA-30683, ORA-12535, ORA-06512 DBMS_DEBUG_JDWP If SQL…