Popular Posts

View unified audit report - oracle database script

View unified audit report - Oracle Database Script.- Unified reportFOR LAST 1 HOUR:SET lines 299 col SQL_TEXTFOR a23 col action_nameFOR a18 col UNIFIED_AUDIT_POLICIESFOR a23SELECT action_name, SQL_TEXT, UNIFIED_AUDIT_POLICIES, EVENT_TIMESTAMPFROM unified_AUDIT_trailWHERE EVENT_TIMESTAMP > sysdate -1/24; read more

View privileges granted to an user - oracle database script

View Privileges granted to an user - Oracle Database Script.-- System privileges granted to an user ( scott) SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT'; -- Roles granted to an user ( scott) SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT'; -- Object privileges granted to an user ( SCOTT) SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT'; -- Column specific privileges granted SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT'; read more

View dataguard message or errors - oracle database script

View dataguard message or errors - Oracle Database Script.SELECT MESSAGEFROM V$DATAGUARD_STATUS; read more

View container db information - oracle database script

View container DB information - Oracle Database Script.COLUMN NAME FORM A8SELECT NAME, CON_ID, DBID, CON_UID, GUIDFROM V$CONTAINERS; read more

View all scheduler schedules - oracle database script

View all scheduler schedules - Oracle Database Script.SET pagesize 200SET lines 299 col START_DATEFOR a45 col REPEAT_INTERVALFOR a45 col schedule_nameFOR a34SELECT schedule_name, schedule_type, start_date, repeat_intervalFROM dba_scheduler_schedules; read more

View acl information - oracle database script

View ACL information - Oracle Database Script.SET lines 200 COL ACL_OWNERFOR A12 COL ACLFOR A67 COL HOSTFOR A34 col PRINCIPALFOR a20 col PRIVILEGEFOR a13SELECT ACL_OWNER, ACL, HOST, LOWER_PORT, UPPER_PORTFROM dba_network_acls;SELECT ACL_OWNER, ACL, PRINCIPAL, PRIVILEGEFROM dba_network_acl_privileges; read more

User creation details in user$ table - oracle database script

User creation details in user$ table - Oracle Database Script.SELECT NAME, type#, ctime, ptime, exptime, ltimeFROM sys.user$WHERE NAME IN ('SYS', 'SYSTEM')ORDER BY NAME;NOTE: CTIME -> USER CREATION TIME PTIME -> LAST PASSWORD CHANGE TIME EXPTIME -> PASSWORD EXPIRY DATE LTIME - > ACCOUNT LOCK TIME read more

Unified audit policies present in db - oracle database script

Unified audit policies present in db - Oracle Database Script.-- Audit policies present in db: select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES; -- Enabled audit policies in db: select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES; -- Get the audit options included in an policy select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME='ORA_SECURECONFIG'; read more

Top index sizes of table/schema - oracle database script

Top index sizes of table/schema - Oracle Database Script.--- Find index name and their sizes of a table SELECT idx.table_name,bytes/1024/1024/1024 FROM dba_segments seg, dba_indexes idx where idx.table_name='&TABLE_NAME' AND idx.index_name = seg.segment_name GROUP BY idx.table_name order by 1; -- Find total_index_size of respective tables in a schema SELECT idx.table_name, SUM(bytes/1024/1024/1024) FROM dba_segments seg, dba_indexes idx WHERE idx.table_owner = 'SIEBEL' AND idx.owner = seg.owner AND idx.index_name = seg.segment_name GROUP BY idx.table_name order by 1 read more

Temp tablespace details in multitenant - oracle database script

Temp tablespace details in Multitenant - Oracle Database Script. SELECT a.name, b.FILE_ID, b.tablespace_name, b.file_nameFROM V$CONTAINERS a , CDB_TEMP_FILES bWHERE a.con_id=b.con_id; read more

Target list monitored by oem - oracle database script

target list monitored by OEM - Oracle Database Script.-- Run from oms server($OMS_HOME/bin) -- List all the target ./emcli get_targets -- List the target types present: ./emcli get_target_types -- List targets of particular target_type(say oracle_database) ./emcli get_targets -targets="oracle_database" read more

Tablespace quota for a user - oracle database script

Tablespace quota for a user - Oracle Database Script.-- Get the current tablespace quota information of an user set lines 299 select TABLESPACE_NAME,BYTES/1024/1024 "UTILIZIED_SPACE" ,MAX_BYTES/1024/1024 "QUOTA_ALLOCATED" from dba_ts_quotas where username='&USER_NAME'; TABLESPACE_NAME                  UTILIZIED_SPACE        QUOTA_ALLOCATED ------------------------------         ---------------------------        -------------------------- USERS                                           .0625                                                   1024   --- Change the tablespace quota for the user to 5G ALTER USER SCOTT QUOTA 5G ON USERS;   --- Grant unlimited tablespace quota: ALTER USER SCOTT QUOTA UNLIMITED ON USERS; read more

Table_exists_action option with impdp - oracle database script

TABLE_EXISTS_ACTION option with impdp - Oracle Database Script.TABLE_EXISTS_ACTION OPTION IN IMPDP: TABLE_EXISTS_ACTION Action TO take IF imported OBJECT already exists. VALID keywords ARE: APPEND,REPLACE, [SKIP]AND TRUNCATE. TABLE_EXISTS_ACTION=SKIP: This IS the DEFAULT OPTION WITH impdp. I.e IF the the TABLE EXISTS, it will skip that table. TABLE_EXISTS_ACTION=APPEND: WHILE importing the TABLE, IF the TABLE EXISTS IN the DATABASE, THEN it will append the DATA ON top the EXISTING DATA IN the table. impdp dumpfile=emp_tab.dmp LOGFILE=emp_tab.log directory=VEN table_exists_action=APPEND TABLE_EXISTS_ACTION=TRUNCATE: WHILE importing the TABLE, IF the TABLE EXISTS IN DATABASE, it will TRUNCATE the TABLEAND LOAD the data. impdp dumpfile=emp_tab.dmp LOGFILE=emp_tab.log directory=VEN table_exists_action=TRUNCATE TABLE_EXISTS_ACTION=REPLACE: WHILE importing, IF the TABLE EXISTS IN DATABASE, THEN it willDROP itAND recreate itFROM the DUMP impdp dumpfile=emp_tab.dmp LOGFILE=emp_tab.log directory=VEN table_exists_action=REPLACE read more

Stop/start oms in cloud control - oracle database script

Stop/start oms in cloud control - Oracle Database Script.----stop/start oms in oem 12c/13c. cd $ORACLE_HOME/bin emctl stop oms emctl start oms -- status of oms emctl status oms read more

Stop/start mrp process in standby - oracle database script

Stop/start MRP process in standby - Oracle Database Script.--Cancel MRP(media recovery) process in standby: alter database recover managed standby database cancel; --Start MRP(media recovery): alter database recover managed standby database disconnect from session; -- For real time media recovery alter database recover managed standby database using current logfile disconnect from session; read more

Stop/start agent in oem cloud control - oracle database script

stop/start agent in oem cloud control - Oracle Database Script.--- stop/start agent in oem 12c//13c cd $AGENT_HOME/bin ./emctl start agent ./emctl stop agent ---- status of agent ./emctl status agent read more

Stop and start pluggable db - oracle database script

stop and start pluggable db: - Oracle Database Script.-- Open/close all the pluggable db: -- Connect to root container: alter pluggable database all open; alter pluggable database all close immediate; -- Stop/start a pluggable db: SQL> alter session set container=PDB1; Session altered. SQL> startup Pluggable Database opened. SQL> shutdown Pluggable Database closed. read more

Status of pdbs in multitenant - oracle database script

Status of PDBS in multitenant - Oracle Database Script.SQL>SELECT dbid, name, open_mode, TOTAL_SIZE/1024/1024FROM v$pdbs;DBID NAME OPEN_MODE TOTAL_SIZE/1024/1024 ---------- ------------------------------ ---------- -------------------- 3987628790 PDB$SEED READ ONLY 830 1360187792 PDB1 READ WRITE 905 3819422575 PDB2 MOUNTED 0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTED read more

Statements audited in oracle

Statements audited in oracle col user_nameFOR a12 heading "User name" col audit_option format a30 heading "Audit Option"SET pages 1000 prompt prompt SYSTEM auditing OPTIONS across the SYSTEMAND BY USERSELECT user_name, audit_option, success, failureFROM sys.dba_stmt_audit_optsORDER BY user_name, proxy_name, audit_option / read more

Sqlfile option with impdp - oracle database script

sqlfile option with impdp - Oracle Database Script.It can be used, ONLY WITH impdp. This helps IN generating the DDLsFROM a dumpfile. Suppose We have a DUMP FILE OF TABLE AODBA.DEP_TAB . IF you need the DDL OF the TABLE, THEN USE sqlfile WITH impdp command AS below. PARFILE SAMPLE: dumpfile=test.dmp LOGFILE=test1.log directory=TEST TABLES=AODBA.DEP_TAB sqlfile=emp_tab.sql note- DDL OUTPUT will be logged IN the emp_tab.sql FILE read more

Space usage by lob column - oracle database script

space usage by LOB column - Oracle Database Script.SPACE USED BY LOB COLUMN:SELECT s.bytesFROM dba_segments sJOIN dba_lobs l USING (OWNER, segment_name)WHERE l.table_name = '&table_name';ACTUAL SPACE USED BY LOB:SELECT nvl((sum(dbms_lob.getlength(&lob_column))),0) AS bytesFROM &TABLE_NAME; read more

Show history of pdbs - oracle database script

show History of PDBS - Oracle Database Script.SET lines 299SET pagesize 299 col db_nameFOR a10 col CLONED_FROM_PDB_NAMEFOR a12 col pdb_nameFOR a18SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAMEFROM CDB_PDB_HISTORY; read more

Set local_listener in db - oracle database script

Set local_listener in db - Oracle Database Script.-- Make the sure the a listener is already running with that port(i.e 1524 here) alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 162.20.217.15)(PORT = 1524))' scope=both; alter system register; select type, value from v$listener_network where TYPE='LOCAL LISTENER'; read more

Services associated with pdbs - oracle database script

Services associated with PDBs - Oracle Database Script.COLUMN NETWORK_NAMEFOR A34 COLUMN PDBFOR A15 COLUMN CON_IDFOR 999SELECT PDB, NETWORK_NAME, CON_IDFROM CDB_SERVICESWHERE PDB IS NOT NULL AND CON_ID > 2ORDER BY PDB; read more

Scheduler shell script in dbms_scheduler - oracle database script

scheduler shell script in dbms_scheduler - Oracle Database Script.-- This feature in available from oracle 12c onward -- Create an credential store: BEGIN dbms_credential.create_credential ( CREDENTIAL_NAME => 'ORACLEOSUSER', USERNAME => 'oracle', PASSWORD => 'oracle@98765', DATABASE_ROLE => NULL, WINDOWS_DOMAIN => NULL, COMMENTS => 'Oracle OS User', ENABLED => true ); END; / -- Create the job: exec dbms_scheduler.create_job(- job_name=>'myscript4',- job_type=>'external_script',- job_action=>'/export/home/oracle/ttest.2.sh',- enabled=>true,- START_DATE=>sysdate,- REPEAT_INTERVAL =>'FREQ=MINUTELY; byminute=1',- auto_drop=>false,- credential_name=>'ORACLEOSUSER'); read more

Scheduler job detail in cdb - oracle database script

Scheduler job detail in CDB - Oracle Database Script.SELECT CON_ID, JOB_NAME, JOB_TYPE, ENABLED, STATE, NEXT_RUN_DATE, REPEAT_INTERVALFROM cdb_scheduler_jobs; read more

Rename/move a datafile - oracle database script

Rename/move a datafile - Oracle Database Script.--------------- For oracle 12c, move or rename of datafile can be done online with one line: SQL> alter database move datafile '/home/oracle/producing1.dbf' to '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf'; -- -------------For 11g, u have to follow below steps:( It needs downtime for the datafile) --Make the tablespace offline: alter database datafile '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' offline; -- Move the file physically to a new location. mv /home/oracle/app/oracle/oradata/cdb1/testin1.dbf /home/oracle/producing1.dbf -- Rename at db level alter database rename file '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' to '/home/oracle/producing1.dbf'; -- Recover the datafile: recover datafile 37; -- Make the datafile online: alter database datafile '/home/oracle/producing1.dbf' online; read more

Rename tablespace in oracle db

Rename tablespace in oracle db SQL❯SELECT file_id, file_name, tablespace_nameFROM dba_data_filesWHERE file_id=37;FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------------- ------------------------------ 37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf TESTING --- Rename the tablespace_name from TESTING to PRODUCING; SQL❯ALTER TABLESPACE TESTING RENAME TO PRODUCING;TABLESPACE altered. SQL❯SELECT file_id, file_name, tablespace_nameFROM dba_data_filesWHERE file_id=37;FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------------- ------------------------------ 37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf PRODUCING read more

Privileges audited in database - oracle database script

Privileges Audited in database: - Oracle Database Script.col user_nameFOR a12 heading "User name" col privilegeFOR a30 heading "Privilege"SET pages 1000 prompt prompt SYSTEM PRIVILEGES audited across SYSTEMSELECT user_name, privilege, success, failureFROM dba_priv_audit_optsORDER BY user_name, proxy_name, privilege / read more

Plugins installed on oms server - oracle database script

Plugins installed on OMS server - Oracle Database Script.-- Run from OMS server -- List of plugins installed on OMS server. ./emcli list_plugins_on_server -- List of plugins installed on the target agents. ./emcli list_plugins_on_agent -- List plugins deployed on particular agent ./emcli list_plugins_on_agent -agent_names="172.15.36.93" read more

Occupants usage in sysaux tablespace - oracle database script

Occupants usage in sysaux tablespace - Oracle Database Script.SELECT occupant_name, occupant_desc, space_usage_kbytesFROM v$sysaux_occupants; read more

Object with mix or lowercase name - oracle database script

object with mix or lowercase name - Oracle Database Script.SET lines 132 pages 1000 col object_name format a30 heading "Object Name";col object_type format a10 heading "Object|Type";col created format a30 heading "Created";col status format a30 heading "Status";SELECT OWNER, object_name, object_type, created, statusFROM dba_objectsWHERE (object_name = lower(object_name) OR object_name = initcap(lower(object_name))) AND object_name != upper(object_name); read more

Move lob segment to another tablespace - oracle database script

Move LOB segment to another tablespace - Oracle Database Script.-- Find the lob segment details select table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where OWNER='AODBA' -- Move to new tablespace alter table AODBA.LOB_SEG1 move lob (PAYLOAD) store as SYS_LOB0000100201C00011$$ ( tablespace USERS); read more

Move aud$ table to new tablespace - oracle database script

Move aud$ table to new tablespace - Oracle Database Script.-- Moving aud$ table to new tablespace AUDIT_DATA BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_DATA'); END; / -- Query to view new tablespace select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; read more

Monitor standby background process - oracle database script

Monitor standby background process - Oracle Database Script.SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKSFROM V$MANAGED_STANDBY ; read more

Monitor scheduler jobs - oracle database script

Monitor scheduler jobs - Oracle Database Script.-- Monitor currently running jobs SELECT job_name, session_id, running_instance, elapsed_time, FROM dba_scheduler_running_jobs; -- View the job run details select * from DBA_SCHEDULER_JOB_RUN_DETAILS; -- View the job related logs: select * from DBA_SCHEDULER_JOB_LOG; read more

Monitor recovery progress in standby db - oracle database script

Monitor recovery progress in standby db - Oracle Database Script.SELECT to_char(START_TIME, 'DD-MON-YYYY HH24:MI:SS') "Recovery Start Time", to_char(item)||' = '||to_char(sofar)||' '||to_char(units) "Progress"FROM v$recovery_progressWHERE start_time= (SELECT max(start_time) FROM v$recovery_progress); read more

Monitor lag in standby including rac - oracle database script

Monitor lag in standby including RAC - Oracle Database Script.-- Applicable for 2 NODE RAC ALSO column applied_time for a30 set linesize 140 select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual; SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP , (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or ((LOG_ARCHIVED-LOG_APPLIED) > 5)) then 'Error! Log Gap is ' else 'OK!' end) Status FROM ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE where INST_ID = 1 ), ( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1 ), ( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1 ), ( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1 ) UNION SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP, (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or ((LOG_ARCHIVED-LOG_APPLIED) > 5)) then 'Error! Log Gap is ' else 'OK!' end) Status from ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE where INST_ID = 2 ), ( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2 ), ( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2 ), ( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2 ) / read more

Modify scan listener port - oracle database script

Modify scan listener port - Oracle Database Script.-- Modify the scan listener to use new port 1523: srvctl modify scan_listener -p 1523 -- Restart scan_listenr $GRID_HOME/bin/srvctl stop scan_listener $GRID_HOME/bin/srvctl start scan_listener -- update remote_listener in database Alter system set remote_listener='orcl-scan.stc.com.sa:1523' scope=both sid='*'; read more

Managing columns of table - oracle database script

managing columns of table - Oracle Database Script.-- Add column alter table scott.emp add( empname varchar2(20)); alter table scott.emp add( empid number,deptid number); -- Drop column alter table scott.emp drop (empname); alter table scott.emp drop (empid,deptid); -- Rename column alter table scott.emp rename column empname to asocname; -- set column unused alter table scott.emp set unused (empname); -- Drop unused columns from a table alter table scott.emp drop unused columns. read more

Manage listener in oracle

Manage listener in oracle -- stop/start listener lsnrctl stop LISTENER_AODBA lsnrctl start LISTENER_AODBA -- Reload listener lsnrctl reload LISTENER_AODBA -- Check status of listener lsnrctl status LISTENER_AODBA --- view listener version lsnrctl version LISTENER_AODBA -- View listener services lsnrctl services LISTENER_AODBA -- View listener service acl summary: lsnrctl servacls LISTENER_AODBA read more

Manage dbms_schedulerjobs - oracle database script

Manage dbms_schedulerjobs - Oracle Database Script.--- Enable a job EXECUTE DBMS_SCHEDULER.ENABLE('SCOTT.MONTHLYBILLING'); --- Disable a job EXECUTE DBMS_SCHEDULER.DISABLE('SCOTT.MONTHLYBILLING'); -- Stop a running job EXECUTE DBMS_SCHEDULER.STOP_JOB('SCOTT.MONTHLYBILLING'); --- Drop a running job EXECUTE DBMS_SCHEDULER.DROP_JOB('SCOTT.MONTHLYBILLING'); -- Run a job immediately EXECUTE DBMS_SCHEDULER.RUN_JOB('SCOTT.MONTHLYBILLING'); read more

Manage acls in oracle

Manage ACLS in oracle -- Create ACLS exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('scott_utl_mail.xml','Allow mail to be send','SCOTT', TRUE, 'connect'); -- Assign ACL to nework exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('scott_utl_mail.xml','*',25); -- grant privilege to user: exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml','SCOTT', TRUE, 'connect'); exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml' ,'SCOTT', TRUE, 'resolve'); --Unassign network from ACL: exec DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL('scott_utl_mail.xml','*',25); -- remove privilege from an user: exec DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('scott_utl_mail.xml','SCOTT', TRUE, 'connect'); -- Drop ACL: exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL ('scott_utl_mail.xml' ); read more

Make index invisible - oracle database script

Make index invisible - Oracle Database Script.SQL>SELECT VISIBILITYFROM dba_indexesWHERE index_name='IDX_SESS' AND OWNER='AODBA';VISIBILIT --------- VISIBLE SQL> ALTER INDEX AODBA.IDX_SESS INVISIBLE; Index altered. SQL> select VISIBILITY from dba_indexes where index_name='IDX_SESS' and owner='AODBA'; VISIBILIT --------- INVISIBLE -- Revert again to visible. SQL> ALTER INDEX AODBA.IDX_SESS VISIBLE; Index altered. read more

Log information for all scheduler jobs - oracle database script

log information for all Scheduler jobs - Oracle Database Script.SET pagesize 299SET lines 299 col job_nameFOR a24 col log_dateFOR a40 col OPERATIONFOR a19 col additional_info a79SELECT job_name, log_date, status, OPERATION, ADDITIONAL_INFOFROM dba_scheduler_job_logORDER BY log_date DESC; read more

Last log applied/received - oracle database script

Last log applied/Received - Oracle Database Script.SELECT 'Last Log applied : ' Logs, to_char(next_time, 'DD-MON-YY:HH24:MI:SS') TIMEFROM v$archived_logWHERE sequence# = (SELECT max(sequence#) FROM v$archived_log WHERE applied='YES')UNIONSELECT 'Last Log received : ' Logs, to_char(next_time, 'DD-MON-YY:HH24:MI:SS') TIMEFROM v$archived_logWHERE sequence# = (SELECT max(sequence#) FROM v$archived_log); read more

Is the database is a multitenant or not - oracle database script

Is the Database is a Multitenant or not - Oracle Database Script.-- If the output is YES mean it is a multitenant database, else normal db SELECT CDB FROM V$DATABASE; CDB --- YES read more

Index rebuild in oracle

Index rebuild in oracle -- Index rebuild online alter index TEST_INDX rebuild online ; --- Fast Index rebuild alter index TEST_INDX rebuild online parallel 8 nologging; alter index TEST_INDX noparallel; alter index TEST_INDX logging; read more

History of all scheduler job runs - oracle database script

history of all scheduler job runs - Oracle Database Script.SET pagesize 299SET lines 299 col JOB_NAMEFOR a24 col actual_start_dateFOR a56 col RUN_DURATIONFOR a34SELECT job_name, status, actual_start_date, run_durationFROM DBA_SCHEDULER_JOB_RUN_DETAILSORDER BY ACTUAL_START_DATE DESC; read more

Grant table/column privilege to user - oracle database script

grant table/column privilege to user - Oracle Database Script.-- Table privileges GRANT READ ANY TABLE TO SCOTT; GRANT SELECT ANY TABLE TO SCOTT; GRANT INSERT, UPDATE, DELETE ON TESTUSER1.EMPTABL on SCOTT; GRANT ALL ON TESTUSER1.EMPTABL on SCOTT; -- Grant privilege on few columns of a table --Only INSERT,UPDATE can be granted at COLUMN level. GRANT insert (emp_id) ON TESTUSER1.EMPTABL TO SCOTT; GRANT UPDATE(emp_id) ON TESTUSER1.EMPTABL TO SCOTT; read more

Get standby redo log info - oracle database script

Get standby redo log info - Oracle Database Script.SET lines 100 pages 999 col member format a70SELECT st.group# , st.sequence# , ceil(st.bytes / 1048576) mb , lf.member from v$standby_log st , v$logfile lf where st.group# = lf.group# / read more

Get oms/agent url details - oracle database script

Get oms/agent url details - Oracle Database Script.-- OMS URL Details cd $OMS_HOME/bin ./emctl status oms -details -- agnet url details cd $AGENT_HOME/bin ./emctl status agent -details read more

Get oms repository details - oracle database script

Get oms repository details - Oracle Database Script.--- Oem repository is a target db ,which contains all target details cd $OMS_HOME/bin ./emctl config oms -list_repos_details read more

Get ddl of a scheduler job - oracle database script

Get DDL of a scheduler job - Oracle Database Script.SELECT dbms_metadata.get_ddl('PROCOBJ', '<JOB_NAME>', '<JOB_OWNER>')FROM dual;SELECT dbms_metadata.get_ddl('PROCOBJ', 'DUP_ACC', 'SCOTT')FROM dual; read more

Find tables with lob seg in db - oracle database script

Find tables with LOB seg in DB - Oracle Database Script.SET pagesize 200SET lines 200SET long 999 col OWNERFOR a15 col TABLE_NAMEFOR a20 col COLUMN_NAMEFOR a21SELECT a.owner, a.table_name, a.column_name, data_typeFROM dba_lobs a, dba_tab_columns bWHERE a.column_name=b.column_name AND a.table_name = b.table_name AND a.owner = b.owner AND b.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS'); read more

Find nested tables in db - oracle database script

Find nested tables in db - Oracle Database Script.--- Script to find nested tables of a schema: set pagesize 200 set lines 200 set long 999 col owner for a18 col table_name for a20 col table_type_owner for a20 col table_type_name for a20 col parent_table_name for a20 col parent_table_column for a20 SELECT owner, table_name, table_type_owner, table_type_name, parent_table_name, parent_table_column, LTRIM (storage_spec) storage_spec, LTRIM (return_type) return_type FROM dba_nested_tables WHERE owner='&SCHEMA_NAME' And upper(table_name) like '&&TABLE_NAME' ORDER BY owner; read more

Find dependents of an object - oracle database script

Find dependents of an object - Oracle Database Script.SELECT *FROM dba_dependenciesWHERE OWNER='&SCHEMA_NAME' AND name='&OBJECT_NAME';SELECT *FROM dba_dependenciesWHERE referenced_owner = 'USER_NAME' AND referenced_name = 'OBJECT_NAME'; read more

Find column usage statistics - oracle database script

Find column usage statistics - Oracle Database Script.SET lines 150SET pages 500 col TABLE_NAMEFOR a20 col COLUMN_NAMEFOR a20SELECT a.object_name TABLE_NAME, c.column_name, equality_preds, equijoin_preds, range_preds, like_predsFROM dba_objects a, col_usage$ b, dba_tab_columns cWHERE a.object_id=b.OBJ# and c.COLUMN_ID=b.INTCOL# and a.object_name=c.table_name and b.obj#=a.object_id and a.object_name='&table_name' and a.object_type='TABLE' and a.owner='&owner' order by 3 desc,4 desc, 5 desc; read more

Find chained rows in table - oracle database script

Find chained rows in table - Oracle Database Script.-- First, analyze the table as below: ANALYZE TABLE SCOTT.EMPTABLE LIST CHAINED ROWS; -- Then check the row_count in chained_row table select count(*) from chained_rows where table_name='EMPTABLE'; The output of this query returns the number of chained rows in that table. read more

Find active services in db - oracle database script

Find active services in db - Oracle Database Script.--- It will show all the registered services for the database. col NETWORK_NAME for a25 set pagesize 299 set lines 299 select NAME,INST_ID,NETWORK_NAME,CREATION_DATE,GOAL,GLOBAL from GV$ACTIVE_SERVICES where name not like 'SYS$%'; read more

Expdp/impdp with parallel option - oracle database script

expdp/impdp with parallel option - Oracle Database Script.-- Create the directory if not present create directory EXPDIR as '/export/home/oracle/ORADUMP' -- Par file for export with parallel degree 4 cat parfile=parallel.par dumpfile=parallel_%U.dmp logfile=tables.log directory=EXPDIR schemas=PROD_DATA parallel=4 NOTE - mention parallel value as per cpu core. -- Run expdp command expdp parfile=parallel.par Same is the command for IMPDP. read more

Expdp/impdp for tables - oracle database script

expdp/impdp for TABLES - Oracle Database Script.-- Create the directory if not present create directory EXPDIR as '/export/home/oracle/ORADUMP' --- Par file for export of multiple tables cat parfile=tables.par dumpfile=tables.dmp logfile=tables.log directory=EXPDIR tables=PROD_DATA.EMPLOYEE, PROD_DATA.DEPT, DEV_DATA.STAGING -- Run expdp command expdp parfile=tables.par read more

Expdp/impdp for schemas - oracle database script

expdp/impdp for schemas - Oracle Database Script.-- Create the directory if not present create directory EXPDIR as '/export/home/oracle/ORADUMP' -- Par file for export of SCHEMAS(PROD_DATA,DEV_DATA) --cat parfile=schema.par dumpfile=schema.dmp logfile=tables.log directory=EXPDIR schemas=PROD_DATA, DEV_DATA -- Run expdp expdp parfile=schema.par For impdp also use the similar command. read more

Expdp with query clause - oracle database script

expdp with query clause - Oracle Database Script.--- For exporting table data with query condition ----select * from AODBA.EMP_TAB WHERE created > sysdate -40; -- Parfile cat expdp_query.par dumpfile=test.dmp logfile=test1.log directory=TEST tables=aodba.EMP_TAB QUERY=aodba.EMP_TAB:"WHERE created > sysdate -40" read more

Expdp with compression parameter - oracle database script

expdp with compression parameter - Oracle Database Script.-- Create the directory if not present create directory EXPDIR as '/export/home/oracle/ORADUMP' -- Below is the parfile for full db export cat parfile=compressed.par dumpfile=schema.dmp logfile=tables.log directory=EXPDIR FULL=Y compression=ALL -- Run expdp command expdp parfile=compressed.par read more

Expdp to multiple directories - oracle database script

expdp to multiple directories - Oracle Database Script.Suppose you wish TO take a expdp BACKUP OF a big TABLE, but you don’t sufficient SPACE IN a single MOUNT POINT TO keep the dump. IN this CASE, we take expdp DUMP TO multiple directory.CREATE directories TO pointing TO diff PATH SQL>CREATE directory DIR1 AS '/home/oracle/DIR1';Directory created. SQL>CREATE directory DIR2 AS '/home/oracle/DIR2';Directory created. parfile content dumpfile=DIR1:test_%U.dmp, DIR2:test_%U.dmp LOGFILE=test.log directory=DIR1 PARALLEL=2 TABLES=raj.test read more

Expdp to asm diskgroup - oracle database script

expdp to asm diskgroup - Oracle Database Script.CREATE a directory pointing TO asm diskgroup( FOR dumpfiles) SQL>CREATE directory SOURCE_DUMP AS '+NEWTST/TESTDB2/TEMPFILE';Directory createdCREATE a directory pointing TO aNORMAL filesystem (required FOR logfiles) SQL>CREATE directory EXPLOG AS '/export/home/oracle';Directory created. export parfile dumpfile=test.dmp LOGFILE=EXPLOG:test.log directory=SOURCE_DUMP TABLES=dbatest.EMPTAB exclude=STATISTICS read more

Exclude/include option in expdp - oracle database script

EXCLUDE/INCLUDE option in expdp - Oracle Database Script.EXCLUDE/INCLUDE OPTION: These two OPTIONS can be used IN BOTH expdpOR impdp TO excludeOR include, particular objectsOR object_types: Export a schemas AODBA, EXCLUDING TABLE EMP_TABAND DEPT dumpfile=test.dmp LOGFILE=test1.log directory=TEST exclude=TABLE:"IN ('EMP_TAB','DEPT')" schemas=AODBA Exclude few schemas WHILE import: dumpfile=test.dmp LOGFILE=test1.log directory=TEST EXCLUDE=SCHEMA:"IN ('WMSYS', 'OUTLN')" export/Import ONLY TABLEAND INDEX (OBJECT_TYPE) dumpfile=FULL.dmp LOGFILE=full.log directory=exp_dir directory=DBATEST INCLUDE=TABLE, INDEX read more

Estimate space require for index creation - oracle database script

