How to Manage Schema Objects in Oracle

1. How to manage Schema Objects Schema Objects: A collection of databaseobjects is called schema. A schema is belonging to a user of the database and both share the same name. Logical structures created by the users represents Schema Objects. Data held is by some objects like table and indices. Other objects, such as views or synonyms, consist of a meaning only. Join the Oracle dba certification course right now. Schema Object Management Either in your own schema or some other schema you can either create, modify and delete schema objects as a database administrator. For various reasons of this conversation, a data base administrator means any professional who has the command over the DBA part. SYS and SYSTEM users are present there automatically. Oracle suggests allowing the DBA part only to those customers who require management type accessibility. You can allow other customers to handle schema objects without actually allowing them with the DBA rights. So let us see the some of the subtopics of Schema Objects: The SQL ALTER TABLE statement has the usage to add, modify, or drop/delete content in a table. The SQL ALTER TABLE declaration is also used to relabel a table. Add column in table Syntax For adding a column in a table, the ALTER TABLE format in SQL is: ALTER TABLE table_name ADD column_name column-definition; Example Here is an SQL ALTER TABLE example for the above syntax. For example: ALTER TABLE empdet ADD ENAME char(50); This SQL ALTER TABLE example will include a column known as ENAME to the EMPLOYEE table. -----------------------------------------------------------------------------------------------

2. Add several Columns in table Syntax For adding multiple columns to the current table, the SQL ALTER TABLE syntax is: ALTER TABLE table_name ADD (column_1 column-definition, column_2 column-definition, ... column_n column_definition); Example Here is an SQL ALTER TABLE example for the above syntax. For example: ALTER TABLE empdet ADD (ENAME char(50), EADD char(45)); This will include two columns, ENAME as a char(50) and EADD as a char(45) to the Employee table. ------------------------------------------------------------------------------------------------ Modify column in table Syntax For modifying a column in a current table, the SQL ALTER TABLE format is: For Oracle, MySQL, MariaDB: ALTER TABLE table_name MODIFY column_name column_type; For SQL Server: ALTER TABLE table_name ALTER COLUMN column_name column_type; For PostgreSQL: ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition;

3. Example here is an example for the above syntax and please note that different database have different format. For Oracle: ALTER TABLE empdet MODIFY ENAME char(100) NOT NULL; For MySQL and MariaDB: ALTER TABLE empdet MODIFY ENAME VARCHAR(100) NOT NULL; For SQL Server: ALTER TABLE empdet ALTER COLUMN ENAME VARCHAR(100) NOT NULL; For PostgreSQL: ALTER TABLE empdet ALTER COLUMN ENAME TYPE CHAR(100), ALTER COLUMN ENAME SET NOT NULL; ------------------------------------------------------------------------------------------------ Modify several column in table Syntax For modifying multiple columns in the current table, the SQL ALTER TABLE format is: For Oracle: ALTER TABLE table_name MODIFY (column_1 column_type, column_2 column_type, ... column_n column_type); For MySQL and MariaDB: ALTER TABLE table_name MODIFY column_1 column_definition [ FIRST | AFTER column_name ], MODIFY column_2 column_definition [ FIRST | AFTER column_name ],

4. ... ; For PostgreSQL: ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition, ALTER COLUMN column_name TYPE column_definition, ... ; Example Here is an example for modifying multiple columns and we are targeting ENAME and EADD For Oracle: ALTER TABLE empdet MODIFY (ENAME char(100) NOT NULL, EADD char(75)); For MySQL and MariaDB: ALTER TABLE empdet MODIFY ENAME VARCHAR(100) NOT NULL, MODIFY EADD VARCHAR(75); For PostgreSQL: ALTER TABLE empdet ALTER COLUMN ENAME TYPE CHAR(100), ALTER COLUMN ENAME SET NOT NULL, ALTER COLUMN EADD TYPE CHAR(75); ------------------------------------------------------------------------------------------------ Drop column in table Syntax For dropping a column in a current table, the SQL ALTER TABLE syntax is: ALTER TABLE table_name DROP COLUMN column_name; Example Here is an example for the above syntax drop (ie: deletes) a column from a table. For example: ALTER TABLE empdet

5. DROP COLUMN ENAME; This SQL ALTER TABLE example will DROP the column ENAME from the table empdet. ----------------------------------------------------------------------------------------------- Rename column in table Syntax To relabel a column in a current table, the SQL ALTER TABLE format is: For Oracle and PostgreSQL: ALTER TABLE table_name RENAME COLUMN old_name TO new_name; For SQL Server (using the saved process known as sp_rename): sp_rename 'table_name.old_column', 'new_name', 'COLUMN'; For MySQL and MariaDB: ALTER TABLE table_name CHANGE COLUMN old_name TO new_name; Example Here is an example For Oracle (9i Rel2 and up) and PostgreSQL: ALTER TABLE empdate RENAME COLUMN ENAME TO Name; For SQL Server (using the saved process known as sp_rename): sp_rename 'empdate.ENAME', 'Name', 'COLUMN'; For MySQL and MariaDB: ALTER TABLE empdate CHANGE COLUMN ENAME Name VARCHAR(100); In MySQL and MariaDB, you must specify the data type of the column when you relabel it.

