grant revoke delete truncate update drop statement

grant select on schema.table to &user;

revoke select on schema.table from &user;

delete schema.table where column='&column_name';

truncate table schema.table;

update schema.table set &column_name=&value where &column_name=&value;

Pluggable databases (PDBS)

 select * from v$pdbs;

alter pluggable database all open;

alter pluggable database pdb_name save state;

alter pluggable database all save state ;

SQL> show pdbs;
    CON_ID CON_NAME            OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                                   READ WRITE NO

ALTER PLUGGABLE DATABASE pdb$seed,pdb OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb$seed,pdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb$seed OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb CLOSE IMMEDIATE;

startup force;

startup open read write;

startup open read only;

startup upgrade;

shutdown immediate;

shutdown abort;

Oracle DB Patching Steps

Hint:- first shutdown database and listener 

1. copy patch zip and latest opatch utility  to desired directory 

2. unzip patch zip file 

3. cp opatch_zip file to $ORACLE_HOME/   

4. cd $ORACLE_HOME/ 

5. mv OPatch OPatch_old 

6. unzip opatch_zip 

7. you will see new OPatch directory in $ORACLE_HOME/

8. cd OPatch/ 

9. run  ./opatch version 

10. cd to patch_zip directory 

11. cd 32435354/ 

12. 32435354~]$  $ORACLE_HOME/OPatch/opatch apply 

13. if unwanted happens then rollback changes using below 

32435354~]$  $ORACLE_HOME/OPatch/opatch rollback  -id 32435354

14. In the Final step run datapatch utility after starting database and listener 

$ORACLE_HOME/OPatch/datapatch -verbose 

if possible open all pdbs (pluggable databases)

select * from v$pdbs;

alter pluggable database all open;

alter pluggable database pdb_name save state;

alter pluggable database all save state ;

GRID PATCHING :-

cd patch_directory then execute below:-

make sure you've exported all oracle environment variables ORACLE_HOME,ORACLE_SID,PATH, 

LD_LIBRARY_PATH,etc. 

$ORACLE_HOME/OPatch/opatchauto apply -oh $ORACLE_HOME

$ORACLE_HOME/OPatch/opatchauto apply  /path/to/patch_directory  -oh  $ORACLE_HOME

UNLOCK STATS ON TABLES, INDEXES, PARTITION, MATERIALIZED VIEW, SCHEMA

Unlock tables stats

BEGIN

  DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'schema_name', tabname => 'table_name');

END;

/

Unlock Index Stats 

BEGIN

  DBMS_STATS.UNLOCK_INDEX_STATS(ownname => 'schema_name', indname => 'index_name');

END;

/

Unlock Partition Stats

BEGIN

  DBMS_STATS.UNLOCK_PARTITION_STATS(ownname => 'schema_name', tabname => 'table_name', partname => 'partition_name');

END;

/

Unlock Materialized View Stats 

BEGIN

  DBMS_STATS.UNLOCK_MVIEW_STATS(ownname => 'schema_name', mvname => 'materialized_view_name');

END;

/

Unlock Schema Stats

BEGIN

  DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'schema_name');

END;

/

 

EXPDP and IMPDP In Oracle Database

 Best Practice Examples For EXPDP and IMPDP Jobs:-

By default everything in database is exported and imported you can choose for your needs parameters.

select * from all_directories where directory_name='&directory';

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

How to use export import utility from linux terminal :-

$ expdp user_who_started_job/password attach=job_name

  How to export / import using "sysdba"  

          $ expdp \"/ as sysdba\"   

          $ impdp \"/ as sysdba\"

$ impdp user_who_started_job/password attach=job_name

export > status

import > status

export > exit

import > exit

import / export > help 

Note:-

Default behaviour of impdp:-

1. by default imports all objects

2. impdp customize using 'include' and 'exclude' if you don't specify include/exclude then

impdp will imports all objects from the dmp files.

** all objects :- tables, indexes, views, triggers, procedures, functions, packages, constraints, types, etc.**

3.'impdp also imports others related data, such as constraints, grants and synonyms.

4. target db already contains objects with the same name as the imported objects, default behaviour is

to overwrite the existing objects with the imported objects. you can change behaviour using remap_table,

remap_schema, remap_

when to use remap_* :-

schema consolidation, renaming objects, data migration, filtering data

• remap_data

impdp system/password@db_name \

  REMAP_DATA=old_schema.employees:description:OLD_STRING:NEW_STRING \

  dumpfile=export.dmp \

  logfile=import.log

• remap_datafile

impdp system/password@db_name \

  REMAP_DATAFILE=old_datafile.dbf:/new_datafile.dbf \

  dumpfile=export.dmp \

  logfile=import.log