Estimate space require for index creation - Oracle Database Script.--Below script is to get the required space for index creation, before actually it is being created. --- Lets check for create index AODBA.INDEX1 on AODBA.EMP(EMPNO) SET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); BEGIN DBMS_SPACE.CREATE_INDEX_COST ( 'create index AODBA.INDEX1 on AODBA.EMP(EMPNO)', v_used_Bytes, v_Allocated_Bytes ); DBMS_OUTPUT.PUT_LINE('Used Bytes MB: ' || round(v_used_Bytes/1024/1024)); DBMS_OUTPUT.PUT_LINE('Allocated Bytes MB: ' || round(v_Allocated_Bytes/1024/1024)); END; / read more

Enable/disable/drop a dbms_job - oracle database script

Enable/disable/drop a dbms_job - Oracle Database Script.-- Get the job number from dba_jobs. select job "jobno",schema_user,what from dba_jobs; -- Disable a job EXEC DBMS_IJOB.BROKEN(jobno,TRUE); -- Enable a job EXEC DBMS_IJOB.BROKEN(jobno,FALSE); --REMOVE A DBMS_JOBS: EXEC DBMS_IJOB.remove(jobno) ; read more

Enable/disable triggers of a schema - oracle database script

Enable/disable triggers of a schema - Oracle Database Script.-FOR disabling TRIGGERS OF a SCHEMASELECT 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' DISABLE '||';'FROM dba_triggersWHERE OWNER='&SCHEMA_NAME';-FOR disableing TRIGGERSFOR a TABLESELECT 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' DISABLE '||';'FROM dba_triggersWHERE TABLE_NAME =('&TABLE_NAME') AND OWNER='&SCHEMA_NAME'; -- Similarly for enabling select 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' ENABLE '||';' from dba_triggers where owner='&SCHEMA_NAME'; select 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' ENABLE '||';' from dba_triggers where table_name =('&TABLE_NAME') and owner='&SCHEMA_NAME'; read more

Enable/disable em express 12c - oracle database script

Enable/disable em express 12c - Oracle Database Script.-- Check whether em is enabled or not.(if output 0 means, emexpress disabled) select dbms_xdb.getHttpPort() from dual; select dbms_xdb_config.getHttpsPort() from dual; -- Enable emexpress with https: SQL> exec dbms_xdb_config.sethttpsport(5500); -- Enable emexpress with http: SQL> exec dbms_xdb_config.sethttpport(8080); -- Disable em express (set port to 0) SQL> exec dbms_xdb_config.sethttpsport(0); SQL> exec dbms_xdb_config.sethttpport(0); read more

Enable tracing for a listener - oracle database script

Enable tracing for a listener - Oracle Database Script.-SET TO the listener you want TO trace LSNRCTL>SET cur LISTENER_TEST -- Enable Trace: LSNRCTL> set trc_level ADMIN Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST))) LISTENER_TEST parameter "trc_level" set to admin The command completed successfully read more

Enable pure unified auditing 12c - oracle database script

Enable pure unified auditing 12c - Oracle Database Script.-- False means mixed auditing; SELECT value FROM v$option WHERE parameter = 'Unified Auditing'; VALUE ----------------- FALSE -- relink the library as mentioned. shutdown immediate; cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk unaiaud_on ioracle startup SELECT value FROM v$option WHERE parameter = 'Unified Auditing'; VALUE ----------------- TRUE read more

Enable auditing in database - oracle database script

Enable auditing in database - Oracle Database Script.-- Auditing is disabled, when audit_trail is set to NONE, SQL> show parameter audit_trail NAME               TYPE               VALUE ------------------------------------ ----------- -------------------------- audit_trail       string                NONE - Either set audit_trail to DB or DB,EXTENDED. alter system set audit_trail='DB' scope=spfile; (or) alter system set audit_trail='DB, EXTENDED' scope=spfile; -- Restart the database. shutdown immediate; startup; SQL> show parameter audit_trail NAME                 TYPE                   VALUE ------------------------------------ ----------- -------------------------- audit_trail           string               DB read more

Enable auditing for datapump jobs - oracle database script

Enable auditing for datapump jobs - Oracle Database Script.-- Create policy create audit policy expdp_aduit actions component=datapump export; -- Enable policy audit policy expdp_aduit; -- View audit report: select DBUSERNAME,DP_TEXT_PARAMETERS1 from UNIFIED_AUDIT_TRAIL where DP_TEXT_PARAMETERS1 is not null; read more

Enable audit for sys operations - oracle database script

Enable audit for sys operations - Oracle Database Script.SQL>ALTER SYSTEMSET audit_sys_operations=TRUE SCOPE=spfile;SQL> SHUTDOWN IMMEDIATE SQL> STARTUP SQL> SHOW PARAMETER audit_sys_operations NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_sys_operations boolean TRUE read more

Drop tablespace in oracle db

Drop tablespace in oracle db -- Drop a tablespace without removing the physical database files. SQL❯DROP TABLESPACE TESTING;TABLESPACE dropped. SQL❯SELECT file_nameFROM dba_data_filesWHERE tablespace_name='TESTING';NO ROWS selected -- Drop tablespace including the physical datafiles. SQL❯DROP TABLESPACE TESTING INCLUDING CONTENTSAND datafiles;TABLESPACE dropped. read more

Drop a schedule - oracle database script

Drop a schedule - Oracle Database Script.BEGIN DBMS_SCHEDULER.DROP_SCHEDULE(schedule_name => 'DAILYBILLINGJOB_SCHED', FORCE => TRUE);END; read more

Drop a pluggable database - oracle database script

Drop a pluggable database - Oracle Database Script.-- Need to run from root container; SQL> show con_name CON_NAME ------------------------ CDB$ROOT ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE; DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILE; read more

Definition of job in dbms_jobs - oracle database script

Definition of job in dbms_jobs - Oracle Database Script.-- First get the job_id and owner; select job,log_user,schema_user from dba_jobs; 743,DBATEST --- connect to the owner , and get the definition of the job alter session set current_schema=DBATEST; set serveroutput on SQL> DECLARE callstr VARCHAR2(500); BEGIN dbms_job.user_export(743, callstr); dbms_output.put_line(callstr); END; / read more

Default users in oracle 12c

Default users in oracle 12c -- List default users ( valid from 12c onwards) select username from dba_users where ORACLE_MAINTAINED='Y'; read more

Currently connected pdb name - oracle database script

currently connected PDB name - Oracle Database Script.SQL> SHOW con_name CON_NAME ------------------------------ PDB1 SQL> select sys_context('USERENV','CON_NAME') FROM DUAL; SYS_CONTEXT('USERENV','CON_NAME') ----------------------------------- PDB1 read more

Create/drop synonyms - oracle database script

Create/drop synonyms - Oracle Database Script.-- Create public synonym CREATE PUBLIC SYNONYM emp_view FOR scott.emp; -- Create private synonym CREATE SYNONYM priv_view FOR scott.emp; -- Drop synonym DROP PUBLIC SYNONYM emp_view; DROP SYNONYM priv_view; -- View synonym related info SELECT * FROM DBA_SYNONYMS; read more

Create/drop database link - oracle database script

Create/drop database link - Oracle Database Script.-- Create public database link Create public database link LINK_PUB connect to system identified by oracle using 'PRODB'; where PRODB - > tnsname of the target db added in tnsnames.ora -- Create private database link under Scott connect scott/tiger create database link LINK_PRIV connect to system identified by oracle using 'PRODB'; -- Drop public database link drop public database link TEST_LINK ; -- Drop private database link connect scott/tiger drop database link LINK_PRIV; NOTE - Private database link can be dropped only by the owner of the database link read more

Create/drop database link - oracle database script

Create/drop database link - Oracle Database Script.-- Create public database link Create public database link LINK_PUB connect to system identified by oracle using 'PRODB'; where PRODB - > tnsname of the target db added in tnsnames.ora -- Create private database link under Scott connect scott/tiger create database link LINK_PRIV connect to system identified by oracle using 'PRODB'; -- Drop public database link drop public database link TEST_LINK ; -- Drop private database link connect scott/tiger drop database link LINK_PRIV; NOTE - Private database link can be dropped only by the owner of the database link read more

Create user in oracle

Create user in oracle SYNTAX :CREATE USER <USER_NAME> IDENTIFIED BY <PASSWORD> DEFAULT TABLESPACE <TABLESPACE_NAME>TEMPORARY TABLESPACE <TEMP_TABLESPACE>;Eg:CREATE USER SCOTT IDENTIFIED BY oracle#41234 DEFAULT TABLESPACE usersTEMPORARY TABLESPACE TEMP;-TOCREATE an USER, which will promptFOR NEW password upon login:CREATE USER SCOTT IDENTIFIED BY oracle#41234 DEFAULT TABLESPACE usersTEMPORARY TABLESPACE TEMP password expire; read more

Create unified audit policy - oracle database script

Create unified audit policy - Oracle Database Script.-- Create audit policy with audit options: create audit policy test_case2 ACTIONS CREATE TABLE, INSERT ON bsstdba.EMP_TAB, TRUNCATE TABLE, select on bsstdba.PROD_TAB; select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME='TEST_CASE2'; -- Enable policy: audit policy TEST_CASE2; select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name='TEST_CASE2'; read more

Create tablespace in oracle db

Create tablespace in oracle db -- Create New tablespace Create tablespace DATA datafile '/u01/aodba/oradata/data01.dbf' size 5G autoextend on next 500M; -- Create tablespace on ASM diskgroup Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M; -- Create big tablespace: CREATE BIGFILE TABLESPACE BIGTS datafile '/u01/aodba/oradata/bigts01.dbf' size 100G autoextend on NEXT 1G; read more

Create static listener for oracle db

Create static listener for oracle db LISTENER_AODBA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.20.211.236)(PORT = 1527))) SID_LIST_LISTENER_AODBA = (SID_LIST = (SID_DESC = (ORACLE_HOME = /oracle/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = AODBA))) lsnrctlSTART LISTENER_AODBA read more

Create db link w/o modifying tnsnames.ora - oracle database script

create db link w/o modifying tnsnames.ora - Oracle Database Script.CREATE PUBLIC DATABASE LINK IMFP CONNECT TO iwf IDENTIFIED BY thr3iwf USING '(DESCRIPTION=(ADDRESS_LIST=( ADDRESS=(PROTOCOL=TCP)(HOST=testoracle.com)(PORT=1522))) (CONNECT_DATA=(SERVICE_NAME=IMFP)))'; read more

Create and scheduler a scheduler job - oracle database script

Create and scheduler a scheduler job - Oracle Database Script.-- TO schedule a job, first create a schedule, then a program and then a job --Create a schedule BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE ( Schedule_name => 'DAILYBILLINGJOB', Start_date => SYSTIMESTAMP, Repeat_interval =>'FREQ=DAILY;BYHOUR=11; BYMINUTE=30', Comments => 'DAILY BILLING JOB' ); END; -- Create a program BEGIN DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'DAILYBILLINGJOB', program_type => 'STORED_PROCEDURE', program_action => 'DAILYJOB.BILLINGPROC' number_of_arguments =>0, enabled => TRUE, comments => 'DAILY BILLING JOB' ); END; -- Now create the job: DBMS_SCHEDULER.CREATE_JOB ( job_name => 'DAILYBILLINGJOB_RUN', program_name => 'DAILYBILLINGJOB', schedule_name => 'DAILYBILLINGJOB_SCHED', enabled => FLASE, comments => 'daily billing job' ); END; -- ENABLE THE JOB DBMS_SCHEDULER.ENABLE('DAILYBILLINGJOB_RUN'); read more

Create /alter profile in database - oracle database script

Create /alter profile in database - Oracle Database Script.-- CREATE PROFILE CREATE PROFILE "APP_PROFILE" LIMIT COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME 90 CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME 180 PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED; -- ALTER PROFILE: ALTER PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; *SESSION_PER_USER – No. of allowed concurrent sessions for a user *CPU_PER_SESSION – CPU time limit for a session, expressed in hundredth of seconds. *CPU_PER_CALL – Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds. *CONNECT_TIME – Specify the total elapsed time limit for a session, expressed in minutes. *IDLE_TIME – Specify the permitted periods of continuous inactive time during a session, expressed in minutes. *LOGICAL_READS_PER_SESSION – Specify the permitted number of data blocks read in a session, including blocks read from memory and disk *LOGICAL_READS_PER_CALL –permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch). *PRIVATE_SGA – SGA a session can allocate in the shared pool of the system global area (SGA), expressed in bytes. *FAILED_LOGIN_ATTEMPTS – No. of failed attempts to log in to the user account before the account is locked *PASSWORD_LIFE_TIME : No. of days the account will be open. after that it will expiry. *PASSWORD_REUSE_TIME : number of days before which a password cannot be reused *PASSWORD_REUSE_MAX : number of days before which a password can be reused *PASSWORD_LOCK_TIME :Number of days the user account remains locked after failed login *PASSWORD_GRACE_TIME :Number of grace days for user to change password *PASSWORD_VERIFY_FUNCTION :PL/SQL that can be used for password verification read more

Copy scheduler job from one user to other - oracle database script

Copy scheduler job from one user to other - Oracle Database Script.EXEC dbms_scheduler.copy_job(',''); exec dbms_scheduler.copy_job('SCOTT.MY_JOB_2','AODBA.MY_JOB_2'); read more

Connect to user without knowing password - oracle database script

Connect to user without knowing password - Oracle Database Script.--- You can connect to another user without knowing the password, with grant connect through privilege --- Suppose a user TEST1 wants to connect to TEST2 user and create a table and we don’t know the password of TEST2. Conn / as sysdba SQL >alter user TEST2 grant connect through TEST1; User altered. SQL >conn TEST1[TEST2] Enter password:< Give password for TEST1> SQL >show user USER is "TEST2" SQL >create table emp_test as select * from emp; Table created. SQL > conn / as sysdba connected SQL > select owner from dba_tables where table_name='EMP_TEST'; OWNER ------ TEST2 read more

Compile invalid objects - oracle database script

Compile invalid objects - Oracle Database Script.-- To compile all objects in database @$ORACLE_HOME/rdbms/admin/utlrp.sql -- Compile objects of a particular schema: EXEC DBMS_UTILITY.compile_schema(schema => 'APPS'); -- Compiling a package; ALTER PACKAGE APPS.DAIL_REPORT COMPILE; ALTER PACKAGE APPS.DAIL_REPORT COMPILE BODY; -- Compiling a procedure: ALTER PROCEDURE APPS.REPORT_PROC COMPILE; -- Compiling a view: ALTER VIEW APPS.USERSTATUS_VW COMPILE; -- Compiling a function: ALTER FUNCTION APPS.SYNC_FUN COMPILE; read more

Common user/role in cdb - oracle database script

Common user/role in CDB - Oracle Database Script.---A user that is present in both root container and PDB is known as common user. User need to be created after connecting to CDB root. create user c##aodba identified by aodba container=all; -- Similar to user, common role we can create in CDB root. Create role C##DBAROLE; read more

Cluster parameter in rac - oracle database script

CLUSTER PARAMETER IN RAC - Oracle Database ScriptIN a RAC DATABASE, IF you ARE taking export WITH PARALLEL OPTIONAND the datapump directory IS NOT SHARED BETWEEN the nodes, THENSET CLUSTER=N IN expdp/impdp parfile content: dumpfile=asset_%U.dmp LOGFILE=asset.log directory=VEN PARALLEL=32 CLUSTER=N read more

Checkpoint time of datafiles - oracle database script

Checkpoint time of datafiles - Oracle Database Script.-- REFERENCE - ORAFAQ set feed off set pagesize 10000 set linesize 500 break on grantee skip 1 column datum new_value datum noprint column file_nr format 999999 heading 'File#' column checkpoint_time format A20 heading 'Checkpoint|Time' column file_name format A59 heading 'Filename' select FILE# file_nr, to_char(CHECKPOINT_TIME,'DD.MM.YYYY:HH24:MI:SS') checkpoint_time, name file_name from v$datafile_header; read more

Check undo mode in multitenant db (oracle 12.2)

Check undo mode in Multitenant db (oracle 12.2) -- Local undo mode means that each container has its own undo tablespace for every instance in which it is open. -- Shared undo mode means that there is one active undo tablespace for a single-instance CDB select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; read more

Check the syntax of rman commands - oracle database script

check the syntax of RMAN commands - Oracle Database Script.--- check the syntax of RMAN commands interactively without actually executing the commands $ rman checksyntax Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 29 12:04:24 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. -- Now put the command for checking syntax RMAN> backup database; The command has no syntax errors read more

Check encryption wallet status - oracle database script

Check encryption wallet status - Oracle Database Script.-- Encryption wallet path and status: SELECT * FROM gv$encryption_wallet; read more

Check db role(primary/standby) - oracle database script

Check DB role(PRIMARY/STANDBY) - Oracle Database Script.SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUSFROM V$DATABASE; read more

Change sysman pwd in oem cloud - oracle database script

change sysman pwd in oem cloud - Oracle Database Script.-- Syntax to update sysman password in oms repository ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd -new_pwd < new sysman password> -- Example (need only existing sys password) ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd oracle1234 -new_pwd oracle1234 -- Restart oms ./emctl stop oms ./emctl start oms read more

Change default tablespace of user - oracle database script

Change default tablespace of user - Oracle Database Script.-- Get default tablespace of a user: set lines 200 col username for a23 select username,DEFAULT_TABLESPACE from dba_users where username='SCOTT'; USERNAME               DEFAULT_TABLESPACE ----------------------- ------------------------------ SCOTT                          USERS -- Change default tablespace of a user: ALTER USER SCOTT DEFAULT TABLESPACE DATATS; select username,DEFAULT_TABLESPACE from dba_users where username='SCOTT';   USERNAME               DEFAULT_TABLESPACE ----------------------- ------------------------------ SCOTT                           DATATS read more

Audit records of an user - oracle database script

audit records of an user: - Oracle Database Script.col user_nameFOR a12 heading "User name" col timest format a13 col userid format a8 trunc col obn format a10 trunc col name format a13 trunc col object_name format a10 col object_type format a6 col priv_used format a15 truncSET verify OFFSET pages 1000SET PAGESIZE 200SET LINES 299SELECT username userid, to_char(TIMESTAMP, 'dd-mon hh24:mi') timest, action_name acname, priv_used, obj_name obn, ses_actionsFROM sys.dba_audit_trailWHERE TIMESTAMP>sysdate-&HOURS*(1/24) AND username='&USER_NAME'ORDER BY TIMESTAMP / read more

Alter an user - oracle database script

Alter an user - Oracle Database Script.-- Change password of an user ALTER USER SCOTT identified by NEW_PWD; -- Change user profile; ALTER USER SCOTT PROFILE SIEBEL_PROFILE; -- Unlock/lock a user ALTER USER SCOTT account unlock; ALTER USER SCOTT account lock; -- Make sure account expiry, so upon login, it will ask for new one ALTER USER SCOTT password expire; read more

All scheduler windows - oracle database script

All scheduler windows - Oracle Database Script.--ALL SCHEDULER WINDOWS --Reference : Gwen Shapira set pagesize 300 linesize 200 select * from dba_scheduler_windows; read more

Add/drop/alter datafile - oracle database script

Add/Drop/Alter datafile - Oracle Database Script.-- Add a datafile to a tablespace Alter tablespace USERS add datafile '/u01/data/users02.dbf' size 5G; -- Enable autoextend on for a datafile; Alter database datafile '/u01/data/users02.dbf' autoextend on; -- Resize a datafile alter database datafile '/u01/data/users02.dbf' resize 10G; -- Make a datafile offline/online Alter database datafile '/u01/data/users02.dbf' offline; Alter database datafile '/u01/data/users02.dbf' online; -- Drop a datafile: Alter tablespace USERS drop datafile '/u01/data/users02.dbf'; read more

Add/drop tempfile - oracle database script

Add/drop Tempfile - Oracle Database Script.-- Add tempfile to temp tablespace: alter tablespace TEMP1 add tempfile '/u01/aodba/tempfile/temp02.dbf' size 1G autoextend on next 200M; -- Resize temp file: alter database tempfile '/u01/aodba/tempfile/temp02.dbf' resize 2G; -- Drop tempfile: ALTER DATABASE TEMPFILE '/u01/aodba/tempfile/temp02.dbf' DROP INCLUDING DATAFILES; read more

Xplain plan of sql_id from cursor - oracle database script

xplain plan of sql_id from cursor - Oracle Database Script.--- First get the child number of the sql_id .One sql_id can have multiple child number( one for each plan_hash_value)   SQL> select sql_id,child_number,plan_hash_value from gv$sql where sql_id='9n2a2c8pvu6bm';SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ ---------------9n2a2c8pvu6bm 1 13761463 --- Now get the explain plan for cursor:SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sqlid',&child_number)); read more

Xplain plan of sql_id from awr - oracle database script

xplain plan of sql_id from AWR - Oracle Database Script.SET lines 200SELECT *FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id')); read more

Xplain plan of a sql statement - oracle database script

xplain plan of a sql statement - Oracle Database Script.-- Generate explain plan -- Syntax EXPLAIN PLAN FOR < SQL STATEMENT> ; explain plan for select count(*) from aodba; -- View explain plan  select * from table(dbms_xplan.display); read more

Xplain plan of a sql baseline - oracle database script

xplain plan of a sql baseline - Oracle Database Script.--- SYNTAX --  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'<SQL BASELINE NAME>')); SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_gbhrw1v44209a5b2f7514')); read more

View/modify stats retention - oracle database script

View/modify stats retention - Oracle Database Script.-- View current stats retention select dbms_stats.get_stats_history_retention from dual; -- Modify the stats retention exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); read more

View/modify awr retention - oracle database script

View/modify AWR retention - Oracle Database Script.-- View current AWR retention period select retention from dba_hist_wr_control; -- Modify retention period to 7 days and interval to 30 min select dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080); NOTE - 7 DAYS = 7*24*3600= 10080 minutes read more

View timezone info in db - oracle database script

View timezone info in db - Oracle Database Script.SELECT VERSIONFROM v$timezone_file;SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE 'DST_%'ORDER BY PROPERTY_NAME; read more

View hidden parameter setting - oracle database script

View hidden parameter setting - Oracle Database Script.SET lines 2000 col NAMEFOR a45 col DESCRIPTIONFOR a100SELECT name, descriptionFROM SYS.V$PARAMETERWHERE name LIKE '\_%' ESCAPE '\' / read more

Utilization of current redo log ( in % ) - oracle database script

utilization of current redo log ( in % ) - Oracle Database Script.SELECT le.leseq "Current log sequence No", 100*cp.cpodr_bno/le.lesiz "Percent Full", cp.cpodr_bno "Current Block No", le.lesiz "Size of Log in Blocks"FROM x$kcccp cp, x$kccle leWHERE le.leseq =CP.cpodr_seq AND bitand(le.leflg, 24) = 8 / read more

Upgrade statistics in db - oracle database script

Upgrade statistics in db - Oracle Database Script.-- If we are importing stats table from higher version to lower version, then before importing in the database, we need to upgrade the stats table. EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(OWNNAME =>'RAJ',STATTAB =>'STAT_TEST'); read more

Truncate partitions - oracle database script

Truncate partitions - Oracle Database Script.- SYNTAX :ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> TRUNCATE PARTITION < PARTITION_NAME> <UPDATE GLOBAL INDEXES(optional)>; --- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present ALTER TABLE CMADMIN.AODBA TRUNCATE PARTITION AODBA_JAN UPDATE GLOBAL INDEXES; --- In oracle 12c, we can truncate multiple partitions in one command ALTER TABLE CMADMIN.AODBA TRUNCATE PARTITIONS AODBA_JAN, AODBA_FEB, AODBA_MAR UPDATE GLOBAL INDEXES; read more

Tracing all session of a user - oracle database script

Tracing all session of a user - Oracle Database Script.--- CREATE THE BELOW TRIGGER TO ENABLE TRACE ALL SESSION OF USER ( SCOTT) CREATE OR REPLACE TRIGGER USER_TRACE_TRG AFTER LOGON ON DATABASE BEGIN     IF USER = 'SCOTT'   THEN     execute immediate 'alter session set events ''10046 trace name context forever, level 12''';   END IF; EXCEPTION WHEN OTHERS THEN NULL; END; / read more

Top sql queries using literal values - oracle database script

Top sql queries using literal values - Oracle Database Script.SELECT *FROM (SELECT plan_hash_value, count(distinct(hash_value)), sum(executions), sum(parse_calls) FROM gv$sql GROUP BY plan_hash_value HAVING count(distinct(hash_value)) > 10 ORDER BY 2 DESC)WHERE rownum<21; read more

Top running queries from ash - oracle database script

Top running queries from ASH - Oracle Database Script.--Query to get list of top running sqls in PAST between sysdate-1 to sysdate-23/34 . You can change accordingly SELECT active_session_history.user_id, dba_users.username, sqlarea.sql_text, SUM(active_session_history.wait_time + active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds FROM v$active_session_history active_session_history, v$sqlarea sqlarea, dba_users WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24 AND active_session_history.sql_id = sqlarea.sql_id AND active_session_history.user_id = dba_users.user_id and dba_users.username not in ('SYS','DBSNMP') GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username ORDER BY 4 DESC / read more

Top query with high elapsed time - oracle database script

Top Query with high elapsed time - Oracle Database Script.--- Queries in last 1 hour ( Run from Toad, for proper view) Select module,parsing_schema_name,inst_id,sql_id,CHILD_NUMBER,sql_plan_baseline,sql_profile,plan_hash_value,sql_fulltext, to_char(last_active_time,'DD/MM/YY HH24:MI:SS' ),executions, elapsed_time/executions/1000/1000, rows_processed,sql_plan_baseline from gv$sql where last_active_time>sysdate-1/24  and executions <> 0 order by elapsed_time/executions desc read more

Top cpu consuming sessions - oracle database script

Top cpu consuming sessions - Oracle Database Script.col program form a30 heading "Program" col CPUMins form 99990 heading "CPU in Mins"SELECT rownum AS rank, a.*FROM (SELECT v.sid, program, v.value / (100 * 60) CPUMinsFROM v$statname s, v$sesstat v, v$session sessWHERE s.name = 'CPU used by this session' AND sess.sid = v.sid AND v.statistic#=s.statistic# and v.value>0 ORDER BY v.value DESC) a where rownum < 11; read more

Table statistics history - oracle database script

Table statistics history - Oracle Database Script.-- For getting history of TABLE statistics setlines 200 col owner for a12 col table_name for a21 select owner,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&TABLE_NAME'; read more

Table not having index on fk column - oracle database script

Table not having index on fk column - Oracle Database Script.SELECT *FROM (SELECT c.table_name, co.column_name, co.position column_position FROM user_constraints c, user_cons_columns co WHERE c.constraint_name = co.constraint_name AND c.constraint_type = 'R' MINUS SELECT ui.table_name, uic.column_name, uic.column_position FROM user_indexes ui, user_ind_columns uic WHERE ui.index_name = uic.index_name )ORDER BY TABLE_NAME, column_position;SELECT a.constraint_name cons_name , a.table_name tab_name , b.column_name cons_column , nvl(c.column_name, '***No Index***') ind_columnFROM user_constraints aJOIN user_cons_columns b ON a.constraint_name = b.constraint_nameLEFT OUTER JOIN user_ind_columns c ON b.column_name = c.column_nameAND b.table_name = c.table_nameWHERE constraint_type = 'R'ORDER BY 2, 1; read more

Stop/start cluster in rac standalone. - oracle database script

Stop/start cluster in rac standalone. - Oracle Database Script.-- Oracle RAC in standalone is known as oracle restart, where only HAS(high availability service)  component is available. crsctl stop has crsctl start has read more

Stop/start a service - oracle database script

stop/start a service - Oracle Database Script.SYNTAX: --------- srvctl start servicec -d {DB_NAME} -s {SERVICE_NAME} srvctl stop servicec -d {DB_NAME} -s {SERVICE_NAME} EXAMPLE: --------------- srvctl start service -d PREDB -s PRDB_SRV srvctl stop service -d PREDB -s PRDB_SRV read more

Stop and start instance using srvctl - oracle database script

Stop and start instance using srvctl - Oracle Database Script.--SYNTAX FOR STOPPING INSTANCE -- srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f] e.g srvctl stop instance -d PRODB  -i PRODB1  --SYNTAX FOR STARTING INSTANCE -- srvctl start instance -d db_unique_name  [-i "instance_name_list"} [-o start_options] e.g srvctl start instance -d PRODB -i PRODB1 read more

Stop and start db using srvctl - oracle database script

Stop and start db using srvctl - Oracle Database Script.-- SYNTAX FOR STOP DB --- srvctl stop database -d db_name [-o stop_options] where stop_options is normal/immediate(default)/transactional/abort e.g srvctl stop database -d PRODB -o normal srvctl stop database -d PRODB -o immediate srvctl stop database -d PRODB -o transactional srvctl stop database -d PRODB -o abort -- SYNTAX FOR START DB -- srvctl start database -d db_name [-o start_options] where start_option is nomount/mount/open(default) e.g  srvctl start database -d PRODB -o nomount srvctl start database -d PRODB -o mount srvctl start database -d PRODB -o open read more

Stop and start crs - oracle database script

Stop and start CRS - Oracle Database Script.-- stop crs ( run from root) $GRID_HOME/bin/crsctl stop crs -- start crs( run from root) $GRID_HOME/bin/crsctl start crs read more

Sqls doing full table scan - oracle database script

Sqls doing full table scan - Oracle Database Script.SELECT sql_id, object_owner, object_nameFROM V$SQL_PLANWHERE OPERATION='TABLE ACCESS' AND OPTIONS='FULL' AND object_owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP'); read more

Sql tuning advisor for sql_id from cursor - oracle database script

Sql tuning advisor for sql_id from cursor - Oracle Database Script.-- Create tuning task set long 1000000000 DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'apwfwjhgc9sk8', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 500, task_name => 'apwfwjhgc9sk8_tuning_task_1', description => 'Tuning task for statement apwfwjhgc9sk8'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / -- Execute tuning task EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'apwfwjhgc9sk8_tuning_task_1'); -- Generate report SET LONG 10000000; SET PAGESIZE 100000000 SET LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('apwfwjhgc9sk8_tuning_task_1') AS recommendations FROM dual; SET PAGESIZE 24 read more

Spool sql query output to html - oracle database script

Spool sql query output to HTML - Oracle Database Script.-- We can spool output of an sql query to html format: set pages 5000 SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON - HEAD "<TITLE>EMPLOYEE REPORT</TITLE> - <STYLE type='text/css'> - <!-- BODY {background: #FFFFC6} --> - </STYLE>" - BODY "TEXT='#FF00Ff'" - TABLE "WIDTH='90%' BORDER='5'" spool report.html Select * from scott.emp; spool off exit read more

Split partition online(12cr2 only) - oracle database script

Split partition online(12cR2 only) - Oracle Database Script.SQL>ALTER TABLE order_tab split PARTITION CREATED_MX INTO (PARTITION CREATED_2106_P2 VALUES LESS THAN (TO_DATE('01/03/2016', 'DD/MM/YYYY')),PARTITION CREATED_MX) ONLINE;TABLE altered. SQL>SELECT partition_name, read_only, high_valueFROM dba_tab_partitionsWHERE TABLE_NAME='ORDER_TAB'; read more

