GRANT & REVOKE Commands for the new_user on Oracle 19c Database ?

---------- Grant Commands ------------

GRANT CREATE CLUSTER, CREATE ANY CLUSTER, ALTER ANY CLUSTER, DROP ANY CLUSTER TO &new_user;
GRANT ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM TO &new_user;
GRANT CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX TO &new_user;
GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE TO &new_user;
GRANT CREATE ROLE, ALTER ANY ROLE, DROP ANY ROLE, GRANT ANY ROLE TO &new_user;
GRANT CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT TO &new_user;
GRANT CREATE USER, ALTER USER, DROP USER, CREATE SESSION TO &new_user;
GRANT CREATE VIEW, CREATE ANY VIEW, DROP ANY VIEW TO &new_user;
GRANT CREATE SYNONYM, CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, DROP ANY SYNONYM, DROP PUBLIC SYNONYM TO &new_user;
GRANT CREATE SESSION, ALTER SESSION, RESTRICTED SESSION, ALTER RESOURCE COST TO &new_user;
GRANT CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE TO &new_user;
GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, MANAGE TABLESPACE TO &new_user;

----- Revoke Commands -----

REVOKE CREATE CLUSTER, CREATE ANY CLUSTER, ALTER ANY CLUSTER, DROP ANY CLUSTER FROM &new_user;

REVOKE ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM FROM &new_user;

REVOKE CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX FROM &new_user;

REVOKE CREATE PROFILE, ALTER PROFILE, DROP PROFILE FROM &new_user;

REVOKE CREATE ROLE, ALTER ANY ROLE, DROP ANY ROLE, GRANT ANY ROLE FROM &new_user;

REVOKE CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT FROM &new_user;

REVOKE CREATE USER, ALTER USER, DROP USER, CREATE SESSION FROM &new_user;

REVOKE CREATE VIEW, CREATE ANY VIEW, DROP ANY VIEW FROM &new_user;

REVOKE CREATE SYNONYM, CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, DROP ANY SYNONYM, DROP PUBLIC SYNONYM FROM &new_user;

REVOKE CREATE SESSION, ALTER SESSION, RESTRICTED SESSION, ALTER RESOURCE COST FROM &new_user;

REVOKE CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE FROM &new_user;

REVOKE CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, MANAGE TABLESPACE FROM &new_user;

ALTER USER &new_user QUOTA 100M ON &tablespace;

GRANT UNLIMITED TABLESPACE TO &new_user;

Oracle 19c Architecture Explanation ?

Library cache:-

stores SQL and PL/SQL statements along with their execution plans.

Data Dictionary Cache:-

stores info about database schema such as tables, columns, definitions, indexes, privileges. 

Shared Pool:-

shared memory structures for SQL and PL/SQL execution.

it contains frequently executed SQL statements, execution plans, data dictionary information.

--

buffer memory :- i/o operations happen.

cache memory :- read/write operations happen.

filesystem handling in Oracle 19c RAC ?

 1. verify ACFS installation.

2. Create an ACFS File System:-

srvctl add filesystem -d <device> -m <mount_point>

eg. srvctl add filesystem -d /dev/asm/acfs-vol1-123 -m /acfs_file

3. Start the ACFS File System:-

srvctl start filesystem -d <device>

eg. srvctl start filesystem -d /dev/asm/acfs-vol1-123

4. Mount the ACFS File System:

mount -t acfs <device> <mount_point>

eg. mount -t acfs /dev/asm/acfs-vol1-123 /acfs_file

5. Verify ACFS Status:

srvctl status filesystem -d <device>

eg. srvctl status filesystem -d /dev/asm/acfs-vol1-123

6. Resize ACFS File System :-

acfsutil size +<size> <mount_point>

eg. acfsutil size +100G /acfs_file 

7. List all ACFS file systems:

acfsutil info fs

8. Display information about a specific ACFS file system:

acfsutil info fs -o volume <mount_point>

eg. acfsutil info fs -o volume  /acfs_file

9. Stop  an ACFS FILE SYSTEM:

srvctl stop filesystem -d <device>

eg. srvctl stop filesystem -d /dev/asm/acfs-vol1-123

10. Remove an ACFS file system:

srvctl remove filesystem -d <device>

eg. srvctl remove filesystem -d /dev/asm/acfs-vol1-123

11. Display ACFS resource dependency:

srvctl config fs -d <device>

eg. srvctl config fs -d /dev/asm/acfs-vol1-123

12. 

optimizer parameters in oracle 19c db

begin

for x in (select name from v$parameter where name like 'optimizer%')

loop

dbms_output.put_line(x.name);

end loop;

end;

