Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article we will be discussing the Primary Key Creation Procedures in Oracle Database.
1. Check if there is another Primary Key with the same name.
1 | select * from dba_indexes where index_name like '%EVENT_DATA%'; |
2. Parallel is given to ensure that the result occurs in a short time.
1 2 | alter session force parallel dml parallel 8; alter session force parallel query parallel 8; |
3. Since the study reporting will be done in the database, the relevant REPLICAT Process is STOPPED.
1 2 3 | view params MWREPEDR stop MWREPEDR |
4. At this time, if the database is in archive mode, the archive amount should be checked.
There should be twice the space of the table size.
If there is not enough space, the second step of the return plan should be applied and then the operations should be started.
1 | select log_mode from v$database; |
** If in archive mode;
1 | select name, total_mb, free_mb from v$asm_diskgroup; |
5. The tablespace in which the table is located should be checked.
If there is not enough space, RETURN PLAN step 3 should be applied.
1 2 | select d.tablespace_name tablespace, round(d.used_percent) yuzde, a.free_mb free from DBA_TABLESPACE_USAGE_METRICS d, v$asm_diskgroup a where a.name='DATA' and tablespace_name='WEBTVNEW'; |
6. The Primary Key creation process is started on the table.
It is more logical to do this process on the TERMINAL Server, not on the personal computer.
Thus, the processes are completed without disconnecting the connection.
1 2 | ALTER TABLE WEBTELREPORTING_QSDP.EVENT_DATA_RECORD_QSDP ADD CONSTRAINT WQ_EVENT_DATA_RECORD_QSDP_PK1 PRIMARY KEY(EVENT_ID, EVENT_TIME) using index LOCAL; |
7. Database RAM and CPU usage should be monitored with both OEM and TOP commands.
1 | top -c -M |
1 | https://172.24.78.81:1158/em/console/logon/logon |
8. Check INDEX.
9. Restart REPLICAT.
1 | start MWREPEDR |
Return Plan
1. If an increase in server RAM and CPU is observed, the process that started the INDEX process should be killed to free up resources.
** This process should be done on the same NODE.
1 2 3 4 5 6 7 | select sysdate, t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname, s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time from v$transaction t, v$session s where s.saddr = t.ses_addr order by start_time; ALTER SYSTEM KILL SESSION ',' IMMEDIATE; |
2. Index creation process causes high amount of archive logs.
Therefore, FRA area should be kept under observation.
If this area starts to fill up, the oldest archive records should be deleted to make room in FRA.
1 2 3 | rman target / delete noprompt archivelog all completed before 'sysdate-5'; |
3. The tablespace size of the table should be kept under control.
If there is a possibility of a space problem, the following steps should be followed.
1 | ALTER TABLESPACE WEBTVNEW ADD DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED; |
