Generated DDL from tools may not be executable sometimes. I got ORA-02494 when I execute DDL scripts that were generated from other database by TOAD,… Read More »How to Resolve ORA-02494: invalid or missing maximum file size in MAXSIZE clause
We can compose the execution statement for getting the definition of all tablespaces by the following.SQL> select 'exec dbms_output.put_line(dbms_metadata.get_ddl(''TABLESPACE'',''' || tablespace_name || '''));' ddl from… Read More »How to Get Tablespace Definition (DDL)
According to SQL syntax of Oracle database, you can't resize the data files by using ALTER TABLESPACE except the tablespace was created as BIGFILE, but… Read More »You Cannot Resize a DataFile in "ALTER TABLESPACE" Except ...
Without declaring SCOPEIf you do not specify this clause, then:If a server parameter file was used to start up the database, then BOTH is the… Read More »The Default Values in "ALTER SYSTEM SET" Statement
To prevent owner's tables to be changed by other users is easily controlled through object privilege practices, but how to prevent the owner to change… Read More »How to Make Your Tables Read Only - 5 Ways to Achieve the Goal
External procedures are very special procedures stored outside of the database, and it can be coded by C or Java language. Since the external libraries… Read More »External Procedures May Consume a Lot of Memory
After the blocking sessions are identified, DBA should notify the session owners and leave them a chance to exit sessions gracefully by themselves. If things… Read More »How to Compose Kill Blocking Sessions Statements at a Time
You can order a specific column in a query by adding ORDER BY clause, but the result always returns and lists all the rows that… Read More »How to Limit Number of Rows Returned in a Query