Space used to store stats - oracle database script

Space used to store stats - Oracle Database Script.--- Space currently used to store statistics in SYSAUX in KBytes, select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%'; read more

Set env variables using srvctl - oracle database script

Set env variables using srvctl - Oracle Database Script.-- setenv to set env variables.(ORCL is the db_unique_name) srvctl setenv database -db ORCL -env "ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1" srvctl setenv database -db ORCL -env "TNS_ADMIN=/oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin" --getenv to view the env setting: srvctl getenv database -db ORCL read more

Sessions holding library cache lock - oracle database script

Sessions holding library cache lock - Oracle Database Script.-- For standalone db: select sid Waiter, p1raw, substr(rawtohex(p1),1,30) Handle, substr(rawtohex(p2),1,30) Pin_addr from v$session_wait where wait_time=0 and event like '%library cache%'; -- For RAC DB: select a.sid Waiter,b.SERIAL#,a.event,a.p1raw, substr(rawtohex(a.p1),1,30) Handle, substr(rawtohex(a.p2),1,30) Pin_addr from v$session_wait a,v$session b where a.sid=b.sid and a.wait_time=0 and a.event like 'library cache%'; or set lines 152 col sid for a9999999999999 col name for a40 select a.sid,b.name,a.value,b.class from gv$sesstat a , gv$statname b where a.statistic#=b.statistic# and name like '%library cache%'; read more

Sessions accessing an object - oracle database script

Sessions accessing an object - Oracle Database Script.SET lines 299 COLUMN OBJECT format a30 COLUMN OWNER format a10SELECT *FROM v$accessWHERE OWNER='&OWNER' andobject='&object_name' AND / read more

Session login history from ash - oracle database script

Session login history from ASH - Oracle Database Script.SELECT c.username, a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.module, a.machine, b.SQL_TEXTFROM DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b, dba_users cWHERE a.SQL_ID = b.SQL_ID(+) AND a.user_id=c.user_id AND c.username='&username'ORDER BY a.SQL_EXEC_START ASC; read more

Segments with high physical read - oracle database script

segments with high physical read - Oracle Database Script.SET pagesize 200 setlinesize 120 col segment_name format a20 col OWNER format a10SELECT segment_name, object_type, total_physical_readsFROM (SELECT OWNER||'.'||object_name AS segment_name, object_type, value AS total_physical_reads FROM v$segment_statistics WHERE statistic_name IN ('physical reads') ORDER BY total_physical_reads DESC)WHERE rownum <=10; read more

Script to monitor temp tablespace usage - oracle database script

Script to Monitor TEMP tablespace usage - Oracle Database Script.SELECT a.tablespace_name TABLESPACE, d.TEMP_TOTAL_MB, SUM (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB, d.TEMP_TOTAL_MB - SUM (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MBFROM v$sort_segment a, (SELECT b.name, c.block_size, SUM (c.bytes) / 1024 / 1024 TEMP_TOTAL_MBFROM v$tablespace b, v$tempfile cWHERE b.ts#= c.ts# group by b.name, c.block_size ) d where a.tablespace_name = d.name group by a.tablespace_name, d.TEMP_TOTAL_MB; read more

Script to monitor tablespace usage - oracle database script

Script to Monitor tablespace usage - Oracle Database Script.SET feedback OFFSET pagesize 70;SET linesize 2000SET head ON COLUMN TABLESPACE format a25 heading 'Tablespace Name' COLUMN autoextensible format a11 heading 'AutoExtend' COLUMN files_in_tablespace format 999 heading 'Files' COLUMN total_tablespace_space format 99999999 heading 'TotalSpace' COLUMN total_used_space format 99999999 heading 'UsedSpace' COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace' COLUMN total_used_pct format 9999 heading '%Used' COLUMN total_free_pct format 9999 heading '%Free' COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto' COLUM total_auto_used_pct format 999.99 heading 'Max%Used' COLUMN total_auto_free_pct format 999.99 heading 'Max%Free' WITH tbs_auto AS (SELECT DISTINCT tablespace_name, autoextensible FROM dba_data_files WHERE autoextensible = 'YES'), files AS (SELECT tablespace_name, COUNT (*) tbs_files, SUM (BYTES/1024/1024) total_tbs_bytes FROM dba_data_files GROUP BY tablespace_name), fragments AS (SELECT tablespace_name, COUNT (*) tbs_fragments, SUM (BYTES)/1024/1024 total_tbs_free_bytes, MAX (BYTES)/1024/1024 max_free_chunk_bytes FROM dba_free_space GROUP BY tablespace_name), AUTOEXTEND AS (SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow FROM dba_data_files WHERE autoextensible = 'YES' GROUP BY tablespace_name UNION SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow FROM dba_data_files WHERE autoextensible = 'NO' GROUP BY tablespace_name) GROUP BY tablespace_name)SELECT c.instance_name, a.tablespace_name TABLESPACE, CASE tbs_auto.autoextensible WHEN 'YES' THEN 'YES' ELSE 'NO' END AS autoextensible, files.tbs_files files_in_tablespace, files.total_tbs_bytes total_tablespace_space, (files.total_tbs_bytes - fragments.total_tbs_free_bytes) total_used_space, fragments.total_tbs_free_bytes total_tablespace_free_space, round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes) / files.total_tbs_bytes) * 100)) total_used_pct, round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100)) total_free_pctFROM dba_tablespaces a, v$instance c, files, fragments, AUTOEXTEND, tbs_autoWHERE a.tablespace_name = files.tablespace_name AND a.tablespace_name = fragments.tablespace_name AND a.tablespace_name = AUTOEXTEND.tablespace_name AND a.tablespace_name = tbs_auto.tablespace_name(+)ORDER BY total_free_pct; read more

Script to find active sessions in database - oracle database script

Script to find active sessions in database - Oracle Database Script.SET echo OFFSET linesize 95SET head ONSET feedback ON col sid head "Sid" form 9999 trunc col serial# form 99999 trunc head "Ser#" col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head "Client|Machine" col program form a15 trunc head "Client|Program" col login form a11 col "last call" form 9999999 trunc head "Last Call|In Secs" col status form a6 truncSELECT sid, serial#, substr(username, 1, 10) username, substr(osuser, 1, 10) osuser, substr(program||MODULE, 1, 15) program, substr(machine, 1, 22) machine, to_char(logon_time, 'ddMon hh24:mi') login, last_call_et "last call", statusFROM gv$sessionWHERE status='ACTIVE'ORDER BY 1 / read more

Scn to timestamp and viceversa - oracle database script

Scn to timestamp and viceversa - Oracle Database Script.-- Get current scn value: select current_scn from v$database; -- Get scn value at particular time: select timestamp_to_scn('19-JAN-08:22:00:10') from dual; -- Get timestamp from scn: select scn_to_timestamp(224292)from dual; read more

Run shared pool advisory - oracle database script

Run shared pool advisory - Oracle Database Script.SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB", shared_pool_size_factor "Size Factor", estd_lc_time_saved "Time Saved in sec"FROM v$shared_pool_advice; read more

Run sga target advisory - oracle database script

run sga target advisory - Oracle Database Script.- STATISTICS_LEVEL should be TYPICAL/ALL. SQL> SHOW PARAMETER statistics_level NAME TYPE VALUE ------------------------------------ -------------------------------- -------------------------- statistics_level string TYPICAL select * from v$sga_target_advice order by sga_size; read more

Rman tablespace backup run block - oracle database script

rman tablespace backup run block - Oracle Database Script.configure CONTROLFILE autobackup ON;configure CONTROLFILE autobackup formatFOR device TYPE disk TO '/archiva/backup/%F';configure maxsetsize TOUNLIMITED;configure device TYPE disk parallelism 4;run { ALLOCATE channel c1 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c2 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;BACKUP TABLESPACE USERS, TOOLS;release channel c1 ;release channel c2 ;} read more

Rman incremental db backup run block script - oracle database script

RMAN incremental db backup run block script - Oracle Database Script.configure BACKUP optimization ON;configure CONTROLFILE autobackup ON;configure CONTROLFILE autobackup formatFOR device TYPE disk TO '/archiva/backup/%F';configure maxsetsize TOUNLIMITED;configure device TYPE disk parallelism 4;run { ALLOCATE channel c1 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c2 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c3 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c4 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;BACKUP AS compressed backupset incremental LEVEL 1 CHECK logical DATABASE plus ARCHIVELOG;release channel c1 ;release channel c2 ;release channel c3 ;release channel c4 ;} read more

Rman full database backup script - oracle database script

rman full database backup script - Oracle Database Script.configure BACKUP optimization ON;configure CONTROLFILE autobackup ON;configure CONTROLFILE autobackup formatFOR device TYPE disk TO '/archiva/backup/%F';configure maxsetsize TOUNLIMITED;configure device TYPE disk parallelism 4;run { ALLOCATE channel c1 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c2 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c3 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c4 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;BACKUP AS compressed backupset incremental LEVEL 0 CHECK logical DATABASE plus ARCHIVELOG;release channel c1 ;release channel c2 ;release channel c3 ;release channel c4 ;} read more

Rman datafile(s) backup run block - oracle database script

RMAN datafile(s) backup run block - Oracle Database Script.configure CONTROLFILE autobackup ON;configure CONTROLFILE autobackup formatFOR device TYPE disk TO '/archiva/backup/%F';configure maxsetsize TOUNLIMITED;configure device TYPE disk parallelism 4;run { ALLOCATE channel c1 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3G;ALLOCATE channel c2 TYPE disk format '/archiva/backup/%I-%Y%M%D-%U' maxpiecesize 3g;BACKUP DATAFILE 3, 4;release channel c1 ;release channel c2 ;} read more

Restore archivelog from rman tape - oracle database script

Restore archivelog from rman tape - Oracle Database Script.-----Below  script will restore the archive sequences from 7630 to 7640 to /dumparea location connect target sys/******@CRM_DB connect catalog RMAN_tst/*****@catdb run { allocate channel t1 type SBT_TAPE parms ‘ENV=(NSR_SERVER=nwwerpw,NSR_CLIENT=tsc_test01,NSR_DATA_VOLUME_POOL=DD086A1)’connect sys/****@CRM_DB; set archivelog destination to ‘/dumparea/'; restore archivelog from sequence 7630 until sequence 7640; release channel t1; } read more

Rename a partition - oracle database script

Rename a partition - Oracle Database Script.ALTER TABLE employee RENAME PARTITION TAB3 TO TAB4; read more

Relocate a service - oracle database script

Relocate a service - Oracle Database Script.SYNTAX - srvctl relocate service -d {database_name} -s {service_name} -i {old_inst_name} -r {new_inst_name} EXAMPLE:(Relocating service PRDB_SRV FROM PREDB2 TO PREDB1) srvctl relocate service -d PREDB -s PRDB_SVC -i PREDB2 -t PREDB1 -- Check the status of service srvctl status service -d PREDB -s PRDB_SVC read more

Recover dropped table with rman 12c - oracle database script

Recover dropped table with RMAN 12c - Oracle Database Script.RMAN>RECOVER TABLE SCOTT.SALGRADE UNTIL TIME “to_date(’08/09/2016 18:49:40′,’mm/dd/yyyy hh24:mi:ss’)” auxiliary destination ‘/u03/arch/TEST/BACKUP’ datapump destination ‘/u03/arch/TEST/BACKUP';auxiliary destination – LOCATIONWHERE ALL the related files FOR auxiliary INSTANCE will be placed datapump destination – LOCATION WHERE the export DUMP OF the TABLE will be placed NOTE - This feature IS available ONLY IN oracle 12c AND later. read more

Recover a dropped table - oracle database script

Recover a dropped table - Oracle Database Script.Flashback TABLE AODBA.EMP TOBEFOREDROP; -- Restore the dropped table with a new name Flashback table AODBA.EMP to before drop rename to EMP_BACKUP; Note - To recover the table, table should be present in recyclebin: select * from dba_recyclebin; read more

Queries causing high physical read - oracle database script

Queries causing high physical read - Oracle Database Script.SELECT SCHEMA, sql_text, disk_reads, round(cpu, 2)FROM (SELECT s.parsing_schema_name SCHEMA, t.sql_id, t.sql_text, t.disk_reads, t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time FROM v$sqlstats t JOIN v$sql s on(t.sql_id = s.sql_id) WHERE parsing_schema_name = 'SCOTT' ORDER BY disk_reads DESC)WHERE rownum <= 5; read more

Purge recyclebin in database - oracle database script

Purge recyclebin in database - Oracle Database Script.SQL> SQL>SELECT count(*)FROM DBA_RECYCLEBIN ;COUNT(*) ---------- 2132 SQL> purge recyclebin; Recyclebin purged. SQL> select count(*) from DBA_RECYCLEBIN ; COUNT(*) ---------- 0 read more

Purge old awr snapshots - oracle database script

Purge old awr snapshots - Oracle Database Script.-- Find the AWR snapshot details. select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id -- Purge snapshot between snapid 612 to 700 execute dbms_workload_repository.drop_snapshot_range(low_snap_id =>612 , high_snap_id =>700); -- Verify again select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id read more

Publish pending stats - oracle database script

Publish Pending stats - Oracle Database Script.-- Publish Pending stats for table EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('SCHEMA_NAME,'TABLE_NAME'); -- Publish pending stats for a schema exec dbms_stats.publish_pending_stats('SCHEMA_NAME',null); read more

Pga usage by sessions - oracle database script

Pga usage by sessions - Oracle Database Script.SET lines 2000SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024), 2) MBFROM v$sesstat a, v$statname bWHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%') AND a.statistic# = b.statistic# order by ROUND(a.VALUE/(1024*1024),2) desc read more

Oracle license usage info

oracle license usage info SELECT samp.dbid, fu.name, samp.version, detected_usages, total_samples, decode(to_char(last_usage_date, 'MM/DD/YYYY, HH:MI:SS'), NULL, 'FALSE', to_char(last_sample_date, 'MM/DD/YYYY, HH:MI:SS'), 'TRUE', 'FALSE') currently_used, first_usage_date, last_usage_date, aux_count, feature_info, last_sample_date, last_sample_period, sample_interval, mt.descriptionFROM wri$_dbu_usage_sample samp, wri$_dbu_feature_usage fu, wri$_dbu_feature_metadata mtWHERE samp.dbid = fu.dbid AND samp.version = fu.version AND fu.name = mt.name AND fu.name NOT LIKE '_DBFUS_TEST%' AND /* filter out test features */ bitand(mt.usg_det_method, 4) != 4 /* filter out disabled features */; read more

Oracle db is 32bit or 64 bit?

oracle db is 32bit or 64 bit? SELECT length(addr)*4 || '-bits' word_lengthFROM v$processWHERE ROWNUM =1; read more

Open database link information - oracle database script

Open database link information - Oracle Database Script.SET pagesize 200SET lines 200 col db_linkFOR a19SET long 999SELECT db_link, owner_id, logged_on, heterogeneous, open_cursors, in_transaction, update_sentFROM gv$dblinkORDER BY db_link; read more

Objects locked by library cache - oracle database script

Objects locked by library cache - Oracle Database Script.SELECT to_char(SESSION_ID, '999') sid, substr(LOCK_TYPE, 1, 30) TYPE, substr(lock_id1, 1, 23) Object_Name, substr(mode_held, 1, 4) HELD, substr(mode_requested, 1, 4) REQ, lock_id2 Lock_addrFROM dba_lock_internalWHERE mode_requested'None' AND mode_requestedmode_held AND session_id IN (SELECT sid FROM v$session_wait WHERE wait_time=0 AND event LIKE '%library cache%') ; read more

Objects causing latch contention - oracle database script

Objects causing latch contention - Oracle Database Script.col OBJECT_NAMEFOR a30 col OWNERFOR a12 WITH bh_lc AS (SELECT lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets, lc.immediate_misses, lc.spin_gets, lc.sleeps, bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class, bh.state, bh.objFROM v$session_wait sw, v$latchname ld, v$latch_children lc, x$bh bhWHERE lc.addr =sw.p1raw AND sw.p2= ld.latch# and ld.name='cache buffers chains' and lower(sw.event) like '%latch%' and bh.hladdr=lc.addr ) select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type, bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets, bh_lc.immediate_misses, spin_gets, sleeps from bh_lc, dba_objects o where bh_lc.obj = o.data_object_id(+) order by 1,2 desc; read more

Objects causing flushing of shared pool - oracle database script

Objects causing flushing of shared pool - Oracle Database Script.SET lines 160 pages 100SELECT *FROM x$ksmlruORDER BY ksmlrnum; read more

Non-partitioned to partitioned online(12cr2 only) - oracle database script

Non-partitioned to partitioned online(12CR2 only) - Oracle Database Script.-- In Oracle 12cR2, we can convert non partitioned table to partitioned online using alter table command. alter table BSSTDBA.ORDER_TAB modify PARTITION BY RANGE (CREATED) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')), PARTITION Created_MX VALUES LESS THAN (MAXVALUE)) ONLINE; read more

Mutex sleep in database - oracle database script

Mutex sleep in database - Oracle Database Script.COLUMN mux format a18 heading 'Mutex Type' trunc;COLUMN loc format a32 heading 'Location' trunc;COLUMN sleeps format 9, 999, 999, 990 heading 'Sleeps';COLUMN wt format 9, 999, 990.9 heading 'Wait |Time (s)';SELECT e.mutex_type mux , e.location loc , e.sleeps - nvl(b.sleeps, 0) sleeps , (e.wait_time - nvl(b.wait_time, 0))/1000000 wtFROM DBA_HIST_MUTEX_SLEEP b , DBA_HIST_MUTEX_SLEEP eWHERE b.snap_id(+) = &bid AND e.snap_id = &eid AND b.dbid(+) = e.dbid AND b.instance_number(+) = e.instance_number AND b.mutex_type(+) = e.mutex_type AND b.location(+) = e.location AND e.sleeps - nvl(b.sleeps, 0) > 0ORDER BY e.wait_time - nvl(b.wait_time, 0) DESC; read more

Move voting disk to new diskgroup - oracle database script

Move voting disk to new diskgroup - Oracle Database Script.$GRID_HOME/bin/crsctlREPLACE votedisk +NEW_DG CHECK the status USING below command.$GRID_HOME/bin/crsctl query css votedisk read more

Move partition to new tablespace - oracle database script

Move partition to new tablespace - Oracle Database Script.- MOVE a single PARTITION TO a NEW TABLESPACEALTER TABLE SCOTT.EMP MOVE PARTITION EMP_Q1 TABLESPACE TS_USERS; --- Move a single partition to a new tablespace WITH PARALLEL ALTER TABLE SCOTT.EMP MOVE PARTITION EMP_Q1 TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING; - Dynamic script to move all partitions of a table select 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;' from dba_tab_partitions where table_name='&TABLE_NAME' and table_owner='&SCHEMA_NAME'; read more

Mount/dismount asm diskgroups - oracle database script

Mount/dismount ASM diskgroups - Oracle Database Script.-- For mount a diskgroup,(This is instance specific, for mounting on all nodes, run the same on all nodes) SQL>alter diskgroup DATA mount;  or asmcmd>mount DATA -- For umount a diskgroup,(This is instance specific, for unmounting on all nodes, run the same on all nodes) SQL>alter diskgroup DATA dismount;  or asmcmd>umount DATA -- To mount/Dismount all the diskgroups SQL>alter diskgroup ALL mount;  SQL>alter diskgroup ALL dismount; read more

Monitor undo tablespace usage - oracle database script

Monitor undo tablespace usage - Oracle Database Script.SELECT a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMBFROM (SELECT sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name FROM dba_data_files a, dba_tablespaces b WHERE a.tablespace_name = b.tablespace_name AND b.contents = 'UNDO' GROUP BY b.tablespace_name) a, (SELECT c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB FROM DBA_UNDO_EXTENTS c WHERE status <> 'EXPIRED' GROUP BY c.tablespace_name) bWHERE a.tablespace_name = b.tablespace_name; read more

Monitor rollback transactions - oracle database script

Monitor rollback transactions - Oracle Database Script.SELECT state, UNDOBLOCKSDONE, UNDOBLOCKSTOTAL, UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100FROM gv$fast_start_transactions;ALTER SESSIONSET nls_date_format='dd-mon-yyyy hh24:mi:ss';SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", decode(cputime, 0, 'unknown', sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"FROM v$fast_start_transactions;SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublkFROM v$session a, v$transaction bWHERE a.saddr=b.ses_addrORDER BY 5 DESC; read more

Monitor rman backup progress - oracle database script

Monitor rman backup progress - Oracle Database Script.SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100, 2) "%_COMPLETE"FROM V$SESSION_LONGOPSWHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK; read more

Monitor parallel queries - oracle database script

Monitor parallel queries - Oracle Database Script.SELECT s.inst_id, decode(px.qcinst_id, NULL, s.username, ' - '||lower(substr(s.program, length(s.program)-4, 4))) "Username", decode(px.qcinst_id, NULL, 'QC', '(Slave)') "QC/Slave", to_char(px.server_set) "Slave Set", to_char(s.sid) "SID", decode(px.qcinst_id, NULL, to_char(s.sid), px.qcsid) "QC SID", px.req_degree "Requested DOP", px.degree "Actual DOP", p.spidFROM gv$px_session px, gv$session s, gv$process pWHERE px.sid=s.sid (+) AND px.serial#=s.serial# and      px.inst_id = s.inst_id      and p.inst_id = s.inst_id      and p.addr=s.paddr   order by 5 , 1 desc read more

Monitor index usage - oracle database script

Monitor index usage - Oracle Database Script.---Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead. -- First enable monitoring usage for the indexes. alter index siebel.S_ASSET_TEST monitoring usage; --Below query to find the index usage: select * from v$object_usage; read more

Monitor asm diskgroup i/o - oracle database script

Monitor asm diskgroup i/o - Oracle Database Script.--- Run from toad,sql devl select * from V$ASM_DISK_IOSTAT; read more

Monitor asm disk rebalance - oracle database script

Monitor ASM disk rebalance - Oracle Database Script.SET pagesize 299SET lines 2999SELECT GROUP_NUMBER, OPERATION, STATE, POWER, ACTUAL, ACTUAL, EST_MINUTESFROM gv$asm_operation; read more

Modify moving window baseline size - oracle database script

Modify moving window baseline size - Oracle Database Script.-- Check the current moving window baseline size: select BASELINE_TYPE,MOVING_WINDOW_SIZE from dba_hist_baseline; -- Modify window_size to (7 days): execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7); read more

Modify asm user password - oracle database script

Modify asm user password - Oracle Database Script.-- list asm users  ASMCMD> lspwusr Username sysdba sysoper sysasm SYS      TRUE   TRUE     TRUE ASMSNMP  TRUE   FALSE    FALSE -- >    -- Modify user password  ASMCMD> orapwusr --modify asmsnmp Enter password: ******** read more

Merge partition - oracle database script

merge partition - Oracle Database Script.--  MERGE PARTITION  - FOR COMBINING MULTIPLE PARTITIONS TO A NEW ONE ( 12C ONWARS) -- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> MERGE PARTITIONS < PARTITION1,PARTITION2,...> < UPDATE GLOBAL INDEXES(optional)>; --- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present ALTER TABLE CMADMIN.AODBA MERGE PARTITIONS AODBA_JAN, AODBA_FEB, AODBA_MAR INTO partition AODBA_Q1; read more

Manual backup of ocr and list backups - oracle database script

Manual backup of ocr and list backups - Oracle Database Script.-- List down the backups of OCR $GRID_HOME/bin/ocrconfig -showbackup   -- Take manual OCR backup $GRID_HOME/bin/ocrconfig -manualbackup read more

Manage mgmtdb in 12c rac - oracle database script

Manage MGMTDB in 12c RAC - Oracle Database Script.-- check status of mgmtdb in orcle 12c RAC srvctl status mgmtdb -- stop and start MGMT db. srvctl stop mgmtdb srvctl start mgmtdb read more

Make a partition ready only(12cr2) - oracle database script

Make a partition ready only(12CR2) - Oracle Database Script.-- From oracle 12.2.0.1 Relase, we can make few partitions of a table read only. SQL> alter table dbatest.ORDER_TAB modify partition CREATED_2105_P10 read only; Table altered. SQL> select partition_name,read_only from dba_tab_partitions where table_name='ORDER_TAB'; PARTITION_NAME READ -------------------------------- ---- CREATED_2105_P10 YES CREATED_2105_P11 NO CREATED_2105_P12 NO CREATED_2105_P8 NO CREATED_2105_P9 NO CREATED_MX NO 6 rows selected. read more

Lock/unlock statistics - oracle database script

Lock/unlock statistics - Oracle Database Script.--- Lock  statistics EXEC DBMS_STATS.lock_schema_stats('SCOTT'); EXEC DBMS_STATS.lock_table_stats('SCOTT', 'TEST'); EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'TEST', 'TEST_JAN2016'); -- Unlock statistics EXEC DBMS_STATS.unlock_schema_stats('SCOTT'); EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'TEST'); EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'TEST', 'TEST_JAN2016'); --- check stats status: SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT'; read more

List flashback restore points - oracle database script

List flashback restore points - Oracle Database Script.-- From SQL prompt: SQL>Select * from v$restore_points: -- From RMAN prompt: RMAN>LIST RESTORE POINT ALL; read more

Latch type and sql hash value - oracle database script

Latch type and sql hash value - Oracle Database Script.SET lines 160 pages 100 COLUMN event format A35 COLUMN name format A35SELECT x.event, x.sql_hash_value, CASE WHEN x.event LIKE 'latch%' THEN l.name ELSE ' ' END name, x.cntFROM (SELECT substr(w.event, 1, 28) event, s.sql_hash_value, w.p2, count(*) cnt FROM v$session_wait w, v$session s, v$process p WHERE s.sid=w.sid AND p.addr = s.paddr AND s.username IS NOT NULL AND w.event NOT LIKE '%pipe%' AND w.event NOT LIKE 'SQL*%' GROUP BY substr(w.event, 1, 28), sql_hash_value, w.p2) x, v$latch lWHERE x.p2 = l.latch#(+)ORDER BY cnt; read more

Kill snipped session in db - oracle database script

Kill snipped session in db - Oracle Database Script.-- It will generate kill session statements for all snipped sessions: select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='SNIPED' ; read more

Kill all sessions of a sql_id - oracle database script

Kill all sessions of a sql_id - Oracle Database Script.SELECT 'alter system kill session ' ||''''||SID||','||SERIAL#||' immediate ;'FROM v$sessionWHERE sql_id='&sql_id'; --- FOR RAC select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;' from gv$session where sql_id='&sql_id' read more

Kill all session of a user - oracle database script

kill all session of a user - Oracle Database Script.BEGINFOR r IN (SELECT sid, serial# FROM v$session WHERE username = 'TEST_ANB') LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# || ''''; END LOOP; END; / read more

I/o usage of each tempfile - oracle database script

I/O usage of each tempfile - Oracle Database Script.SELECT SUBSTR(t.name, 1, 50) AS file_name, f.phyblkrd AS blocks_read, f.phyblkwrt AS blocks_written, f.phyblkrd + f.phyblkwrt AS total_ioFROM v$tempstat f, v$tempfile tWHERE t.file# = f.file# ORDER BY f.phyblkrd + f.phyblkwrt DESC; select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB, i.inst_id,i.host_name FROM gv$session s, gv$sort_usage u ,gv$instance i WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10; read more

Installed rdbms components - oracle database script

Installed RDBMS components - Oracle Database Script.col comp_idFOR a10 col comp_nameFOR a56 col VERSIONFOR a12 col statusFOR a10SET pagesize 200SET lines 200SET long 999SELECT comp_id, comp_name, VERSION, statusFROM dba_registry; read more

How to change asm sys password - oracle database script

How to Change ASM sys password - Oracle Database Script.$ export ORACLE_SID=+ASM $ asmcmd ASMCMD> orapwusr --modify --password sys Enter password: ****** ASMCMD> exit Alternatively, we can use orapwd to recreate pwd file. read more

How far we can flashback - oracle database script

How far we can flashback - Oracle Database Script.--How Far Back Can We Flashback To (Time) select to_char(oldest_flashback_time,’dd-mon-yyyy hh24:mi:ss’) “Oldest Flashback Time” from v$flashback_database_log; --How Far Back Can We Flashback To (SCN) col oldest_flashback_scn format 99999999999999999999999999 select oldest_flashback_scn from v$flashback_database_log; read more

Get statistics preference setting - oracle database script

Get statistics preference setting - Oracle Database Script.-- Setting Publish preference exec dbms_stats.set_table_prefs('SCOTT','EMP','PUBLISH','FALSE'); --- Check the publish preference status select dbms_stats.get_prefs('PUBLISH', 'SCOTT','EMP') FROM DUAL; Similarly for schema also use as below: select dbms_stats.get_prefs('PUBLISH', 'SCOTT') from dual exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','PUBLISH','FALSE'); --- FOR INDEX SET_INDEX_STATS GET_INDEX_STATS -- FOR DATABASE SET_DATABASE_PREFS read more

Get sql_text from sid - oracle database script

Get sql_text from sid - Oracle Database Script.col sql_text form a80SET lines 120SELECT sql_textFROM gv$sqltextWHERE hash_value= (SELECT sql_hash_value FROM gv$session WHERE sid=&1 AND inst_id=&inst_id)ORDER BY piece / read more

Get sql_profile of a sql_id - oracle database script

Get sql_profile of a sql_id - Oracle Database Script.-- Script for getting sql_profile created for a sql_id select distinct p.name sql_profile_name, s.sql_id from dba_sql_profiles p, DBA_HIST_SQLSTAT s where p.name=s.sql_profile and s.sql_id='&sql_id'; read more

Get size of the database - oracle database script

Get size of the database - Oracle Database Script.col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20SELECT round(sum(used.bytes) / 1024 / 1024 / 1024) || ' GB' "Database Size", round(sum(used.bytes) / 1024 / 1024 / 1024) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space", round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"FROM (SELECT bytes FROM v$datafile UNION ALL SELECT bytes FROM v$tempfile UNION ALL SELECT bytes FROM v$log) used , (SELECT sum(bytes) AS p FROM dba_free_space) FREEGROUP BY free.p / read more

Get sid from os pid - oracle database script

Get sid from os pid - Oracle Database Script.- GET sidFROM os pid (server process) col sid format 999999 col username format a20 col osuser format a15SELECT b.spid, a.sid, a.serial#,a.username, a.osuserFROM v$session a, v$process bWHERE a.paddr= b.addr AND b.spid='&spid'ORDER BY b.spid; read more

Get row_count of partitions of a table - oracle database script

