Skip to main content

Some Oracle Questions and Answers

Where are datafiles?
SELECT FILE_NAME, TABLESPACE_NAME, BYTES FROM DBA_DATA_FILES;

Is database using pfile or spfile?

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "File Type" FROM V_$PARAMETER WHERE NAME = 'spfile';

Is database in flashback mode or not?
SELECT FLASHBACK_ON FROM V$DATABASE;

Is database in archivelog mode or not?
SELECT LOG_MODE FROM V$DATABASE;

How can i flashback a table to 30 minutes earlier state?
ALTER TABLE <TABLE NAME> ENABLE ROW MOVEMENT;
FLASHBACK TABLE <TABLE NAME> TO TIMESTAMP (SYSTIMESTAMP – INTERVAL '30' minute);
ALTER TABLE <TABLE NAME> DISABLE ROW MOVEMENT;

How can i lock or unlock a user?
ALTER USER <USER NAME> ACCOUNT LOCK;
ALTER USER <USER NAME> ACCOUNT UNLOCK;

Where is control files?
SELECT VALUE FROM V$PARAMETER WHERE NAME LIKE 'control_files%';

How can i rebuild an index?
ALTER INDEX <INDEX NAME> REBUILD;

How can i shrink a table?
ALTER TABLE <TABLE NAME> SHRINK SPACE COMPACT;
ALTER TABLE <TABLE NAME> SHRINK SPACE;

Comments

Popular posts from this blog