https://medjava.ro/ 2026-04-05T16:18:25+00:00 https://medjava.ro/ https://medjava.ro/lib/exe/fetch.php?media=wiki:dokuwiki.svg text/html 2025-01-02T18:22:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:adjust-tablespace-size&rev=1735842138&do=diff Adjust tablespace size to free up space Check how much space from datafiles are occupied for a specific tablespace Replace <TBS_NAME> with the actual tablespace to check: SELECT df.tablespace_name, df.file_name, df.bytes, SUM(e.bytes) AS used_bytes FROM dba_data_files df LEFT JOIN dba_extents e ON df.file_id = e.file_id WHERE df.tablespace_name='<TBS_NAME>' GROUP BY df.tablespace_name, df.file_name, df.bytes text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:apply-patch&rev=1688488590&do=diff Apply an Oracle Patch (on Windows) * Extract patch (p28574555_122010_MSWIN-x86-64.zip) to a folder (C:\) * set ORACLE_HOME=C:\Oracle\product\12.2.0\dbhome_1 * set PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch * cd C:\p28574555_122010_MSWIN-x86-64\28574555 text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:change-character-set&rev=1688488590&do=diff Change character set (and national character set) for a database sqlplus /nolog SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 24 23:38:27 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. SQL> connect SYS@PMJWE8 as SYSDBA; text/html 2025-01-02T18:22:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:create-bigfile-tablespace&rev=1735842138&do=diff Create bigfile tablespace BIGFILE tablespaces are useful for databases that require very large files, as they can theoretically accommodate up to 8 exabytes (EB) in size. Syntax is: CREATE BIGFILE TABLESPACE tablespace_name DATAFILE 'file_location_and_name' SIZE file_size [AUTOEXTEND ON [NEXT size_increment] [MAXSIZE { UNLIMITED | max_size }]] LOGGING | NOLOGGING EXTENT MANAGEMENT LOCAL [SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }]; text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:drop-tablespace&rev=1688488590&do=diff Completely drop a tablespace and all the associated data files Check data files location First, note all the datafiles associated with your tablespace: SELECT DECODE(r, 1, tablespace_name, null) tablespace_name, file_name FROM (select tablespace_name, file_name, rank() over (partition by tablespace_name order by tablespace_name, file_name) r from dba_data_files order by tablespace_name, file_name ) text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:get-objects&rev=1688488590&do=diff Get Objects From Current Schema List tables select table_name from user_tables; List indexes select index_name, table_name from user_indexes; text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:import-dump&rev=1688488590&do=diff Oracle - import dump Optional - create a dedicated tablespace, otherwise use the default one CREATE TABLESPACE "NEWTBS" NOLOGGING DATAFILE 'C:\DEV\ORACLE\ORADATA\MJ\5E1C9A696BD54020916EA3277599305C\DATAFILE\NEWTBS' SIZE 307200K AUTOEXTEND ON EXTENT MANAGEMENT LOCAL ONLINE SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 8192 text/html 2025-01-02T18:22:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:install-oracle19c-centos7&rev=1735842138&do=diff Install Oracle 19c on Centos 7 yum update -y yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh compat-libstdc++-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64 zip unzip groupadd -g 54421 oinstall group… text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:limit-rows&rev=1688488590&do=diff Limit returned rows (Oracle 12) select e.employee_id from employees e FETCH first 10 rows only EMPLOYEE_ID 100 text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:parallel-sql&rev=1688488590&do=diff Run a query using multiple threads start parallel.sql 8 See [ parallel.zip ] file attached. Set it back to normal (use one thread) alter table <table_name> noparallel text/html 2025-01-02T18:22:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:plsql-naming-conventions&rev=1735842138&do=diff Oracle PL/SQL Naming Conventions * Variables: * Local: prefix with l_ (e.g., l_counter). * Global: prefix with g_ (for those declared in a package specification). * Parameters: * Prefix with p_ (e.g., p_emp_id). * Cursors: * Prefix with text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:pluggable-database&rev=1688488590&do=diff Oracle 12 Pluggable database Auto start a pluggable database when Oracle starts export ORACLE_HOME=/path/to/db_home export PATH=$PATH:$ORACLE_HOME/bin export ORACLE_SID=GDB_SID - SID of the global database name to connect to the main database - CDB (not pluggable - PDB) sqlplus / as sysdba text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:return-rows-between-range&rev=1688488590&do=diff Return rows between specific range (Oracle 12) select e.employee_id from employees e OFFSET 9 ROWS FETCH NEXT 10 ROWS ONLY EMPLOYEE_ID text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:session-locking-package&rev=1688488590&do=diff Check which session is locking a package Replace the PACKAGE_NAME with the real name of the package. SELECT s.sid, l.lock_type, l.mode_held, l.mode_requested, l.lock_id1, 'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate' kill_sid FROM dba_lock_internal l, v$session s WHERE s.sid = l.session_id AND UPPER(l.lock_id1) LIKE '%PACKAGE_NAME' AND l.lock_type = 'Body Definition Lock' text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:sqlplus-format-output&rev=1688488590&do=diff Format sqlplus output set linesize 2500 set wrap off set trimout on set trimspool on set long 100000 set pagesize 0 col inmemory_service_name for a10 col table_name for a10 col cluster_name for a20 col cluster_owner for a20 col iot_name for a10 col default_collation for a10 COLUMN first_name FORMAT A10 WORD_WRAP HEADING 'Name' JUSTIFY CENTER spool C:\buffer\out.txt @C:\buffer\sql.txt spool off exit text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:switch-to-pluggable-db-sqlplus&rev=1688488590&do=diff Switch to a pluggable db (Oracle 12) After you are connected with sqlplus to a container database (which is happening by default), run the connect command inside your sqlplus session. conn SYS@PMJ as SYSDBA Then you'll be required to enter the SYS password. text/html 2023-07-04T16:36:30+00:00 Anonymous (anonymous@undisclosed.example.com) https://medjava.ro/doku.php?id=database:oracle:user-rights&rev=1688488590&do=diff Create user with minimal rights CREATE USER SNTSP6 IDENTIFIED BY sntsp6 DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT / GRANT RESOURCE TO SNTSP6 / GRANT CONNECT TO SNTSP6 / GRANT UNLIMITED TABLESPACE TO SNTSP6 /