Get row_count of partitions of a table - Oracle Database Script.SET serverout ON SIZE 1000000SET verify OFF DECLARE sql_stmt varchar2(1024);ROW_COUNT number;CURSOR get_tab ISSELECT TABLE_NAME, partition_nameFROM dba_tab_partitionsWHERE table_owner=upper('&&TABLE_OWNER') AND TABLE_NAME='&&TABLE_NAME';BEGIN dbms_output.put_line('Checking Record Counts for table_name');dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');dbms_output.put_line('....');FOR get_tab_rec IN get_tab LOOP BEGIN sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name ||' partition ( '||get_tab_rec.partition_name||' )';EXECUTE IMMEDIATE sql_stmt INTO ROW_COUNT;dbms_output.put_line('Table '||rpad(get_tab_rec.table_name ||'('||get_tab_rec.partition_name||')', 50) ||' '||TO_CHAR(ROW_COUNT)||' rows.');EXCEPTION WHEN others THEN dbms_output.put_line ('Error counting rows for table '||get_tab_rec.table_name);END;END LOOP;END;/SET verify ON read more

Get redo log member info - oracle database script

Get redo log member info - Oracle Database Script.col memberFOR a56SET pagesize 299SET lines 299SELECT l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) "Size (MB)"FROM v$log l, v$logfile fWHERE f.group# = l.group# order by 1,2 / read more

Get parallel query detail - oracle database script

get parallel query detail - Oracle Database Script.col usernameFOR a9 col sidFOR a8SET lines 299SELECT s.inst_id, decode(px.qcinst_id, NULL, s.username, ' - '||lower(substr(s.program, length(s.program)-4, 4))) "Username", decode(px.qcinst_id, NULL, 'QC', '(Slave)') "QC/Slave", to_char(px.server_set) "Slave Set", to_char(s.sid) "SID", decode(px.qcinst_id, NULL, to_char(s.sid), px.qcsid) "QC SID", px.req_degree "Requested DOP", px.degree "Actual DOP", p.spidFROM gv$px_session px, gv$session s, gv$process pWHERE px.sid=s.sid (+) AND px.serial#=s.serial# and px.inst_id = s.inst_id and p.inst_id = s.inst_id and p.addr=s.paddr order by 5 , 1 desc / read more

Get os pid from sid - oracle database script

Get os pid from sid - Oracle Database Script.SET lines 123 col USERNAMEFOR a15 col OSUSERFOR a8 col MACHINEFOR a15 col PROGRAMFOR a20SELECT b.spid, a.username, a.program, a.osuser, a.machine, a.sid, a.serial#, a.statusFROM gv$session a, gv$process bWHERE addr=paddr(+) AND sid=&sid; read more

Get olr info in rac - oracle database script

Get OLR info in RAC - Oracle Database Script.-- OLR(ORACLE LOCAL REGISTRY) -- Get current OLR location:(run from root only) $GRID_HOME/bin/ocrcheck -local -- List the OLR backups: $GRID_HOME/bin/ocrconfig -local -showbackup -- Take manual OLR backup: $GRID_HOME/bin/ocrconfig -local -manualbackup read more

Get node info using olsnodes - oracle database script

get node info using olsnodes - Oracle Database Script.-- List of nodes in the cluster olsnodes -- Nodes with node number olsnodes -n -- Node with vip olsnodes -i olsnodes -s -t -- Leaf or Hub olsnodes -a -- Getting private ip details of the local node olsnodes -l -p -- Get cluster name olsnodes -c read more

Get interface info in rac - oracle database script

Get interface info in RAC - Oracle Database Script.oifcfg iflist -p -n backup0 172.21.56.0 PRIVATE 255.255.254.0 cdnet0 162.168.1.0 PRIVATE 255.255.255.0 cdnet0 169.254.0.0 PUBLIC 255.255.128.0 cdnet1 162.168.2.0 PRIVATE 255.255.255.0 cdnet1 169.254.128.0 PUBLIC 255.255.128.0 pap-ipmp0 172.20.179.128 PUBLIC 255.255.255.128 tan-ipmp0 172.20.128.0 PRIVATE 255.255.252.0 dppp0 162.168.224.0 PRIVATE 255.255.255.0 read more

Get installed sqlpatches in db - oracle database script

Get installed sqlpatches in db - Oracle Database Script.--- From 12c onward set lines 2000 select patch_id,status,description from dba_registry_sqlpatch; --- For 11g and below: set lines 2000 select * from dba_registry_history; read more

Get disktimeout values - oracle database script

get disktimeout values - Oracle Database Script.-- Disk timeout from node to voting disk(disktimeout) crsctl get css disktimeout CRS-4678: Successful get disktimeout 200 for Cluster Synchronization Services. -- Network latency in the node interconnect (Misscount) crsctl get css misscount CRS-4678: Successful get misscount 30 for Cluster Synchronization Services. read more

Get ddl of privileges granted to user - oracle database script

Get DDL of privileges granted to user - Oracle Database Script.SET feedback OFF pages 0 long 900000 lines 20000 pagesize 20000 serveroutput ON accept USERNAME prompt "Enter username :" --This line add a semicolon at the end of each statement execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true); -- This will generate the DDL for the user and add his objects,system and role grants SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username' UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1 UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1 UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1; read more

Get ddl of all tablespaces - oracle database script

Get DDL of all tablespaces - Oracle Database Script.SET heading OFF;SET echo OFF;SET pages 999;SET long 90000;spool ddl_tablespace.sqlSELECT dbms_metadata.get_ddl('TABLESPACE', tb.tablespace_name)FROM dba_tablespaces tb;spool OFF read more

Get database uptime - oracle database script

Get database uptime - Oracle Database Script.SELECT to_char(startup_time, 'DD-MM-YYYY HH24:MI:SS'), floor(sysdate-startup_time) DAYSFROM v$Instance; read more

Get database incarnation info - oracle database script

Get database incarnation info - Oracle Database Script.SET heading OFFSET feedback OFFSELECT 'Incarnation Destination Configuration'FROM dual;SELECT '*************************************'FROM dual;SET heading ONSET feedback ONSELECT INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS, FLASHBACK_DATABASE_ALLOWED FB_OKFROM v$database_incarnation; read more

Get cpu memory info of db server - oracle database script

Get cpu memory info of db server - Oracle Database Script.SET pagesize 200SET lines 200 col nameFOR a21 col stat_nameFOR a25 col valueFOR a13 col commentsFOR a56SELECT STAT_NAME, to_char(VALUE) AS VALUE, COMMENTSFROM v$osstatWHERE stat_name IN ('NUM_CPUS', 'NUM_CPU_CORES', 'NUM_CPU_SOCKETS')UNIONSELECT STAT_NAME, VALUE/1024/1024/1024 || ' GB', COMMENTSFROM v$osstatWHERE stat_name IN ('PHYSICAL_MEMORY_BYTES'); read more

Get cluster_interconnect details - oracle database script

Get cluster_interconnect details - Oracle Database Script.$GRID_HOME/bin/oifcfg getif app-ipmp0 172.21.39.128 GLOBAL PUBLIC loypredbib0 172.16.3.192 GLOBAL cluster_interconnect loypredbib1 172.16.4.0 GLOBAL cluster_interconnectSELECT NAME, IP_ADDRESSFROM v$cluster_interconnects;NAME IP_ADDRESS --------------- ---------------- loypredbib0 172.16.3.193 loypredbib1 172.16.4.1 read more

Get bind values of a sql_id - oracle database script

Get bind values of a sql_id - Oracle Database Script.SELECT sql_id, b. LAST_CAPTURED, t.sql_text sql_text, b.HASH_VALUE, b.name bind_name, b.value_string bind_valueFROM gv$sql tJOIN gv$sql_bind_capture b USING (sql_id)WHERE b.value_string IS NOT NULL AND sql_id='&sqlid' / read more

Get background process details - oracle database script

Get background process details - Oracle Database Script.col ksbddidnFOR a15 col ksmfsnamFOR a20 col ksbdddscFOR a60SET lines 150 pages 5000SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddscFROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsvWHERE ksbdd.indx = ksbdp.indx AND ksbdp.addr = ksmfsv.ksmfsadrORDER BY ksbdd.ksbddidn; read more

Get asm diskgroup details - oracle database script

Get ASM diskgroup details - Oracle Database Script.SELECT name, free_mb, total_mb, free_mb/total_mb*100 AS percentageFROM v$asm_diskgroup; read more

Get asm disk info - oracle database script

Get asm disk info - Oracle Database Script.SET pagesize 2000SET lines 2000SET long 999 col PATHFOR a54SELECT name, PATH, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mbFROM v$asm_disk; read more

Get alert log location in db - oracle database script

Get Alert log location in db - Oracle Database Script.SET pagesize 299SET lines 299 col valueFOR a65SELECT *FROM v$diag_infoWHERE NAME='Diag Trace'; read more

Get active sid of a pl/sql object - oracle database script

Get active sid of a pl/sql object - Oracle Database Script.SELECT sid, sql_id, serial#, status, username, programFROM v$sessionWHERE PLSQL_ENTRY_OBJECT_ID IN (SELECT object_id FROM dba_objects WHERE object_name IN ('&PROCEDURE_NAME')); read more

Get acl details in database - oracle database script

Get ACL details in database - Oracle Database Script.SET lines 200 COL ACL_OWNERFOR A12 COL ACLFOR A67 COL HOSTFOR A34 col PRINCIPALFOR a20 col PRIVILEGEFOR a13SELECT ACL_OWNER, ACL, HOST, LOWER_PORT, UPPER_PORTFROM dba_network_acls;SELECT ACL_OWNER, ACL, PRINCIPAL, PRIVILEGEFROM dba_network_acl_privileges; read more

Ger row_count of all the tables of a schema - oracle database script

Ger row_count of all the tables of a schema - Oracle Database Script.SELECT TABLE_NAME, to_number(extractvalue(dbms_xmlgen.getXMLtype('select /*+ PARALLEL(8) */ count(*) cnt from "&&SCHEMA_NAME".'||TABLE_NAME), '/ROWSET/ROW/CNT')) rows_in_tableFROM dba_TABLESWHERE OWNER='&&SCHEMA_NAME'; read more

Generate resize datafile script - oracle database script

generate resize datafile script - Oracle Database Script.SELECT 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';'FROM (SELECT /*+ rule */ a.tablespace_name, a.file_name, a.bytes/1024/1024 file_size_MB, (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater FROM dba_data_files a , (SELECT file_id, max(block_id) maximum FROM dba_extents GROUP BY file_id) b, dba_extents c, (SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') d WHERE a.file_id= b.file_id AND c.file_id = b.file_id AND c.block_id = b.maximum ORDER BY a.tablespace_name, a.file_name); read more

Generate multiple awr report - oracle database script

Generate multiple AWR report - Oracle Database Script.SET lines 150SET pages 500 col BEGIN_INTERVAL_TIMEFOR a30 col END_INTERVAL_TIMEFOR a30SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIMEFROM dba_hist_snapshotWHERE trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&NO)ORDER BY 1;CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/awrreports';DECLARE -- Adjust before use. l_snap_start NUMBER := 4884; --Specify Initial Snap ID l_snap_end NUMBER := 4892; --Specify End Snap ID l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR'; l_last_snap NUMBER := NULL; l_dbid v$database.dbid%TYPE; l_instance_number v$instance.instance_number%TYPE; l_file UTL_FILE.file_type; l_file_name VARCHAR(50); BEGIN SELECT dbid INTO l_dbid FROM v$database; SELECT instance_number INTO l_instance_number FROM v$instance; FOR cur_snap IN (SELECT snap_id FROM dba_hist_snapshot WHERE instance_number = l_instance_number AND snap_id BETWEEN l_snap_start AND l_snap_end ORDER BY snap_id) LOOP IF l_last_snap IS NOT NULL THEN l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.html', 'w', 32767); FOR cur_rep IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id))) LOOP UTL_FILE.put_line(l_file, cur_rep.output); END LOOP; UTL_FILE.fclose(l_file); END IF; l_last_snap := cur_snap.snap_id; END LOOP; EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END; / read more

Generate addm report - oracle database script

Generate addm report - Oracle Database Script.cd $ORACLE_HOME/rdbms/ADMIN SQL> @addmrpt.sql Specify the BEGINAND END SNAPSHOT Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter valueFOR begin_snap: 1058 BEGIN SNAPSHOT Id specified: 1058 Enter valueFOR end_snap: 1059 END SNAPSHOT Id specified: 1059 read more

Gather stats for schema - oracle database script

Gather stats for schema - Oracle Database Script.BEGIN dbms_stats.gather_schema_stats( ownname => 'SCOTT', --- schema name  options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 24 ); END; / read more

Gather stats for a table - oracle database script

Gather stats for a table - Oracle Database Script.BEGIN DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SCOTT', tabname => 'TEST', CASCADE => TRUE, ---- For collecting stats for respective indexes  method_opt=>'for all indexed columns size 1', granularity => 'ALL', estimate_percent =>dbms_stats.auto_sample_size,  degree => 8); END; / -- For a single table partition BEGIN DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SCOTT', tabname => 'TEST', --- TABLE NAME partname => 'TEST_JAN2016' --- PARTITOIN NAME method_opt=>'for all indexed columns size 1', GRANULARITY => 'APPROX_GLOBAL AND PARTITION', degree => 8); END; / read more

Flush a sql query from cursor - oracle database script

Flush a sql query from cursor - Oracle Database Script.-- First get the address, hash_value of the sql_id select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '5qd8a442c328k'; ADDRESS HASH_VALUE --------------- ------------ C000007067F39FF0 4000666812 -- Now flush the query SQL> exec DBMS_SHARED_POOL.PURGE ('C000007067F39FF0, 4000666812', 'C'); Note : For RAC, same need to be executed on all the nodes . read more

Flashback query as of timestamp - oracle database script

Flashback query as of timestamp - Oracle Database Script.SELECT *FROM AODBA.EMP AS OF TIMESTAMP TO_TIMESTAMP('2017-01-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');SELECT *FROM AODBA.EMP AS OF TIMESTAMP SYSDATE -1/24; read more

Flashback db using restore point - oracle database script

Flashback db using restore point - Oracle Database Script.--- Below are the steps for flashback database to a guaranteed restore point; 1. Get the restore point name: SQL> select NAME,time from v$restore_point; NAME                                                            TIME --------------------------------          ----------------------------------------------- GRP_1490100093811                         21-MAR-17 03.41.33.000000000 PM 2. Shutdown database and start db in Mount stage: shutdown immediate; startup mount; 3. flashback db to restore point: flashback database to restore point GRP_1490100093811; 4. Open with resetlog: alter database open resetlogs: read more

Flashback area usage info - oracle database script

Flashback area usage info - Oracle Database Script.SELECT *FROM V$FLASH_RECOVERY_AREA_USAGE; read more

Flashback a table to point in time - oracle database script

Flashback a table to point in time - Oracle Database Script.ALTER TABLE AODBA.EMP ENABLE ROW MOVEMENT;FLASHBACK TABLE AODBA.EMP TO TIMESTAMP TO_TIMESTAMP('2017-01-10 09:00:00', YYYY-MM-DD HH24:MI:SS'); read more

Flashback a procedure/package - oracle database script

Flashback a procedure/package - Oracle Database Script.--- Like, tables ,If you have dropped or recreated a package/procedure, by using flashback ,we can get the proc code, before drop. -- get the object_id SQL> select object_id from dba_objects where owner='AODBA' and object_name='VOL_DISCOUNT_INSERT'; OBJECT_ID ---------- 2201943 -- Now get the flashback code using timestamp select SOURCE from sys.source$ as of timestamp to_timestamp('23-Apr-2017 10:00:20','DD-Mon-YYYY hh24:MI:SS') where obj#=2201943 ; read more

Find who locked your account - oracle database script

Find who locked your account - Oracle Database Script.-- Return code 1017 ( INVALID LOGIN ATTEMPT) -- Return code 28000 ( ACCOUNT LOCKED) set pagesize 1299 set lines 299 col username for a15 col userhost for a13 col timestamp for a39 col terminal for a23 SELECT username,userhost,terminal,timestamp,returncode FROM dba_audit_session WHERE username='&USER_NAME' and returncode in (1017,28000); read more

Find waitevents in database - oracle database script

Find waitevents in database - Oracle Database ScriptSELECT a.sid, substr(b.username, 1, 10) username, substr(b.osuser, 1, 10) osuser, substr(b.program||b.module, 1, 15) program, substr(b.machine, 1, 22) machine, a.event, a.p1, b.sql_hash_valueFROM v$session_wait a, V$session bWHERE b.sid=a.sid AND a.event NOT IN('SQL*Net message from client', 'SQL*Net message to client', 'smon timer', 'pmon timer') AND username IS NOT NULLORDER BY 6 / read more

Find top disk_reads by an user - oracle database script

Find top disk_reads by an user - Oracle Database Script.SELECT username users, round(DISK_READS/Executions) DReadsExec, Executions EXEC, DISK_READS DReads, sql_textFROM gv$sqlarea a, dba_users bWHERE a.parsing_user_id = b.user_id AND Executions > 0 AND DISK_READS > 100000ORDER BY 2 DESC; read more

Find the temp usage of sessions - oracle database script

Find the temp usage of sessions - Oracle Database Script.SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, a.inst_id AS INSTANCE, a.sid||','||a.serial# AS sid_serial, NVL(a.username, '(oracle)') AS username, a.program, a.status, a.sql_id FROM gv$session a, gv$sort_usage b, gv$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id ORDER BY temp_size desc / read more

Find the table partition keys - oracle database script

Find the table partition keys - Oracle Database Script.--- describes the partitioning key columns for all partitioned objects of a schema set pagesize 200 set lines 200 set long 999 col owner for a12 col name for a20 col object_type for a20 col column_name for a32 SELECT owner, NAME, OBJECT_TYPE,column_name FROM dba_part_key_columns where owner='&OWNER' ORDER BY owner, NAME; read more

Find the locked objects - oracle database script

Find the locked objects - Oracle Database Script.SET PAGESIZE 1000SET VERIFY OFF COLUMN OWNER FORMAT A20 COLUMN username FORMAT A20 COLUMN object_owner FORMAT A20 COLUMN object_name FORMAT A30 COLUMN locked_mode FORMAT A15SELECT b.inst_id, b.session_id AS sid, NVL(b.oracle_username, '(oracle)') AS username, a.owner AS object_owner, a.object_name, Decode(b.locked_mode, 0, 'None', 1, 'Null (NULL)', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share (S)', 5, 'S/Row-X (SSX)', 6, 'Exclusive (X)', b.locked_mode) locked_mode, b.os_user_nameFROM dba_objects a, gv$locked_object bWHERE a.object_id = b.object_idORDER BY 1, 2, 3, 4;SET PAGESIZE 14SET VERIFY ON read more

Find the grid version - oracle database script

Find the grid version - Oracle Database Script.SYNTAX - $GRID_HOME/bin/crsctl query crs softwareversion $GRID_HOME/bin/crsctl query crs softwareversion HOST-aodba1 read more

Find the cluster name in rac - oracle database script

Find the cluster name in RAC - Oracle Database Script.$GRID_HOME/bin/cemutlo -nOR $GRID_HOME/bin/olsnodes -c read more

Find sql baseline info from sql_id - oracle database script

Find sql baseline info from sql_id - Oracle Database Script.-- Pass the sql_id to get the respective sql baseline SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID') read more

Find sessions generating undo - oracle database script

Find sessions generating undo - Oracle Database Script.SELECT a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocksFROM v$session a, v$transaction bWHERE a.saddr=b.ses_addr ; read more

Find sessions generating alot of redo - oracle database script

Find sessions generating alot of redo - Oracle Database ScriptSET lines 2000SET pages 1000 col sidFOR 99999 col nameFOR a09 col usernameFOR a14 col PROGRAMFOR a21 col MODULEFOR a25SELECT s.sid, sn.SERIAL#,n.name, round(value/1024/1024, 2) redo_mb, sn.username, sn.status, substr (sn.program, 1, 21) "program", sn.type, sn.module, sn.sql_idFROM v$sesstat sJOIN v$statname n ON n.statistic# = s.statistic# join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by redo_mb desc; read more

Find queries triggered from a procedure - oracle database script

Find queries triggered from a procedure - Oracle Database Script.-- Below script will provide the dependent queries getting triggered from a procedure.  SELECT s.sql_id, s.sql_text FROM gv$sqlarea s JOIN dba_objects o ON s.program_id = o.object_id and o.object_name = '&procedure_name'; read more

Find optimal undo retention size - oracle database script

Find optimal undo retention size - Oracle Database Script.SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value, 1, 25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024 * 1024) "NEEDED UNDO SIZE [MByte]"FROM (SELECT SUM(a.bytes) undo_sizeFROM gv$datafile a, gv$tablespace b, dba_tablespaces cWHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#) d, gv$parameter e, gv$parameter f, (SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec FROM v$undostat) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'; read more

Find ocr and vd location - oracle database script

Find OCR and VD location - Oracle Database Script.-- Find voting disk location $GRID_HOME/bin/crsctl query css votedisk -- Find OCR location. $GRID_HOME/bin/ocrcheck read more

Find long running operations - oracle database script

Find long running operations - Oracle Database Script.SELECT sid, inst_id, opname, totalwork, sofar, start_time, time_remainingFROM gv$session_longopsWHERE totalwork<>sofar / read more

Find locks present in database - oracle database script

Find locks present in database - Oracle Database Script.col session_id head 'Sid' form 9999 col object_name head "Table|Locked" form a30 col oracle_username head "Oracle|Username" form a10 TRUNCATE col os_user_name head "OS|Username" form a10 TRUNCATE col process head "Client|Process|ID" form 99999999 col mode_held form a15SELECT lo.session_id, lo.oracle_username, lo.os_user_name, lo.process, do.object_name, decode(lo.locked_mode, 0, 'None', 1, 'Null', 2, 'Row Share (SS)', 3, 'Row Excl (SX)', 4, 'Share', 5, 'Share Row Excl (SSX)', 6, 'Exclusive', to_char(lo.locked_mode)) mode_heldFROM v$locked_object lo, dba_objects DOWHERE lo.object_id = do.object_idORDER BY 1, 5 / read more

Find duplicate rows in table - oracle database script

Find duplicate rows in table - Oracle Database Script.--- Reference metalink id - 332494.1 -- Save as duplicate.sql and run as @duplicate.sql REM This is an example SQL*Plus Script to detect duplicate rows from REM a table. REM set echo off set verify off heading off undefine t undefine c prompt prompt prompt Enter name of table with duplicate rows prompt accept t prompt 'Table: ' prompt select 'Table '||upper('&&t') from dual; describe &&t prompt prompt Enter name(s) of column(s) which should be unique. If more than prompt one column is specified, you MUST separate with commas. prompt accept c prompt 'Column(s): ' prompt select &&c from &&t where rowid not in (select min(rowid) from &&t group by &&c) / read more

Find current running sqls - oracle database script

find current running sqls - Oracle Database Script.SELECT sesion.sid, sesion.username, optimizer_mode, hash_value, address, cpu_time, elapsed_time, sql_textFROM v$sqlarea sqlarea, v$session sesionWHERE sesion.sql_hash_value = sqlarea.hash_value AND sesion.sql_address = sqlarea.address AND sesion.username IS NOT NULL; read more

Find column usage statistics - oracle database script

Find column usage statistics - Oracle Database Script.SET lines 150SET pages 500 col TABLE_NAMEFOR a20 col COLUMN_NAMEFOR a20SELECT a.object_name TABLE_NAME, c.column_name, equality_preds, equijoin_preds, range_preds, like_predsFROM dba_objects a, col_usage$ b, dba_tab_columns cWHERE a.object_id=b.OBJ# and c.COLUMN_ID=b.INTCOL# and a.object_name=c.table_name and b.obj#=a.object_id and a.object_name='&table_name' and a.object_type='TABLE' and a.owner='&owner' order by 3 desc,4 desc, 5 desc; read more

Find buffer cache usage - oracle database script

Find buffer cache usage - Oracle Database Scriptcol object_name format a30 col to_total format 999.99SELECT OWNER, object_name, object_type, COUNT, (COUNT / value) * 100 to_totalFROM (SELECT a.owner, a.object_name, a.object_type, count(*) COUNT FROM dba_objects a, x$bh b WHERE a.object_id = b.obj AND a.owner NOT IN ('SYS', 'SYSTEM') GROUP BY a.owner, a.object_name, a.object_type ORDER BY 4), v$parameterWHERE name = 'db_cache_size' AND (COUNT / value) * 100 .005ORDER BY to_total DESC / read more

Find blocking sessions - oracle database script

Find blocking sessions - Oracle Database Script.SELECT s.inst_id, s.blocking_session, s.sid, s.serial#, s.seconds_in_waitFROM gv$session sWHERE blocking_session IS NOT NULL; read more

Find blocking sessions from ash - oracle database script

Find blocking sessions from ASH - Oracle Database Script.--- Query will list the blocking session details between SYSDATE - 1 AND SYSDATE-23/24 ( PAST) set pagesize 50 set linesize 120 col sql_id format a15 col inst_id format '9' col sql_text format a50 col module format a10 col blocker_ses format '999999' col blocker_ser format '999999' SELECT distinct a.sql_id , a.inst_id, a.blocking_session blocker_ses, a.blocking_session_serial# blocker_ser, a.user_id, s.sql_text, a.module,a.sample_time FROM GV$ACTIVE_SESSION_HISTORY a, gv$sql s where a.sql_id=s.sql_id and blocking_session is not null and a.user_id <> 0 -- exclude SYS user and a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24 / read more

Find active transactions in db - oracle database script

Find active transactions in db - Oracle Database Script.col name format a10 col username format a8 col osuser format a8 col start_time format a17 col status format a12 tti 'Active transactions'SELECT s.sid, username, t.start_time, r.name, t.used_ublk "USED BLKS", decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) statusFROM sys.v_$TRANSACTION t, sys.v_$rollname r, sys.v_$SESSION sWHERE t.xidusn = r.usn AND t.ses_addr = s.saddr / read more

Export import statistics - oracle database script

Export import statistics - Oracle Database Script.--- Create staging table to store the statistics data exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS'); -- Export stats exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true); -- Import stats exec dbms_stats.import_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true); read more

Execution detail of a sql_id in cursor - oracle database script

execution detail of a sql_id in cursor - Oracle Database Script.SELECT MODULE, parsing_schema_name, inst_id, sql_id, plan_hash_value, child_number, sql_fulltext, to_char(last_active_time, 'DD/MM/YY HH24:MI:SS'), sql_plan_baseline, executions, elapsed_time/executions/1000/1000, rows_processedFROM gv$sqlWHERE sql_id IN ('&sql_id'); read more

Execute runcluvfy.sh for rac precheck - oracle database script

execute runcluvfy.sh for RAC precheck - Oracle Database Script.-- Runcluvfy.sh script is available after unzipping the grid software. syntax - ./runcluvfy.sh stage -pre crsinst -n host1,host2,host3 -verbose ./runcluvfy.sh stage -pre crsinst -n classpredb1,classpredb2 -verbose read more

Enable/disable db/instance using srvctl - oracle database script

Enable/disable db/instance using srvctl - Oracle Database Script.-- ENABLE - Reenables management by Oracle Restart for a component. -- DISABLE - Disables management by Oracle Restart for a component. srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME srvctl enable database -d DB_UNIQUE_NAME srvctl disable database -d DB_UNIQUE_NAME read more

Enable/disable autorestart of crs - oracle database script

Enable/Disable autorestart of crs - Oracle Database Script.-- Run as root user( $GRID_HOME/bin/crsctl enable crs  CRS-4622: Oracle High Availability Services autostart is enabled. $GRID_HOME/bin/crsctl disable crs CRS-4621: Oracle High Availability Services autostart is disabled. read more

Enable tracing for asmcmd - oracle database script

Enable tracing for asmcmd - Oracle Database Script.$export DBI_TRACE=1 $ asmcmd read more

Enable tracing for a listener - oracle database script

Enable tracing for a listener - Oracle Database Script.-SET TO the listener you want TO trace LSNRCTL>SET cur LISTENER_TEST -- Enable Trace: LSNRCTL> set trc_level ADMIN Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST))) LISTENER_TEST parameter "trc_level" set to admin The command completed successfully read more

Enable trace for srvctl commands - oracle database script

Enable trace for srvctl commands - Oracle Database Script.-- set this to enable trace at os SRVM_TRACE=true export SRVM_TRACE -- run any srvctl command srvctl status database -d ORACL read more

Enable trace for rman - oracle database script

Enable trace for RMAN - Oracle Database Script.-- To diagnose rman script, use trace as below. spool trace to '/tmp/rman_trace.out'; report schema; list backup summary; list backup of datafile 1; list copy of datafile 1; spool trace off; read more

Enable trace for a sql_id - oracle database script

Enable trace for a sql_id - Oracle Database Script.ALTER SYSTEMSET EVENTS 'sql_trace [sql:8krc88r46raff]'; read more

Enable trace for a session - oracle database script

Enable trace for a session - Oracle Database Script.EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>321, serial#=>1234, sql_trace=>FALSE); --- Get the trace file name SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = 321; read more

Enable incremental stats collection - oracle database script

Enable incremental stats collection - Oracle Database Script.-- Check the status of incremental pref select dbms_stats.get_prefs('INCREMENTAL', tabname=>'EMPLOYEE',ownname=>'SCOTT') from dual; FALSE -- Enable incremental stats collection SQL> exec DBMS_STATS.SET_TABLE_PREFS('SCOTT','EMPLOYEE','INCREMENTAL','TRUE'); PL/SQL procedure successfully completed. -- Check the pref again: select dbms_stats.get_prefs('INCREMENTAL', tabname=>'EMPLOYEE',ownname=>'SCOTT') from dual; TRUE read more

Enable flashback for database - oracle database script

Enable flashback for database - Oracle Database Script.-- Make sure database is in archivelog mode alter system set db_recovery_file_dest_size=10G scope=both; alter system set db_recovery_file_dest='/dumparea/FRA/B2BRBMT3' scope=both; alter database flashback on; read more

Enable block change tracking - oracle database script

Enable block change tracking - Oracle Database Script.ALTER DATABASE ENABLE BLOCK CHANGE tracking USING FILE '/export/home/oracle/RMAN/TESTDB/TRACKING_FILE/block_change_TESTDB.log'; -- Check status: select filename,status from v$block_change_tracking; read more

Enable archivelog mode in standalone db - oracle database script

Enable archivelog mode in standalone db - Oracle Database Script.-- Set log archive dest alter system set log_archive_dest_1='LOCATION=/uv1249/arch/PROD' scope=spfile; -- Enable archive mode in mount stage shutdown immediate; startup mount; alter database archivelog; -- Open db alter database open; read more

Dropping partition 11g/12c - oracle database script

Dropping partition 11g/12c - Oracle Database Script.-- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> DROP PARTITION < PARTITION_NAME> < UPDATE GLOBAL INDEXES(optional)>; --- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present ALTER TABLE CMADMIN.AODBA DROP PARTITION AODBA_JAN UPDATE GLOBAL INDEXES; --- In oracle 12c, we can drop multiple partitions in one command ALTER TABLE CMADMIN.AODBA DROP PARTITIONS AODBA_JAN, AODBA_FEB, AODBA_MAR UPDATE GLOBAL INDEXES; read more

Drop asm diskgroup - oracle database script

