You can execute the following statements in the given order in order to shrink the data taken by your tables:alter table APPS.XXACCOUNT_BALANCES enable row movement;alter table APPS.XXACCOUNT_BALANCES shrink space COMPACT; …
Gather schema statsistics:exec dbms_stats.gather_schema_stats( - ownname => 'SCOTT', - options => 'GATHER AUTO' );Show schema stats:select * from DBA_IND_STATISTICS WHERE OWNER='SCOTT'select * from DBA_TAB_STATISTICS WHERE OWNER='SCOTT'select * from DBA_TAB_COL_STATISTICS WHERE…
If you have the following message:ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'Then the undo tablespace is possibly corrupted.Try the following:create undo tablespace UNDOTBS2 datafile 'c:\app\vag\oradata\ora11g\UNDOTBS02.DBF' size 100000;alter…
In the hierarchical queries, we can depict the parent-child relationship, which exists in a table.In the following example we have a table with hierarchy of regions.We will try to find…
Use the following statement:sqlplus <username>/<password>@<host>:<instance>If you want to directly without username/password, you must belong to usergroup ora_dba (windows):sqlplus / as sysdba
Use the following statements as an example:Create table test_items (item_id number, item_desc varchar2(20), item_attrib varchar2(100));Alter table test_items add constraint no_double_codes unique(item_desc, item_attrib);
Here is how to track a transaction's progress:select t.used_urec, --> if increasing, then the transaction is moving forward, otherwise is rolling back t.start_time, sysdate - to_date(t.start_time,'dd/mm/yy hh24:mi:ss') date_diff, s.sid,s.serial#, s.username,…
Use the following code in order to search in a LONG field:SELECT * FROM sys.trigger$ /* table, not view */ WHERE sys.dbms_metadata_util.long2varchar( 4000 ,'SYS.TRIGGER$','ACTION#', ROWID) LIKE '%rvtrig_upd%';Note, that for the…
Use the following code to create a simple user: CREATE USER nista IDENTIFIED BY kookoo;GRANT "CONNECT" TO nista;--optional:ALTER USER nista DEFAULT TABLESPACE "USERS" QUOTA UNLIMITED ON "USERS";GRANT "SSE_ROLE" TO nista;--for…
With the following command you can get the OS version: SELECT DBMS_UTILITY.PORT_STRING FROM DUAL; e.g. IA64/HPUX 64.0.9.0.1