Commonly Used Oracle Scripts

How to reset SYSTEM password for Oracle

RUN > sqlplus "/as sysdba"

alter user system account unlock;

How to set SYSTEM login password

alter user system identified by test;

Find count in a given table
This will display a count of all records in a given table. Conditions can also be added thru the where clause

Select count(*) from tablename

How to drop columns on a table

alter table

   table_name

drop column

   col_name1;  -- drop one column

alter table

   table_name

drop

   (col_name1, col_name2);  -- drop many columns

How to move tables

select 'ALTER TABLE TRIDATA_GRAINGER.' || TABLE_NAME || ' MOVE TABLESPACE TRIDATA_GRAINGER_DATA;'

from dba_tables

where owner = 'TRIDATA_GRAINGER'

and tablespace_name = 'TRIDATA_DATA'

How to find all field names for a BOs

select * from all_tab_columns where owner='TRIDATA' and table_name='T_TRIREALESTATECONTRACT'

desc t_trirealestatecontract

How to find build number

select * from environment_properties where property like 'db.build.number';

How to set passwords (e.g. for the system user) to null, making a password not needed to get into the system.

This example resets password to Null for system and for user account mjane 

First, login to the meta schema (pre-2.5 platforms) in the database and run the following SQL command:

update TRIRIGA.USER_CREDENTIALS set TRIRIGA.USER_CREDENTIALS.PASSWORD = NULL where USER_ACCOUNT = 'system';

Note that if you are doing this on a post-2.5.x platform installation you'll need to replace the "TRIRIGA" schema with the "TRIDATA" schema. For example:

update TRIDATA.USER_CREDENTIALS set TRIDATA.USER_CREDENTIALS.PASSWORD = NULL where USER_ACCOUNT = 'system';

or for account mjane

update USER_CREDENTIALS set USER_CREDENTIALS.PASSWORD = NULL where USER_ACCOUNT = 'mjane';

Sequences out of sync primary key violation

After restoring a TRIRIGA database, you start seeing errors like this:

... nested exception is java.sql.SQLException: ORA-00001: unique constraint (TRIDATA.PK_WF_TEMPLATE) violated...

Then run the following script, copy and paste the output, and run that to increment everything by 10000

select 'ALTER SEQUENCE ' || sequence_name || ' INCREMENT by 1000 ; ' from all_sequences where sequence_owner = 'TRIDATA'
UNION ALL
select 'SELECT ' || sequence_name || '.nextval FROM DUAL ;' from all_sequences where sequence_owner = 'TRIDATA'
UNION ALL
select 'ALTER SEQUENCE ' || sequence_name || ' INCREMENT by ' || INCREMENT_BY || ';' from all_sequences where sequence_owner = 'TRIDATA';

This is due to taking a hot backup while there are workflows processing, and/or the application server has a handle on sequences and Oracle hasn't had a chance to write them from the cache to disk for the export.

Copy the results from the above script (results are in the table below) and run it in SQL Plus to increment the sequence by 1000