Drop ASM diskgroup - Oracle Database Script.-- To drop a diskgroup, make sure the diskgroup has been dismounted from all the remote nodes,  It should be mounted only on the local nodes, where we will run the drop command. drop diskgroup NSMREDOA including contents; read more

Drop an asm disk - oracle database script

drop an asm disk - Oracle Database Script.-----Dropping one disk: alter diskgroup data drop disk DATA_ASM0001; -----Dropping multiple disk: alter diskgroup data drop disk DATA_ASM0001,DATA_ASM00002, DATA_ASM0003 rebalance power 100; ---- Monitoring the rebalance operation: select * from v$asm_operation; read more

Drop a sql profile - oracle database script

drop a sql profile - Oracle Database Script.BEGIN DBMS_SQLTUNE.drop_sql_profile (name => '&sql_profile', IGNORE => TRUE);END;/ You can GET the respective sql_profile OF a sql_idFROM below:SELECT DISTINCT p.name sql_profile_name, s.sql_idFROM dba_sql_profiles p, DBA_HIST_SQLSTAT sWHERE p.name=s.sql_profile AND s.sql_id='&sql_id'; read more

Drop a sql baseline - oracle database script

drop a sql baseline - Oracle Database Script.DECLARE drop_result pls_integer;BEGIN drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(plan_name => '&sql_plan_baseline_name');dbms_output.put_line(drop_result);END;/ You can GET the SQL baselineFROM a sql_idFROM below command:SELECT sql_handle, plan_nameFROM dba_sql_plan_baselinesWHERE signature IN (SELECT exact_matching_signature FROM gv$sql WHERE sql_id='&SQL_ID'); read more

Disable/enable sql profile - oracle database script

Disable/enable sql profile - Oracle Database Script.EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('&sql_profile_name', 'STATUS', 'DISABLED'); read more

Disable/enable all triggers of schema - oracle database script

Disable/enable all triggers of schema - Oracle Database Script.-----  Connect to the user and run this. BEGIN FOR i IN (SELECT trigger_name FROM user_triggers) LOOP EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' DISABLE'; END LOOP; END; / read more

Dictionary cache hit ratio - oracle database script

Dictionary cache hit ratio - Oracle Database Script.SELECT sum(gets) AS "Gets", sum(getmisses) AS "Misses", (1-(sum(getmisses)/sum(gets)))*100 AS "CACHE HIT RATIO"FROM gv$rowcache;NOTE - CACHE HIT RATIO SHOULD BEMORE THAN 95 PERCENT. read more

Delete statistics - oracle database script

Delete statistics - Oracle Database Script.-- Delete statistics of the complete database EXEC DBMS_STATS.delete_database_stats; -- Delete statistics of a single schema EXEC DBMS_STATS.delete_schema_stats('AODBA'); -- Delete statistics of single tabale EXEC DBMS_STATS.delete_table_stats('AODBA', 'DEPT'); -- Delete statistics of a column EXEC DBMS_STATS.delete_column_stats('AODBA', 'DEPT', 'CLASS'); --Delete statistics of an index EXEC DBMS_STATS.delete_index_stats('AODBA', 'CLASS_IDX'); --Delete dictionary statistics in db EXEC DBMS_STATS.delete_dictionary_stats; read more

Delete archive older than 1 day - oracle database script

delete archive older than 1 day - Oracle Database Script.DELETE ARCHIVELOG ALL COMPLETEDBEFORE 'sysdate-1';CROSSCHECK ARCHIVELOG ALL;DELETE EXPIRED ARCHIVELOG ALL; read more

Db optimizer processing rate - oracle database script

db optimizer processing rate - Oracle Database Script.SELECT OPERATION_NAME, DEFAULT_VALUEFROM V$OPTIMIZER_PROCESSING_RATEWHERE OPERATION_NAME IN ('IO_BYTES_PER_SEC', 'CPU_BYTES_PER_SEC', 'CPU_ROWS_PER_SEC'); read more

Database growth per month - oracle database script

Database growth per month - Oracle Database Script.SELECT to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth IN GBFROM sys.v_$DATAFILEWHERE to_char(creation_time, 'RRRR')='&YEAR_IN_YYYY_FORMAT'GROUP BY to_char(creation_time, 'MM-RRRR')ORDER BY to_char(creation_time, 'MM-RRRR'); read more

Current sga usage - oracle database script

Current SGA usage - Oracle Database Script.SELECT round(used.bytes /1024/1024, 2) used_mb, round(free.bytes /1024/1024, 2) free_mb, round(tot.bytes /1024/1024, 2) total_mbFROM (SELECT sum(bytes) bytes FROM v$sgastat WHERE name != 'free memory') used , (SELECT sum(bytes) bytes FROM v$sgastat WHERE name = 'free memory') FREE, (SELECT sum(bytes) bytes FROM v$sgastat) tot / read more

Create/drop flashback restore point - oracle database script

Create/drop flashback restore point - Oracle Database Script.-- To create a guarantee flashback restore point; create restore point BEFORE_UPG guarantee flashback database; -- Check the restore_points present in database select * from v$restore_point; -- Drop restore point; drop restore point BEFORE_UPG; read more

Create sql baseline from cursor cache - oracle database script

Create sql baseline from cursor cache - Oracle Database Script.DECLARE l_plans_loaded PLS_INTEGER;BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '&sql_id');END;/ -- Create baseline with a particular hash value DECLARE   l_plans_loaded  PLS_INTEGER; BEGIN   l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(     sql_id => '&sql_id', plan_hash_value => '&plan_hash_value'); END; / read more

Create password file in asm dg - oracle database script

Create password file in ASM DG - Oracle Database Script.-- For oracle 12c  only  ASMCMD> pwcreate –dbuniquename {db_unique_name} {file_path}  {sys_password} ASMCMD> pwcreate --dbuniquename PRDPRE +DATA/PWDFILE/pwdPRDPRE oracle --- For all version. orapwd file='+DATA/orapwPRODPRE' ENTRIES=10 DBUNIQUENAME='PRODPRE' read more

Create baselines for all sqls of a schema - oracle database script

create baselines for all sqls of a schema - Oracle Database Script.DECLARE nRet NUMBER;BEGIN nRet := dbms_spm.load_plans_from_cursor_cache(attribute_name => 'PARSING_SCHEMA_NAME', attribute_value => '&schema_name');END; read more

Create asm disk in Linux using oracleasm

Create ASM disk in Linux using oracleasm -- Check the asm disk labelling #/etc/init.d/oracleasm querydisk /dev/sdn1 Device "/dev/sdn" is not marked as an ASM disk -- Create asm disk # /etc/init.d/oracleasm createdisk ARCDATA /dev/sdn1 Marking disk "ARCDATA" as an ASM disk: [ OK ] -- Check the asm disk labelling # /etc/init.d/oracleasm querydisk /dev/sdn1 Device "/dev/sdn1" is marked an ASM disk with the label "ARCDATA" -- List the asm disks present # /etc/init.d/oracleasm listdisks ARCDATA read more

Copy asm file to remote asm instance - oracle database script

copy asm file to remote asm instance - Oracle Database Script.--- ASM file can be copied to remote asm instance(diskgroup) using asmcmd command. SYNTAX - asmcmd> cp - -port asm_port  file_name remote_asm_user/remote_asm_pwd@remote_host:Instancce_name:TARGET_ASM_PATH ASMCMD> cp --port 1521 s_srv_new21.dbf sys/[email protected].+ASM1:+ARCL/s_srv_new21.dbf read more

Copy archive from asm to mount point - oracle database script

Copy archive from ASM to Mount point - Oracle Database Script.--- Copy archive log from ASM to regular mount point using RMAN: --- Connect to RMAN in RAC db RMAN> copy archivelog '+B2BSTARC/thread_2_seq_34.933' to '/data/thread_2_seq_34.933'; read more

Clock synchronization status in rac - oracle database script

Clock Synchronization status in RAC - Oracle Database Script.-- Clock Synchronization across the cluster nodes cd $GRID_HOME/bin cluvfy comp clocksync -n all - Check whether ctss or ntp is running crsctl check ctss CRS-4700: The Cluster Time Synchronization Service is in Observer mode. Observer means - Time sync between nodes are taken care by NTP Active means - Time sync between nodes are taken care by CTSS read more

Cleanup orphaned datapump jobs - oracle database script

Cleanup orphaned datapump jobs - Oracle Database Script.-- Find the orphaned Data Pump jobs: SELECT owner_name, job_name, rtrim(operation) "OPERATION", rtrim(job_mode) "JOB_MODE", state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' and state='NOT RUNNING' ORDER BY 1,2; -- Drop the tables SELECT 'drop table ' || owner_name || '.' || job_name || ';' FROM dba_datapump_jobs WHERE state='NOT RUNNING' and job_name NOT LIKE 'BIN$%' read more

Check stale stats - oracle database script

Check stale stats - Oracle Database Script.--- STALE STATS FOR TABLE select owner,table_name,STALE_STATS from dba_tab_statistics where owner='&SCHEMA_NAME' and table_name='&TABLE_NAME'; -- FOR INDEX select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and index_name='&INDEX_NAME'; read more

Check open cursors - oracle database script

Check open cursors - Oracle Database Script.-- Current open cursor select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'; -- Max allowed open cursor and total open cursor select max(a.value) as highest_open_cur, p.value as max_open_cur from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value; read more

Check cluster component status - oracle database script

check cluster component status - Oracle Database Script.$GRID_HOME/bin/crsctl stat res -t $GRID_HOME/bin/crsctl CHECK crs $GRID_HOME/bin/crsctl CHECK cssd $GRID_HOME/bin/crsctl CHECK crsd $GRID_HOME/bin/crsctl CHECK evmd read more

Characterset info of database - oracle database script

Characterset info of database - Oracle Database Script.SET pagesize 200SET lines 200SELECT PARAMETER, valueFROM v$nls_parametersWHERE PARAMETER LIKE 'NLS_%CHAR%'; read more

Change asm rebalance power - oracle database script

Change asm rebalance power - Oracle Database Script.-- Default value of asm_power_limit. SQL> show parameter asm_power_limit NAME                                 TYPE        VALUE ------------------------------------ ----------- ----------------------------- asm_power_limit                      integer     1 -- Check for ongoing rebalance operations and their power. select INST_ID,GROUP_NUMBER, OPERATION, STATE, POWER, EST_RATE, EST_MINUTES from GV$ASM_OPERATION; - Alter the asm rebalance.  alter diskgroup SALDATA rebalance power 4; read more

Buffer cache hit ratio - oracle database script

Buffer Cache hit ratio - Oracle Database Script.SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100, 2) "Cache Hit Ratio"FROM v$sysstat cur, v$sysstat con, v$sysstat phyWHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads' / read more

Backup archivelogs using rman - oracle database script

backup archivelogs using RMAN - Oracle Database Script.---  Backup all archivelogs known to controlfile backup archivelog all; -- Backup all archivelogs known to controlfile and delete them once backed up backup archivelog all delete input ; -- Backup archivlogs known to controlfile and the logs which haven't backed up once also backup archivelog all not backed up 1 times; read more

Backup archive between 2 sequence number - oracle database script

backup archive between 2 sequence number - Oracle Database Script.--- For taking backup of archivelog between seq number 1000 to 1050 RMAN> backup format '/archive/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 1000 until sequence 1050; -- For RAC ,need to mention the thread number also RMAN> backup format '/archive/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 1000 until sequence 1050 thread 2; read more

Archive generation per hour - oracle database script

Archive generation per hour - Oracle Database Script.SET lines 299SELECT TO_CHAR(TRUNC(FIRST_TIME), 'Mon DD') "DG Date", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '9999') "12AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '9999') "01AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '9999') "02AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '9999') "03AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '9999') "04AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '9999') "05AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '9999') "06AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '9999') "07AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '9999') "08AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '9999') "09AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '9999') "10AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '9999') "11AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '9999') "12PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '9999') "1PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '9999') "2PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '9999') "3PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '9999') "4PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '9999') "5PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '9999') "6PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '9999') "7PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '9999') "8PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '9999') "9PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '9999') "10PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '9999') "11PM"FROM V$LOG_HISTORYGROUP BY TRUNC(FIRST_TIME)ORDER BY TRUNC(FIRST_TIME) DESC / read more

Alter/disable a sql plan baseline - oracle database script

Alter/disable a sql plan baseline - Oracle Database Script.--- To disable a baseline: Begin dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_SQL_5818768f40d7be2a', plan_name => 'SQL_PLAN_aaxsg8yktm4h100404251', attribute_name=> 'enabled', attribute_value=>'NO'); END; / Begin dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_SQL_5818768f40d7be2a', plan_name => 'SQL_PLAN_aaxsg8yktm4h100404251', attribute_name=> 'fixed', attribute_value=>'NO'); END; / -- To enable again, just modify the attribute_value to YES, read more

Add/remove instance using srvctl - oracle database script

Add/remove instance using srvctl - Oracle Database Script.-- SYNTAX FOR REMOVING INSTANCE ---srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME e.g srvctl remove instance -d PRODB - I PRODB1 -- SYNTAX FOR ADDING INSTANCE  --- srvctl add instance –d db_unique_name –i inst_name -n node_name e.g srvctl add instance -d PRODB - i PRODB1 -n rachost1 read more

Add/remove db using srvctl - oracle database script

add/remove db using srvctl - Oracle Database Script.--- SYNTAX FOR REMOVING DB SERVICE:  ---srvctl remove database -d db_unique_name [-f] [-y] [-v] e.g: srvctl remove database -d PRODB -f -y --- SYNTAX FOR ADDING DB SERVICE : -- srvctl add database -d db_unique_name -o ORACLE_HOME  [-p spfile] e.g: srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora read more

Add/remove a service - oracle database script

Add/remove a service - Oracle Database Script.srvctl add servicec -d {DB_NAME} -s {SERVICE_NAME} -r {"preferred_list"} -a {"available_list"} [-P {BASIC | NONE | PRECONNECT}] EXAMPLE: --------------- srvctl add service -d PREDB -s PRDB_SRV -r "PREDB1,PREDB2" -a "PREDB2" -P BASIC REMOVING A SERVICE: ------------------------------------------ SYNTAX: ------------- srvctl remove service -d {DB_NAME} -s {SERVICE_NAME} EXAMPLE: -------- srvctl remove service -d PREDB -s PRDB_SRV read more

Adding partitions 11g/12c - oracle database script

Adding partitions 11g/12c - Oracle Database Script.-- SYNTAX : ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD PARTITION < PARTITION_NAME> VALUES LESS THAN < HIGH_VALUE> TABLESPACE <TABLESPACE_NAME > < UPDATE GLOBAL INDEXES(optional)>; -- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present ALTER TABLE CMADMIN.AODBA ADD PARTITION AODBA_JAN VALUES LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS  UPDATE GLOBAL INDEXES;   -- In oracle 12c(new feature), we can add multiple partition in one command: ALTER TABLE CMADMIN.AODBA ADD PARTITION AODBA_JAN VALUES LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS, PARTITION AODBA_FEB VALUES LESS THAN (TO_DATE('01-MAR-2016','DD-MON-YYYY')) TABLESPACE USERS, PARTITION AODBA_MAR VALUES LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY')) TABLESPACE USERS, UPDATE GLOBAL INDEXES; read more

10053 optimizer trace - oracle database script

10053 OPTIMIZER TRACE - Oracle Database Script.BEGIN dbms_sqldiag.dump_trace(p_sql_id=>'dmx08r6ayx800', p_child_number=>0, p_component=>'Compiler', p_file_id=>'TEST_OBJ3_TRC');END;/ read more

How to create a new document in MongoDB

Use db.collection.insert() method to insert new document to MongoDB collection. You don’t need to create the collection first. Insert method will automatically create collection if not exists.Syntax db.COLLECTION_NAME.insert(document) Insert Single DocumentInsert single ducument using the insert() method. It’s required a json format of document to pass as arguments. db.users.insert({ "id": 1001, "user_name": "A", "name": [ {"first_name": "A"}, {"middle_name": ""}, {"last_name": "D"} ], "email": "ad@aodba", "designation": "Some Guy", "location": "Global"})Output:WriteResult({ “nInserted” : 1 })Insert Multiple DocumentsTo insert multiple documents in single command. The best way to create an array of documents like following: var users = [ { "id": 1002, "user_name": "jack", "name": [ {"first_name": "Jack"}, {"middle_name": ""}, {"last_name": "Daniel"} ], "email": "ao@gmail", "designation": "Worker", "location": "US" }, { "id": 1003, "user_name": "Mark", "name": [ {"first_name": "Mark"}, {"middle_name": "S"}, {"last_name": "Henry"} ], "email": "ao@aodba", "designation": "DBA", "location": "US" }];Now pass the array as argument to insert() method to insert all documents. db.users.insert(users);Output:BulkWriteResult({ "writeErrors" : [ ], "writeConcernErrors" : [ ], "nInserted" : 2, "nUpserted" : 0, "nMatched" : 0, "nModified" : 0, "nRemoved" : 0, "upserted" : [ ]}) read more

How to create a new Database in MongoDB

MongoDB does not provide commands to create a database."weird"  You can use use dbName statement to select a database in the mongo shell. Use the following example:Syntax:use DATABASE_NAMELimitationsMongoDB database names cannot be empty and must have fewer than 64 characters.Create DatabaseFirst, of the all, Use below command to select database. To create a new database make sure it not exists.use newdbYou have a database name selected. Now you need to insert at least one document to keep this database. As you have already executed use command above, so all the statements will be executed on above database.db.users.insert({ id: 1 })Show Databases – After inserting the first record the database will be listed in show dbs command.show dbslocal 0.000GBnewdb 0.000GBAlso, You can run db command to find current selected database.dbnewdb read more

How to Create/List and Rename a MongoDB Collection a

What is a MongoDB Collection A grouping of MongoDB documents. A collection is the equivalent of an RDBMS table. A collection exists within a single database. Collections do not enforce a schema. Documents within a collection can have different fields. Typically, all documents in a collection have a similar or related purpose.SyntaxUse db.createCollection() command to create collection. You need to specify the name of collection, Also can provide some optional options like memory size and indexing.db.createCollection(name, options) MongoDB – Create CollectionLogin to Mongo shell and select your database with the following command. use mydbNow create your collection with createCollection() command. The below command will create collection named mycol in current database. db.createCollection("aodba") { "ok" : 1 }MongoDB – Show CollectionUse show collections command from MongoDB shell to list all collection created in the current database. First, select the database you want to view the collection. use mydb show collections-- output aodba....MongoDB Rename CollectionUse db.collection.renameCollection() method to rename existing collection in MongoDB database.Syntax:db.collection.renameCollection(target, dropTarget)Example:For example, You have a collection “aodba“. Let’s use the following command on mongo Shell to correct collection name to “ao“.db.AODBA.renameCollection("ao")Output:{ "ok" : 1 } read more

How to access MongoDb with Mongo Shell client

What is Mongo Shell?Mongo shell is a command line interface between user and database. You can connect to your MongoDB server using this shell and manage your databases and collections. You can also perform the administrative tasks on MongoDB.Connect to MongoDB ShellType mongo on your system terminal. It will automatically connect to your local MongoDB server running on port 27017.mongoMongoDB shell version v3.4.4connecting to: mongodb://127.0.0.1:27017MongoDB server version: 3.4.4You need to specify the hostname to connect remote database. Also, specify port if MongoDB is running on different port. mongo --host 10.10.8.1 --port 27017MongoDB shell version: 2.6.4connecting to: 10.10.8.1:27017/testExit the mongo ShellUse quit() function or press CTRL+C to quit from mongo shell. quit() read more

How to install MongoDB on Linux

How to install MongoDB on Linux.Step 1 – Add MongoDB Yum RepositoryAdd following content in yum repository configuration file mongodb.repo as per your required MongoDB version and system architecture.CentOS and RedHat systems Onlyvi /etc/yum.repos.d/mongodb.repo[MongoDB]name=MongoDB Repositorybaseurl=http://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/4.0/x86_64/gpgcheck=0enabled=1Fedora users can install it directory from its official yum repositories.Step 2 – Install MongoDB ServerLet’s use the yum package manager to install mongodb-org package, it will automatically install all its dependencies. To install any specific revision of MongoDB specify package name with version like mongodb-org-4.0.0. The following command will install latest stable version available.yum install mongodb-orgStep 3 – Start MongoDB ServicePackage mongodb-org-server provided MongoDB init script, Use that script to start service./etc/init.d/mongod restartConfigure MongoDB to autostart on system boot.chkconfig mongod onStep 4 – Check MongoDB VersionUse the following command to check installed MongoDB version[root@tecadmin ~]# mongod --versiondb version v4.0.0git version: 3b07af3d4f471ae89e8186d33bbb1d5259597d51OpenSSL version: OpenSSL 1.0.0-fips 29 Mar 2010allocator: tcmallocmodules: nonebuild environment: distmod: amazon distarch: x86_64 target_arch: x86_64Connect MongoDB using the command line and execute some test commands for checking proper working.[root@tecadmin ~]# mongo use mydb; db.test.save( { a: 1 } ) db.test.find() { "_id" : ObjectId("452l3i4j2a2l34ih2h4"), "a" : 1 }Congratulation’s You have successfully installed mongodb server on your system. For practice only you may use MongoDB browser shell. read more

How to Install Python 3.5.5 on CentOS/RHEL and Fedora

Python is a powerful programming language. It is very friendly and easy to learn. At the writing time of this article Python 3.5.5 is the latest stable version available to install. This tutorial will help you to install Python 3.5.5 on your CentOS, Red Hat & Fedora operating systems.Step 1 – PrerequsitesUse the following command to install prerequisites for Python before installing it.yum install gccStep 2 – Download Python 3.5Download Python using following command from python official site. You can also download latest version in place of specified below.cd /usr/srcwget https://www.python.org/ftp/python/3.5.5/Python-3.5.5.tgzNow extract the downloaded package.# tar xzf Python-3.5.5.tgzCompile Python SourceUse below set of commands to compile python source code on your system using altinstall.cd Python-3.5.5./configure --enable-optimizationsmake altinstallmake altinstall is used to prevent replacing the default python binary file /usr/bin/python.Now remove downloaded source archive file from your systemrm Python-3.5.5.tgzCheck Python VersionCheck the latest version installed of python using below commandpython3.5 -VPython 3.5.5 read more

How to Optimize PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX

If you have your application running on a PostgreSQL database, there are some commands that can be run to PostgreSQL database performance optimization. Three of these will be introduced in this article: VACUUM, ANALYZE, and REINDEX.In the default PostgreSQL configuration, the AUTOVACUUM daemon is enabled and all required configuration parameters are set as needed. The daemon will run VACUUM and ANALYZE at regular intervals. If you have the damon enabled, these commands can be run to supplement the daemon's work. To confirm whether the autovacuum daemon is running on UNIX, you can check the processlist$ ps aux|grep autovacuum|grep -v greppostgres 334 0.0 0.0 2654128 1232 ?? Ss 16Mar17 0:05.63 postgres: autovacuum launcher process On UNIX or Windows, you can find the status of autovacuum in the pg_settings database with the query below:<code class=" language-sql">select name,setting from pg_settings where name = 'autovacuum';VacuumThe VACUUM command will reclaim space still used by data that had been updated. In PostgreSQL, updated key-value tuples are not removed from the tables when rows are changed, so the VACUUM command should be run occasionally to do this.VACUUM can be run on its own, or with ANALYZE.ExamplesIn the examples below, [tablename] is optional. Without a table specified, VACUUM will be run on available tables in the current schema that the user has access to. Plain VACUUM: Frees up space for re-useVACUUM [tablename] Full VACUUM: Locks the database table, and reclaims more space than a plain VACUUM<code class=" language-sql">VACUUM(FULL)[tablename] Full VACUUM and ANALYZE: Performs a Full VACUUM and gathers new statistics on query executions paths using ANALYZE<code class=" language-sql">VACUUM(FULL,ANALYZE)[tablename] Verbose Full VACUUM and ANALYZE: Same as #3, but with verbose progress output<code class=" language-sql">VACUUM(FULL,ANALYZE,VERBOSE)[tablename]ANALYZEANALYZE gathers statistics for the query planner to create the most efficient query execution paths. Per PostgreSQL documentation, accurate statistics will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing.ExampleIn the example below, [tablename] is optional. Without a table specified, ANALYZE will be run on available tables in the current schema that the user has access to.<code class=" language-sql">ANALYZE VERBOSE [tablename]REINDEXThe REINDEX command rebuilds one or more indices, replacing the previous version of the index. REINDEX can be used in many scenarios, including the following (from Postgres documentation): An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes can become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method. An index has become "bloated", that is it contains many empty or nearly-empty pages. This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. You have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect. An index build with the CONCURRENTLY option failed, leaving an "invalid" index. Such indexes are useless but it can be convenient to use REINDEX to rebuild them. Note that REINDEX will not perform a concurrent build. To build the index without interfering with production you should drop the index and reissue the CREATE INDEX CONCURRENTLY command.ExamplesAny of these can be forced by adding the keyword FORCE after the command Recreate a single index, myindex:<code class=" language-sql">REINDEX INDEX myindex Recreate all indices in a table, mytable:<code class=" language-sql">REINDEX TABLE mytable Recreate all indices in schema public:REINDEX SCHEMA public Recreate all indices in database postgres:<code class=" language-sql">REINDEX DATABASE postgres Recreate all indices on system catalogs in database postgres:<code class=" language-sql">REINDEX SYSTEM postgres read more

How to find the size of PostgreSQL databases and tables

This article demonstrates how to determine the size of PostgreSQL databases and tables. You can do this by using the psql command-line program (for databases and tables).USING THE COMMAND LINEYou can use the psql command-line program to determine the sizes of PostgreSQL databases and tables. To do this, follow these steps: Log in to your account using SSH. At the command line, type the following command. Replace dbname with the name of the database, and username with the database username:psql dbname username At the Password prompt, type the database user's password. When you type the correct password, the psql prompt appears. To determine the size of a database, type the following command. Replace dbnamewith the name of the database that you want to check:SELECT pg_size_pretty( pg_database_size('dbname') );Psql displays the size of the database. To determine the size of a table in the current database, type the following command. Replace tablename with the name of the table that you want to check:SELECT pg_size_pretty( pg_total_relation_size('tablename') );Psql displays the size of the table. read more

How to merge two Json Flowfiles in Apache NiFi

The Apache NiFi template demonstrate how to Merge the content of two json incoming flow files into a single flowfile.See Download link:[download id="6205"] read more

MySQL Automatic Date Dimension Build