6. Rename table Syntax To relabel a table, the SQL ALTER TABLE syntax is: For Oracle, MySQL, MariaDB, PostgreSQL and SQLite: ALTER TABLE table_name RENAME TO new_table_name; For SQL Server (using the saved process known as sp_rename): sp_rename 'table_name', 'new_table_name'; Example Let's look at an example that renames a table known as empdet to the new EMP. For Oracle, MySQL, MariaDB, PostgreSQL and SQLite: ALTER TABLE empdate RENAME TO EMP; For SQL Server (using the saved process known as sp_rename): sp_rename 'empdate', 'EMP'; ------------------------------------------------------------------------------------------------ Drop Table The SQL DROP TABLE declaration allows you to eliminate or remove a table from the SQL data base. Syntax The format for the DROP TABLE declaration in SQL is: DROP TABLE table_name; Parameters or Arguments table_name The name available to eliminate from the data base.

7. Example If you find that the table is not required for you, then you can can use this syntax. Let's look at an example that reveals the DROP TABLE declaration. For example: DROP TABLE empdate; this will remove the table and its data completely and if you want to regenerate the table again then you can do it without any disturbance or error from the deleted table. Let's look at one more example where we prefix the table name with the data source name. For example: DROP TABLE totn.contacts; In this example, we have dropped a table called contacts from the database. ------------------------------------------------------------------------------------------------ Marking Columns Unused If you are worried about how long it could take to go drop column data from all of the rows in a huge table, you can use the ALTER TABLE...SET UNUSED declaration. This declaration represents one or more columns as unused, but does not actually take away the focus column data or recover the disk space filled by these columns. To indicate the ENAME and EADD columns as unused, perform the following statement: ALTER TABLE empdate SET UNUSED (ENAME, EADD); You can later eliminate columns that are noticeable as unused by providing an ALTER TABLE...DROP UNUSED COLUMNS declaration. Rarely used columns are also eliminated from the focus on Table whenever a precise drop of any particular column or columns on the Table is shared.

8. Removing Rarely used Columns The ALTER TABLE...DROP UNUSED COLUMNS declaration is the only activity permitted on unused columns. It actually eliminates unused columns from the table and reclaims hard drive space. ALTER TABLE empdate DROP UNUSED COLUMNS CHECKPOINT 250; Dropping Content in Compacted Tables If you allow compact for all operations on a table, you can drop table columns. If you allow compression for direct-path inserts only, you cannot drop columns. ------------------------------------------------------------------------------------------------ Enable/Disable Constraint We can also use "alter table" format to allow or turn off constraints: alter table table_name ENABLE constraint constraint_name; alter table table_name DISABLE constraint constraint_name; alter table table_name add constraint check_constraint_name CHECK (check_column_name IN ( 'check_constraint1_value', 'check_constraint2_value', 'check_constraint3_value', 'check_constraint4_value' ) ) DISABLE|ENABLE;

9. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ DROP DELETING CONSTRAINTS Here is another example of Oracle "alter table" syntax to drop constraints. ALTER TABLE cust_table drop constraint fk_cust_table_ref; Create index using create table An index can be produced in a table to discover information more easily. Customers only want to search and they are not interested in seeing with the indices. SQL CREATE INDEX Syntax Creates an index on a table. Value repetition is allowed: CREATE INDEX index_name ON table_name (column_name) SQL CREATE UNIQUE INDEX Syntax Creates an original index on a table. Value repetitions are not allowed: CREATE UNIQUE INDEX index_name ON table_name (column_name) CREATE INDEX Example The SQL declaration below makes an index known as "P_Index" on the "ENAME" column in the "empdet" table: CREATE INDEX P_Index ON empdet (ENAME) If you want to make an index on a mixture of columns, you can record the column titles within the parentheses, divided by commas: CREATE INDEX P_Index ON empdet (ENAME, EADD)

10. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The DROP INDEX Statement The DROP INDEX declaration is used to remove a index in a table DROP INDEX Format for MS Access: DROP INDEX index_name ON table_name DROP INDEX Format for MS SQL Server: DROP INDEX table_name.index_name DROP INDEX Format for DB2/Oracle: DROP INDEX index_name DROP INDEX Format for MySQL: ALTER TABLE table_name DROP INDEX index_name The DROP TABLE Statement The DROP TABLE declaration is used to remove a table DROP TABLE table_name The DROP DATABASE Statement The DROP DATABASE declaration is used to remove a database. DROP DATABASE database_name The TRUNCATE TABLE Statement What if we only want to remove the information within the table, and not the table itself? Then, use the TRUNCATE TABLE statement: TRUNCATE TABLE table_name

11. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Purge Table Use the PURGE declaration to get rid of a table or index from your trash and restore all of the place associated with the object, or to take away the entire trash, or to get rid of part of all of a dropped tablespace from the trash. trash(recyclebin) To see the material of your trash, query the USER_RECYCLEBIN data dictionary. You can use the RECYCLEBIN replacements instead. The following two declarations return the same rows: SELECT * FROM RECYCLEBIN; SELECT * FROM USER_RECYCLEBIN; RECYCLEBIN Use this word to get rid of the current user's trash. Oracle Data source will eliminate all things from the user's trash and restore all space associated with objects in the trash. DBA_RECYCLEBIN This word does work only if you have SYSDBA program benefit. It allows you eliminate all things from the system-wide trash, and matches getting rid of the trash of every customer. This function is useful, for example, before in back tracking. Finally after all the topics group discussions will be conducted in our oracle institute in Pune.