Posts

Oracle Partioning information

  SELECT table_name, partitioning_type, ref_ptn_constraint_name  FROM user_part_tables  WHERE table_name IN ('PROJECT','PROJECT_CUSTOMER','PROJ_CUST_ADDRESS'); SELECT table_name, partition_name, high_value  FROM user_tab_partitions  WHERE table_name in ('PROJECT','PROJECT_CUSTOMER’)  ORDER BY table_name, partition_position; ALTER TABLE project_cust_address DROP PARTITION pd; CREATE TABLE EVENTS( sensor_type VARCHAR2(50), channel VARCHAR2(50), …) PARTITION BY LIST (sensor_type, channel) ( partition p1 values (‘GYRO’,’CH1’),  partition p2 values (‘GYRO’,’CH2’),  partition p3 values (‘CAMERA’,’CH4’), …  partition p44 values ((‘THERMO’,’CH8’),  (‘THERMO’,’CH14’)),  partition p45 values (DEFAULT) );  CREATE TABLE orders ( order_id number,  order_date DATE, … ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY data_dir  ACCESS PARAMETERS (..) ) REJECT LIMIT unlimited PARTITION BY RANGE(order_date) ( partition q1_...
Post a Comment
Read more

Shell scripting cookbook

▸ Proficiency in scripting with Python (Pandas, PySpark, Airflow) and Windows/UNIX shell scripting.  https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/03/oracle-rac-database-cloning-shell-script.html https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/03/oracle-password-encrypt-for-shell.html Setting the Oracle Environment Before running Oracle utilities like sqlplus or rman, you must set specific environment variables within your script. A common approach involves sourcing the system's oraenv utility or explicitly defining the ORACLE_HOME and ORACLE_SID #!/bin/bash # Set Oracle environment variables export ORACLE_SID=ORCL export ORACLE_SID=OP export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export ORACLE_HOME=/home/oracle/app/oracle/product/19.3.0/dbhome_1 export TNS_ADMIN=$ORACLE_HOME/network/admin # Optional: Use the oraenv script (requires /etc/oratab to be correctly configured) #. oraenv <<< $ORACLE_SID Basic Scripting Concepts Variables:  Define variables using...
Post a Comment
Read more

asm sample commands

  select name, redundancy      from v$asm_template      where system = 'Y'      and group_number = (          select group_number from v$asm_diskgroup where name = 'DATA'      )      order by name; select group_number, name, type from v$asm_diskgroup; select GROUP_NUMBER, FILE_NUMBER, TYPE, REDUNDANCY      from v$asm_file      where group_number = (            select group_number from v$asm_diskgroup where name = 'DATA'      );  select MOUNT_STATUS,HEADER_STATUS,STATE,REDUNDANCY,FAILGROUP,PATH from v$asm_disk where group_number=2; select * from v$asm_attribute  select * from v$asm_file where group_number = 2 select * from v$asm_alias where group_NUMBER=2;  select * from gv$asm_client; SELECT name, compatibility, database_compatibility      FROM v$asm_diskgroup; #################...
Post a Comment
Read more

Oracle Rac rac-status.sh

  Blog  https://unknowndba.blogspot.com/2018/04/rac-statussh-overview-of-your-rac-gi.html SCript https://raw.githubusercontent.com/freddenis/oracle-scripts/master/rac-status.sh ./rac-status.sh -h -d  ./rac-status.sh /rac-status.sh -a $ ./rac-status.sh -g 12            # Only grep 12c databases $ ./rac-status.sh -a -g Shut       # Only the lines containing "Shut" $ ./rac-status.sh -n -l -v Online  # Only the listeners that are not Online The default shows any resource restarted less than 24 hours ago. You can modify this default in the script with the variable DIFF_HOURS: DIFF_HOURS="24" Or in the command line with the -w option knowing that hours is the default unit but you can also use d for day, w for week, m for month and y for year: $ ./rac-status.sh -w 360        # 360 hours $ ./rac-status.sh -w 3d         # 3 days $ ./rac-status.sh -w 1w        ...
Post a Comment
Read more