-------------------------------------------------------
optimizer_features_enable
optimizer_mode
optimizer_index_cost_adj
optimizer_index_caching
optimizer_dynamic_sampling
optimizer_ignore_hints
optimizer_secure_view_merging
optimizer_use_pending_statistics
optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
optimizer_use_invisible_indexes
optimizer_adaptive_reporting_only
optimizer_adaptive_plans
optimizer_inmemory_aware
optimizer_adaptive_statistics
optimizer_ignore_parallel_hints

----------------------------------------------------------

-- Enable timing of SQL statements

SET TIMING ON;

-- Enable autotrace for detailed execution plans

SET AUTOTRACE ON;

-- Set the threshold for identifying slow queries (adjust as needed)

ALTER SESSION SET STATISTICS_LEVEL = ALL;

ALTER SESSION SET TIMED_STATISTICS = TRUE;

ALTER SESSION SET SQL_TRACE = TRUE;

-- Your SQL query goes here

SELECT *

FROM your_table

WHERE your_condition;

-- Reset session settings

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

ALTER SESSION SET TIMED_STATISTICS = FALSE;

ALTER SESSION SET SQL_TRACE = FALSE;

-- Disable timing of SQL statements

SET TIMING OFF;

-- Display execution plan

-- Uncomment the line below if you want to see the execution plan

-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

> /dev/null 2>&1 to prevent errors

> /dev/null : Redirects standard output to /dev/null, discarding it.

2>&1 : Redirects standard error to the same location as standard output. This ensures that both

standard output and standard error are discarded.

script.sh > script.log 2>&1

to prevent errors :-

command or script > /dev/null 2>&1

echo -e "something" > filename enable backlash escape

 echo -e "india\nmaharashtra" > india.txt

cat india.txt

india

maharashtra 

#!/usr/bin/expect to automate Linux commands ?

 #!/usr/bin/expect

spawn some_command   # Replace some_command with the command you want to automate

expect "ExpectedPrompt"   # Replace ExpectedPrompt with the prompt you expect

send "your_input\r"   # Replace your_input with the input you want to provide

interact   # Allows you to interact with the spawned process manually if needed

chmod 777 example.exp 

./example.exp

example.: 

#!/usr/bin/expect

spawn su - goldengate

expect "Password: \r"

send "your_password\r"

expect eof    # wait for spawn processes to finish

\r :- carriage return simulate "Enter" 

\e or \x1B :- for escape key

Ctrl-a : - control plus a 

[root@localhost ~]# cat send.sh

#!/bin/bash

 

 

expect<<!!

spawn vim ./file02.txt

expect "insert mode:\r"

send "i\r"

expect "type some content:\r"

send "I am living in Pune.\r"

expect "save exit\r"

send "\x1B"

send ":wq\r"

expect eof

!!

grep command

 grep -v "^#|^$" filename 

^# :- line start with comment #

^$ :- empty lines 

[a-zA-Z0-9_] :- alpha numeric with _ 

grep -o "^.\{1,10\}" filename  :- find first 10 character word at the beginning of line.

grep -o ".\{1,10\}$" filename :- find last 10 character word at the end of line.

grep -o "^." filename  :- find one character for beginning of each line.

grep -o ".$" filename  :- find one character for end of the each line.

grep -e "^$" filename :- find the empty lines in the file.

grep -e "$" filename :- end of the line.

grep -e "^[a-zA-Z\]\{0,255\}\+\@\+[a-zA-Z\]\{0,255\}\+\:\+\~\+\\$" fun.txt : kayyum@AbdulKayyum:~$

grep -v "^[a-zA-Z\]\{0,255\}\+\@\+[a-zA-Z\]\{0,255\}\+\:\+\~\+\\$" fun.txt  : kayyum@AbdulKayyum:~$

kayyum@AbdulKayyum:~$ cat new.txt | grep -e "grep\|#" | grep -o "grep.*\|#.*"

# grep :- remove empty lines

grep -v "^$" sample.txt

# grep :- remove commented lines.

grep -v "^#" sample.txt

# grep :- to find oracle datafiles

grep -o "[a-zA-Z0-9_\]\{0,255\}\.dbf" sample.txt

# grep :- find first 10 characters each line

grep -o "^.\{1,10\}" sample.txt

# grep :- find last 10 characters each line

grep -o ".\{1,10\}$" sample.txt

# grep :- remove commented and empty lines

grep -v "^#\|^$" sample.txt

# grep :- to find ' " ' double quotes in the file

grep -e "\"" file.txt

# grep :- to find '{ ' and '}' curly braces

grep -e "\{" file.txt

grep -e "\}" file.txt

# combine

grep -e "\{\|\}" file.txt

