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;
How to Create a Primary Key in Oracle
Here is an example of how to create a primary key in Oracle. The primary key (sample_table_pk) is for the table sample_table. The two column primary key consists of the columns some_string and some_number. ALTER TABLE SAMPLE_TABLE ADD CONSTRAINT SAMPLE_TABLE_PK PRIMARY KEY (SOME_STRING, SOME_NUMBER);
How to Create a Unique Index in Oracle
Here is an example of how to create a unique index in Oracle. The unique index (sample_table_uix) is for the table sample_table. The two columns being indexed are some_string and some_number. CREATE UNIQUE INDEX SAMPLE_TABLE_UIX ON SAMPLE_TABLE ( SOME_STRING ASC, SOME_NUMBER ASC );
How to Create a Non-Unique Index in Oracle
Here is an example of how to create a non-unique index in Oracle. The index (sample_table_ix) is for the table sample_table. The two columns being indexed are some_string and some_date. CREATE INDEX SAMPLE_TABLE_IX ON SAMPLE_TABLE ( SOME_STRING ASC, SOME_DATE ASC );
How to Create a View in Oracle
Here are a couple examples of how to create a view in Oracle. The view has 3 columns (some_string_name, some_number_name, some_date_name) that are sourced from a table called sample_table. Example View 1: CREATE OR REPLACE VIEW SAMPLE_VIEW( SOME_STRING_NAME, SOME_NUMBER_NAME, SOME_DATE_NAME) AS SELECT SOME_STRING, SOME_NUMBER, SOME_DATE FROM SAMPLE_TABLE; Example View 2: CREATE OR REPLACE VIEW SAMPLE_VIEW AS [...]