Archives

2023

Jan

January 13, 2026 5 min read Posts

🎺 How to Create Custom Widget

Posts Tutorials Migration Hugo

How to create a custom sidebar widget in a Hugo static website.

January 1, 2026 1 min read HB Cards Theme

Projects

Projects Devops OpenSources

Just a short list of personnal projects, I am currently working on.

Read more about Oracle Clients.

August 1, 2024 2 min read Docs

Procedures

Docs DBA Oracle

Basics find a procedures 1SELECT * 2 FROM USER_OBJECTS 3 WHERE object_type = 'PROCEDURE' 4 AND object_name = 'grant_RW' Example which give SELECT right on one schema to the role 1CREATE OR REPLACE PROCEDURE grant_RO_to_schema( 2 username VARCHAR2, 3 grantee VARCHAR2) 4AS 5BEGIN 6 FOR r IN ( 7 SELECT owner, table_name 8 FROM all_tables 9 WHERE owner = username 10 ) 11 LOOP 12 EXECUTE IMMEDIATE 13 'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee; 14 END LOOP; 15END; 16/ 17 18-- See if procedure is ok -- 19SHOW ERRORS 20 21CREATE ROLE '${ROLE_NAME}' NOT IDENTIFIED; 22GRANT CONNECT TO '${ROLE_NAME}'; 23GRANT SELECT ANY SEQUENCE TO '${ROLE_NAME}'; 24GRANT CREATE ANY TABLE TO '${ROLE_NAME}'; 25 26-- Play the Procedure -- 27EXEC grant_RO_to_schema('${SCHEMA}','${ROLE_NAME}') Procedure which give Read/Write right to one schema: 1su - oracle -c ' 2export SQLPLUS="sqlplus -S / as sysdba" 3export ORAENV_ASK=NO; 4export ORACLE_SID='${SID}'; 5. oraenv | grep -v "remains"; 6 7${SQLPLUS} <<EOF2 8set lines 200 pages 2000; 9CREATE OR REPLACE PROCEDURE grant_RW_to_schema( 10 username VARCHAR2, 11 grantee VARCHAR2) 12AS 13BEGIN 14 FOR r IN ( 15 SELECT owner, table_name 16 FROM all_tables 17 WHERE owner = username 18 ) 19 LOOP 20 EXECUTE IMMEDIATE 21 '\''GRANT SELECT,DELETE,UPDATE,INSERT,ALTER ON '\''||r.owner||'\''.'\''||r.table_name||'\'' to '\'' || grantee; 22 END LOOP; 23END; 24/ 25CREATE ROLE '${ROLE_NAME}' NOT IDENTIFIED; 26GRANT CONNECT TO '${ROLE_NAME}'; 27GRANT SELECT ANY SEQUENCE TO '${ROLE_NAME}'; 28GRANT CREATE ANY TABLE TO '${ROLE_NAME}'; 29GRANT CREATE ANY INDEX TO '${ROLE_NAME}'; 30EXEC grant_RW_to_schema('\'''${SCHEMA}''\'','\'''${ROLE_NAME}''\'') 31exit; 32EOF2 33unset ORAENV_ASK; 34' 1-- This one is working better : 2CREATE OR REPLACE PROCEDURE grant_RW_to_schema( 3myschema VARCHAR2, 4myrole VARCHAR2) 5AS 6BEGIN 7for t in (select owner,object_name,object_type from all_objects where owner=myschema and object_type in ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) loop 8if t.object_type in ('TABLE','VIEW') then 9EXECUTE immediate 'GRANT SELECT, UPDATE, INSERT, DELETE ON '||t.owner||'.'||t.object_name||' TO '|| myrole; 10elsif t.object_type in ('PROCEDURE','FUNCTION','PACKAGE') then 11EXECUTE immediate 'GRANT EXECUTE ON '||t.owner||'.'||t.object_name||' TO '|| myrole; 12end if; 13end loop; 14end; 15/

August 1, 2024 3 min read Docs

Scripting

Docs DBA Oracle Shell

