APPSDBA Data Lake

Can I flush the Undo tablespace by any chance... ? You don't need to flush any thing from the undo tablespace. It will reuse the undo data automatically. You can create a new undo tablespace and make it active for your database. Later on drop your old undo tablespace. UNDO_RETENTION time undo tablespace is filled and any query fails, then if another query is going to do a DML and tries to use UNDO segments then, I think that will also fail because of unavailability of UNDO SEGMENTS.. > create undo tablespace undotbl_new datafile size 1m; Tablespace created. > alter system set undo_tablespace = undotbl_new scope=both; System altered. > drop tablespace undotbs; Tablespace dropped.

 Once connected, ALTER USER command, specifying the new password. ALTER USER myuser IDENTIFIED BY MyNewPassword; To use special characters, remember to enclose the password in double quotes. ALTER USER myuser IDENTIFIED BY "MyNewPassword#"; SQL*Plus and SQLcl Use the PASSWORD command from the SQL*Plus and SQLcl utilities. It will be prompted for your current password and the new password. SQL> password Changing password for MYUSER Old password: ******** New password: ******** Retype new password: ******** Password changed SQL> SQL Developer From SQL Developer Right-click on the connection. Select the "Reset Password..." option from the popup menu. In the subsequent dialog, enter the current password and the new password with confirmation. Click the OK button. TOAD For TOAD do the following. select "Session > Change Password". In the subsequent dialog, enter the current password and the new password with verification. Click the OK button.

  This statement explicitly recompiles the specification and body of the  hr.emp_mgmt  package. ALTER PACKAGE emp_mgmt COMPILE PACKAGE; If the database encounters no compilation errors while recompiling the  emp_mgmt  specification and body, then  emp_mgmt  becomes valid. The user  hr  can subsequently invoke or reference all package objects declared in the specification of  emp_mgmt  without runtime recompilation. If recompiling  emp_mgmt  results in compilation errors, then the database returns an error and  emp_mgmt  remains invalid. The database also invalidates all objects that depend upon  emp_mgmt . If you subsequently reference one of these objects without explicitly recompiling it first, then the database recompiles it implicitly at run time. To recompile the body of the  emp_mgmt  package in the schema  hr , issue this statement: ALTER PACKAGE hr.emp_mgmt COMPILE BODY; This statement...

  Oracle DROP INDEX statement DROP INDEX [schema_name.]index_name; DECLARE index_count INTEGER; BEGIN SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'index_name' ; IF index_count > 0 THEN EXECUTE IMMEDIATE 'DROP INDEX index_name' ; END IF; END; / In case want to check whether index exists and then delete it. If try to drop a non-existing, you will get the following error: SQL Error : ORA -01418 : specified index does not exist In SQLplus, set these before running the commands set long 100000 set longchunksize 100000 select DBMS_METADATA.GET_DDL( 'INDEX' , 'index_name') from all_indexes where owner in ( USER , 'USER_OTHER_THAN_LOGGED_IN_USER' ); Use below script to fetch metadata other information. SELECT DBMS_METADATA,GET_GRANTED_DDL('SYSTEM_GRANT','SSO******') from dual;