admin
How to Create a Sequence in Oracle
Here is the syntax to create a sequence. This can be used to create primary keys or to generate a number sequence. The below sequence will start with 1 and increment by 1. There is no MAXVALUE defined so the sequence’s max value will default to 999999999999999999999999999. The CACHE is set to 50 so it [...]
How to Create a Function in Oracle with Examples
The following function will add a new column called SOME_NEW_COLUMN with a NUMBER datatype to all tables in the current schema. This function does not take any parameters. CREATE OR REPLACE FUNCTION ADD_COLUMN_FUNCTION RETURN INTEGER IS temp_String varchar2(200); temp_Table_Name varchar2(30); CURSOR cursor_User_Tables IS SELECT TABLE_NAME FROM USER_TABLES; BEGIN FOR rec IN cursor_User_Tables LOOP temp_Table_Name := [...]
How to Create a Stored Procedure in Oracle with Examples
The following stored procedure will add a new column called SOME_NEW_COLUMN with a NUMBER datatype to all tables in the current schema. This stored procedure does not take any parameters. CREATE OR REPLACE PROCEDURE ADD_COLUMN_STORED_PROCEDURE IS temp_String varchar2(200); temp_Table_Name varchar2(30); CURSOR cursor_User_Tables IS SELECT TABLE_NAME FROM USER_TABLES; BEGIN FOR rec IN cursor_User_Tables LOOP temp_Table_Name := [...]
Issues with Sequence Generators when Upgrading from Informatica PowerCenter 8.1 to 8.6?
Here is a simple way to upgrade all sequence generator datatypes so that version 8.1 Informatica mappings will work with version 8.6. UPDATE OPB_WIDGET_FIELD SET wgt_prec = 19, wgt_datatype = -5 WHERE field_name IN ( ‘CURRVAL’,’NEXTVAL’) AND wgt_datatype = 4;
How to add a Column to a Table in Oracle
Here is the statement to add a column to a table. ALTER TABLE SAMPLE_TABLE ADD ANOTHER_DATE DATE; Here is the statement to add multiple columns to a table. ALTER TABLE SAMPLE_TABLE ADD ( ANOTHER_STRING VARCHAR2(100 CHAR), ANOTHER_NUMBER NUMBER(32,12) );
How to Analyze a Table or Index in Oracle Using the Analyze Statement
Here are a few examples of how to analyze a table and index. ANALYZE TABLE SAMPLE_TABLE COMPUTE STATISTICS; –completely computes statistics ANALYZE INDEX SAMPLE_TABLE_PK COMPUTE STATISTICS; –completely computes statistics ANALYZE TABLE SAMPLE_TABLE ESTIMATE STATISTICS SAMPLE 100 ROWS; –estimates statistics based on the set number of rows ANALYZE TABLE SAMPLE_TABLE ESTIMATE STATISTICS SAMPLE 15 PERCENT; –estimates statistics [...]
How to Truncate a Table in Oracle
Here is an example of how to truncate a table in Oracle. To do a truncate, you must be connected to the schema the table is in. TRUNCATE TABLE SAMPLE_TABLE;
How to Delete Data from a Table in Oracle
Here is an example of how to delete data from a table. To do a delete, you must do a commit for the delete to be permanent. You can do a delete in any schema that has access to the necessary table. DELETE FROM SAMPLE_TABLE; COMMIT; Here is an example of how to delete data from a [...]
How to Add a Datafile in Oracle
Here is an example of how to add a datafile in Oracle. The datafile size is 2 GB. You will need to replace the ‘/u01/oracle/’ path with a valid directory. ALTER TABLESPACE ”SAMPLEDATAFILE” ADD DATAFILE ‘/u01/oracle/some_folder.dbf’ size 2048M;
How to Create a Copy of a Table in Oracle
Here is how to create a backup of an existing table (with data). CREATE TABLE SAMPLE_TABLE_COPY AS SELECT * FROM SAMPLE_TABLE; Here is how to create a backup of an existing table (without data). CREATE TABLE SAMPLE_TABLE_COPY AS SELECT * FROM SAMPLE_TABLE WHERE ROWNUM < 1;