• remap_schema

impdp system/password@db_name \

  REMAP_SCHEMA=old_schema:new_schema \

  dumpfile=export.dmp \

  logfile=import.log

• remap_table

impdp system/password@db_name \

  REMAP_TABLE=old_schema.employees:new_schema.staff \

  dumpfile=export.dmp \

  logfile=import.log

• remap_tablespace

impdp system/password@db_name \

  REMAP_TABLESPACE=old_tablespace:new_tablespace \

  dumpfile=export.dmp \

  logfile=import.log

On SOURCE DB:-

sudo -iu oracle

$ nohup expdp user/pwd directory=DATAPUMP dumpfile=file.dmp schemas=schema_name exclude=statistics logfile=file_expdp.log &

or

$ nohup expdp parfile=filename.par &

ON TARGET DB:-

sudo -iu oracle

$ nohup impdp user/pwd directory=DATAPUMP dumpfile=file.dmp logfile=file_impdp.log &

or

$ nohup impdp parfile=filename.par &

schema_objects:-

**** If you want to empty schema then drop below objects for that schemas *********

tables, views, indexes, constraints, procedures, functions, packages, triggers and synonyms

Hints: --->

In toad, go to "schema browser"

In SQLDEVELOPER, go to left pane, (+) button on each connection then search for object and drop

---------------------------------------------------------------------------------------------- ********************************* SCHEMA EXPORT ********************************* [user@hostname]$ cat schema_export_prod_<date-time>.par DIRECTORY=DATA_PUMP SCHEMAS=<schema_name> DUMPFILE=exp_prod_01132023_%U.dmp LOGFILE=exp_prod_schema_01132023.log PARALLEL=6 FILESIZE=10G EXCLUDE=STATISTICS COMPRESSION=ALL CLUSTER=N STATUS=30 JOB_NAME=exp_prod_schema_01132023 ************************************ TABLE EXPORT ********************************** [user@hostname]$ cat schema_prod_expdp_table_tablename.par DIRECTORY=DATA_PUMP SCHEMAS=<schema_name> DUMPFILE=exp_prod_<schema_name>_<table_name>_%U.dmp LOGFILE=exp_prod_<schema_name>_<table_name>.log PARALLEL=12 FILESIZE=15G COMPRESSION=ALL CLUSTER=N STATUS=30 #JOB_NAME=exp_prod_<schema_name>_<table_name>_01132023 INCLUDE=TABLE:"IN('<table_name>')" ************************************ SCHEMA import ********************************** [user@hostname]$ cat imp_<schema_name>.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=exp_prod_<schema_name>_01282023_%U.dmp LOGFILE=IMP_<schema_name>_<table_name>_02022023.log TABLE_EXISTS_ACTION=REPLACE #JOB_NAME=<schema_name>_<table_name> EXCLUDE=VIEW,PROCEDURE,PACKAGE,FUNCTION,TRIGGER #schemas=<schema_name> EXCLUDE=SCHEMA:"IN('<schema1_name>','<schema2_name>')" Note:- Practice on home lab environment and observer take notes for different scenarios. PARALLEL=32 CLUSTER=N FULL=Y ************************************ SCHEMA import ********************************** [user@hostname]$ cat <schema_name>_<host_name>.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=<schema_name>_<table_name>_%U.dmp LOGFILE=IMP_<schema_name>_<table_name>_10022018.log EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')" SCHEMAS=<schema_name> ##SCHEMAS=<schema_name> parallel=25 TABLE_EXISTS_ACTION=REPLACE CLUSTER=N ************************************ SCHEMA import ********************************** [user@hostname]$ cat <schema_name>.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=<schema_name>_<table_name>_%U.dmp LOGFILE=IMP_<schema_name>_<table_name>.log EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')" SCHEMAS=<schema_name> ##SCHEMAS=<schema_name> parallel=25 TABLE_EXISTS_ACTION=REPLACE CLUSTER=N *********************** FULL DATABASE EXPORT ******************************** [user@hostname]$ cat <database>_FULL_EXP.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=Full_database_%U.dmp LOGFILE=Full_<schema_name>_<table_name>.log ##JOB_NAME=03282016_IMP_EXPORT_ST1_NEW FILESIZE=10G CLUSTER=N PARALLEL=4 full=y EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>)" ********************************** FULL DATABASE EXPORT ******************] [user@hostname]$ cat <database>_FULL_EXP.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=Full_DATABASE_exp_%U.dmp LOGFILE=Full_database_exp.log ##JOB_NAME=<full_database_exp> FILESIZE=10G CLUSTER=N PARALLEL=4 full=y EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>')" *********************************** table index create script ************************** [user@hostname]$ cat DATABASE_TABLE_INDEX_CREATE_SCRIPT.par DIRECTORY=DATA_PUMP_DIR DUMPFILE=NAME_%U.dmp LOGFILE=database_INDEX.log JOB_NAME=database_INDEX CLUSTER=N PARALLEL=24 INCLUDE=INDEX sqlfile=DATABASE_INDEXES_CREATE.sql *****************************************Schema exports ******************************** [user@hostname]$ cat <schema_name>_export_prod.par DIRECTORY=DATA_PUMP_DIR SCHEMAS=<schema_name> DUMPFILE=exp_prod_<schema_name>_%U.dmp LOGFILE=exp_prod_<schema_name>.log PARALLEL=12 FILESIZE=15G EXCLUDE=STATISTICS COMPRESSION=ALL CLUSTER=N STATUS=30 EXCLUDE=TABLE:"IN ('<table_name>',...,'<table_name>')" JOB_NAME=exp_prod_<schema_name>_<table_name> **************************************************************************************