In every system a Date Dimension is needed for multiple reasons and addresses multiple use business use cases.What is Date Dimension?Date Dimension is a table that has one record per each day, no more, no less! Depends on the period used in the business you can define start and end of the date dimension.In this post i will post the code i use to generate a data dimension from scratch in MySQL.Table creation DROP TABLE IF EXISTS time_dimension;CREATE TABLE time_dimension ( id INTEGER PRIMARY KEY, -- year*10000+month*100+day db_date DATE NOT NULL, year INTEGER NOT NULL, month INTEGER NOT NULL, -- 1 to 12 day INTEGER NOT NULL, -- 1 to 31 quarter INTEGER NOT NULL, -- 1 to 4 week INTEGER NOT NULL, -- 1 to 52/53 day_name VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'... month_name VARCHAR(9) NOT NULL, -- 'January', 'February'... holiday_flag CHAR(1) DEFAULT 'f', weekend_flag CHAR(1) DEFAULT 'f', event VARCHAR(50), UNIQUE td_ymd_idx (year,month,day), UNIQUE td_dbdate_idx (db_date)) Engine=MyISAM;Procedure to generate the Date Dimension dataDROP PROCEDURE IF EXISTS fill_date_dimension;DELIMITER //CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)BEGIN DECLARE currentdate DATE; SET currentdate = startdate; WHILE currentdate < stopdate DO INSERT INTO time_dimension VALUES ( YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate), currentdate, YEAR(currentdate), MONTH(currentdate), DAY(currentdate), QUARTER(currentdate), WEEKOFYEAR(currentdate), DATE_FORMAT(currentdate,'%W'), DATE_FORMAT(currentdate,'%M'), 'f', CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END, NULL); SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY); END WHILE;END//DELIMITER ;Execute ProcedureCALL fill_date_dimension('start date ','end date ');OPTIMIZE TABLE time_dimension;I hope this help ! read more

How to drop Influxdb series using tags

How do we go about cleaning a old series using where clause in InfluxDB ?In many cases resources are no longer used/available but they will still persist as tags in the series, so is ideal to have a process in place that will clean them.Use This script to remove old/unused tags for and of your InfluxDB databases:use <database nameDROP SERIES FROM /.*/ WHERE "tag-name" = 'tag-value-to-delete-data'Hope this helps.Note:Dont run this in prod before testing!!!! read more

How to clean orphaned EBS SnapShots in Amazon Web Services

Managing AWS resources is the key and i believe is the single most important thing you need to do as AWS consumer.  Amazon will give access to unlimited resources so do get carried away.From time to time you will have EBS SnapShots stacking up so for this i use this easy script to identify and remove EBS SnapShots that are not attached to any AMI.#!/bin/bashset -eAWS_ACCOUNT_ID=selfREGION=ap-southeast-2ORPHANED_SNAPSHOTS_COUNT_LIMIT=10WORK_DIR=/tmpaws ec2 --region $REGION describe-snapshots --owner-ids $AWS_ACCOUNT_ID --query Snapshots[*].SnapshotId --output text | tr '\t' '\n' | sort $WORK_DIR/all_snapshotsaws ec2 --region $REGION describe-images --filters Name=state,Values=available --owners $AWS_ACCOUNT_ID --query "Images[*].BlockDeviceMappings[*].Ebs.SnapshotId" --output text | tr '\t' '\n' | sort $WORK_DIR/snapshots_attached_to_amiORPHANED_SNAPSHOT_IDS=comm -23 <(sort $WORK_DIR/all_snapshots) <(sort $WORK_DIR/snapshots_attached_to_ami)if [ -z "$ORPHANED_SNAPSHOT_IDS" ]; then echo "OK - no orphaned (not attached to any AMI) snapshots found" exit 0fiORPHANED_SNAPSHOT_IDS=echo "$ORPHANED_SNAPSHOT_IDS" | grep "snap"ORPHANED_SNAPSHOTS_COUNT=echo "$ORPHANED_SNAPSHOT_IDS" | wc -lif (( ORPHANED_SNAPSHOTS_COUNT ORPHANED_SNAPSHOTS_COUNT_LIMIT )); then echo "CRITICAL - $ORPHANED_SNAPSHOTS_COUNT orphaned (not attached to any AMI) snapshots found: [ $ORPHANED_SNAPSHOT_IDS ]" echo "To delete them, use commands below:" IFS=$'\n' for snapshot_id in $ORPHANED_SNAPSHOT_IDS; do echo "aws ec2 --region $REGION delete-snapshot --snapshot-id $snapshot_id"; done exit 1else echo "OK - $ORPHANED_SNAPSHOTS_COUNT orphaned (not attached to any AMI) snapshots found" if (( ORPHANED_SNAPSHOTS_COUNT 0 )); then echo "[ $ORPHANED_SNAPSHOT_IDS ]" fi exit 0fiThis script will identify and generate the delete command.Note: - review the script before you go ahead on the delete path. read more

How to Migrate a MariaDB to AWS RDS MariaDB with myqldump

In this post we will demonstrate how to migrate a MariaDB Database to AWS RDS MariaDB database using mysqldump utility.Here is the mysqldump export code used:mysqldump -u <user name --databases <db name --single-transaction --order-by-primary --gtid --force -r <dump file location -p <passwordAnd the import into AWS RDS pipe example:mysqldump -u <user name --databases <db name --single-transaction --order-by-primary --gtid --force -p <password | mysql -u <AWS RDS username -P <AWS RDS port -h <AWS RDS Endpoint -pAlso the video tutorial demos how this is done: read more

How to Migrate a MariaDB to AWS RDS MariaDB with Apache NiFi

In this post we will see how can Apache NiFi be used as an orchestrator to facilitate data migration from Local MySQL to AWS RDS. read more

Script to extract the DDL in AWS RedShift

If you wanna find the table details/description you can use the below script and output the full metadata for a table.Just replace INSERT_TABLENAME_HERE and INSERT_SCHEMA_NAME_HERE with the scheme and table name.SELECT DISTINCT n.nspname AS schemaname ,c.relname AS tablename ,a.attname AS COLUMN ,a.attnum AS column_position ,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE ,pg_catalog.format_encoding(a.attencodingtype) AS encoding ,a.attisdistkey AS distkey ,a.attsortkeyord AS sortkey ,a.attnotnull AS notnull ,a.attencodingtype AS compression ,con.conkey AS primary_key_column_ids ,con.contype AS con_typeFROM pg_catalog.pg_namespace n ,pg_catalog.pg_class c ,pg_catalog.pg_attribute a ,pg_constraint con ,pg_catalog.pg_stats statsWHERE n.oid = c.relnamespace AND c.oid = a.attrelid AND a.attnum 0 AND c.relname NOT LIKE '%pkey' AND lower(c.relname) = 'INSERT_TABLENAME_HERE' AND n.nspname = 'INSERT_SCHEME_NAME_HERE' AND c.oid = con.conrelid(+)ORDER BY A.ATTNUM; read more

How to create a schema and grant access to it in AWS RedShift

If you are new to the AWS RedShift database and need to create schemas and grant access you can use the below SQL to manage this processSchema creationTo create a schema in your existing database run the below SQL and replace my_schema_name with your schema nameCREATE SCHEMA my_schema_name;If you need to adjust the ownership of the schema to another user - such as a specific db admin user run the below SQL and replace my_schema_name with your schema name my_user_name with the name of the user that needs accessALTER SCHEMA my_schema_name OWNER TO my_user_name;PermissionsNow to allow only SELECT access to the new my_schema_name schema to the user my_user_name run the below SQL and replace my_schema_name with your schema name my_user_name with the name of the user that needs accessGRANT USAGE ON SCHEMA my_schema_name TO my_user_name;GRANT SELECT ON ALL TABLES IN SCHEMA my_schema_name TO my_user_name;ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema_name GRANT SELECT ON TABLES TO my_user_name;To allow ALL access to the new my_schema_name schema to the user my_user_name run the below SQL and replace my_schema_name with your schema name my_user_name with the name of the user that needs accessGRANT ALL ON SCHEMA my_schema_name TO my_user_name;GRANT ALL ON ALL TABLES IN SCHEMA my_schema_name TO my_user_name;ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema_name GRANT ALL ON TABLES TO my_user_name;If the user my_user_name does not already have access rights to the database that the schema belongs to run the below SQL and replace my_databasea_name with your database name my_user_name with the name of the user that needs accessGRANT CONNECT ON DATABASE my_database_name TO my_user_name;Group accessTo assign permissions to a user group rather than individual user in RedShift change the abover queriers from TO my_user_name to TO GROUP my_user_group. Replacing my_user_group with the name of your RedShift user group. read more

Step by Step PostgreSQL installation on Linux

In this post we will install the latest version of PostgreSQL using the yum package manager.  SSH into EC2 box and run the following commands yum update yum install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs Start your DB Service service postgresql initdb Edit the pg_hba.conf file the pg_hba.conf file enables client authentication between the PostgreSQL server and the client application. This file consists of a series of entries , which define a host and its associated permissions (e.g., the database it is allowed to connect to, the authentication method to use, and so on).# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all power_user 0.0.0.0/0 md5host all other_user 0.0.0.0/0 md5host all storageLoader 0.0.0.0/0 md5# IPv6 local connections:host all all ::1/128 md5How the pg_hba.conf worksWhen PostgreSQL receives a connection request it will check the pg_hba.conf file to verify that the machine from which the application is requesting a connection has rights to connect to the specified database. If the machine requesting access has permission to connect, PostgreSQL will check the conditions that the application must meet in order to successfully authenticate. This affects connections that are initiated locally as well as remotely.PostgreSQL will check the authentication method via the pg_hba.conf for every connection request. This check is performed every time a new connection is requested from the PostgreSQL server, so there is no need to re-start PostgreSQL after you add, modify or remove an entry in the pg_hba.conf file.When a connection is initialized, PostgreSQL will read through the pg_hba.conf one entry at a time, from the top down. As soon a matching record is found, PostgreSQL will stop searching and allow or reject the connection, based on the found entry. If PostgreSQL does not find a matching entry in the pg_hba.conf file, the connection fails completely.Table-level permissions still apply to a database, even if a user has permissions to connect to the database. If you can connect, but cannot select data from a table, you may want to verify that your connected user has permission to use SELECT on that table. Edit the postgresql.confEdit the following lines - #listen_addresses = 'localhost'  change to  listen_addresses='*' #port = 5432 change to port = 5432 Restart PostgreSQL Server service postgresql start Login and change the password for your posgres user.ALTER USER postgres WITH PASSWORD 'mynewpasswd';And we are done. read more

How to Load Data Into AWS Redshift with Apache NiFi

How to Load Data Into AWS Redshift with Apache NiFihttps://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html--jdbc stringjdbc:redshift://"redshift cluster endpoint"--driver Class Name com.amazon.redshift.jdbc42.Driver--jar location "full path"/RedshiftJDBC42-1.2.8.1005.jar read more

How Stream Data Into AWS Redshift with Apache NiFi

How Stream Data Into AWS Redshift with Apache NiFi read more

How to use GeoEnrichIp in Apache NiFi

How to use GeoEnrichIp in Apache NiFiHow to use GeoEnrichIp in Apache NiFi read more

How to Query a FlowFile in Apache NiFi Using QueryRecord

How to Query a FlowFile in Apache NiFi Using QueryRecordSee More details in the Video Description on the Code used. read more

How to convert CSV data into JSON in Apache NiFi

How to convert CSV data into JSON in Apache NiFi read more

How to install Apache Maven on Amazon Linux

Very simple way to install Maven on Amazon Linux (copy + paste) sudo wget http://repos.fedorapeople.org/repos/dchen/apache-maven/epel-apache-maven.repo -O /etc/yum.repos.d/epel-apache-maven.repo\sudo sed -i s/\$releasever/6/g /etc/yum.repos.d/epel-apache-maven.reposudo yum install -y apache-mavenmvn --version read more

How to Upload to AWS S3 as Public by Default

In a normal file upload the files uploaded to Amazon S3 are private, requiring a separate action to make public. In some cases we want the file to be loaded with public access granted to them, to be able to have this implemented add this policy to your S3 bucket.{ "Version": "2008-10-17", "Statement": [{ "Sid": "AllowPublicRead", "Effect": "Allow", "Principal": { "AWS": "*" }, "Action": [ "s3:GetObject" ], "Resource": [ "arn:aws:s3:::Bucket_Name/*" ] }]}To add this policy to your bucket follow the steps below: click on bucket expand the Permissions row click “Add Bucket Policy” copy/paste the snippet below into the text area. change Bucket_Name to the bucket you want submit your changesHope this was useful ! read more

Apache Nifi Template Integrate Multiple Files Single Flow

read more

Apache Nifi Template Integrate Multiple Files Single Flow 2

read more

How to Bulk Load Data in Vertica with Apache Nifi

This is just reusable Template.[download id="6055"] read more

Oracle Solution for Error ora-62001

ORA-62001: value for parameter cannot contain a comma What triggered the Error: Parameter value contained a comma.What should we do to fix it: Remove the comma from the parameter value.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60025

ORA-60025: Event for temp segment cleanup used for temp lobs What triggered the Error: Temp LOB segments used for temporary LOBs are deleted only on session exit which may lead to large amounts of memory being held across multiple sessions.What should we do to fix it: Setting this event will cause temporary LOB segments to be freed when there are no active temporary LOBs in the session. Setting this event will have a significant performance impact as it can cause temporary lob segments to be allocated and deleted many times during a session rather than once per session. Use this event only when temporary LOB segment memory use is an issue.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60019

ORA-60019: Creating initial extent of size string in tablespace of extent size string What triggered the Error: Creation of SECUREFILE segment failed due to small tablespace extent size.What should we do to fix it: Create tablespace with larger extent size and reissue command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60018

ORA-60018: adding string blocks to rollback segment string with MAXSIZE (string) What triggered the Error: Extending a rollback segment violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60016

ORA-60016: Operation not supported on SECUREFILE segment What triggered the Error: The operation to ALTER FREELIST/RETENTION was not supported on SECUREFILE segment.What should we do to fix it: Check the LOB type and reissue the statement.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60015

ORA-60015: invalid RETENTION storage option value What triggered the Error: Value of MIN retention should have been nonzero.What should we do to fix it: Correct the value and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60014

ORA-60014: invalid MAXSIZE storage option value What triggered the Error: Minimum of 1M should have been specified against the MAXSIZE storage clause.What should we do to fix it: Correct the value and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60013

ORA-60013: invalid MAXSIZE storage option value What triggered the Error: Invalid value was specified for MAXSIZE storage clause.What should we do to fix it: Correct the value and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60012

ORA-60012: adding (string) blocks to table string.string subpartition string with MAXSIZE (string) What triggered the Error: Extending a table subpartition violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60011

ORA-60011: adding (string) blocks to lob segment string.string subpartition string with MAXSIZE (string) What triggered the Error: Extending a LOB segment violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60010

ORA-60010: adding (string) blocks to LOB segment string.string with MAXSIZE (string) What triggered the Error: Extending a LOB segment violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60009

ORA-60009: adding (string) blocks to LOB segment string.string partition string with MAXSIZE (string) What triggered the Error: Extending a LOB segment violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60008

ORA-60008: adding (string) blocks to index string.string with MAXSIZE (string) What triggered the Error: Extending an index violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60007

ORA-60007: adding (string) blocks to index string.string subpartition string with MAXSIZE (string) What triggered the Error: Extending an index subpartition violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60006

ORA-60006: adding (string) blocks to index string.string partition string with MAXSIZE (string) What triggered the Error: Extending an index partition violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60005

ORA-60005: adding (string) blocks to cluster string.string with MAXSIZE (string) What triggered the Error: Extending a cluster violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60004

ORA-60004: adding (string) blocks to table string.string with MAXSIZE (string) What triggered the Error: Extending a table violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60003

ORA-60003: adding (string) blocks to table string.string partition string with MAXSIZE (string) What triggered the Error: Extending a table partition violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60002

ORA-60002: adding (string) blocks to temporary segment in tablespace string with MAXSIZE (string) What triggered the Error: Extending a temporary segment violated MAXSIZE limit.What should we do to fix it: Increase the MAXSIZE limit and retry command.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-60001

ORA-60001: adding (string) blocks to save undo segment in tablespace string with MAXSIZE (string) What triggered the Error: Save undo for the offline tablespace at segment MAXSIZE.What should we do to fix it: Check the storage parameters for the system tablespace. The tablespace needs to be brought back online so the undo can be applied.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-57000

ORA-57000: TimesTen IMDB error: string, number, string What triggered the Error: An OCI interface error occurred during a TimesTen operation.What should we do to fix it: Look up the error code in the TimesTen error documentation to diagnose.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56974

ORA-56974: Invalid set of import options What triggered the Error: The import options specified are inconsistentWhat should we do to fix it: Check that import options are consistent and have valid valuesIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56973

ORA-56973: import options do not match export options What triggered the Error: The import options specified do not match the export optionsWhat should we do to fix it: Check that import is being invoked with the same values for options like export environment, export data and export metadataIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56972

ORA-56972: referenced file not found What triggered the Error: One of the file specified in the test case package is misingWhat should we do to fix it: rebuild the SQL test case or make sure that all the referenced files are readable and located in the same directory as the SQL test case manifest.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56971

ORA-56971: Invalid set of export options What triggered the Error: The options specified are inconsistentWhat should we do to fix it: Check and a consistent set of option parametersIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56950

ORA-56950: Invalid value for incident identifier What triggered the Error: Invalid incident identifier argument passed.What should we do to fix it: Check and correct the identifierIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56904

ORA-56904: pivot value must have datatype that is convertible to pivot column What triggered the Error: Datatype of pivot value is not convertible to the datatype of pivot column.What should we do to fix it: Check and correct pivot value.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56903

ORA-56903: sys_op_pivot function is not allowed here What triggered the Error: invalid use of sys_op_pivot function.What should we do to fix it: Remove sys_op_pivot function.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56902

ORA-56902: expect aggregate function inside pivot operation What triggered the Error: Attempted to use non-aggregate expression inside pivot operation.What should we do to fix it: Use aggregate function.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56901

ORA-56901: non-constant expression is not allowed for pivot|unpivot values What triggered the Error: Attempted to use non-constant expression for pivot|unpivot values.What should we do to fix it: Use constants for pivot|unpivot values.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56900

ORA-56900: bind variable is not supported inside pivot|unpivot operation What triggered the Error: Attempted to use bind variables inside pivot|unpivot operation.What should we do to fix it: This is not supported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56866

ORA-56866: No IP parameter What triggered the Error: No IP address is set in OSSINIT.ORA.What should we do to fix it: Check that one or more valid IP addresses are set in OSSINIT.ORA.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56865

ORA-56865: Invalid IP address in OSSINIT.ORA What triggered the Error: One or more of the specified IP addresses in OSSINIT.ORA is not valid.What should we do to fix it: Check that all IP addresses in OSSINIT.ORA are valid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56800

ORA-56800: DSKM process died unexpectedly What triggered the Error: An explicit kill or internal error caused the death of the DSKM background process.What should we do to fix it: Restart the instance.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56725

ORA-56725: Could not spawn additional calibration slaves What triggered the Error: An error occurred when spawning calibration slave process - Calibration process aborted.What should we do to fix it: Check OS resources required for spawning processesIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56723

ORA-56723: I/O request limit exceeded - session terminated What triggered the Error: The Resource Manager SWITCH_IO_REQS limit was exceeded.What should we do to fix it: Reduce the complexity of the update or query, or contact your database administrator for more information.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56722

ORA-56722: I/O request limit exceeded - call aborted What triggered the Error: The Resource Manager SWITCH_IO_REQS limit was exceeded.What should we do to fix it: Reduce the complexity of the update or query, or contact your database administrator for more information.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56721

ORA-56721: I/O data limit exceeded - session terminated What triggered the Error: The Resource Manager SWITCH_IO_MEGABYTES limit was exceeded.What should we do to fix it: Reduce the complexity of the update or query, or contact your database administrator for more information.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56720

ORA-56720: I/O data limit exceeded - call aborted What triggered the Error: The Resource Manager SWITCH_IO_MEGABYTES limit was exceeded.What should we do to fix it: Reduce the complexity of the update or query, or contact your database administrator for more information.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56719

ORA-56719: Error spawning or communicating with calibration slave What triggered the Error: An error occurred in calibration slave process - Calibration process aborted.What should we do to fix it: Review trace files for errors.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56718

ORA-56718: Timeout occurred while setting resource plan What triggered the Error: A timeout occurred while waiting for one or more RAC instances to set the resource plan.What should we do to fix it: Since the resource plan may have actually been set successfully, first check the current resource plan for each instance by querying gv$rsrc_plan. If the resource plan was not successfully set on all instances, then retry the operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56717

ORA-56717: SWITCH_TIME is set without specifying SWITCH_GROUP What triggered the Error: The plan directive specifies a SWITCH_TIME without a SWITCH_GROUP.What should we do to fix it: Specify a SWITCH_GROUP parameter in the plan directive.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56716

ORA-56716: Category string does not exist What triggered the Error: A non-existent category was specified as an argument to a procedure in the package, DBMS_RESOURCE_MANAGER.What should we do to fix it: Specify an existing category name or create a new category with this name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56715

ORA-56715: string resource plan contains a reserved word What triggered the Error: The specified plan name is prefixed with a reserved prefix such as FORCE or SCHED.What should we do to fix it: Do not prefix resource plan name with FORCE or SCHED.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56714

ORA-56714: Plan name string exceeds the maximum length allowed What triggered the Error: Plan name is greater than 30 characters long.What should we do to fix it: Do not exceed 30 characters when naming a resource plan.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56713

ORA-56713: Insufficient Resource Manager privileges What triggered the Error: An attempt was made to switch the consumer group of a user without the appropriate privilege.What should we do to fix it: Ask the database administrator to perform the switch operation or grant switch or system privilege to the user.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56711

ORA-56711: string is an invalid string argument What triggered the Error: The named argument is invalid.What should we do to fix it: Specify a valid argument for this procedure.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56710

ORA-56710: DBRM process died unexpectedly What triggered the Error: An explicit kill or internal error caused the death of the DBRM background process.What should we do to fix it: Restart the instance.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56709

ORA-56709: timed_statistics set to FALSE What triggered the Error: timed_statistics parameter in database is set to FALSE. Needs to be enabled for calibration.What should we do to fix it: set timed_statistics=TRUE in init.ora or "alter system set timed_statistics=TRUE"If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56708

ORA-56708: Could not find any datafiles with asynchronous i/o capability What triggered the Error: There are no datafiles which are asynchronous I/O capable.What should we do to fix it: Make sure asynchronous i/o is permitted to datafiles.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56707

ORA-56707: INTERNAL_QUIESCE plan cannot be specified as a top-level Resource Manager plan What triggered the Error: An attempt was made to specify INTERNAL_QUIESCE as a top-level Resource Manager plan.What should we do to fix it: Do not attempt to set INTERNAL_QUIESCE as a Resource Manager plan.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56706

ORA-56706: The specified Resource Manager plan is a subplan and cannot be set as a top-level plan What triggered the Error: An attempt was made to set a subplan as a top-level plan.What should we do to fix it: Do not attempt to set subplans as top-level plans.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56705

ORA-56705: I/O calibration already in progress What triggered the Error: An attempt was made to run a second instance of I/O CalibrationWhat should we do to fix it: Wait until the first I/O calibration run is complete; then, retry the operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56704

ORA-56704: EXPLICIT consumer group mapping priority must be set to 1 What triggered the Error: An attempt was made to set the EXPLICIT mapping priority to a value other than 1.What should we do to fix it: Set the mapping priorities to unique integers within the documented range with the EXPLICT priority set to 1.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56703

ORA-56703: VKTM process died unexpectedly What triggered the Error: An explicit kill or internal error caused the death of VKTM background process.What should we do to fix it: Restart the instance.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56702

ORA-56702: consumer group string is for internal use only and cannot be a switch target What triggered the Error: An attempt was made to specify an INTERNAL_USE consumer group as a switch target.What should we do to fix it: Do not attempt to switch to INTERNAL_USE consumer groups.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56701

ORA-56701: INTERNAL_USE attribute of consumer group string cannot be modified What triggered the Error: An attempt was made to modify the INTERNAL_USE attribute of the specified consumer group.What should we do to fix it: Do not attempt to modify the INTERNAL_USE attribute.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56700

ORA-56700: plan string is a subplan and SUB_PLAN attribute cannot be modified What triggered the Error: An attempt was made to modify the SUB_PLAN attribute of the specified plan.What should we do to fix it: Do not attempt to modify the SUB_PLAN attribute.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56609

ORA-56609: Usage not supported with DRCP What triggered the Error: This usage was not supported on a DRCP connection.What should we do to fix it: Use a dedicated connection to perform this task.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56608

ORA-56608: DRCP: Server Group feature is not supported What triggered the Error: Server Group attribute was set on the server handle, connected to a Database Resident connection pool.What should we do to fix it: Do not set Server Group attribute on server handles while using Database Resident connection pool.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56607

ORA-56607: DRCP: Connection is already authenticated What triggered the Error: Attempt to reauthenticate the connection which is authenticated.What should we do to fix it: Logoff the connection before reauthenticating.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56606

ORA-56606: DRCP: Client version doesnot support the feature What triggered the Error: The client version is lower than 11g.What should we do to fix it: Upgrade to a higher client version or turn off (SERVER=POOLED) in the connect string.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56605

ORA-56605: DRCP: Session switching and migration not allowed What triggered the Error: Application tried to switch or migrate session across connections.What should we do to fix it: This usage is irrelevant in the Database Resident connection pooling context and is not supported. Release existing session.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56604

ORA-56604: DRCP: Length[string] for string exceeded the MAX allowed What triggered the Error: Length exceeded MAX for the value.What should we do to fix it: Use a value within the MAX allowed.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56603

ORA-56603: DRCP: Internal error What triggered the Error: Malformed input values.What should we do to fix it: Input well-formed values.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56602

ORA-56602: DRCP: Illegal purity What triggered the Error: Wrong value for purity was provided.What should we do to fix it: Check the documentation for Database Resident connection pool usage.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56601

ORA-56601: DRCP: Illegal connection class What triggered the Error: Wrong value for connection class was given.What should we do to fix it: Check the documentation for Database Resident connection pool usage.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56600

ORA-56600: DRCP: Illegal Call What triggered the Error: An illegal OCI function call was issuedWhat should we do to fix it: Check the documentation for Database Resident connection pool usageIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56514

ORA-56514: DRCP: invalid value for maximum number of connections to Connection broker What triggered the Error: The value passed exceeded the maximum allowed.What should we do to fix it: No action required. The maximum number of connections was automatically set to the maximum allowed value.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56513

ORA-56513: DRCP: Cannot perform requested operation using pooled connection What triggered the Error: This operation was not supported using connections from a pool.What should we do to fix it: Use a regular connection to perform this operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56512

ORA-56512: DRCP: Failed to synchronize RAC instances [string] What triggered the Error: Some of the RAC instances were not synchronized.What should we do to fix it: Perform the same operations on all the failed instances.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56511

ORA-56511: DRCP: Cross instance synchronization failed What triggered the Error: Publish message to all RAC instances failed.What should we do to fix it: Check logs for details.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56510

ORA-56510: DRCP: Pool alter configuration failed What triggered the Error: Connection Broker failed to configure the pool.What should we do to fix it: Check logs for details.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56509

ORA-56509: DRCP: Pool shutdown failed What triggered the Error: Connection Broker failed to shutdown the pool.What should we do to fix it: Check logs for details.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56508

ORA-56508: DRCP: Pool startup failed What triggered the Error: Connection Broker failed to startup the pool.What should we do to fix it: Check logs for details.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56507

ORA-56507: DRCP: Pool alter configuration failed What triggered the Error: Connection pool failed to configure pool.What should we do to fix it: Check logs for details.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56506

ORA-56506: DRCP: Pool shutdown failed What triggered the Error: Connection pool failed to shutdown.What should we do to fix it: Check logs for details.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56505

ORA-56505: DRCP: Invalid pool configuration parameter value What triggered the Error: The configuration paramter value is null or invalid input.What should we do to fix it: Input a valid configuration parameter value.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56504

ORA-56504: DRCP: Invalid pool configuration parameter name What triggered the Error: The configuration paramter name is null or invalid input.What should we do to fix it: Input a valid configuration parameter name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56503

ORA-56503: DRCP: Pool is active What triggered the Error: The operation is only supported on an inactive pool.What should we do to fix it: Shutdown the pool.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56502

ORA-56502: DRCP: Pool is inactive What triggered the Error: The operation is only supported on an active pool.What should we do to fix it: Start the pool.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56501

ORA-56501: DRCP: Pool startup failed What triggered the Error: The connection pool failed to start up.What should we do to fix it: Check logs for details.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-56500

ORA-56500: DRCP: Pool not found What triggered the Error: The pool name passed was either null or an invalid pool name.What should we do to fix it: Input a valid pool name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55800

ORA-55800: NLS errors while processing Oracle number What triggered the Error: Error occurred when trying to convert Oracle number to an integer.What should we do to fix it: Check the input parameters.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55713

ORA-55713: GLOBAL_TXN_PROCESSES cannot be set to 0 at runtime What triggered the Error: An attempt was made to set initialization parameter GLOBAL_TXN_PROCESSES to 0 at runtime.What should we do to fix it: Set the initialization parameter GLOBAL_TXN_PROCESSES to 0 before starting RAC instance to disable GTX background processes. Note that XA transactions are not supported on RAC database when GTX background processes are disabled.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55712

ORA-55712: XA transactions on RAC are not supported with GLOBAL_TXN_PROCESSES set to 0 What triggered the Error: The initialization parameter GLOBAL_TXN_PROCESSES was set to 0.What should we do to fix it: Set the initialization parameter GLOBAL_TXN_PROCESSES to a value greater than 0.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55711

ORA-55711: Unable to bind clusterwide global transactions to compatible undo What triggered the Error: Undo tablespace was not onlined for automatic undo management.What should we do to fix it: Create undo tablespace if it has not been created.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55710

ORA-55710: Unable to alter system parameter GLOBAL_TXN_PROCESSES at this time What triggered the Error: The system was in the process of adjusting the number of global transaction background processes.What should we do to fix it: Retry the operation at a later time.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55632

ORA-55632: Tablespace has Flashback Archive tables What triggered the Error: An attempt was made to remove a tablespace that has Flashback Archive tables.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55631

ORA-55631: Table has columns with data types that are not supported by Flashback Data Archive What triggered the Error: An attempt was made to add a column of data type that is not supported by Flashback Data Archive. Or, the table on which Flashback Data Archive is being enabled contains column(s) with data types not supported by Flashback Data Archive.What should we do to fix it: Do not use FLASHBACK ARCHIVE clause for this object. If adding column, do not use LONG or Nested Table column data type.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55630

ORA-55630: Flashback Data Archive cannot be enabled on this object What triggered the Error: An attempt was made to enable Flashback Data Archive on an object which is not supported by Flashback Data Archive.What should we do to fix it: Do not use Flashback Archive clause for this object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55629

ORA-55629: Event to test Flashback Archiver internal management tasks What triggered the Error: The purpose of this event is for testing.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55628

ORA-55628: Flashback Archive supports Oracle 11g or higher What triggered the Error: An attempt was made to created a Flashback Archive with incorrect compatible mode or without auto undo management.What should we do to fix it: Use compatible mode equal to 11.0 or higher, and use auto undo management.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55627

ORA-55627: Flashback Archive tablespace must be ASSM tablespace What triggered the Error: An attempt was made to add a tablespace that was not an ASSM tablespace.What should we do to fix it: Add tablespace that is created with segment space management auto.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55626

ORA-55626: Cannot remove the Flashback Archive's primary tablespace What triggered the Error: An attempt was made to remove the primary tablespace of the Flashback Archive.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55625

ORA-55625: Cannot grant Flashback Archive privilege to a role What triggered the Error: An attempt was made to grant or revoke Flashback Archive privilege to a role.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55624

ORA-55624: The table "string"."string" cannot be enabled for Flashback Archive at this point What triggered the Error: An attempt was made to enable Flashback Archive again on a table which was just disabled.What should we do to fix it: Try again later.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55623

ORA-55623: Flashback Archive "string" is blocking and tracking on all tables is suspended What triggered the Error: Flashback archive tablespace has run out of space.What should we do to fix it: Add tablespace or increase tablespace quota for the flashback archive.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55622

ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "string"."string" What triggered the Error: An attempt was made to write to or alter or create unique index on a Flashback Archive internal table.What should we do to fix it: No action required. Only Oracle is allowed to perform such operations on Flashback Archive internal tables.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55621

ORA-55621: User quota on tablespace "string" is not enough for Flashback Archive What triggered the Error: An attempt was made to create or alter a Flashback Archive quota which is larger than the user's quota.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55620

ORA-55620: No privilege to use Flashback Archive What triggered the Error: An attempt was made to enable Flashback Archive on a table without such privileges.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55619

ORA-55619: Invalid privilege to grant on Flashback Archive What triggered the Error: An attempt was made to grant invalid privilege on a Flashback Archive object.What should we do to fix it: Specify valid privilege.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55618

ORA-55618: Insufficient privilege to grant Flashback Archive privilege What triggered the Error: An attempt was made to grant Flashback Archive privilege.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55617

ORA-55617: Flashback Archive "string" runs out of space and tracking on "string" is suspended What triggered the Error: Flashback archive tablespace quota is running out.What should we do to fix it: Add tablespace or increase tablespace quota for the flashback archive.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55616

ORA-55616: Transaction table needs Flashback Archiver processing What triggered the Error: Too many transaction table slots were being taken by transactions on tracked tables.What should we do to fix it: Wait for some amount of time before doing tracked transactions.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55615

ORA-55615: Event to test archiver scheduled internal tasks What triggered the Error: The purpose of this event is for testing.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55614

ORA-55614: AUM needed for transactions on tracked tables What triggered the Error: An attempt was made to execute DML on a tracked table without enabling Auto Undo Management.What should we do to fix it: Disable tracking on the table or enable Auto Undo Management.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55613

ORA-55613: Invalid Flashback Archive quota size What triggered the Error: An attempt was made to specify invalid Flashback Archive quota size.What should we do to fix it: Specify size in MB, GB, TB, or PB.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55612

ORA-55612: No privilege to manage Flashback Archive What triggered the Error: An attempt was made to create, alter, or drop a Flashback Archive.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55611

ORA-55611: No privilege to manage default Flashback Archive What triggered the Error: An attempt was made to create, alter, or drop the default Flashback Archive.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55610

ORA-55610: Invalid DDL statement on history-tracked table What triggered the Error: An attempt was made to perform certain DDL statement that is disallowed on tables that are enabled for Flashback Archive.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55609

ORA-55609: Attempt to create duplicate default Flashback Archive What triggered the Error: An attempt was made to create a default Flashback Archive while one already exists.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55608

ORA-55608: Default Flashback Archive does not exist What triggered the Error: The default Flashback Archive did not exist.What should we do to fix it: Create the default Flashback Archive first.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55607

ORA-55607: Event to enable debugging of archiver What triggered the Error: The purpose of this event is for debugging.What should we do to fix it: Attach to process specified.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55606

ORA-55606: Event to modify archiver sleep time in seconds What triggered the Error: 30 seconds is recommended as the archiver sleep time for tests.What should we do to fix it: The default archiver sleep time is 300 secondsIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55605

ORA-55605: Incorrect Flashback Archive is specified What triggered the Error: An attempt was made to operate on a Flashback Archive that does not exist, or to create a Flashback Archive that already exists.What should we do to fix it: Check the SQL statement.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55604

ORA-55604: Incorrect tablespace is specified What triggered the Error: An incorrect tablespace is specified for the Flashback Archive.What should we do to fix it: Check the SQL statement and verify the possible causes, 1, the tablespace that was already used by the Flashback Archive. 2, the tablespace was not used by the Flashback Archive. 3, the tablespace block size is less than 8K.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55603

ORA-55603: Invalid Flashback Archive command What triggered the Error: An invalid Flashback Archive command was specified.What should we do to fix it: Check the SQL statement.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55602

ORA-55602: The table "string"."string" is not enabled for Flashback Archive What triggered the Error: An attempt was made to disable Flashback Archive on a table on which Flashback Archive is not enabled.What should we do to fix it: Check the table name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55601

ORA-55601: The table "string"."string" cannot be enabled for Flashback Archive What triggered the Error: An attempt is made to enable Flashback Archive for a table which should never be enabled for Flashback Archive.What should we do to fix it: Check the table name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55600

ORA-55600: The table "string"."string" is already enabled for Flashback Archive What triggered the Error: The specified table is already enabled for Flashback Archive.What should we do to fix it: No action required.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55566

ORA-55566: SQL statement issued when the database was not open for queries What triggered the Error: Tried to access SMON time zone information when database was not open for queries.What should we do to fix it: Reissue the SQL statement after the database is open for queries.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55565

ORA-55565: string is not a valid undo segment number What triggered the Error: the given usn is not a valid oneWhat should we do to fix it: check undo$If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55564

ORA-55564: string is not a valid transaction id What triggered the Error: the given txn is not a valid oneWhat should we do to fix it: check txn idIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55563

ORA-55563: number is not a valid undo segment number What triggered the Error: the given usn is not a valid oneWhat should we do to fix it: check undo$If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55558

ORA-55558: string is not a corrupted transaction What triggered the Error: the given transaction is not in the corrupt list v$corrupt_xid_listWhat should we do to fix it: check v$corrupt_xid_listIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55557

ORA-55557: Trigger 4144 corruption What triggered the Error: Above events used for testing corruption pathWhat should we do to fix it: NoneIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55520

ORA-55520: Log record in compatibility lower than 11.0 What triggered the Error: The logical change record shows that the compatibility of the mined redo is lower than version 11.0. Flashback transaction works only on redo versions 11.0 and above.What should we do to fix it: Advance the compatibility and try to back out transactions that have occurred after the compatibility increase.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55519

ORA-55519: Supplemental logging not available for mining SCN range What triggered the Error: Flashback Transaction cannot work if there are regions in the mining range where supplemental logging is not enabled.What should we do to fix it: If you have provided a SCN/time hint which is approximate and far beyond the specified transaction start time, then readjust the SCN hint and try again. If the system has figured out the transaction start time or you are sure of the range, then the specified transaction cannot be backed out.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55518

ORA-55518: Mining across reset logs What triggered the Error: Flashback Transaction Backout cannot work with missing changes. This error is thrown if we walk across a reset-logs branch, where we might have missed changes.What should we do to fix it: If the user knows that the transaction happened in the current reset logs branch, then the SCN-hint is possibly incorrect. Provide an SCN in the current reset log branch.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55515

ORA-55515: Mining sees input transaction changes without seeing transaction start What triggered the Error: The start SCN provided was higher than the transaction start but below the transaction commit. The result was only partial changes for the given transaction.What should we do to fix it: Please provide a lower scn hint to flashback transaction.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55514

ORA-55514: Backing out a DDL transaction What triggered the Error: One of the transactions in the dependency graph is a DDL transaction and could not be backed out.What should we do to fix it: NoneIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55513

ORA-55513: Backing out an AQ transaction What triggered the Error: One of the transactions in the dependency graph touched an AQ table. As AQ externalizes database information, these transactions are not backed out, as the entire effects of the transaction cannot be seen from inside the database.What should we do to fix it: NoneIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55512

ORA-55512: Backing out PDML or XA-RAC transaction What triggered the Error: One of the transactions in the dependency graph was a PDML transaction or a local transaction which is a branch of a global XA transaction, spanning multiple RAC instances. Currently flashback transaction does not support this type of transaction.What should we do to fix it: NoneIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55511

ORA-55511: Flashback Transaction experienced error in executing undo SQL What triggered the Error: There was a constraint violation exception when executing in NOCASCADE_FORCE mode. Users could also get this error if appropriate supplemental logging is not enabled causing constraint dependencies to go unnoticed.What should we do to fix it: Either use CASCADE or NONCONFLICT_ONLY options or add appropriate level for supplemental logging.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55510

ORA-55510: Mining could not start What triggered the Error: Mining could not start for the following reasons.*1. A logminer session was processin.*2. The database was not mounted or not opened for read and writ.*3. Minimum supplemental logging was not enable.*4. Archiving was not enable.What should we do to fix it: Fix the mentioned problems and try again. Note that if you enable supplemental logging now, you will not be able to remove a transaction that has committed without supplemental logging.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55509

ORA-55509: Creation of dependencies could not finish What triggered the Error: One or more input transaction or any of its dependents are not committed or have been aborted after more than 1 minute of calling the backout function.What should we do to fix it: Commit all the active transactions associated with this table and try again.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55508

ORA-55508: Invalid input to Flashback Transaction Backout What triggered the Error: Null varrays passed or invalid input specifiedWhat should we do to fix it: Specify properly formed varrays and valid optionsIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55507

ORA-55507: Encountered mining error during Flashback Transaction Backout. function:string What triggered the Error: Mining error.What should we do to fix it: NoneIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55506

ORA-55506: Transaction performed unsupported change What triggered the Error: A transaction in the dependency DAG performed someWhat should we do to fix it: The specified transaction cannot be backed out.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55505

ORA-55505: DDL done on an interesting object after mining start SCN What triggered the Error: The Flashback Transaction Backout process encountered an interesting object which had its last DDL operation done on it after the mining start time. An interesting object is one that has been modified by either the specified transactions or any of their dependents.What should we do to fix it: Specify transactions that have committed after the last DDL done on all the objects they touched.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55504

ORA-55504: Transaction conflicts in NOCASCADE mode What triggered the Error: Transactions other than the ones specified conflicts with the specified transactions.What should we do to fix it: Try using other options like NONCONFLICT_ONLY or CASCADE or NOCASCADE_FORCE.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55503

ORA-55503: Mining finished without seeing specified transactions What triggered the Error: The SCN hit passed was not good. The SCN hit may have come after the start of any of the input transactions.What should we do to fix it: Give a lesser and more conservative SCN hint, with greater probability of having seen the start of a transaction.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55502

ORA-55502: Specified input transaction by name has no SCN hint What triggered the Error: The specified transaction names for Flashback Transaction Backout was missing an SCN hint.What should we do to fix it: Provide an SCN hint, and guarantee that the named transactions start before the given SCN.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55501

ORA-55501: Backing out live transaction What triggered the Error: Flashback Transaction Backout was requested on a transaction that has not committed yet.What should we do to fix it: Commit the transaction before using this feature.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55462

ORA-55462: internal error What triggered the Error: An internal error occurred during a semantic operator query or during the creation of an index of type SEM_INDEXTYPE.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55461

ORA-55461: no distance information available What triggered the Error: Distance information was not generated during rules index creation.What should we do to fix it: Retry query without the SEM_DISTANCE operator and/or without specifying bounds in the SEM_RELATED operator. See the documentation for information on when distance information is generated.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55460

ORA-55460: incorrect usage of semantic operators What triggered the Error: There was a syntax error in the call to the SEM_RELATED operatorWhat should we do to fix it: See the documentation for information on how to use the SEM_RELATED operator. Check that the value returned by SEM_RELATED is compared to 1 or 0.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55459

ORA-55459: invalid parameter string What triggered the Error: The parameter string used in the creation of an index of type SEM_INDEXTYPE had invalid or malformed parameters.What should we do to fix it: See the documentation for information on how to write a valid parameter string.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55458

ORA-55458: object not found in model What triggered the Error: The object value passed in as an argument to the semantic operator did not exist in the model.What should we do to fix it: In the query using semantic operators, use an object value that exists in the model and retry the operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55457

ORA-55457: predicate not found in model What triggered the Error: The predicate value passed in as an argument to the semantic operator did not exist in the model.What should we do to fix it: In the query using semantic operators, use a predicate value that exists in the model and retry the operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55456

ORA-55456: no valid rules index for this model-rulebase combination What triggered the Error: A valid rules index did not exist for specified combination of models and rulebases.What should we do to fix it: Create a rules index for the specified models and rulebases combination, or use a combination of models and rulebases for which a rules index exists, and retry the operation. Also ensure that the rules index status matches the status for the rules index specified in the query.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55455

ORA-55455: rules index status not recognized ( string ) What triggered the Error: The specified rules index status was not recognized.What should we do to fix it: Ensure that the rules index status is VALID, INCOMPLETE, or INVALID, and retry the operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55430

ORA-55430: query pattern is null What triggered the Error: The query pattern specified in the SEM_MATCH query was null.What should we do to fix it: Modify the query pattern to be non-null.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55379

ORA-55379: too many triples What triggered the Error: An internal error occurred during validation. Too many triples were passed in.What should we do to fix it: Specify a valid number of triples for the operation, or contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55378

ORA-55378: invalid error code What triggered the Error: An invalid error code was passed in during validation.What should we do to fix it: Specify a valid error code.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55377

ORA-55377: number of triples less than 1 or null error indication marker What triggered the Error: An internal error occurred during validation. The number of triples was less than 1 or the error indication marker was null.What should we do to fix it: Check the input parameters, and ensure that the number of triples is 1 or greater and that the error indication marker is not null; or contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55376

ORA-55376: cannot alter or drop column 'string' because this column owns RDF objects What triggered the Error: A table column containing RDF data could not be altered or dropped without first dropping its RDF model.What should we do to fix it: Drop the corresponding RDF model or models, and then alter or drop the column.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55375

ORA-55375: cannot drop table 'string' because this table owns RDF objects What triggered the Error: A table containing RDF data could not be dropped without first dropping its RDF model.What should we do to fix it: Drop the corresponding RDF model or models, and then drop the table.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55374

ORA-55374: query constants not in the database; no rows selected What triggered the Error: URIs or literals used in the query did not exist in the database.What should we do to fix it: Check the query, and ensure that the URIs or literals do exist.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55373

ORA-55373: inference internal error: string What triggered the Error: An unexpected internal error condition occurred.What should we do to fix it: Check the error message and the stack trace.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55372

ORA-55372: entailment (rules index) 'string' already exists What triggered the Error: The entailed graph (rules index) already exists.What should we do to fix it: Specify a different rules index name, or drop the existing rules index and then create a new rules index with that name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55371

ORA-55371: RDF rules index 'string' exists for different model-rulebase combination What triggered the Error: A rules index with the specified name has already been built for a different model-rulebase combination.What should we do to fix it: Specify a different rules index name, or drop the existing rules index and then create a new rules index with that name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55370

ORA-55370: input parameter not a zero or positive integer What triggered the Error: The input parameter was not zero or a positive integer.What should we do to fix it: Change the input parameter to zero or a positive integer.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55331

ORA-55331: user owns RDF objects What triggered the Error: The user could not be dropped because it owns RDF objects.What should we do to fix it: Drop the RDF objects and then retry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55330

ORA-55330: rulebase or rules index string is busy What triggered the Error: The specified rulebase or rules index was busy and could not be used.What should we do to fix it: Retry your operation later.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55329

ORA-55329: same model string specified more than once in the list of models What triggered the Error: The specified model occurred more than once in the list of models.What should we do to fix it: Eliminate duplicate occurrences of the model in the list of models.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55328

ORA-55328: literal value string insert attempt failed What triggered the Error: The attempt to create the specified literal value failed.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55327

ORA-55327: rule string yields a triple with a literal subject or predicate What triggered the Error: The specified rule created an invalid triple containing a literal in the subject or predicate position.What should we do to fix it: Check and modify the rule to avoid creation of invalid triple.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55326

ORA-55326: rules index (string) create attempt failed: string What triggered the Error: The attempt to create the specified rules index did not succeed.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55325

ORA-55325: rulebase or rules index string already exists string What triggered the Error: A rulebase or rules index with the specified name already existed.What should we do to fix it: Choose a different name, or delete the existing rulebase or rules index.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55324

ORA-55324: no rulebases specified What triggered the Error: Rulebases for the operation were not specified.What should we do to fix it: Specify at least one rulebase.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55323

ORA-55323: rulebase(s) exist What triggered the Error: An attempt was made to drop a network that contained one or more rulebases.What should we do to fix it: Drop all the rulebase(s) and then retry the operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55322

ORA-55322: model(s) exist What triggered the Error: An attempt was made to drop a network that contained one or more models.What should we do to fix it: Drop all the model(s) and then retry the operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55321

ORA-55321: network already exists What triggered the Error: Attempt to create the network failed because the network already existed.What should we do to fix it: If necessary, drop the network before trying to recreate the network.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55320

ORA-55320: model string drop attempt failed: string What triggered the Error: The attempt to drop the specified model did not succeed.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55319

ORA-55319: model string create attempt failed: string What triggered the Error: The attempt to create the specified model did not succeed.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55318

ORA-55318: column string in table string already contains data What triggered the Error: At model creation time, the table column contained data.What should we do to fix it: Ensure that the table column does not contain data before model creation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55317

ORA-55317: model string already exists What triggered the Error: A model with the specified name was already present.What should we do to fix it: Choose a different model name, or delete the existing model and create a new model with the specified name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55316

ORA-55316: model string does not match model string for table and column What triggered the Error: This column of the table was not associated with the specified model.What should we do to fix it: Make sure to use the correct target model.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55315

ORA-55315: batch load attempt failed: string What triggered the Error: The batch load operation failed.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55314

ORA-55314: invalid temporary table name (string) for use with batch load What triggered the Error: Specified temporary table name was not valid.What should we do to fix it: See documentation for rules for temporary table name validity.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55313

ORA-55313: SDO_RDF_TRIPLE_S constructor failed to process triple containing bNode What triggered the Error: The SDO_RDF_TRIPLE_S constructor without bNode reuse option was invalid for triple containing bNode.What should we do to fix it: Use SDO_RDF_TRIPLE_S constructor that allows bNode reuse.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55312

ORA-55312: parse failed for triple: id-form: string string string What triggered the Error: Attempts to insert triple failed.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55311

ORA-55311: invalid value type string for long lexical value string What triggered the Error: The value type of this long (length > 4000) lexical value was invalid.What should we do to fix it: Make sure that the long value has a valid value type.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55310

ORA-55310: parse failed for the string lexical value: string What triggered the Error: Attempts to insert the specified lexical value failed.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55309

ORA-55309: hash collision resolution failed for lexical value string What triggered the Error: Attempts to resolve hash collision exceeded the maximum retry count.What should we do to fix it: This may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55308

ORA-55308: invalid time zone string for lexical value string What triggered the Error: The time zone of this lexical value was invalid.What should we do to fix it: Make sure that the lexical value format is correct. If this error occurs during bulk load from a staging table then this may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55307

ORA-55307: invalid value type for lexical value: string What triggered the Error: The value type of this lexical value was invalid.What should we do to fix it: Make sure that the lexical value format is correct. If this error occurs during bulk load from a staging table, then this may be an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55306

ORA-55306: internal error: invalid string: value_name=string value_type=string What triggered the Error: The value type of this component of the RDF triple was invalid.What should we do to fix it: This is an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55305

ORA-55305: reification constructor functions not supported What triggered the Error: Unsupported reification constructor functions were used.What should we do to fix it: Insert each triple in the reification quad individually. See documentation for more information.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55304

ORA-55304: specified reuse-bNode model-id string != target model-id string What triggered the Error: The reuse-bNode model-id specified for the SDO_RDF_TRIPLE_S constructor was neither 0 nor the model-id of the target model.What should we do to fix it: Make sure that the reuse-bNode model is either 0 or the model-id of the target model.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55303

ORA-55303: SDO_RDF_TRIPLE_S constructor failed: string What triggered the Error: SDO_RDF_TRIPLE_S constructor failed.What should we do to fix it: Check the stack trace for additional information.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55302

ORA-55302: insufficient privileges string What triggered the Error: Sufficient privileges were not granted.What should we do to fix it: Ask the database administrator to grant the appropriate privileges.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55301

ORA-55301: rulebase string does not exist What triggered the Error: The specified rulebase could not be found.What should we do to fix it: Make sure that the rulebase has been created.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55300

ORA-55300: model string does not exist What triggered the Error: The specified model could not be found.What should we do to fix it: Make sure that the model has been created.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55208

ORA-55208: Too many matching levels were found for the input data What triggered the Error: Necessary field values were missing.What should we do to fix it: Add additional field value pairs that are necessary to determine a unique matching level.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55207

ORA-55207: Tag data translation rule evaluation failure What triggered the Error: Error occurred when idcode translator tried to evaluate the rule specified in the tag data translation XML.What should we do to fix it: Java proxy must be set in order to evaluate manager look up rules. Call MGD_ID_UTL.setProxy(PROXY_HOST, PROXY_PORT) to enable java proxy.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55206

ORA-55206: Tag data translation field not found What triggered the Error: Invalid input field.What should we do to fix it: Make sure the spellings of the input fields are correct.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55205

ORA-55205: Tag data translation field validation failed What triggered the Error: Invalid field value.What should we do to fix it: Make sure the field value of the input data is within the range specified in the tag data translation XML.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55204

ORA-55204: Tag data translation option not found What triggered the Error: No matching option could be found for the input data format.What should we do to fix it: Make sure the input data format is supported and correct.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55203

ORA-55203: Tag data translation level not found What triggered the Error: No matching level could be found for the input data format.What should we do to fix it: Make sure the input data format is supported and correct.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55202

ORA-55202: Tag data translation scheme not found What triggered the Error: No matching scheme could be found for the input data format.What should we do to fix it: Make sure the input data format is supported and correct.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55201

ORA-55201: Tag data translation category not found What triggered the Error: No matching category ID could be found.What should we do to fix it: Make sure the input category name, category version or category ID is correct.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-55200

ORA-55200: Java exception from tag data translation java stack What triggered the Error: Java exceptions.What should we do to fix it: Turn on java output by calling dbms_java.set_output(OUTPUT_SIZE); Set java logging level by calling MGD_ID_UTL.setJavaLoggingLevel('INFO'); Analyze java logging messages.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54665

ORA-54665: CREATE_TIN: internal error [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services. ////////////////////////////////////////////////// 55200 - 55300 Reserved for MGD RFID Exceptions //////////////////////////////////////////////////If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54664

ORA-54664: TO_GEOMETRY: internal error [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54663

ORA-54663: CLIP_TIN: internal error [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54662

ORA-54662: CLIP_TIN: query and blkid parameters cannot both be null What triggered the Error: Both the query and blkid parameters were null in the call to the CLIP_TIN operation.What should we do to fix it: Either specify a query geometry that is not null, or specify a blkid for use as a query.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54661

ORA-54661: CLIP_TIN: SRIDs of query and TIN are incompatible What triggered the Error: The TIN and the query geometry had incompatible SRID values.What should we do to fix it: Change the query geometry SRID to be compatible with that of TIN.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54660

ORA-54660: CLIP_TIN: invalid Point Cloud; extent is empty What triggered the Error: The input TIN for the CLIP_TIN operation was invalid.What should we do to fix it: Specify a TIN that was created using the CREATE_TIN operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54659

ORA-54659: CREATE_TIN: input extent has to be 2-D for geodetic data What triggered the Error: The extent of the TIN was more than 2-D for geodetic data.What should we do to fix it: Change the extent to 2-D (longitude, latitude).If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54658

ORA-54658: CREATE_TIN: input extent cannot be null What triggered the Error: The extent of the TIN was null.What should we do to fix it: Specify an extent for the TIN that is not null.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54657

ORA-54657: CREATE_TIN: error writing TIN LOB What triggered the Error: An internal LOB write error occurred during TIN creation. The cause might be lack of table space.What should we do to fix it: Look for information from other errors in the stack, or contact Oracle Support Services with the error number reported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54656

ORA-54656: CREATE_TIN: error fetching data from input points table What triggered the Error: An internal read error occurred during TIN creation.What should we do to fix it: Contact Oracle Support Services with the error number reported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54655

ORA-54655: CREATE_TIN: scratch tables/views(string) exist and need to be dropped What triggered the Error: Transient tables from previous CREATE_TIN operation still existed.What should we do to fix it: Delete the invalid TIN from the base table (for cleanup of scratch tables), and initialize and create the TIN again. Alternately, use SDO_UTIL.DROP_WORK_TABLES with oidstring as its parameter.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54654

ORA-54654: CREATE_TIN: input points table should not be empty What triggered the Error: The input points table had no data.What should we do to fix it: Insert data into the input points table, and then create the TIN.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54653

ORA-54653: CREATE_TIN: specified total dimensionality cannot exceed 8 What triggered the Error: The specified total dimensionality for the TIN exceeded the maximum limit of 8.What should we do to fix it: Create the TIN with fewer dimensions. You can store the rest in the output points table.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54652

ORA-54652: CREATE_TIN: input points table string does not exist What triggered the Error: The specified table for loading points into a TIN did not exist.What should we do to fix it: Create the points table with appropriate columns, and then create the TIN.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54651

ORA-54651: CREATE_TIN: invalid parameters specified in creation of TIN What triggered the Error: An invalid or unknown parameter was specified in the creation of the TIN.What should we do to fix it: Check the Spatial documentation for CREATE_TIN.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54644

ORA-54644: PARTITION_TABLE utility: error in reading input, output tables What triggered the Error: The names for the input/output tables were invalid, or the tables did not exist or did not have the right structure.What should we do to fix it: Check the Spatial documentation for PARTITION_TABLE.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54643

ORA-54643: PARTITION_TABLE utility: invalid WORKTABLESPACE parameter What triggered the Error: An invalid string was specified for the WORKTABLESPACE parameter.What should we do to fix it: Specify an existing valid tablespace for WORKTABLESPACE (to hold the scratch tables).If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54642

ORA-54642: PARTITION_TABLE utility: invalid SORT_DIMENSION specified What triggered the Error: An invalid string was specified for the SORT_DIMENSION.What should we do to fix it: Specify the SORT_DIMENSION as 'BEST_DIM', 'DIMENSION_1', 'DIMENSION_2', or 'DIMENSION_3'.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54641

ORA-54641: PARTITION_TABLE utility: scratch tables exist with oidstr = string What triggered the Error: Scratch tables/views could not be created because they already existed.What should we do to fix it: Use SDO_UTIL.DROP_WORK_TABLES with the specified oidstr parameter to clean up the scratch tables.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54640

ORA-54640: PARTITION_TABLE utility: invalid input parameters [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54623

ORA-54623: CREATE_PC: internal error [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54622

ORA-54622: TO_GEOMETRY: internal error [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54621

ORA-54621: TO_GEOMETRY: TOTAL_DIMENSIONALITY not same as in INIT operation What triggered the Error: The specified TOTAL_DIMENSIONALITY was invalid.What should we do to fix it: Ensure that the TOTAL_DIMENSIONALITY matches that specified in the call to the initialization operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54620

ORA-54620: CLIP_PC: internal error [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54619

ORA-54619: CLIP_PC: query and BLKID parameters cannot both be null What triggered the Error: Both the query and BLKID parameters were null in the call to the CLIP_PC operation.What should we do to fix it: Either specify a query geometry that is not null, or specify a BLKID for use as a query.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54618

ORA-54618: CLIP_PC: SRIDs of query and Point Cloud are incompatible What triggered the Error: The Point Cloud and the query geometry had incompatible SRID values.What should we do to fix it: Change the query SRID to be compatible with that of the Point Cloud.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54617

ORA-54617: CLIP_PC: invalid Point Cloud; extent is empty What triggered the Error: The input Point Cloud for the CLIP_PC operation was invalid.What should we do to fix it: Specify a point cloud that was created using the CREATE_PC procedure.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54616

ORA-54616: INIT: internal error [number, string] What triggered the Error: An internal error occurred.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54614

ORA-54614: INIT: block table name has to be unique What triggered the Error: The specified block table name was not unique. For example, it might have been used for another block table.What should we do to fix it: Specify a different block table name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54613

ORA-54613: INIT: internal error creating DML trigger What triggered the Error: The necessary privileges to create the trigger were not granted.What should we do to fix it: Grant the necessary privileges to create the trigger. If necessary, contact Oracle Support Services for help with privileges for trigger creation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54611

ORA-54611: INIT: either invalid basetable/schema or they do not exist What triggered the Error: The base table or schema, or both, were invalid strings; or the base table and schema combination did not exist.What should we do to fix it: Ensure that the specified base table exists in the specified schema before performing the initialization operation.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54610

ORA-54610: CREATE_PC: input extent cannot be more than 2-D for geodetic data What triggered the Error: The extent of the Point Cloud was more than 2-D for geodetic data.What should we do to fix it: Change the extent to 2-D (longitude, latitude).If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54609

ORA-54609: CREATE_PC: input extent cannot be null What triggered the Error: The extent of the Point Cloud was null.What should we do to fix it: Specify an extent for the Point Cloud that is not null.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54608

ORA-54608: CREATE_PC: error writing Point Cloud LOB What triggered the Error: An internal LOB write error occurred during Point Cloud creation. The cause might be lack of table space.What should we do to fix it: Look for information from other errors in the stack, or contact Oracle Support Services with the error number reported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54607

ORA-54607: CREATE_PC: error fetching data from input points table What triggered the Error: An internal read error occurred during Point Cloud creation.What should we do to fix it: Contact Oracle Support Services with the error number reported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54605

ORA-54605: CREATE_PC: scratch-tables/views (string) exist and need to be dropped What triggered the Error: Transient tables/views from a previous CREATE_PC operation were still in existence.What should we do to fix it: Delete the invalid Point Cloud from the base table (for cleanup of scratch tables), and initialize and create the Point Cloud again. Alternately, use SDO_UTIL.DROP_WORK_TABLES with oidstring as the parameter.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54604

ORA-54604: CREATE_PC: input points table should not be empty What triggered the Error: The input points table had no data.What should we do to fix it: Insert data into the input points table and then create the Point Cloud.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54603

ORA-54603: CREATE_PC: specified total dimensionality cannot exceed 8 What triggered the Error: The specified total dimensionality for the Point Cloud exceeded the maximum limit of 8.What should we do to fix it: Create the Point Cloud with fewer dimensions. You can store the rest in the output points table.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54602

ORA-54602: CREATE_PC: input points table string does not exist What triggered the Error: The specified table for loading points into a Point Cloud did not exist.What should we do to fix it: Create the points table with appropriate columns, and then create the Point Cloud.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54601

ORA-54601: CREATE_PC: invalid parameters for creation of Point Cloud What triggered the Error: An invalid or unknown parameter was specified in the creation of Point Cloud.What should we do to fix it: Check for valid set of parameters.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54557

ORA-54557: incomplete composite solid What triggered the Error: The end of composite solid was reached before all necessary solids were defined.What should we do to fix it: Add more solids to match the geometry definition, or reduce the specified number of solids.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54556

ORA-54556: operation is not supported for 3-D geometry What triggered the Error: A 3-D geometry was passed into an operation that supports only 2-D geometries.What should we do to fix it: Check the Spatial documentation for operations that are supported and not supported on 3-D geometries.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54555

ORA-54555: invalid geometry dimension What triggered the Error: The geometry did not have three dimensions.What should we do to fix it: Ensure that geometry has three dimensions.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54554

ORA-54554: arcs are not supported as defined What triggered the Error: An arc was defined in a geometry type in which arcs are not supported. Arcs are supported for 2-D (circle) polygons, 2-D compound polygons, 2-D single arc, and 2-D compound (composite) curves only.What should we do to fix it: Remove or simplify the arcs.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54553

ORA-54553: incorrect geometry for appending What triggered the Error: The geometry could not be appended to a homogeneous collection (for example, multi-geometry) or to a heterogeneous geometry (for example, collection). In other words, the gtype of the geometry to be appended was neither GYTPE_COLLECTION or GTYPE_MULTI-X (where X is point, curve, surface, or solid).What should we do to fix it: Ensure that the geometries involved in the append operation have appropriate gtypes.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54552

ORA-54552: height entries must be >= to ground height entries What triggered the Error: In the definition of a solid, the height values were less than the ground height.What should we do to fix it: Ensure that that height values are greater than or equal to ground height values.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54551

ORA-54551: grdHeight and/or Height array sizes incorrect What triggered the Error: The sizes of grdHeight and Height arrays were not equal to half the size of input 2-D polygon's ordinates array. As a result, each point in the 2-D polygon could not be extruded from the grdHeight entry to the Height entry.What should we do to fix it: Ensure that the sizes of the grdHeight and Height arrays are half that of input 2-D polygon ordinates array.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54550

ORA-54550: input 2-D polygon not valid What triggered the Error: The 2-D polygon violated the rules for polygons and rings.What should we do to fix it: Correct the polygon definition.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54549

ORA-54549: input geometry has incorrect elemInfo What triggered the Error: The input 2-D polygon did not have only one outer ring.What should we do to fix it: Ensure that the input 2-D polygon has only one outer ring.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54548

ORA-54548: input geometry gtype must be GTYPE_POLYGON for extrusion What triggered the Error: The input geometry gtype was not GTYPE_POLYGON.What should we do to fix it: Ensure that the gtype of the input polygon is GTYPE_POLYGON.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54547

ORA-54547: wrong input for COUNT_SHARED_EDGES What triggered the Error: The COUNT_SHARED_EDGES parameter value was not 1 or 2.What should we do to fix it: Ensure that the COUNT_SHARED_EDGES parameter value is either 1 or 2.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54546

ORA-54546: volume of solid cannot be 0 or less What triggered the Error: The solid geometry having one outer and multiple inner geometries had a negative or zero volume.What should we do to fix it: Correct the orientation or specification of the outer solid geometry to obey outer geometry rules so that the outer geometry has a positive volume. Additionally, correct the orientation or specification of inner solid geometries to obey inner geometry rules so that each inner geometry has a negative volume.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54545

ORA-54545: holes incorrectly defined What triggered the Error: The holes were defined with incorrect etype.What should we do to fix it: Ensure that the etype is correct in the definition of the inner geometry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54540

ORA-54540: at least one element must be a surface or solid What triggered the Error: One of the geometries had holes, and the geometries were neither (A) simple, composite, or multisurfaces, or (B) simple, composite, or multisolids. (Surfaces and solids are the only geometries that can have holes. Points and curves cannot have holes.)What should we do to fix it: Ensure that each geometry having holes is a surface or solid (simple, composite, or multi).If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54539

ORA-54539: cannot process the geometry(s) for this operation What triggered the Error: The geometry had errors in it.What should we do to fix it: Validate the geometry or geometries to ensure that each is valid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54538

ORA-54538: unexpected gtype What triggered the Error: The gtype of the geometry was not GTYPE_SOLID, GTYPE_SURFACE, GTYPE_CURVE or GTYPE_POINT.What should we do to fix it: Correct the elemInfo array to fix any invalid gtype and etypes that violate the geometry hierarchy.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54537

ORA-54537: incorrect box surface due to wrong orientation What triggered the Error: The rectangular surface in shortcut format did not have its first x,y,z coordinates all greater than or equal to or all less than or equal to its second x,y,z coordinates.What should we do to fix it: Ensure that the first x,y,z coordinates are either all greater than or equal to or all less than or equal to the second x,y,z coordinates.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54536

ORA-54536: axis aligned box surface not defined properly What triggered the Error: The inner geometry etype did not start with 2, or the outer geometry etype did not start with 1, or both occurred.What should we do to fix it: Use the correct etype for the inner and outer geometries.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54535

ORA-54535: incorrect box surface because it is on arbitrary plane What triggered the Error: The axis aligned box surface was not on the yz, xz, or xy plane.What should we do to fix it: Ensure that the first and fourth coordinates, or the second and fifth coordinates, or the third and sixth coordinates are the same. This means that the surface is on the yz, xz or xy plane, respectively.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54534

ORA-54534: incorrect box surface due to wrong specification What triggered the Error: The elemInfo definition was not correct for the surface of the axis aligned box.What should we do to fix it: Change the interpretation to 3 in the elemInfo definition.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54533

ORA-54533: invalid etype in composite surface of solid What triggered the Error: The etype of the composite surface of a solid was not valid.What should we do to fix it: Ensure that the etype is orient*1000+ETYPE_SOLID, where orient is 1 for outer solid and 2 for inner solid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54532

ORA-54532: incomplete composite surface What triggered the Error: The end of composite surface was reached before all necessary surfaces were defined.What should we do to fix it: Add more surfaces to match the geometry definition, or reduce the specified number of surfaces.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54531

ORA-54531: invalid orientation for element at element offset What triggered the Error: The orientation of the current geometry was not valid.What should we do to fix it: Reverse the orientation of the geometry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54530

ORA-54530: invalid etype for element at element offset What triggered the Error: An invalid etype was encountered.What should we do to fix it: Correct the etype of the geometry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54529

ORA-54529: geometry should have multi-level hierarchy (like triangle) What triggered the Error: The geometry did not have the multi-level hierarchy required for this operation. For example, if the parameter to element extractor (hierarchy level) is not LOWER_LEVEL, but the geometry etype is ETYPE_SOLID and gtype is GTYPE_SOLID, an extract operation is not allowed, because a simple solid can only be decomposed into lower level geometries, such as composite surfaces.What should we do to fix it: Ensure that the geometry has the appropriate hierarchy. For example, if the geometry etype is ETYPE_SOLID and gtype is GTYPE_SOLID, the parameter to element extractor (hierarchy level) should be LOWER_LEVEL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54528

ORA-54528: inner composite surfaces or surfaces with inner ring(s) expected What triggered the Error: An INNER_OUTER parameter to element extractor was attempted on a surface that was not simple or composite.What should we do to fix it: Ensure that the etype of the geometry for the INNER_OUTER parameter to element extractor is ETYPE_SURFACE or ETYPE_COMPOSITESURFACE.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54527

ORA-54527: operation not permitted on a simple geometry What triggered the Error: A MULTICOMP_TOSIMPLE parameter to element extractor was attempted on a geometry that is already simple.What should we do to fix it: Do not use the MULTICOMP_TOSIMPLE parameter to element extractor on simple geometries.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54526

ORA-54526: multi or composite geometry must be decomposed before extraction What triggered the Error: The extraction could not be performed because the multi or composite geometry must first be decomposed into simple geometries (with or without inner geometries). The multi or composite geometry had a gtype of GTYPE_MULTISOLID, GTYPE_MULTISURFACE, GTYPE_MULTICURVE, GTYPE_MULTIPOINT, or GTYPE_COLLECTION, or the geometry was a line string.What should we do to fix it: Use the MULTICOMP_TOSIMPLE parameter to element extractor to decompose the multi or composite geometry to a simple geometry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54525

ORA-54525: incorrect box volume due to wrong ordinates What triggered the Error: The rectangular box in shortcut format did not have its first x,y,z coordinates either all greater or less than its second x,y,z coordinates.What should we do to fix it: Make sure that the first x,y,z coordinates are either all greater or all less than the second x,y,z coordinates.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54524

ORA-54524: inner ring cannot be inside another inner ring of same outer ring What triggered the Error: An inner ring was inside another ring of the same outer ring.What should we do to fix it: Ensure that no inner ring is inside another inner ring of the same outer ring.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54523

ORA-54523: inner rings of same outer ring cannot touch more than once What triggered the Error: Two inner rings of the same outer ring touched more than once.What should we do to fix it: Ensure that inner rings of the same outer ring touch at no more than one point.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54522

ORA-54522: inner rings of same outer ring cannot intersect or share boundary What triggered the Error: Two inner rings of the same outer ring intersected or shared a boundary.What should we do to fix it: Ensure that line segments of an inner ring do not intersect or fully or partially coincide with line segments of another inner ring sharing the same outer ring.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54521

ORA-54521: inner ring is not inside or is touching outer ring more than once What triggered the Error: An inner ring either was not inside its outer ring or touched its outer ring more than once.What should we do to fix it: Ensure that the inner ring is inside its outer ring and does not touch the outer ring more than once. If an inner ring touches its outer ring more than once, then the outer ring is no longer a topologically simple or singly connected polygon (ring).If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54520

ORA-54520: inner ring not on the same plane as its outer ring What triggered the Error: An inner ring was not on the same plane as its outer ring.What should we do to fix it: Ensure that each inner ring is on the same plane as its outer ring.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54519

ORA-54519: polygon (surface) not attached to composite surface What triggered the Error: Not all polygons of a surface had a common (fully or partially shared) edge.What should we do to fix it: Ensure that each polygon is attached to the composite surface by one of its edges.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54518

ORA-54518: shared edge of composite surface not oriented correctly What triggered the Error: A shared edge (one shared by two polygons) in a composite surface was not correctly oriented. Each shared edge must be oriented in one direction with respect to its first polygon and then in the reverse direction with respect to its second polygon.What should we do to fix it: Reverse one of the directions of the shared edge with respect to its polygons.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54517

ORA-54517: outer ring is on the same plane and overlaps another outer ring What triggered the Error: An outer ring in a composite surface shared a common area with another outer ring.What should we do to fix it: Ensure that no outer rings fully or partially overlap.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54516

ORA-54516: adjacent outer rings of composite surface cannot be on same plane What triggered the Error: The conditional flag was set, and a composite surface had at least two outer rings sharing a common edge on the same plane.What should we do to fix it: Change those outer rings into one larger outer ring.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54515

ORA-54515: outer rings in a composite surface intersect What triggered the Error: Outer rings, either on the same plane or different planes, in a composite surface intersected.What should we do to fix it: Ensure that outer rings do not intersect. They can share edges.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54514

ORA-54514: overlapping areas in multipolygon What triggered the Error: A multipolygon geometry contained one or more common (shared, fully or partially overlapped) polygons.What should we do to fix it: Ensure that no polygons in a multipolygon overlap.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54513

ORA-54513: inner solid surface overlaps outer solid surface What triggered the Error: One or more faces of an inner solid surface either fully or partially overlapped an outer solid surface.What should we do to fix it: Ensure that inner and outer surfaces have no shared (fully or partially overlapping) faces.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54512

ORA-54512: a vertex of an inner solid is outside corresponding outer solid What triggered the Error: A solid geometry contained an inner solid with at least one vertex outside its corresponding outer solid.What should we do to fix it: Ensure that all vertices of inner solids are not outside their corresponding outer solid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54511

ORA-54511: edges of inner and outer solids intersect What triggered the Error: An inner solid had a common edge with outer solid.What should we do to fix it: Ensure that edges of inner and outer solids do not intersect.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54510

ORA-54510: no outer geometry expected What triggered the Error: An outer geometry was found when only inner geometries were expected.What should we do to fix it: Remove all outer geometries.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54509

ORA-54509: solid not attached to composite solid What triggered the Error: To connect solids in a composite solid geometry, at least one of the faces of a solid must be shared (fully or partially) with only another solid. However, at least one of the faces in this composite solid was not shared by exactly two solids only.What should we do to fix it: Ensure that at least one face in a composite solid is shared by exactly two solids.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54508

ORA-54508: overlapping surfaces in a multisolid geometry What triggered the Error: The multisolid geometry contained one or more fully or partially overlapping surfaces.What should we do to fix it: Ensure that the multisolid geometry contains no overlapping areas.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54507

ORA-54507: duplicate points in multipoint geometry What triggered the Error: The multipoint geometry had two points that either had identical coordinates or were the same point considering the geometry tolerance.What should we do to fix it: Make sure all points are different, considering the tolerance.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54506

ORA-54506: compound curve not supported for 3-D geometries What triggered the Error: The 3-D geometry contained one or more compound curves, which are not supported for 3-D geometries.What should we do to fix it: Remove all compound curves from the geometry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54505

ORA-54505: ring does not lie on a plane What triggered the Error: The ring was not flat.What should we do to fix it: Make sure all of the vertices of the ring are on the same plane.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54504

ORA-54504: multiple outer geometries What triggered the Error: The geometry contained more than one outer geometry.What should we do to fix it: Remove all but one of the outer geometries.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54503

ORA-54503: incorrect solid orientation What triggered the Error: The orientation of the solid was not correct.What should we do to fix it: Correct the orientation or specification of the outer or inner solid geometry according to the geometry rules for such a solid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54502

ORA-54502: solid not closed What triggered the Error: The solid geometry was not closed i.e., faces of solid are not 2-manifold due to incorrectly defined, oriented, or traversed line segment because each edge of a solid must be traversed exactly twice, once in one direction and once in the reverse direction.What should we do to fix it: Correct the orientation of the edges of the neighboring polygons.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54501

ORA-54501: no holes expected What triggered the Error: The geometry contained one or more unexpected holes.What should we do to fix it: Remove any holes in the geometry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54500

ORA-54500: invalid combination of elements What triggered the Error: The geometry did not start from the correct level in the hierarchy.What should we do to fix it: Correct the hierarchy in the geometry.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54037

ORA-54037: table must have at least 1 column that is not virtual What triggered the Error: An attempt was made to create a table with only virtual columns.What should we do to fix it: Include at least 1 column that is not virtual in the table being created.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54036

ORA-54036: cannot define referential constraint with ON DELETE SET NULL clause on virtual column What triggered the Error: Attempted to specify ON DELETE SET NULL clause for a referential integrity constraint on a virtual column.What should we do to fix it: Reissue the statement without specifying ON DELETE SET NULL clause.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54035

ORA-54035: keyword HIDDEN cannot be specified here What triggered the Error: Attempted to specify HIDDEN key word for a virtual columnWhat should we do to fix it: This is not supported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54034

ORA-54034: virtual columns not allowed in functional index expressions What triggered the Error: An attempt was made to create a functional index with an expression defined on one or more virtual columns.What should we do to fix it: Specify the index expression using only regular columns.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54033

ORA-54033: column to be modified is used in a virtual column expression What triggered the Error: Attempted to modify the data type of a column that was used in a virtual column expression.What should we do to fix it: Drop the virtual column first or change the virtual column expression to eliminate dependency on the column to be modified.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54032

ORA-54032: column to be renamed is used in a virtual column expression What triggered the Error: Attempted to rename a column that was used in a virtual column expression.What should we do to fix it: Drop the virtual column first or change the virtual column expression to eliminate dependency on the column to be renamed.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54031

ORA-54031: column to be dropped is used in a virtual column expression What triggered the Error: Attempted to drop a column that was used in a virtual column expression.What should we do to fix it: Drop the virtual column first or change the virtual column expression to eliminate dependency on the column to be dropped.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54030

ORA-54030: datatype mismatch between virtual column and expression What triggered the Error: virtual column expression was changed after column was created"What should we do to fix it: change the underlying expression to return datatype that conforms to the virtual columnIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54029

ORA-54029: Virtual column cannot be updated in trigger body What triggered the Error: Attempted to change the value of virtual column in a trigger body"What should we do to fix it: This is not valid, change the trigger definition.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54028

ORA-54028: cannot change the HIDDEN/VISIBLE property of a virtual column What triggered the Error: Attempted to change the HIDDEN/VIRTUAL property of a virtual columnWhat should we do to fix it: re-issue the DDL without the virtual column property changeIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54027

ORA-54027: cannot modify data-type of virtual column What triggered the Error: Attempted to change the data-type of virtual column without modifying the underlying expressionWhat should we do to fix it: change the underlying expression to be compatible with the data-type changeIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54026

ORA-54026: Real column cannot have an expression What triggered the Error: Attempted to alter a real column to have an expression.What should we do to fix it: This is not valid, change the DDL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54025

ORA-54025: Virtual column cannot have a default value What triggered the Error: Attempted to alter a virtual column to have a default value.What should we do to fix it: This is not valid, change the DDL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54024

ORA-54024: expression column is not supported for an organization cube table What triggered the Error: Attempted to create or alter an organization cube table with an expression columnWhat should we do to fix it: These columns are not supported, change the DDL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54023

ORA-54023: Virtual column expression cannot be changed because a constraint is defined on column What triggered the Error: Attempted to change the expression of a virtual column that had a constraint defined on it.What should we do to fix it: Drop constraint and then change expression.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54022

ORA-54022: Virtual column expression cannot be changed because an index is defined on column What triggered the Error: Attempted to change the expression of a virtual column that was indexed.What should we do to fix it: Alter index unsable. Change expression and then rebuild index.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54021

ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns What triggered the Error: Attempted to partition a table on a virtual column that contained PL/SQL expressions.What should we do to fix it: This is not supported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54020

ORA-54020: Virtual column expression cannot be changed because it is a subpartitioning column What triggered the Error: Attempted to modify the expression of a virtual column that was also a subpartitioning column.What should we do to fix it: This is not supported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54019

ORA-54019: Virtual column expression cannot be changed because it is a partitioning column What triggered the Error: Attempted to modify the expression of a virtual column that was also a partitioning column.What should we do to fix it: This is not supported.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54018

ORA-54018: A virtual column exists for this expression What triggered the Error: Specified index expression matches an existing virtual column"What should we do to fix it: Re-issue the statment by replacing the index expression with the matching virtual columnIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54017

ORA-54017: UPDATE operation disallowed on virtual columns What triggered the Error: Attempted to update values of a virtual columnWhat should we do to fix it: Re-issue the statment without setting values for the virtual columnIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54016

ORA-54016: Invalid column expression was specified What triggered the Error: Virtual column expression is not a valid arithmetic expression. it probably refers to another column in the tableWhat should we do to fix it: Change the expression of the virtual columnIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54015

ORA-54015: Duplicate column expression was specified What triggered the Error: Expression of the virtual column being added/created conflicts with an existing/previously specified functional index expression or virtual column expressionWhat should we do to fix it: Change the expression of the virtual column os there are no duplicate expressionsIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54014

ORA-54014: Resulting table from a CTAS operation contains virtual column(s) What triggered the Error: Table being created by a CTAS operation contains a virtual column definitionWhat should we do to fix it: Remove the virtual column definition from the table being createdIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54013

ORA-54013: INSERT operation disallowed on virtual columns What triggered the Error: Attempted to insert values into a virtual columnWhat should we do to fix it: Re-issue the statment without providing values for a virtual columnIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54012

ORA-54012: virtual column is referenced in a column expression What triggered the Error: This virtual column was referenced in an expression of another virtual columnWhat should we do to fix it: Ensure the column expression definition for any virtual column does not refer to any virtual columnIf this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54011

ORA-54011: expression column is not supported for a clustered table What triggered the Error: Attempt to create/alter a clustered table with an expression columnWhat should we do to fix it: These columns are not supported, change the DDL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54010

ORA-54010: expression column is not supported for a temporary table What triggered the Error: Attempt to create/alter a temporary table with an expression columnWhat should we do to fix it: These columns are not supported, change the DDL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54009

ORA-54009: expression column is not supported for an external table What triggered the Error: Attempt to create/alter an external table with an expression columnWhat should we do to fix it: These columns are not supported, change the DDL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54008

ORA-54008: expression column is not supported for an index organized table What triggered the Error: Attempt to create/alter an index organized table with an expression columnWhat should we do to fix it: These columns are not supported, change the DDL.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54007

ORA-54007: keyword HIDDEN cannot be specified here What triggered the Error: The keyword HIDDEN was either repeated or incorrectly specified.What should we do to fix it: Remove the keyword from the specified syntax.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54006

ORA-54006: keyword VISIBLE cannot be specified here What triggered the Error: The keyword VISIBLE was either repeated or incorrectly specified.What should we do to fix it: Remove the keyword from the specified syntax.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54005

ORA-54005: keyword VIRTUAL cannot be specified here What triggered the Error: The keyword VIRTUAL was either repeated or incorrectly specified.What should we do to fix it: Remove the keyword from the specified syntax.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54004

ORA-54004: resultant data type of virtual column is not supported What triggered the Error: The data type of the underlying expression is not supported. Only scalar data types are supported for virtual columns. LONG, BLOB, REF, and BFILE data types are not supported for virtual columns.What should we do to fix it: Specify the expression of virtual column to return a supported scalar data type.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54003

ORA-54003: specified data type is not supported for a virtual column What triggered the Error: Only scalar data types are supported for virtual columns. LONG, BLOB, REF, and BFILE data types are not supported for virtual columns.What should we do to fix it: Specify the expression column with a supported scalar data type.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54002

ORA-54002: only pure functions can be specified in a virtual column expression What triggered the Error: Column expression contained a function whose evaluation is non-deterministic.What should we do to fix it: Rewrite column expression to reference only pure functions.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54001

ORA-54001: string: invalid identifier specified for virtual column expression What triggered the Error: Column expression referenced a column that does not exist in the table.What should we do to fix it: Rewrite column expression to reference only scalar columns in the table.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-54000

ORA-54000: Virtual column feature not yet implemented What triggered the Error: Feature has not been implemented.What should we do to fix it: Feature is being implemented.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53990

ORA-53990: internal error string. What triggered the Error: An internal error occurred while attempting to process a DICOM object.What should we do to fix it: Contact Oracle Support Services, and supply them with a script that can be duplicated as well as the DICOM object that caused this error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53980

ORA-53980: unimplemented feature: string What triggered the Error: The specified feature is not implemented.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53940

ORA-53940: make anonymous error string. What triggered the Error: An error occurred while attempting to make DICOM object anonymous.What should we do to fix it: Ensure that the anonymity document content is valid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53930

ORA-53930: XSLT error string. What triggered the Error: An error occurred while attempting to process XML metadata.What should we do to fix it: Ensure that Oracle XDB and all related schemas are properly installed. And, verify that the mapping and anonymity documents are valid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53920

ORA-53920: XML error string. What triggered the Error: An error occurred when attempting to read from and write to XML metadata.What should we do to fix it: Ensure that Oracle XDB and all related schemas are properly installed.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53910

ORA-53910: SQL error string. What triggered the Error: An error occurred when attempting to run a SQL command.What should we do to fix it: If you are using a JDBC connection string, ensure that the connection string is valid. Check the user account to see if it is locked. Ensure that the listener and the database server are running. And ensure that the setDataModel() function has been invoked before any other DICOM functions are called.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53900

ORA-53900: I/O failure string. What triggered the Error: A device I/O failure occurred when attempting to read from and write to a DICOM object.What should we do to fix it: Check the permissions and privileges that have been granted for I/O operations.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53820

ORA-53820: Invalid locator path. What triggered the Error: A locator path for an attribute was invalid.What should we do to fix it: Please verify the locator path is properly constructed. All attribute used in the locator path must be defined in the DICOM standard and must not be retired. An attribute that is not the last one of the locator path must be a sequence type. If the locator path is correct, please verify the DICOM data dictionary is up-to-date. If the data dictionary is obsolete, please use the ORD_DICOM_ADMIN API to update the data dictionary.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53810

ORA-53810: error logging conformance validation messages What triggered the Error: An error occurred while trying to log conformance validation messages.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53800

ORA-53800: The DICOM object does not contain attribute string. What triggered the Error: The DICOM object did not contain the attribute that was required for conformance validation.What should we do to fix it: Determine if the locator path is properly constructed. All attributes used in the locator path must be defined in the DICOM standard, and must not be retired. All attributes except the last attribute in the locator path must be of sequence type. If the locator path is correct, verify if the DICOM data dictionary is up to date. If the data dictionary is obsolete, update it using the ORD_DICOM_ADMIN repository API.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53502

ORA-53502: Image processing failure. What triggered the Error: An error occurred when processing a DICOM image.What should we do to fix it: Determine if the DICOM image is corrupt, and if it is supported by the current release.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53500

ORA-53500: Not a DICOM image. What triggered the Error: The binary object was not a DICOM image.What should we do to fix it: Determine if the DICOM object is an image. Only DICOM images can be processed with image processing functions.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53434

ORA-53434: Not a DICOM object. What triggered the Error: The binary object is not a DICOM object.What should we do to fix it: Determine if the DICOM object is corrupt. If the DICOM object conforms to the DICOM standard, contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53432

ORA-53432: The DICOM object attribute string has invalid definer name. What triggered the Error: A private attribute definer for a private attribute contained in this DICOM object had invalid characters.What should we do to fix it: Determine if the DICOM object is corrupt. If the DICOM object conforms to the DICOM standard, contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53430

ORA-53430: The DICOM object contains unsupported values string. What triggered the Error: This type of DICOM object is not supported by the current release.What should we do to fix it: Check for software updates, and contact Oracle Support Services for information about feature enhancements.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53414

ORA-53414: The DICOM object contains undefined values string. What triggered the Error: The DICOM object had attribute values that were expected to be part of the data model.What should we do to fix it: Determine if the DICOM object conforms to the DICOM standard. If it does, verify if the data model matches the DICOM object or is more recent than the DICOM object. If the data model is obsolete, update it using the ORD_DICOM_ADMIN repository API, if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53412

ORA-53412: The DICOM object contains an invalid VR value string. What triggered the Error: The DICOM object with explicit value representation encoding had attribute VR values that did not match their definitions in the data dictionary.What should we do to fix it: Determine if the DICOM object conforms to the DICOM standard. If it does, verify if the data dictionary matches the DICOM object or is more recent than the DICOM object. If the data dictionary is obsolete, update it using the ORD_DICOM_ADMIN repository API, if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53410

ORA-53410: The attribute string does not conform to the VM rule. What triggered the Error: The DICOM object either contained an attribute that did not conform to the DICOM value multiplicity rule or was missing an attribute that was required by the DICOM standard.What should we do to fix it: Determine if the DICOM object conforms to the DICOM standard. If it does, verify if the data dictionary matches the DICOM object or is more recent than the DICOM object. If the data dictionary is obsolete, update it using the ORD_DICOM_ADMIN repository API, if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53408

ORA-53408: The DICOM object encoding is wrong string. What triggered the Error: The DICOM object did not conform to the DICOM standard's binary encoding rules.What should we do to fix it: Determine if the DICOM object is corrupt. If the DICOM object is not corrupt, verify whether it conforms to the binary encoding rules in the DICOM standard. If the DICOM object is corrupt, fix it if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53406

ORA-53406: The DICOM object contains invalid attribute value string. What triggered the Error: The DICOM object did not conform to the DICOM standard and contained invalid attribute values.What should we do to fix it: Fix the DICOM object or the DICOM object source, if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53404

ORA-53404: Missing the mandatory DICOM attribute string. What triggered the Error: One or more mandatory DICOM attributes were missing from the DICOM object.What should we do to fix it: Fix the DICOM object or the DICOM object source, if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53402

ORA-53402: Missing DICOM header. What triggered the Error: The DICOM object did not contain the file preamble.What should we do to fix it: Fix the DICOM object or the DICOM object source, if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53400

ORA-53400: Missing DICOM magic number. What triggered the Error: The DICOM object did not contain the DICOM magic number "dicm" required by part 10 of the DICOM standard.What should we do to fix it: Fix the DICOM object or the DICOM object source, if possible. Otherwise, update the DICOM preference document to ignore this category of error.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53259

ORA-53259: cannot extract metadata that conforms to the schema definition What triggered the Error: The extracted metadata did not conform to its schema definition.What should we do to fix it: Check the metadata schema definition, the mapping document and the parameters for the extractMetadata method to ensure that they are correct. See the Oracle Multimedia documentation for information about creating repository documents and extracting metadata.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53258

ORA-53258: Metadata attribute is not available. What triggered the Error: The value of the metadata attribute of the ORDDicom object was null.What should we do to fix it: Call the setProperties method first.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53257

ORA-53257: Attribute does not exist. What triggered the Error: The specified attribute did not exist.What should we do to fix it: Ensure that the attribute name is valid.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53256

ORA-53256: cannot export to a null or invalid destination data type What triggered the Error: A null or invalid destination data type was specified in the export procedure.What should we do to fix it: See the Oracle Multimedia documentation for information about the supported destination data types for the DICOM object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53255

ORA-53255: cannot import from a null or invalid source type What triggered the Error: A null or invalid source type was specified in the import procedure.What should we do to fix it: See the Oracle Multimedia documentation for information about the supported source types for the DICOM object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53254

ORA-53254: The SOP INSTANCE UID for the new DICOM object is invalid. What triggered the Error: The SOP INSTANCE UID for the new DICOM object was invalid.What should we do to fix it: See the Oracle Multimedia documentation for information about creating a valid SOP INSTANCE UID.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53253

ORA-53253: The metadata for the new DICOM object is invalid. What triggered the Error: The metadata for the new DICOM object did not conform to the default metadata schema (ordcmmd.xsd).What should we do to fix it: Check the metadata argument to ensure it has the correct namespace and conforms to the default metadata schema (ordcmmd.xsd). See the Oracle Multimedia documentation for information about the default metadata schema (ordcmmd.xsd).If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53252

ORA-53252: Constraint does not exist. What triggered the Error: The specified constraint did not exist.What should we do to fix it: Check the installed constraints, and correct the statement to pass an installed constraint name.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53251

ORA-53251: Anonymity document does not exist. What triggered the Error: The specified anonymity document did not exist.What should we do to fix it: Check the installed anonymity definition documents, and correct the statement to pass the name of an installed anonymity definition document.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53250

ORA-53250: Mapping document does not exist. What triggered the Error: The specified mapping document did not exist.What should we do to fix it: Check the installed mapping documents, and correct the statement to pass the name of an installed mapping document.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53234

ORA-53234: The destination BLOB locator is null. What triggered the Error: The destination BLOB locator was null.What should we do to fix it: Correct the statement to pass an initialized BLOB locator.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53233

ORA-53233: unable to write to a nonlocal destination ORDImage object What triggered the Error: The source attribute of the destination ORDImage object was not local.What should we do to fix it: See the Oracle Multimedia documentation for information about constructing a local ORDImage object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53232

ORA-53232: unable to write to an invalid destination ORDImage object What triggered the Error: The value of the source attribute of the destination ORDImage object was null.What should we do to fix it: See the Oracle Multimedia documentation for information about constructing a valid ORDImage object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53231

ORA-53231: unable to write to a nonlocal destination ORDDicom object What triggered the Error: The source attribute of the destination ORDDicom object was not local.What should we do to fix it: See the Oracle Multimedia documentation for information about constructing a local ORDDicom object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53230

ORA-53230: unable to write to an invalid destination ORDDicom object What triggered the Error: The value of the source attribute or the extension attribute of the destination ORDDicom object was null.What should we do to fix it: See the Oracle Multimedia documentation for information about constructing a valid ORDDicom object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53217

ORA-53217: The source LOB locator is null. What triggered the Error: The source BLOB locator or BFILE locator was null.What should we do to fix it: Correct the statement to pass an initialized LOB locator.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53216

ORA-53216: cannot export the ORDDataSource object with an external source What triggered the Error: The source of the ORDDataSource object was not local.What should we do to fix it: Import the data before calling the export procedure. Or, get the data directly from the external source.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53215

ORA-53215: cannot access ORDDataSource object with invalid source type What triggered the Error: An invalid source type was stored in the ORDDataSource object. Or, an error occurred while trying to retrieve a BFILE while the object status was local.What should we do to fix it: See the Oracle Multimedia documentation for information about the supported source types.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53214

ORA-53214: cannot access DICOM image data with invalid source type What triggered the Error: An invalid source type was stored in the source attribute of the ORDImage object.What should we do to fix it: See the Oracle Multimedia documentation for information about the supported source types for DICOM image data.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53213

ORA-53213: cannot access DICOM data with invalid source type What triggered the Error: An invalid source type was stored in the source attribute of the ORDDicom object.What should we do to fix it: See the Oracle Multimedia documentation for information about the supported source types for DICOM data.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53212

ORA-53212: unable to read invalid ORDImage object: attribute string is null What triggered the Error: The ORDImage object was invalid.What should we do to fix it: See the Oracle Multimedia documentation for information about creating a valid ORDImage object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53211

ORA-53211: unable to read invalid ORDDicom object What triggered the Error: The value of the source attribute or the extension attribute of the ORDDicom object was null.What should we do to fix it: See the Oracle Multimedia documentation for information about creating a valid ORDDicom object.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53210

ORA-53210: unable to read empty DICOM object What triggered the Error: There was no data in the specified DICOM object.What should we do to fix it: See the Oracle Multimedia documentation for information about loading DICOM object data into the database.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53203

ORA-53203: security violation What triggered the Error: A possible security violation was detected.What should we do to fix it: Check the alert log and trace file for more information.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53202

ORA-53202: internal error, argument [string] What triggered the Error: The internal argument was invalid.What should we do to fix it: Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53201

ORA-53201: Argument string is null or invalid. What triggered the Error: The argument was expecting a non-null, valid value, but the value of the passed argument was null or invalid.What should we do to fix it: Check your program and ensure that the caller of the routine does not pass a null or invalid argument value.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53200

ORA-53200: Argument string is null. What triggered the Error: The argument was expecting a non-null value, but the value of the passed argument was null.What should we do to fix it: Check your program and ensure that the caller of the routine does not pass a null argument value.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53126

ORA-53126: assertion failure: string What triggered the Error: The operation failed because an assertion error had occurred.What should we do to fix it: This is an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53125

ORA-53125: invalid range tag: string What triggered the Error: The ord_dicom.setDataModel procedure failed to load the repository because an invalid range tag was found in the dictionary table.What should we do to fix it: This is an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53124

ORA-53124: cannot find VR number for data type: string What triggered the Error: The VR number for the listed data type could not be found in the lookup table.What should we do to fix it: This is an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53122

ORA-53122: invalid document type : string What triggered the Error: The ord_dicom.setDataModel procedure failed to load the repository because an invalid document type was found.What should we do to fix it: This is an internal error. Contact Oracle Support Services.If this was not sufficient go over to:Oracle Official Support Knowledge Center Or maybe those geeks at:StackOverflow Board read more

Oracle Solution for Error ora-53112