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;