Inside a Shell script One line command: 1# Set the SID 2ORAENV_ASK=NO 3export ORACLE_SID=HANA 4. oraenv 5 6# Trigger oneline command 7echo -e "select inst_id, instance_name, host_name, database_status from gv\$instance;" | sqlplus -S / as sysdba In bash script: 1su - oracle -c ' 2export SQLPLUS="sqlplus -S / as sysdba" 3export ORAENV_ASK=NO; 4export ORACLE_SID='${SID}'; 5. oraenv | grep -v "remains"; 6 7${SQLPLUS} <<EOF2 8set lines 200 pages 2000; 9select inst_id, instance_name, host_name, database_status from gv\$instance; 10exit; 11EOF2 12 13unset ORAENV_ASK; 14' Inside SQL Prompt 1-- with an absolute path 2@C:\Users\Matthieu\test.sql 3 4-- or trigger from director on which sqlplus was launched 5@test.sql 6 7-- START syntax possible as well 8START test.sql Variables usages 1-- User variable (if not define, oracle will prompt) 2SELECT * FROM &my_table; 3 4-- Prompt user to set a variable 5ACCEPT my_table PROMPT "Which table would you like to interrogate ? " 6SELECT * FROM $my_table; Some Examples Example of Shell script to launch sqlplus command: 1export ORACLE_SID=SQM2DWH3 2 3echo "connect ODS/ODS 4BEGIN 5ODS.PURGE_ODS.PURGE_LOG(); 6ODS.PURGE_ODS.PURGE_DATA(); 7END; 8/" | sqlplus /nolog 9 10echo "connect DSA/DSA 11BEGIN 12DSA.PURGE_DSA.PURGE_LOG(); 13DSA.PURGE_DSA.PURGE_DATA(); 14END; 15/" | sqlplus /nolog Example of script to check tablespaces.sh 1#!/bin/ksh 2 3sqlplus -s system/manager <<! 4SET HEADING off; 5SET PAGESIZE 0; 6SET TERMOUT OFF; 7SET FEEDBACK OFF; 8SELECT df.tablespace_name||','|| 9 df.bytes / (1024 * 1024)||','|| 10 SUM(fs.bytes) / (1024 * 1024)||','|| 11 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1)||','|| 12 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) 13 FROM dba_free_space fs, 14 (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df 15 WHERE fs.tablespace_name (+) = df.tablespace_name 16 GROUP BY df.tablespace_name,df.bytes 17 ORDER BY 1 ASC; 18quit 19! 20 21exit 0 1#!/bin/ksh 2 3sqlplus -s system/manager <<! 4 5set pagesize 60 linesize 132 verify off 6break on file_id skip 1 7 8column file_id heading "File|Id" 9column tablespace_name for a15 10column object for a15 11column owner for a15 12column MBytes for 999,999 13 14select tablespace_name, 15'free space' owner, /*"owner" of free space */ 16' ' object, /*blank object name */ 17file_id, /*file id for the extent header*/ 18block_id, /*block id for the extent header*/ 19CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 20from dba_free_space 21where tablespace_name like '%TEMP%' 22union 23select tablespace_name, 24substr(owner, 1, 20), /*owner name (first 20 chars)*/ 25substr(segment_name, 1, 32), /*segment name */ 26file_id, /*file id for extent header */ 27block_id, /*block id for extent header */ 28CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 29from dba_extents 30where tablespace_name like '%TEMP%' 31order by 1, 4, 5 32/ 33 34quit 35! 36 37exit 0 SPOOL to write on system from sqlplus: 1SQL> SET TRIMSPOOL on 2SQL> SET LINESIZE 1000 3SQL> SPOOL /root/output.txt 4SQL> select RULEID as RuleID, RULENAME as ruleName,to_char(DBMS_LOB.SUBSTR(EPLRULESTATEMENT,4000,1() as ruleStmt from gep_rules; 5SQL> SPOOL OFF from script.sql: 1SET TRIMSPOOL on 2SET LINESIZE 10000 3SPOOL resultat.txt 4ACCEPT var PROMPT "Which table do you want to get ? " 5SELECT * FROM &var; 6SPOOL OFF Generate DATA Duplicate table to fill up tablespace or generate fake data: 1SQL> Create table emp as select * from employees; 2SQL> UPDATE emp SET LAST_NAME='ABC'; 3SQL> commit;

August 1, 2024 0 min read Docs

Scripting

Docs DBA Oracle Storage

2023

Oct

October 1, 2023 12 min read Posts

🌌 How to Create this Blog

Posts Tutorials Hugo Git

The beginning of this blog, the first version.

Aug

August 30, 2023 3 min read Docs

🗿 Partition

Docs SysAdmin Systems Unix-Like Disks Partitions

Checks your disks 1# check partion 2parted -l /dev/sda 3fdisk -l 4 5# check partition - visible before the mkfs 6ls /sys/sda/sda* 7ls /dev/sd* 8 9# give partition after the mkfs or pvcreate 10blkid 11blkid -o list 12 13# summary about the disks, partitions, FS and LVM 14lsblk 15lsblk -f Create Partition 1 on disk sdb in script mode 1# with fdisk 2printf "n\np\n1\n\n\nt\n8e\nw\n" | sudo fdisk "/dev/sdb" 3 4# with parted 5sudo parted /dev/sdb mklabel gpt mkpart primary 1 100% set 1 lvm on Gparted : interface graphique (ce base sur parted un utilitaire GNU - Table GPT)