1.To select distinct result for a specific column, we use the command: select distinct(col1) from table1; For example: select distinct(studentid) from student; 2.If we want to select distinct with more than one column, we can use the command: select distinct col1, col2, col3 from table1;… Read More
1.Oracle Delete Duplicate Row In Oracle, we can delete duplicate rows by a criteria/condition: DELETE FROM T_TB1 WHERE rowid not in (SELECT MIN(rowid) FROM T_TB1 GROUP BY C_C1, C_C2, C_C3); ***C_C1, C_C2, C_C3: list of columns that form a criteria/condition
1.Oracle Remapping User Schema When we export and import database, if we want to remapping the user schema For example, from schema fel to schema fel6: remap_schema=fel:fel6
1.Oracle Switch to Another Database If we are using a database db1, and we want to switch to database fel and start it, we can use: export ORACLE_SID=fel echo $ORACLE_SID We are now using the database fel Now, we will start the database fel sqlplus… Read More
1.Compare Date and Time In this command, we will compare the column c_date with the system current date follow a format WHERE c_date <= trim(to_char(sysdate,’yyyymmdd’));
1.Create a Database Link -The database link is used to link two database together CREATE DATABASE LINK OldToNew CONNECT TO fel IDENTIFIED BY fel USING ‘//192.168.1.128:1521/FEL’; COMMIT; -The user account and the URL are the one of the destination DB that we want… Read More
1.Oracle Change Account Password alter user fel identified by felnew;
1.Command to Access Network Assistant Console netca -This command used to access the Console to Configure for Listener, Service Name, etc. 2.Command to Access Database Management Console dbca
1.Change Charset to UTF-8 conn as sysdba SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8; SHUTDOWN IMMEDIATE; STARTUP;
1.Command to Kill Inactive Session SELECT * FROM v$session WHERE status =’INACTIVE’; select ‘alter system kill session ”’ ||sid|| ‘,’ ||serial#|| ”’ immediate;’ as insess from v$session where status=’INACTIVE’; commit; select ‘alter system kill session ”’ ||sid|| ‘,’ ||serial#|| ”’ immediate;’ from v$session… Read More