ALTER SEQUENCE ASSET_INTERFACE_SEQ INCREMENT by 1000 ; ALTER SEQUENCE ATTACHMENT_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE BUDGET_CURRENCY_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE BUDGET_REPORT_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE BUDGET_TOKEN_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE BUDGET_TRANS_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE DM_CONTENT_SEQ INCREMENT by 1000 ; ALTER SEQUENCE DRAWING_SEQ INCREMENT by 1000 ; ALTER SEQUENCE EFGRAPH_VERSION_SEQ INCREMENT by 1000 ; ALTER SEQUENCE ERROR_UID INCREMENT by 1000 ; ALTER SEQUENCE EXPRESSION_SEQ INCREMENT by 1000 ; ALTER SEQUENCE FACT_SPEC_ID INCREMENT by 1000 ; ALTER SEQUENCE HIERARCHY_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE NOTE_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE ORGANIZATION_INTERFACE_SEQ INCREMENT by 1000 ; ALTER SEQUENCE PEOPLE_INTERFACE_SEQ INCREMENT by 1000 ; ALTER SEQUENCE RECURRENCE_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE REMINDER_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE SCHEDULE_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE SEQ_ADMIN_ANALYZE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_AGENT_STARTUP_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_ALT_GUI_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_APP_SERVICE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_AUDIT_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_BOOKMARK_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_BO_NAME INCREMENT by 1000 ; ALTER SEQUENCE SEQ_CAD_DOOR_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_CAD_LABELSTYLE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_CALENDAR_SET_COLUMN INCREMENT by 1000 ; ALTER SEQUENCE SEQ_CALENDAR_SET_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_CM_SIGNATURE INCREMENT by 1000 ; ALTER SEQUENCE SEQ_DM_UPLOAD_TOKEN INCREMENT by 1000 ; ALTER SEQUENCE SEQ_FACTSOID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_FIELD_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_FORMULA_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_GUI_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_HEARTBEAT_EVENT_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_IBS_SPEC INCREMENT by 1000 ; ALTER SEQUENCE SEQ_IBS_SPEC_TYPE INCREMENT by 1000 ; ALTER SEQUENCE SEQ_LABEL_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_LIST_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_LIST_VALUE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_MANAGER_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_MAP_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_NAV_ITEM_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_OBJECT_TYPE_MAP_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_OM_PKG_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_PORTAL_SECTION_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_REM_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_REP_TEMPLATE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_STATE_TRANSITION_FAMILY_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_TRI_SYS_OBJECT_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_UI_TARGET_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_VIS_QUERY_TYPE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_WEB_MESSAGE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_WF_BUILDER_STEP_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_WF_TEMPLATE_ID INCREMENT by 1000 ; ALTER SEQUENCE SEQ_WORD_ID INCREMENT by 1000 ; ALTER SEQUENCE TASK_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE UOM_TYPE_CODE_SEQUENCE INCREMENT by 1000 ; ALTER SEQUENCE WF_EVENT_FUTURE_ID_SEQ INCREMENT by 1000 ; ALTER SEQUENCE WF_EVENT_ID_SEQ INCREMENT by 1000 ; SELECT ASSET_INTERFACE_SEQ.nextval FROM DUAL ; SELECT ATTACHMENT_ID_SEQ.nextval FROM DUAL ; SELECT BUDGET_CURRENCY_ID_SEQ.nextval FROM DUAL ; SELECT BUDGET_REPORT_ID_SEQ.nextval FROM DUAL ; SELECT BUDGET_TOKEN_ID_SEQ.nextval FROM DUAL ; SELECT BUDGET_TRANS_ID_SEQ.nextval FROM DUAL ; SELECT DM_CONTENT_SEQ.nextval FROM DUAL ; SELECT DRAWING_SEQ.nextval FROM DUAL ; SELECT EFGRAPH_VERSION_SEQ.nextval FROM DUAL ; SELECT ERROR_UID.nextval FROM DUAL ; SELECT EXPRESSION_SEQ.nextval FROM DUAL ; SELECT FACT_SPEC_ID.nextval FROM DUAL ; SELECT HIERARCHY_ID_SEQ.nextval FROM DUAL ; SELECT NOTE_ID_SEQ.nextval FROM DUAL ; SELECT ORGANIZATION_INTERFACE_SEQ.nextval FROM DUAL ; SELECT PEOPLE_INTERFACE_SEQ.nextval FROM DUAL ; SELECT RECURRENCE_ID_SEQ.nextval FROM DUAL ; SELECT REMINDER_ID_SEQ.nextval FROM DUAL ; SELECT SCHEDULE_ID_SEQ.nextval FROM DUAL ; SELECT SEQ_ADMIN_ANALYZE_ID.nextval FROM DUAL ; SELECT SEQ_AGENT_STARTUP_ID.nextval FROM DUAL ; SELECT SEQ_ALT_GUI_ID.nextval FROM DUAL ; SELECT SEQ_APP_SERVICE_ID.nextval FROM DUAL ; SELECT SEQ_AUDIT_ID.nextval FROM DUAL ; SELECT SEQ_BOOKMARK_ID.nextval FROM DUAL ; SELECT SEQ_BO_NAME.nextval FROM DUAL ; SELECT SEQ_CAD_DOOR_ID.nextval FROM DUAL ; SELECT SEQ_CAD_LABELSTYLE_ID.nextval FROM DUAL ; SELECT SEQ_CALENDAR_SET_COLUMN.nextval FROM DUAL ; SELECT SEQ_CALENDAR_SET_ID.nextval FROM DUAL ; SELECT SEQ_CM_SIGNATURE.nextval FROM DUAL ; SELECT SEQ_DM_UPLOAD_TOKEN.nextval FROM DUAL ; SELECT SEQ_FACTSOID.nextval FROM DUAL ; SELECT SEQ_FIELD_ID.nextval FROM DUAL ; SELECT SEQ_FORMULA_ID.nextval FROM DUAL ; SELECT SEQ_GUI_ID.nextval FROM DUAL ; SELECT SEQ_HEARTBEAT_EVENT_ID.nextval FROM DUAL ; SELECT SEQ_IBS_SPEC.nextval FROM DUAL ; SELECT SEQ_IBS_SPEC_TYPE.nextval FROM DUAL ; SELECT SEQ_LABEL_ID.nextval FROM DUAL ; SELECT SEQ_LIST_ID.nextval FROM DUAL ; SELECT SEQ_LIST_VALUE_ID.nextval FROM DUAL ; SELECT SEQ_MANAGER_ID.nextval FROM DUAL ; SELECT SEQ_MAP_ID.nextval FROM DUAL ; SELECT SEQ_NAV_ITEM_ID.nextval FROM DUAL ; SELECT SEQ_OBJECT_TYPE_MAP_ID.nextval FROM DUAL ; SELECT SEQ_OM_PKG_ID.nextval FROM DUAL ; SELECT SEQ_PORTAL_SECTION_ID.nextval FROM DUAL ; SELECT SEQ_REM_ID.nextval FROM DUAL ; SELECT SEQ_REP_TEMPLATE_ID.nextval FROM DUAL ; SELECT SEQ_STATE_TRANSITION_FAMILY_ID.nextval FROM DUAL ; SELECT SEQ_TRI_SYS_OBJECT_ID.nextval FROM DUAL ; SELECT SEQ_UI_TARGET_ID.nextval FROM DUAL ; SELECT SEQ_VIS_QUERY_TYPE_ID.nextval FROM DUAL ; SELECT SEQ_WEB_MESSAGE_ID.nextval FROM DUAL ; SELECT SEQ_WF_BUILDER_STEP_ID.nextval FROM DUAL ; SELECT SEQ_WF_TEMPLATE_ID.nextval FROM DUAL ; SELECT SEQ_WORD_ID.nextval FROM DUAL ; SELECT TASK_ID_SEQ.nextval FROM DUAL ; SELECT UOM_TYPE_CODE_SEQUENCE.nextval FROM DUAL ; SELECT WF_EVENT_FUTURE_ID_SEQ.nextval FROM DUAL ; SELECT WF_EVENT_ID_SEQ.nextval FROM DUAL ; ALTER SEQUENCE ASSET_INTERFACE_SEQ INCREMENT by 1; ALTER SEQUENCE ATTACHMENT_ID_SEQ INCREMENT by 1; ALTER SEQUENCE BUDGET_CURRENCY_ID_SEQ INCREMENT by 1; ALTER SEQUENCE BUDGET_REPORT_ID_SEQ INCREMENT by 1; ALTER SEQUENCE BUDGET_TOKEN_ID_SEQ INCREMENT by 1; ALTER SEQUENCE BUDGET_TRANS_ID_SEQ INCREMENT by 1; ALTER SEQUENCE DM_CONTENT_SEQ INCREMENT by 1; ALTER SEQUENCE DRAWING_SEQ INCREMENT by 1; ALTER SEQUENCE EFGRAPH_VERSION_SEQ INCREMENT by 1; ALTER SEQUENCE ERROR_UID INCREMENT by 1; ALTER SEQUENCE EXPRESSION_SEQ INCREMENT by 1; ALTER SEQUENCE FACT_SPEC_ID INCREMENT by 1; ALTER SEQUENCE HIERARCHY_ID_SEQ INCREMENT by 1; ALTER SEQUENCE NOTE_ID_SEQ INCREMENT by 1; ALTER SEQUENCE ORGANIZATION_INTERFACE_SEQ INCREMENT by 1; ALTER SEQUENCE PEOPLE_INTERFACE_SEQ INCREMENT by 1; ALTER SEQUENCE RECURRENCE_ID_SEQ INCREMENT by 1; ALTER SEQUENCE REMINDER_ID_SEQ INCREMENT by 1; ALTER SEQUENCE SCHEDULE_ID_SEQ INCREMENT by 1; ALTER SEQUENCE SEQ_ADMIN_ANALYZE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_AGENT_STARTUP_ID INCREMENT by 1; ALTER SEQUENCE SEQ_ALT_GUI_ID INCREMENT by 1; ALTER SEQUENCE SEQ_APP_SERVICE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_AUDIT_ID INCREMENT by 1; ALTER SEQUENCE SEQ_BOOKMARK_ID INCREMENT by 1; ALTER SEQUENCE SEQ_BO_NAME INCREMENT by 1; ALTER SEQUENCE SEQ_CAD_DOOR_ID INCREMENT by 1; ALTER SEQUENCE SEQ_CAD_LABELSTYLE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_CALENDAR_SET_COLUMN INCREMENT by 1; ALTER SEQUENCE SEQ_CALENDAR_SET_ID INCREMENT by 1; ALTER SEQUENCE SEQ_CM_SIGNATURE INCREMENT by 1; ALTER SEQUENCE SEQ_DM_UPLOAD_TOKEN INCREMENT by 1; ALTER SEQUENCE SEQ_FACTSOID INCREMENT by 1; ALTER SEQUENCE SEQ_FIELD_ID INCREMENT by 1; ALTER SEQUENCE SEQ_FORMULA_ID INCREMENT by 1; ALTER SEQUENCE SEQ_GUI_ID INCREMENT by 1; ALTER SEQUENCE SEQ_HEARTBEAT_EVENT_ID INCREMENT by 1; ALTER SEQUENCE SEQ_IBS_SPEC INCREMENT by 1; ALTER SEQUENCE SEQ_IBS_SPEC_TYPE INCREMENT by 1; ALTER SEQUENCE SEQ_LABEL_ID INCREMENT by 1; ALTER SEQUENCE SEQ_LIST_ID INCREMENT by 1; ALTER SEQUENCE SEQ_LIST_VALUE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_MANAGER_ID INCREMENT by 1; ALTER SEQUENCE SEQ_MAP_ID INCREMENT by 1; ALTER SEQUENCE SEQ_NAV_ITEM_ID INCREMENT by 1; ALTER SEQUENCE SEQ_OBJECT_TYPE_MAP_ID INCREMENT by 1; ALTER SEQUENCE SEQ_OM_PKG_ID INCREMENT by 1; ALTER SEQUENCE SEQ_PORTAL_SECTION_ID INCREMENT by 1; ALTER SEQUENCE SEQ_REM_ID INCREMENT by 1; ALTER SEQUENCE SEQ_REP_TEMPLATE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_STATE_TRANSITION_FAMILY_ID INCREMENT by 1; ALTER SEQUENCE SEQ_TRI_SYS_OBJECT_ID INCREMENT by 1; ALTER SEQUENCE SEQ_UI_TARGET_ID INCREMENT by 1; ALTER SEQUENCE SEQ_VIS_QUERY_TYPE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_WEB_MESSAGE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_WF_BUILDER_STEP_ID INCREMENT by 1; ALTER SEQUENCE SEQ_WF_TEMPLATE_ID INCREMENT by 1; ALTER SEQUENCE SEQ_WORD_ID INCREMENT by 1; ALTER SEQUENCE TASK_ID_SEQ INCREMENT by 1; ALTER SEQUENCE UOM_TYPE_CODE_SEQUENCE INCREMENT by 1; ALTER SEQUENCE WF_EVENT_FUTURE_ID_SEQ INCREMENT by 1; ALTER SEQUENCE WF_EVENT_ID_SEQ INCREMENT by 1;