In PostgreSQL, “ALTER” and “UPDATE” are two different SQL commands used to modify data in a database. “ALTER” is used to modify the structure of the database, while “UPDATE” is used to modify the data stored within the database. Update: The UPDATE command plays a… Read More
MongoDB CRUD Operations 1 – To create a new database called sample data. Atlas atlas-dsrabt-shard-0 [primary] myFirstDatabase> use sample data. 2 – To create a new collection called students. Atlas atlas-dsrabt-shard-0 [primary] sampledata> db.createCollection(“students”). 3 – To insert one document in to the collection called… Read More
CREATING A NEW TABLE You can create a new table by specifying the table name, along with all column names and their types. Syntax for table creation: CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, columnN datatype, PRIMARY KEY (one or more columns) ); An example for creating table on weather… Read More
Installation of PostgreSQL As PostgreSQL is an open source, we can download it from official website (www.postgresql.org) by following below steps: Go to official website >> Click on Download >> Choose your System OS >> Click “Download the installer” Select latest version (15.2 present) of… 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
CONTROL FLOW Decision making statements in programming languages decide the direction of the flow of program execution based on the given conditions. Python supports if, Elif used to decide the flow of execution. If statement: It is the basic decision-making statement, in this the code… 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
WHAT YOU NEED TO ACHIEVE A JOB? 👉Getting a job as a fresher or experienced seems extremely daunting post-COVID crisis. There is cutthroat competition in the job market, and freshers are always disadvantaged due to this. 👉Nevertheless, landing a job is far from impossible if… Read More
Please check below articles to get good knowledge on DevOps Technology. SNO DESCRIPTION ARTICLE LINK 1 Introduction to DevOps https://www.ktexperts.com/brief-introduction-on-devops/ 2 Introduction to Linux https://www.ktexperts.com/introduction-to-linux/ 3 Basic Linux Commands for DevOps (PART-1) https://www.ktexperts.com/basic-linux-commands-for-devops-part-1/ 4 Linux Basic Commands for DevOps (PART-2) https://www.ktexperts.com/linux-basic-commands-for-devops-part-2/ 5 Introduction to Git… Read More
Amazon Web Services Dear Readers, Please check below articles to get good knowledge on AWS Cloud. SNO DESCRIPTION ARTICLE LINK 1 Introduction to AWS (Amazon Web Services) https://www.ktexperts.com/introduction-to-aws-amazon-web-services/ 2 Create Amazon Web Services Free Tier Account https://www.ktexperts.com/create-amazon-web-services-free-tier-account/ 3 Global Infrastructure in AWS(Amazon Web Services) https://www.ktexperts.com/global-infrastructure-in-awsamazon-web-services/… Read More