Add, Drop and Rename Columns From Table In Oracle Database

 To Add Column

ALTER TABLE &table_name
ADD (&column_name &data_type);

To Drop Column

ALTER TABLE &table_name

DROP COLUMN &column_name;

To Rename Column

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

Indexes And Constraints On Tables

 Indexes

create index &index_name on &table_name (&column_name);

ALTER INDEX &current_index_name RENAME TO &new_index_name;

CREATE INDEX &functional_index_name ON &table_name (LOWER(&column_name));

alter index &index_name unusable;

alter index &index_name rebuild;

alter index &index_name rebuild online;

drop index &index_name ;

views for indexes:

dba_indexes, user_indexes, all_indexes

select * from dba_segments where sement_name='&table_name';

Constraints 

create, add, drop, enable and disable table constraints 

create table &table_name (&column_name &datatype, &column_name &datatype, constraint &con_name unique (&column_name));

example:

CREATE TABLE employees (
    employee_id   NUMBER(5),
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(100),
    hire_date     DATE,
    salary        NUMBER(10,2),
    department_id NUMBER(3),
    
    CONSTRAINT pk_employees PRIMARY KEY (employee_id),
    CONSTRAINT uk_email UNIQUE (email),
    CONSTRAINT fk_department FOREIGN KEY (department_id)
        REFERENCES departments (department_id),
    CONSTRAINT ck_salary CHECK (salary > 15000)
);

ADD CONSTRAINTS TO TABLE

alter table &table_name 
add 
constraint &constraint_name unique (&column_name);

DROP CONSTRAINTS FROM TABLE

alter table &table_name
drop
constraint &constraint_name;

DISABLE CONSTRAINTS ON A TABLE

alter table &table_name 
disable
constraint &constraint_name;

ENABLE CONSTRAINTS ON A TABLE

alter table &table_name
enable
constraint &constraint_name;

TO GET DETAILS/STATUS OF A CONSTRAINT :

select owner, constraint_name, constraint_type, table_name, status, index_owner, index_name from
all_constraints where owner=upper('&schema_name');

select owner, constraint_name, constraint_type, table_name, status, index_owner, index_name from
dba_constraints where owner=upper('&schema_name');

if you've logged as desired user then :

select owner, constraint_name, constraint_type, table_name, status, index_owner, index_name from
user_constraints;

Gather statistics for table,index and schema

Statistics Types:-

1) System statistics :- 

exec dbms_stats.gather_system_stats('Start');

select * from sys.aux_stats$;

2) Optimizer statistics 

Below are the Optimizer Statistics ( tables, indexes and Schema):-

 -- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for a specific table

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname     => 'schema_name',
  tabname     => 'table_name',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade     => TRUE
);

-- Execute the following query to view statistics for a specific table

SELECT *

FROM DBA_TABLES

WHERE OWNER = upper('&schema_name')

AND TABLE_NAME = upper('&table_name');

------------------- Stats for Index -----------------------

-- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for a specific index

EXEC DBMS_STATS.GATHER_INDEX_STATS(

  ownname     => 'schema_name',

  indname     => 'index_name',

  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

);

-- Execute the following query to view statistics for a specific index

SELECT *

FROM DBA_INDEXES

WHERE OWNER = upper('&schema_name')

AND INDEX_NAME = upper('&index_name');

-------------------------------------  Stats for SCHEMA ---------------------------

-- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for all objects in a schema

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(

  ownname     => 'schema_name',

  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

  cascade     => TRUE

);