grep -e "\"\.\"" file.txt

grep -e "[a-zA-Z\]\{0,255\}" file.txt

SED COMMAND ON LINUX

to view file content with line number:-

cat -n file.txt 

-- to delete last line in the file :-

sed -i '$d' filename 

-- to delete first line in the file :-

sed -i '1d' filename 

-- to append something 

[root@localhost ~]# sed -i '$a\

> export ORACLE_HOME=/u02/ogg19c\

> export PATH=$ORACLE_HOME/bin:$PATH\

> export LD_LIBRARY_PATH=$ORACLE_HOME/lib

> ' bash_profile

[root@localhost ~]# cat bash_profile

india

is my country

export ORACLE_HOME=/u02/ogg19c

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

in above :- $ taken for last line of the file so append in the last.

-- to delete lines from file using sed command 

[root@localhost ~]# cat -n bash_profile

     1  india

     2  is my country

     3  export ORACLE_HOME=/u02/ogg19c

     4  export PATH=$ORACLE_HOME/bin:$PATH

     5  export LD_LIBRARY_PATH=$ORACLE_HOME/lib

[root@localhost ~]# # delete line number ---> 3 and 5 using below 

[root@localhost ~]# sed -i '3,5d' bash_profile

[root@localhost ~]#

[root@localhost ~]# cat -n bash_profile

     1  india

     2  is my country

SCHEMAS procedure and functions

set lines 400;
col host for a20;
col owner for a20;
col instance for a20;
col grantee for a20;
col grantor for a20;
col table_name for a40;
select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, object_type, count(*), to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM') "sysdate" from dba_objects where owner=upper('&owner') group by object_type,owner;

select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, count(*) schema_obj_count from dba_objects where owner=upper('&owner') group by owner'

select * from dba_tab_privs where grantee=upper('grantee') and table_name in ('DBMS_LOCK','DBMS_JOB');

-- List all tables in all schemas
SELECT OWNER
	,table_name
FROM dba_tables;

-- List all views in all schemas
SELECT OWNER
	,view_name
FROM dba_views;

-- List all indexes in all schemas
SELECT OWNER
	,index_name
	,table_name
FROM dba_indexes;

-- List all columns of a specific table in all schemas
SELECT OWNER
	,table_name
	,column_name
	,data_type
FROM dba_tab_columns
WHERE table_name = 'your_table_name';

-- List all procedures in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PROCEDURE';

-- List all triggers in all schemas
SELECT OWNER
	,trigger_name
	,table_name
FROM dba_triggers;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all synonyms in all schemas
SELECT OWNER
	,synonym_name
	,table_name
FROM dba_synonyms;

-- List all constraints in all schemas
SELECT OWNER
	,constraint_name
	,constraint_type
	,table_name
FROM dba_constraints;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all materialized views in all schemas
SELECT OWNER
	,mview_name
FROM dba_mviews;

-- List all materialized view logs in all schemas
SELECT OWNER
	,log_table
	,master
FROM dba_mview_logs;

-- List all user-defined types (UDTs) in all schemas
SELECT OWNER
	,type_name
FROM dba_types;

-- List all packages in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PACKAGE';

-- List all package procedures and functions in all schemas
SELECT OWNER
	,object_name
	,PROCEDURE_NAME
	,function_name
FROM dba_procedures
WHERE object_type = 'PACKAGE';

-- List all indexes on a specific table in all schemas
SELECT table_owner
	,index_name
	,column_name
FROM dba_ind_columns
WHERE table_name = 'your_table_name';

-- List all database links in all schemas
SELECT OWNER
	,db_link
FROM dba_db_links;

-----------------------------------------------------

Creating simple procedure:-

-- Create a simple procedure that prints a message
CREATE OR REPLACE PROCEDURE print_message
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, this is a simple procedure!');
END;
/

output:-

-- Execute the procedure

BEGIN

  print_message;

END;

/

--------------------------------------------------------------------------

Creating simple functions:-

-- Create a simple function that adds two numbers

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)

RETURN NUMBER

IS

  result NUMBER;

BEGIN

  result := a + b;

  RETURN result;

END;

/

output:-

-- Call the function

DECLARE

  sum_result NUMBER;

BEGIN

  sum_result := add_numbers(5, 7);

  DBMS_OUTPUT.PUT_LINE('Sum: ' || sum_result);

END;

/

for loop in shell script ?

 #!/bin/bash

start=3

end=8

for i in $(seq $start $end); do

    if test ${i} -lt ${end}

    then

           echo "${i} less than ${n} "

     fi

     if test ${i} -eq ${end}

     then

            echo "${i} equal to ${n} "

    fi 

done