Introduction to SQL Commands (DDL, DML,DQL, DCL, TCL) Beginner-Friendly Professional Documentation 1. SQL Basics & Command Categories What is SQL? SQL (Structured Query Language) is a standard language used to manage and interact with databases. Databases store data in the form of tables (rows and… Read More
Data Retrieval language(DRL) Data Retrieval Language (DRL) is a part of SQL used to retrieve (fetch) data from a database without modifying it. Main Command: SELECT – used to get data from one or more tables. Examples:
|
1 2 3 4 5 |
SELECT * FROM students; → Retrieves all records from the students table. SELECT name, marks FROM students WHERE marks > 60; → Retrieves names and marks of students who scored more than 60. |
Purpose: ● To view data ● To… Read More
Data Definition Language (DDL) Introduction Structured Query Language (SQL) is the standard language used for interacting with relational databases. SQL is divided into different categories based on the type of operations performed on the database. Among these categories, Data Definition Language (DDL) plays a foundational… Read More
Introduction to SQL What is SQL? SQL (Structured Query Language) is a database computer language used to communicate with and retrieve data from relational databases. It allows users to store, manipulate, update, and retrieve data efficiently. SQL is widely used with popular database systems such… Read More
Automating OSWatcher Log Capture with oswlogs_capture.sh Monitoring system performance is essential for ensuring stability, especially in production environments. Oracle’s OSWatcher (OSWbb) is a powerful tool that continuously captures vital operating system metrics. However, extracting logs for specific time periods can be cumbersome without a bit… Read More
Table/index movement using pl/sql procedure
|
1 2 |
CREATE TABLESPACE AWS_DATA01 DATAFILE '+DATA' size 10G ##################### PRE-VALIDATIONS ################## |
1) Gather all below data into a SQL log file for future validations
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
spool TABLE_INDEX_REORG_REBUILD_PREVALIDATIONS.log set echo on set linesize 300 col index_name for a30 col degree for a10 col status for a10 col table_name for a30 col owner for a30 col object_type for a20 col constraint_type for a20 col constraint_name for a30 col validated for a20 col degree for a10 set pagesize 100 select owner,object_type,status,count(*) from dba_objects where status <> 'VALID' and owner='SCOTT' group by owner,object_type,status order by owner; select owner,index_name,status from dba_indexes where TABLE_OWNER='SCOTT' and status<>'VALID'; select index_name,status from dba_ind_partitions where status <> 'USABLE'; select CONSTRAINT_TYPE,STATUS,count(1) from dba_constraints where owner='SCOTT' group by CONSTRAINT_TYPE,STATUS; set linesize 300 col index_name for a30 col degree for a10 col status for a10 select TABLE_NAME,INDEX_NAME,DEGREE,status from dba_indexes where OWNER='SCOTT' and degree not in (0,1); select object_type , count(*),status from dba_objects where owner=upper('SCOTT') group by object_type,status order by object_type; select table_name,index_name,logging from dba_indexes where OWNER='SCOTT' and logging = 'NO'; select distinct STATUS from v$datafile; select constraint_name,constraint_type,table_name,status,validated from dba_constraints where owner='SCOTT' and status <> 'ENABLED'; select distinct owner from dba_segments where tablespace_name='&tablespace_name'; select distinct owner from dba_tables where tablespace_name='&tablespace_name'; select distinct owner from dba_indexes where tablespace_name='&tablespace_name'; select distinct table_owner from dba_tab_partitions where tablespace_name='&tablespace_name'; select distinct table_owner from dba_tab_subpartitions where tablespace_name='&tablespace_name'; select distinct index_owner from dba_ind_partitions where tablespace_name='&tablespace_name'; select distinct index_owner from dba_ind_subpartitions where tablespace_name='&tablespace_name'; select distinct STATUS from v$datafile; select * from v$recover_file; select distinct segment_type from dba_segments where tablespace_name='&tablespace_name'; select distinct status from dba_indexes; select distinct status from dba_ind_partitions; select distinct status from dba_ind_subpartitions; spool off |
2) Export respective schema and whole DB as part of prechecks. 3) Create a Guaranteed Restore point and drop before dropping tablespace if everything is… Read More
Datapump export and import from Source PDB to Target PDB.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 |
Source : SQL> alter session set container=OGGDB1PDB; Session altered. SQL> select username from dba_users where oracle_maintained='N'; USERNAME -------------------------------------------------------------------------------- PDBADMIN HR SQL> select table_name from dba_tables where owner='HR'; TABLE_NAME -------------------------------------------------------------------------------- REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB_HISTORY COUNTRIES 7 rows selected. SQL> select table_name,num_rows from dba_tables where owner='HR'; TABLE_NAME NUM_ROWS ------------------- ---------- REGIONS 4 LOCATIONS 23 DEPARTMENTS 27 JOBS 19 EMPLOYEES 107 JOB_HISTORY 10 COUNTRIES 25 7 rows selected. SQL> select directory_name from dba_directories; DIRECTORY_NAME -------------------------------------------------------------------------------- XMLDIR XSDDIR ORA_DBMS_FCP_LOGDIR ORA_DBMS_FCP_ADMINDIR OPATCH_INST_DIR ORACLE_OCM_CONFIG_DIR DATA_PUMP_DIR ORACLE_OCM_CONFIG_DIR2 OPATCH_SCRIPT_DIR OPATCH_LOG_DIR ORACLE_BASE ORACLE_HOME 1* select directory_name,directory_path from dba_directories SQL> / DIRECTORY_NAME DIRECTORY_PATH ----------------- --------------------------------------------------------------------------------- XMLDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml XSDDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0.1/cfgtoollogs ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0.1/rdbms/admin OPATCH_INST_DIR /u01/app/oracle/product/12.2.0.1/OPatch ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12.2.0.1/ccr/state DATA_PUMP_DIR /u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638 ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0.1/QOpatch OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0.1/rdbms/log ORACLE_BASE /u01/app/oracle ORACLE_HOME /u01/app/oracle/product/12.2.0.1 OGGDB1PDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oggdb1pdb) ) ) expdp sys/oracle@OGGDB1PDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log Export: Release 12.2.0.1.0 - Production on Fri Dec 2 00:02:19 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. UDE-28009: operation generated ORACLE error 28009 ORA-28009: connection as SYS should be as SYSDBA or SYSOPER Username: sys/oracle@OGGDB1PDB as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@OGGDB1PDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/TRIGGER . . exported "HR"."EMPLOYEES" 17.08 KB 107 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638A8C0EB50/hr_employees.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 2 00:03:28 2022 elapsed 0 00:00:37 Target : [oracle@ggate1 ~]$ . oraenv ORACLE_SID = [oggdb1] ? tgtcdb The Oracle base remains unchanged with value /u01/app/oracle SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ---------------------- ---------- ---------- 2 PDB$SEED READ ONLY NO 3 TARGETPDB READ WRITE NO SQL> alter session set container=TARGETPDB; Session altered. 1* select directory_name,directory_path from dba_directories SQL> / DIRECTORY_NAME DIRECTORY_PATH --------------------------------------- ------------------------------------------------------------------------- XMLDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml XSDDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0.1/cfgtoollogs ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0.1/rdbms/admin OPATCH_INST_DIR /u01/app/oracle/product/12.2.0.1/OPatch ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12.2.0.1/ccr/state DATA_PUMP_DIR /u01/app/oracle/admin/tgtcdb/dpdump/EE1DA335F2064F71E0536638A8C034E1 ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0.1/QOpatch OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0.1/rdbms/log ORACLE_BASE /u01/app/oracle ORACLE_HOME /u01/app/oracle/product/12.2.0.1 SQL> select table_name,num_rows from dba_tables where owner='HR'; TABLE_NAME NUM_ROWS ----------------- ----------------- REGIONS 4 LOCATIONS 23 DEPARTMENTS 27 JOBS 19 EMPLOYEES 107 JOB_HISTORY 10 COUNTRIES 25 7 rows selected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- -------------------------- ---------- ---------- 3 TARGETPDB READ WRITE NO SQL> TARGETPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TARGETPDB) ) ) impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace [oracle@ggate1 admin]$ impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace Import: Release 12.2.0.1.0 - Production on Fri Dec 2 00:09:15 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. UDI-28009: operation generated ORACLE error 28009 ORA-28009: connection as SYS should be as SYSDBA or SYSOPER Username: sys/oracle@TARGETPDB as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@TARGETPDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HR"."EMPLOYEES" 17.08 KB 107 rows Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/TRIGGER Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 2 00:10:14 2022 elapsed 0 00:00:32 |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates KTExperts is always active on social media platforms. Facebook : https://www.facebook.com/ktexperts/ LinkedIn : https://www.linkedin.com/company/ktexperts/ Twitter : https://twitter.com/ktexpertsadmin YouTube … Read More
Pluggable database save state Automate Pluggable DB opening during Container instance startup PDB Saved State is a feature in 12.1.0.2 for saving the OPEN state of all PDBS when CDB is restarted. This is for preserving the state of a Pluggable database proir to CDB… Read More
Oracle,Pluggable Different ortypes of creating Pluggable Database manually There are multiple ways to create a pluggable database, in this article we would go through the process of creating PDB manually as in certain cases where GUI model is not feasible as creating a PDB using… Read More
CLONE ORACLE 12c PLUGGABLE DATABASE INTO ANOTHER CDB In this article we will learn how to clone oracle 12c pluggable database (SHOPDB1) from one container database (SHOCDB1) to another container database (SHOCDB2), using the DBLINK The 3 main steps that we will do to clone… Read More