-- Execute the following query to view statistics for all objects in a schema

SELECT *

FROM DBA_TAB_STATISTICS

WHERE OWNER = upper('&schema_name');

----- Optimizer Statistics ----------

exec dbms_stats.gather_database_stats;

exec dbms_stats.gather.dictionary_stats;

***************************************************

select table_name from dba_tables where table_name in ('TABLE1','TABLE2');

select owner,segment_name  from dba_segments where segment_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5');

select * from dba_dependencies where name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5'); --- TABLE1,TABLE2,TABLE3,TABLE4,TABLE5

select (select host_name from v$instance),(select instance_name from v$instance),table_name, count(index_name) "indexes_count" from dba_indexes where table_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5') group by table_name;

--- READY MADE QUERYIES 

set serverout on;

begin 

for x in (select owner,segment_name  from dba_segments where segment_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5''))

loop

dbms_output.put_line('EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> '''||x.owner||''''||','||' tabname=>'||''''||x.segment_name||''''||','||'estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);');

end loop;

end ;

/

--EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE1',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE2',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE3',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE4    ',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

Rebuild/Move tables and Rebuild indexes and Shrink tables

 1) Rebuild tables with no long columns and LOB columns (below example for

sequential move)

SQL> alter table <owner>.<table_name> move tablespace <tablespace_name>;

SQL> alter table &owner.&table_name move tablespace &tablespace_name;

2) If time constraint and having multiple cpu's then use below

SQL> alter table <owner>.<table_name> move tablespace <tablespace name>

parallel < no_of_cpu>;

SQL> alter table &owner.&table_name move tablespace &tablespace_name parallel &no_of_cpu;

3) If we are using parallel, then the above statement permanent

changes to the table  for parallelism, so better to reset after

the table rebuild is done

SQL> alter table table_name parallel 1;

SQL> alter table &table_name parallel &no_of_cpu;

4) We need to rebuild  all the table indexes after move them to new tablespaces

SQL>

select 'alter index '|| owner || '.' || index_name || ' rebuild ;'

from dba_indexes

where table_owner = '&owner'

and table_name = '&table_name'

and partitioned = 'NO'

and index_type != 'LOB'

order by owner, index_name;

5) check index status

SQL> select index_name, status from dba_indexes where table_name='&table' ;

SQL> ALTER TABLE pune.city ENABLE ROW MOVEMENT;

SQL> ALTER TABLE pune.city SHRINK SPACE CASCADE;

SQL> alter tablespace users coalesce;

-- Use Segment Advisor

-- Analyze table

SQL> ANALYZE TABLE pune.city COMPUTE STATISTICS;

SQL> ALTER TABLE pune.city COMPRESS FOR ALL OPERATIONS;

HOW TO CHECK IF INDEX NEEDS TO BE REBUILD

1.  SQL>  analyze index &index_name validate structure;

1.1 SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;

NAME                HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROWS

ASIA_INDEX                1                    3                    1                            0

Hint:- if  HEIGHT > 4 -----> REBUILD THE INDEX

           DEL_LF_ROWS < 20%

2. SQL>  analyze index &index_name monitoring usage;   ----> query v$object_usage;

3.  SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SYS', 'ASIA_INDEX');

To shrink tables in oracle database:

--Enable Row Movement

SQL> alter table employees enable row movement;

--Shrink the table  and compact data and release space back to tablespace

SQL> alter table employees shrink space;

--Rebuild indexes

SQL> alter index emp_idx rebuild;

--Disable row movement

SQL> alter table employees disable row movement;

different ways to connect to sqlplus console from remote server

 sqlplus /  as sysdba  ---------------------> from the host server

sqlplus user/passwd@service_or_sid ---------------------> from remote server

sqlplus user/passwd@host:1521/service_or_sid ---------------------> remote server 

to check database status:- 

sudo -iu oracle----> tnsping servicename ----> thats it as per tnsnames.ora entries 

to check server status :-

ping server as per /etc/hosts entries

from shell script 

export OH

export OS

export PATH

export LD_LIBRARY_PATH

sqlplus \/nolog <<< !! > output.txt

conn $constring;

$query;

exit;

!!

Automatically export ORACLE environment variables in Linux

 

cat /etc/oratab | grep -e "^[\a-Az-Z0-9\]\{0,8\}\:*/[[:graph:]]*" | awk -F: '{print $1,$2}' | read a b

$ echo $a

target

 $ echo $b

/usr/local/opt/oracle/app/oracle/product/11.2.0.4

export ORACLE_HOME=$b

export ORACLE_SID=$a

export PATH=$ORACLE_HOME/bin:$PATH